These are convenience functions to handle interaction with SQLite or mySQL/mariaDB. They open one and only one database, and handle most of the interaction therewith for you.
You will probably first use apop_text_to_db to pull data into the database, then apop_query to clean the data in the database, and finally apop_query_to_data to pull some subset of the data out for analysis.
In all cases, your query may be in printf form. For example:
char tabname[] = "demographics";
char colname[] = "heights";
int min_height = 175;
apop_query("select %s from %s where %s > %i", colname, tabname, colname, min_height);
See the Database moments (plus pow()!) section below for not-SQL-standard math functions that you can use when sending queries from Apophenia, such as pow, stddev, or sqrt.
apop_text_to_db : Read a text file on disk into the database. Data analysis projects often start with a call to this.
apop_data_print : If you include the argument .output_type='d', this prints your apop_data set to the database.
apop_query : Manipulate the database, return nothing (e.g., insert rows or create table).
apop_db_open : Optional, for when you want to use a database on disk.
apop_table_exists : Check to make sure you aren't reinventing or destroying data. Also, a clean way to drop a table.
Apophenia reserves the right to insert temp tables into the opened database. They will all have names beginning with apop_, so the reader is advised to not generate tables with such names, and is free to ignore or delete any such tables that turn up.
If you need to deal with two databases, use SQL's attach database. By default with SQLite, Apophenia opens an in-memory database handle. It is a sensible workflow to use the faster in-memory database as the primary database, and then attach an on-disk database to read in data and write final output tables.
Extracting data from the database
apop_db_to_crosstab : take up to three columns in the database (row, column, value) and produce a table of values.
A few functions have proven to be useful enough to be worth breaking out into their own programs, for use in scripts or other data analysis from the command line:
The apop_text_to_db command line utility is a wrapper for the apop_text_to_db command.
The apop_db_to_crosstab function is a wrapper for the apop_db_to_crosstab function.
Database moments (plus pow()!)
SQLite lets users define new functions for use in queries, and Apophenia uses this facility to define a few common functions.
select ran() from table will produce a new random number between zero and one for every row of the input table, using gsl_rng_uniform.
The SQL standard includes the count(x) and avg(x) aggregators, but statisticians are usually interested in higher moments as well—at least the variance. Therefore, SQL queries using the Apophenia library may include any of these moments:
var and variance; kurt and kurtosis do the same thing; choose the one that sounds better to you. Kurtosis is the fourth central moment by itself, not adjusted by subtracting three or dividing by variance squared. var, var_samp, stddev and stddev_samp give sample variance/standard deviation; variance, var_pop, std and stddev_pop give population standard deviation. The plethora of variants are for mySQL compatibility.
The var/skew/kurtosis functions calculate sample moments. If you want the second population moment, multiply the variance by ; for the third population moment, multiply the skew by . The equation for the unbiased sample kurtosis as calculated in Appendix M of Modeling with Data is not quite as easy to adjust.
Also provided: wrapper functions for standard math library functions—sqrt(x), pow(x,y), exp(x), log(x), and trig functions. They call the standard math library function of the same name to calculate , , , , , , et cetera. For example: