R: Using sqldf and insert to create a dataframe

Question

I am trying to create a dataframe using sqlf and sql insert. Here's a simple version of my code

d2 = data.frame(x=runif(10))
sqldf(c("create table d1(min_x real, max_x real)", 
        "insert into d1 select min(x), max(x) from d2", 
        "select * from d1"))

The output is

       min_x     max_x
1 0.05290026 0.9427019

Now I want to use d1 in my R code, but if I enter

d1

R responds

> d1
Error: object 'd1' not found

I have tried this using SQLite and RH2 with the same results.

How do I use d1 in R?

You can assign the output of sqldf to an object and then use it. — Jota
– Jota, Commented Jun 10, 2016 at 20:20
Interesting, because when I run your code, the value of d1 is 24.18729. — Miha
– Miha, Commented Jun 11, 2016 at 21:34
I tried it again today after rebooting my computer and got the same results. I even tried creating d1 in R as a dataframe, but then I got an error message from sqldf that d1 already existed. So I deleted the code to create table d1 and ran it again. The SQL insert "works" and shows all the records, but d1 in R shows just the original data. In the SQL d1, should be a row with two values, both < 10. — Eric
– Eric, Commented Jun 13, 2016 at 23:38

White River · Accepted Answer · 2016-10-27 03:35:37Z

1

sqldf, when used with the default driver SQLite will setup an on-the-fly in-memory database which will be destroyed upon exit. The only way to save the object is to assign it with :

d1 <- sqldf(c("create table d1(min_x real, max_x real)", 
    "insert into d1 select min(x), max(x) from d2", 
    "select * from d1"))

You can then use it in your R code:

> print(d1)
  min_x     max_x
1 0.0558218 0.8966438

answered Oct 27, 2016 at 3:35

White River

133 bronze badges

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

Collectives™ on Stack Overflow

R: Using sqldf and insert to create a dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related