Skip to content
DevTut

sqldf

Basic Usage Examples

Section titled “Basic Usage Examples”

sqldf() from the package sqldf allows the use of SQLite queries to select and manipulate data in R. SQL queries are entered as character strings.

To select the first 10 rows of the “diamonds” dataset from the package ggplot2, for example:

data("diamonds")
head(diamonds)
# A tibble: 6 x 10
  carat       cut color clarity depth table price     x     y     z
  <dbl>     <ord> <ord>   <ord> <dbl> <dbl> <int> <dbl> <dbl> <dbl>
1  0.23     Ideal     E     SI2  61.5    55   326  3.95  3.98  2.43
2  0.21   Premium     E     SI1  59.8    61   326  3.89  3.84  2.31
3  0.23      Good     E     VS1  56.9    65   327  4.05  4.07  2.31
4  0.29   Premium     I     VS2  62.4    58   334  4.20  4.23  2.63
5  0.31      Good     J     SI2  63.3    58   335  4.34  4.35  2.75
6  0.24 Very Good     J    VVS2  62.8    57   336  3.94  3.96  2.48
require(sqldf)
sqldf("select * from diamonds limit 10")
   carat       cut color clarity depth table price    x    y    z
1   0.23     Ideal     E     SI2  61.5    55   326 3.95 3.98 2.43
2   0.21   Premium     E     SI1  59.8    61   326 3.89 3.84 2.31
3   0.23      Good     E     VS1  56.9    65   327 4.05 4.07 2.31
4   0.29   Premium     I     VS2  62.4    58   334 4.20 4.23 2.63
5   0.31      Good     J     SI2  63.3    58   335 4.34 4.35 2.75
6   0.24 Very Good     J    VVS2  62.8    57   336 3.94 3.96 2.48
7   0.24 Very Good     I    VVS1  62.3    57   336 3.95 3.98 2.47
8   0.26 Very Good     H     SI1  61.9    55   337 4.07 4.11 2.53
9   0.22      Fair     E     VS2  65.1    61   337 3.87 3.78 2.49
10  0.23 Very Good     H     VS1  59.4    61   338 4.00 4.05 2.39

To select the first 10 rows where for the color “E”:

sqldf("select * from diamonds where color = 'E' limit 10")
   carat       cut color clarity depth table price    x    y    z
1   0.23     Ideal     E     SI2  61.5    55   326 3.95 3.98 2.43
2   0.21   Premium     E     SI1  59.8    61   326 3.89 3.84 2.31
3   0.23      Good     E     VS1  56.9    65   327 4.05 4.07 2.31
4   0.22      Fair     E     VS2  65.1    61   337 3.87 3.78 2.49
5   0.20   Premium     E     SI2  60.2    62   345 3.79 3.75 2.27
6   0.32   Premium     E      I1  60.9    58   345 4.38 4.42 2.68
7   0.23 Very Good     E     VS2  63.8    55   352 3.85 3.92 2.48
8   0.23 Very Good     E     VS1  60.7    59   402 3.97 4.01 2.42
9   0.23 Very Good     E     VS1  59.5    58   402 4.01 4.06 2.40
10  0.23      Good     E     VS1  64.1    59   402 3.83 3.85 2.46

Notice in the example above that quoted strings within the SQL query are quoted using ” if the overall query is quoted with "" (this also works in reverse).

Suppose that we wish to add a new column to count the number of Premium cut diamonds over 1 carat:

sqldf("select count(*) from diamonds where carat > 1 and color = 'E'")
  count(*)
1     1892

Results of created values can also be returned as new columns:

sqldf("select *, count(*) as cnt_big_E_colored_stones from diamonds where carat > 1 and color = 'E' group by clarity")
  carat       cut color clarity depth table price    x    y    z cnt_big_E_colored_stones
1  1.30      Fair     E      I1  66.5    58  2571 6.79 6.75 4.50                       65
2  1.28     Ideal     E      IF  60.7    57 18700 7.09 6.99 4.27                       28
3  2.02 Very Good     E     SI1  59.8    59 18731 8.11 8.20 4.88                      499
4  2.03   Premium     E     SI2  61.5    59 18477 8.24 8.16 5.04                      666
5  1.51     Ideal     E     VS1  61.5    57 18729 7.34 7.40 4.53                      158
6  1.72 Very Good     E     VS2  63.4    56 18557 7.65 7.55 4.82                      318
7  1.20     Ideal     E    VVS1  61.8    56 16256 6.78 6.87 4.22                       52
8  1.55     Ideal     E    VVS2  62.5    55 18188 7.38 7.40 4.62                      106

If one would be interested what is the max price of the diamond according to the cut:

sqldf("select cut,  max(price) from diamonds group by cut")


        cut max(price)
1      Fair      18574
2      Good      18788
3     Ideal      18806
4   Premium      18823
5 Very Good      18818