Loop through dataframe and execute SQL query

Question

I have a dataframe which has 6 rows with SQL queries in each line. I want to execute every query and assign the output to a dataframe. currently I have 6 line of codes as below:

df6 <- tbl(conn,sql(df[6,])) Is there a way I can loop through and assign the same in one statement using dplyr mutate function? I tried below:

i <- 1:nrow(df)
df %>% mutate(paste0('df',i) <- tbl(conn,sql(df[i,])))

This throws the following error:

Error: Column q1 must be a 1d atomic vector or a list.

Any help is appreciated.

Shinobi_Atobe · Accepted Answer · 2018-05-14 14:22:51Z

1

Your question seems a little unclear, but assuming you have a dataframe with a column titled 'sql_code', you can just apply a function to each element in that column

lapply(df$sql_code, function(x) tbl(conn, sql(x)))

you will end up with a list that you can put together with bind_cols() (if the data frames are of the same dimensions)

answered May 14, 2018 at 14:22

Shinobi_Atobe

1,9931 gold badge23 silver badges37 bronze badges

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

1 Comment

Pravellika Over a year ago

Thanks this is close to what I was looking. I have a dataframe with sql queries. I need to execute every one of them and assign the output to a new dataframe.Now how do I assign this dynamically? It should be like paste0('df',i) = lapply(...)

Pravellika · Accepted Answer · 2018-05-15 08:51:10Z

0

With the help from Shinobi_Atobe's function I could develop it into what I wanted as below:

list2env(setNames(lapply(df$sql_query,function(x) {tbl(conn, sql(x))}), paste0('df', 1:nrow(df))), envir=.GlobalEnv)

This executes sql query from every row in the dataframe and stores the output in individual dataframes named as df1,df2...

answered May 15, 2018 at 8:51

Pravellika

18214 bronze badges

Collectives™ on Stack Overflow

Loop through dataframe and execute SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related