The R language provides many features in the language for selecting data from data frames: the "[" operator, logical functions, and utility functions like "subset". But if you know SQL (the query language ubiquitous in database systems), none of this is necessary. With the sqldf package, you can just pretend that your data frame is a database, and use SQL directly.
The sqldf function supports the full richness of the SQL language, but applied to data frames in R's memory. This includes:
- SELECT ... WHERE statements to select rows and columns according to logical criteria
- CASE clauses, for queries with special cases
- ORDER BY statements, to sort the resulting data according to specified columns
- LEFT JOIN and INNER JOIN statements for merging data frames
The sqldf package uses its own internal database engine, so there's no special database configuration you need to do. Just enter the following in R:
install.packages("sqldf") library(sqldf)
and you should be good to go. The SQLDF FAQ is a good resource for getting started, and this sqldf video tutorial from Keystone Solutions shows the sqldf package in action with examples of SQL queries from simple to complex.
Google code: sqldf: SQL select on R data frames
This is great! I have worked with Excel, R, SPSS ... but when it came down to writing custom reports requiring heavyweight data lifting and manipulations, I always ended up relying on SQL. Integrating it with R simply makes it easier for analysis. Is this SQL reliant on ANSI standards?
Posted by: Prasad Ajinkya | December 17, 2012 at 23:51
You're much better off doing the relational manipulation of data in the database engine. Just compare the performance of merge in R with join in any decent relational database. A shoemaker had best stick to his last. We have no need for a half-baked SQL engine.
Posted by: Robert Young | December 18, 2012 at 07:00
Forget SQL in R and use the data.table package. This is the most elegant and computationally efficient way to work in R (...and more...) with "tables":
http://files.meetup.com/1406240/Data%20munging%20with%20SQL%20and%2...
Posted by: michele de meo | December 19, 2012 at 02:10
It´s a new and good way to get most of the troubles away. Thank you very much for sharing it.
I took part in a SQL Workshop, where they highlighted all the important points due to SQL and I must admit that I made good experiences with it.
So I would recommend it to those people who wants to take a closer look at it.
Posted by: SQL Workshop | February 26, 2013 at 01:23