Using sqldf with a list of data frames in R

Question

I've got a list of data frames which I want to use in an sqldf query. However I don't seem to be able to reference the data frames directly from the list within sqldf either by using $ or [].

my (vastly simplified) code is:

DF_List <- list(df1 = data.frame(N = 1:26, N2 = 27:52 ), df2 = data.frame(N = 1:26, L = letters[1:26] ))

sqldf("select * from DF_List$df1, DF_List$df2 where DF_List$df1.N = DF_List$df2.N")

The following works - but I want to reference the data frames directly from the list.

DF_List <- list(df1 = data.frame(N = 1:26, N2 = 27:52 ), df2 = data.frame(N = 1:26, L = letters[1:26] ))

new_df1 = DF_List$df1
new_df2 = DF_List$df2

sqldf("select * from new_df1, new_df2 where new_df1.N = new_df2.N")

Note that normally one puts the condition in the join: select * from new_df1 join new_df2 using(N) , — G. Grothendieck
– G. Grothendieck, Commented Nov 14, 2018 at 12:31

IceCreamToucan · Accepted Answer · 2018-11-13 20:32:20Z

3

You can use with

with(
  DF_List, 
  sqldf("select * from df1, df2 where df1.N = df2.N"))

Or, convert your list to an environment and use the envir argument

sqldf("select * from df1, df2 where df1.N = df2.N", envir = as.environment(DF_List))

answered Nov 13, 2018 at 20:32

IceCreamToucan

28.8k2 gold badges27 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Using sqldf with a list of data frames in R

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