{"id":801,"date":"2015-02-26T17:12:07","date_gmt":"2015-02-27T00:12:07","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=801"},"modified":"2015-02-26T17:12:07","modified_gmt":"2015-02-27T00:12:07","slug":"sqldf-sql-on-r-data-frames","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2015\/02\/26\/sqldf-sql-on-r-data-frames\/","title":{"rendered":"sqldf SQL on R data frames"},"content":{"rendered":"

To write it, it took three months; to conceive it \u2013 three minutes; to collect the data in it \u2013 all my life.<\/i> F. Scott Fitzgerald<\/a><\/p>\n

Latest News<\/strong><\/p>\n

(1) sqldf 0.4-10 is on CRAN now. This is a bug fix release to provide further compatibility with the new version of RSQLite. Note that this version requires R (\u2265 3.1.0), gsubfn (\u2265 0.6), RSQLite (\u2265 1.0.0) and DBI (\u2265 0.2-5). If this is a problem for you and you want to use an older version of RSQLite, sqldf, etc. an easy way to revert is to use the checkpoint package:<\/p>\n

library<\/span>(<\/span>checkpoint<\/span>)<\/span>\ncheckpoint<\/span>(<\/span>\"2014-10-08\"<\/span>)<\/span><\/pre>\n
(2) The new RSQLite 1.0.0 changes how it deals with dots in names. They are no longer translated to underscores.<\/p>\n
(3) There is now an sqldf discussion group<\/a> to discuss sqldf (and other of my packages).<\/p>\n
Introduction<\/strong><\/p>\n
sqldf<\/a> is an R package for runing SQL statements<\/a> on R data frames, optimized for convenience. The user simply specifies an SQL statement in R using data frame names in place of table names and a database with appropriate table layouts\/schema is automatically created, the data frames are automatically loaded into the database, the specified SQL statement is performed, the result is read back into R and the database is deleted all automatically behind the scenes making the database’s existence transparent to the user who only specifies the SQL statement. Surprisingly this can at times be<\/a> even<\/a> faster<\/a> than<\/a> the<\/a> corresponding pure R calculation (although the purpose of the project is convenience and not speed). This link<\/a> suggests that for aggregations over highly granular columns that sqldf is faster than another alternative tried. sqldf<\/tt> is free software published under the GNU General Public License that can be downloaded from CRAN<\/a>.<\/p>\n
sqldf supports (1) the SQLite<\/a> backend database (by default), (2) the H2<\/a> java database, (3) the PostgreSQL<\/a> database and (4) sqldf 0.4-0 onwards also supports MySQL<\/a>. SQLite, H2, MySQL and PostgreSQL are free software. SQLite and H2 are embedded serverless zero administration databases that are included right in the R driver packages, RSQLite<\/a> and RH2<\/a>, so that there is no separate installation for either one. A number ofhigh profile projects<\/a> use SQLite. (Also see this lecture<\/a>.) H2 is a java database which contains a large collection of SQL functions and supports Date and other data types. It is the most popular database package among scala packages<\/a>. PostgreSQL is a client\/server database and unlike SQLite and H2 must be separately installed but it has a particularly powerful version of SQL, e.g. its window<\/a> functions<\/a>, so the extra installation work can be worth it. sqldf supports the RPostgreSQL<\/tt> driver in R. Like PostgreSQL, MySQL is a client server database that must be installed independently so its not as easy to install as SQLite or H2 but its very popular and is widely used as the back end for web sites.<\/p>\n
The information below mostly concerns the default SQLite database. The use of H2 with sqldf is discussed in FAQ #10<\/a> which discusses differences between using sqldf with SQLite and H2 and also shows how to modify the code in the Examples<\/a> section to use sqldf\/H2 rather than sqldf\/SQLite. There is some information on using PostgreSQL with sqldf in FAQ #12<\/a> and an example in Example 17. Lag<\/a> . The unit tests provide examples that can work with all five data base drivers (covering four databases) supported by sqldf. They are run by loading whichever database is to be tested (SQLite is the default) and running: demo(\"sqldf-unitTests\")<\/tt><\/p>\n
as discussed here: http:\/\/stackoverflow.com\/questions\/26571232\/sqldf-not-working-after-update<\/a><\/p>\n