Can't use sqldf on my data frame anymore (Error in sqliteExecStatement)

Question

(I'm rather new to R.)

I don't know why I get the following error:

> apps.rsd.c <- sqldf("SELECT appid FROM apps.rsd WHERE rcount > 50")
Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such table: apps.rsd)

I thought these might help, I tried to compare them to data frames which let me work with sqldf, but didn't see what causes the error:

> dput(head(apps.rsd))
structure(list(appid = c(173L, 717L, 996L, 209L, 602L, 255L), 
  cid = c(4L, 15L, 21L, 5L, 13L, 6L), price = c(0, 0, 0, 1.99, 
  0, 0.76), count = c(411, 411, 210, 18, 921, 22), sum = c(1226, 
  1870, 871, 66, 3948, 86), mean = c(2.98296836982968, 4.54987834549878, 
  4.14761904761905, 3.66666666666667, 4.28664495114007, 3.90909090909091
  ), sd = c(1.73897694746568, 0.958668345866094, 1.31370760232218, 
  1.7950549357115, 1.33373734360862, 1.62114131819336), rcount = c(3, 
  3, 3, 5, 5, 7), rsum = c(7, 0, 0, 13, 0, 19), rsd = c(2.3094010767585, 
  2.3094010767585, 2.3094010767585, 2.19089023002066, 2.19089023002066, 
  2.1380899352994)), .Names = c("appid", "cid", "price", "count", 
"sum", "mean", "sd", "rcount", "rsum", "rsd"), class = c("data.table", 
"data.frame"), row.names = c(NA, -6L), .internal.selfref = <pointer: 0x7fd3bc807b78>)

> str(apps.rsd)
Classes ‘data.table’ and 'data.frame':  1518 obs. of  10 variables:
  $ appid : int  173 717 996 209 602 255 1473 442 672 772 ...
  $ cid   : int  4 15 21 5 13 6 31 10 14 17 ...
  $ price : num  0 0 0 1.99 0 0.76 0 7.98 0 0.75 ...
  $ count : num  411 411 210 18 921 22 113 54 564 33 ...
  $ sum   : num  1226 1870 871 66 3948 ...
  $ mean  : num  2.98 4.55 4.15 3.67 4.29 ...
  $ sd    : num  1.739 0.959 1.314 1.795 1.334 ...
  $ rcount: num  3 3 3 5 5 7 7 2 2 2 ...
  $ rsum  : num  7 0 0 13 0 19 0 5 0 0 ...
  $ rsd   : num  2.31 2.31 2.31 2.19 2.19 ...
  - attr(*, ".internal.selfref")=<externalptr>

G. Grothendieck · Accepted Answer · 2014-05-24 12:32:26Z

3

Dot is an operator in SQL, not a legal character in a name, so put apps.rsd in single quotes (or rename it to some name that has no dot in it):

sqldf("SELECT appid FROM 'apps.rsd' WHERE rcount > 50")

Here is a reproducible example. Note that I removed the weird external pointer at the end of your data frame. (How did that get there?)

apps.rsd <- 
  structure(list(appid = c(173L, 717L, 996L, 209L, 602L, 255L), 
  cid = c(4L, 15L, 21L, 5L, 13L, 6L), price = c(0, 0, 0, 1.99, 
  0, 0.76), count = c(411, 411, 210, 18, 921, 22), sum = c(1226, 
  1870, 871, 66, 3948, 86), mean = c(2.98296836982968, 4.54987834549878, 
  4.14761904761905, 3.66666666666667, 4.28664495114007, 3.90909090909091
  ), sd = c(1.73897694746568, 0.958668345866094, 1.31370760232218, 
  1.7950549357115, 1.33373734360862, 1.62114131819336), rcount = c(3, 
  3, 3, 5, 5, 7), rsum = c(7, 0, 0, 13, 0, 19), rsd = c(2.3094010767585, 
  2.3094010767585, 2.3094010767585, 2.19089023002066, 2.19089023002066, 
  2.1380899352994)), .Names = c("appid", "cid", "price", "count", 
"sum", "mean", "sd", "rcount", "rsum", "rsd"), class = c("data.table", 
"data.frame"), row.names = c(NA, -6L))  

library(sqldf)
sqldf("SELECT appid FROM 'apps.rsd' WHERE rcount > 50")

The output is:

[1] appid
<0 rows> (or 0-length row.names)

or with renaming:

apps_rsd <- apps.rsd
sqldf("SELECT appid FROM apps_rsd WHERE rcount > 50")

which gives the same output.

edited May 24, 2014 at 12:32

answered May 24, 2014 at 1:23

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3670566 Over a year ago

Actually I considered that and renaming to something without a dot didn't work either (any ideas why that might be the case?), but the single quotes do! Thanks!

G. Grothendieck Over a year ago

It works for me if I put the data frame in single quotes and remove the weird external pointer at the end of the data frame which should not be there. I have added a reproducible example to the answer.

user3670566 Over a year ago

Yes, with the single quotes it works, with the renaming it doesn't. How did you remove the pointer? Actually I don't know where it came from.

G. Grothendieck Over a year ago

It works for me. I have added renaming to the example. I just omitted the text referring to the external pointer part from the dput output as seen in the reproducible example in the answer.

user3670566 Over a year ago

Ok, probably I did something to the data after renaming it (I tried several things to make it work) or something.

Collectives™ on Stack Overflow

Can't use sqldf on my data frame anymore (Error in sqliteExecStatement)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest