6.330
Querying Data Frames in R with sqldf

Introduction

SQL is a universal query language. Its use has expanded to any tabular data and not just relational databases. sqldf is a package that allows data frames to be queried with SQL as if they were tables in a database. It allows a programmer or data analyst to use SQL to access, process, search, and aggregate data in data frames.

Many queries, while generally doable in Base R or with tidyverse, are often simpler with a SQL query – albeit a bit slower, but that reduction in performance is often not perceptible. The sqldf package actually loads the data frame into an in-memory SQLite database.

sqldf is primarily used to:

• summarize of data in data frames
• harmonize data access via SQL for all tabular data
• import data from CSV files
• process parts of a CSV
• load data into databases
• learn SQL

Aside from sqldf, the tidyverse package also contains numerous functions for processing data frames in a SQL-like manner.

sqldf should only be used for queries (SQL SELECT) and not for updates (SQL INSERT, UPDATE, DELETE) as that actually modifies a copy of the data frame.

Simple Example

In memory data frames can be treated like tables in a relational database. You can simply use the name of a data frame in a sqldf SQL query. Note that each sqldf query returns a data frame – just like any SQL query returns a table.

library(sqldf)

rs <- sqldf("select Species, count(*) as num 
               from iris 
              group by Species")
head(rs, 3)

##      Species num
## 1     setosa  50
## 2 versicolor  50
## 3  virginica  50

The resulting data frame can be further processed in sqldf or with R.

mean(rs$num)

## [1] 50

Parameters in sqldf Queries

You can use R variables as parameters for sqldf queries but you need to build a single string using paste0().

Note that in the example below, the Sepal.Length column contains a ‘dot’, so it must be enclosed in backticks. If not, then you will get a “Column not found” error.

n <- 5
rs <- sqldf(paste0("select count(Species) as 'NumSpecies' 
                      from iris 
                     where `Sepal.Length` < ", n))

print(rs$NumSpecies)

## [1] 22

Note that constructing queries by pasting values into a single string is generally bad practice and can lead to SQL Injection Attacks and therefore should be avoided. However, for data query tasks where the parameters are not user provided, it is acceptable to do.

Querying CSV Data

We will demonstrate the use of sqldf by querying the data in a large CSV file. The file is first loaded into a data frame and then treated as if it were a table in a database even though the data resides fully in memory. Of course, this is not scalable to arbitrarily large data files due to memory constraints.

txns <- read.csv("customertxndata.csv", 
               header = F, 
               col.names = c("visits","numtxns",
                             "os","gender",
                             "total"))

The CSV file contains data on visits to an e-commerce site.

head(txns, 4)

##      visits   numtxns      os gender            total
## 1 NumVisits NumOrders      OS Gender            Total
## 2         7         0 Android   Male                0
## 3        20         1     iOS   <NA> 576.866774966349
## 4        22         1     iOS Female              850

sqldf executes a SQL query against an in-memory data frame and returns the query result as a data frame. The data frame can be captured and then processed further with sqldf or R.

library(sqldf)

sqlq <- "select gender, sum(total) as total 
           from `txns` 
          group by gender"

df <- sqldf(sqlq)

head(df, 3)

##   gender            total
## 1   <NA> 2522332.10607994
## 2 Female          2790500
## 3 Gender                0

# imagine you need to ask user for OS, 
# perhaps by choosing from a menu
theOS <- "Android"

sqlq <- paste0("select gender, sum(total) as total 
                  from `txns` where os='",theOS, "' 
                 group by gender")
df <- sqldf(sqlq)

head(df, 4)

##   gender            total
## 1   <NA> 986845.829137997
## 2 Female           308425
## 3   Male 2804647.56080426

Joins in sqldf

sqldf is able to combine the data in multiple (related) data frames or CSV files through a SQL join. All of the usual join mechanisms of SQL are available: inner join, outer join, natural join, and theta join.

df.txn <- read.csv("pharmaSalesTxn.csv")
df.rep <- read.csv("pharmaReps.csv")

head(df.txn,3)

##   txnID      date                 cust            prod  qty amount country repID
## 1  1001 9/18/2020 Proin Dolor Institut      Xinoprozen  800   2256 Germany   221
## 2  1002 8/10/2020 Proin Dolor Institut Gerantrazeophem 1200   2244 Germany   221
## 3  1003 3/14/2020           Varius Plc     Colophrazen  600    738  Brazil   887

head(df.rep,3)

##   repID   repFN    repLN         repTR
## 1   100  Helmut   Schwab          EMEA
## 2   887 Walison da Silva South America
## 3   332 Lynette   McRowe          East

rs <- sqldf("SELECT r.repLN AS LastName, 
                    printf('$%,d', sum(t.amount))  AS 'Total Sold'
               FROM `df.rep` AS r JOIN `df.txn` AS t 
                                    ON (r.repID = t.repID)
              GROUP BY t.repID")

head(rs)

##     LastName Total Sold
## 1     Schwab   $387,073
## 2 Kappoorthy   $609,647
## 3       Sixt   $281,456
## 4     McRowe   $479,868
## 5   da Silva   $993,434

Applying SQL Directly to a CSV

sqldf contains the function read.csv.sql() which reads a file into R after applying a SQL statement. Only those rows that meet the SQL query criteria are processed by R. This can be helpful when processing large CSV files. The implied table name is file.

sqlQuery <- "SELECT Gender, sum(Total) AS 'SumTotal' 
               FROM file 
              WHERE Gender <> 'NA'
              GROUP BY Gender"

rs <- read.csv.sql("customertxndata.csv", 
                   sql = sqlQuery,
                   header = T)

head(rs,3)

##     Gender SumTotal
## 1 "Female"  2790500
## 2   "Male"  5059692

Note that the CSV file is presumed to have a header row. The header column names are used as column names for the virtual table. If no header row is in the CSV, then the parameter header = F must be specified and columns names are assigned as V1, V2, and so forth.

The function attempts to guess the data types from the values but that can be changed via the colClasses parameter. Column names and types (using SQL data types) can be done via field.types. This can significantly improve read performance as R does not have to derive data types via inspection of values.

Note that quotes are considered part of a field. So, if the CSV file contains “Female”, then the value is read into R and used by SQL as “Female” and not simply the value Female.

sqldf vs R Functions

R functions for querying dataframes, such as which(), are generally preferable as they do not require the dataframe to be copied to a temporary database in order to apply a SQL statement. Use sqldf for grouping or aggregation that are difficult to do in R. By the waym tidyverse offers a set of functions for grouping and other selection that are similar to what can be done with SQL in sqldf. So, as is often the case, there’s many ways to do accomplish the same goal in R.

Potential Issues

A potential issue with sqldf can occur when you connect to MySQL or a non-SQLite database as sqldf attempts to use your existing database connection as a backing store for its data; this will often not work due to security constraints. So, you need to add the R code options(sqldf.driver = ‘SQLite’) which forces sqldf to use SQLite as its backing store where it will create an in-memory database.

It is not uncommon for R to freeze or even crash when a SQL query for sqldf is not properly formatted.

Updating Dataframes via sqldf

Don’t do that… sqldf works on a table in the backing store rather than the actual dataframe, so any updates would happen to that temporary table and not the dataframe. So, no INSERT, UPDATE, DELETE, or ALTER TABLE with sqldf. Use standard R functions for that.

Worked Example

library(sqldf)

url <- "https://s3.us-east-2.amazonaws.com/artificium.us/datasets/customers.csv"

df <- read.csv(url, header = T, stringsAsFactors = F)

sql <- paste0 (
  "SELECT country, count(*) as `num` ",
  "  FROM `df` ",
  " GROUP BY country ",
  " ORDER BY `num` DESC ",
  " LIMIT 1;"
)

sqldf(sql)

##   country num
## 1 Germany 457

or, alternatively,

library(sqldf)

url <- "https://s3.us-east-2.amazonaws.com/artificium.us/datasets/customers.csv"

df <- read.csv.sql(url, header = T, stringsAsFactors = F)

sql <- paste0 (
  "SELECT country, count(*) as `num` ",
  "  FROM `df` ",
  " GROUP BY country ",
  " ORDER BY `num` DESC ",
  " LIMIT 1;"
)

read.csv.sql(file = url,
             sql = sql)

##   country num
## 1 Germany 457

---

Files & Resources

All Files for Lesson 6.330

---

References

G. Grothendieck (2017). sqldf: Manipulate R Data Frames Using SQL. R package version 0.4-11. https://CRAN.R-project.org/package=sqldf

[The sqldf Package. R Documentation.]](https://www.rdocumentation.org/packages/sqldf/versions/0.4-11)

Make R speak SQL with sqldf

read.csv.sql() Documentation

Errata

None collected yet. Let us know.