How to cycle through SQL query on multiple datasets?

Question

I want to simplify some code that I am running. I am trying to pull the dimensions of multiple datasets that result from SQL queries. I want to try to loop through the dataset names

I have been able to get my desired result, I'm just not sure it's the most efficient way of going about this: I have a separate SQL query line for each dataset.

This is the original code:

    library(sqldf)
    dim(sqldf("select Group1, count(*) as Count from Data1 group by Group1"))[1]
    dim(sqldf("select Group1, count(*) as Count from Data2 group by Group1"))[1]
    dim(sqldf("select Group1, count(*) as Count from DataN group by Group1"))[1]

This is my attempt at simplifying the code:

    datalist=c(Data1,Data2,...DataN)
    abc=vector("list",length(datalist))
    for (i in seq_along(datalist))
      abc[[i]]=dim(sqldf("select Group1, count(*) as Count from datalist[i] group by Group1"))[1]

I expect the output to show N numbers for each dataset but I get an error message that reads "no such table: datalist."

January · Accepted Answer · 2019-07-16 13:57:33Z

1

This is because the "datalist[i]" within the string does not get substituted by actual value of the datalist[i] variable, but passed on to SQL. Try

datalist=c("Data1", "Data2", ... "DataN")
queries <- sprintf("select Group1, count(*) as Count from %s group by Group1", datalist)
abc <- lapply(queries, function(q) {
           nrow(sqldf(q))
         })

Some additional remarks:

don't use for to populate a list, use lapply which directly generates the list
if you do, there is no need to initialize a list other than abc <- list()
don't use dim(x)[1], nrow is more explicit

if your data is always "Data1", "Data2"... etc, you can also do

queries <- sprintf("select Group1, count(*) as Count from Data%d group by Group1", 1:10)

edited Jul 16, 2019 at 13:57

answered Jul 16, 2019 at 13:50

January

17.2k7 gold badges57 silver badges83 bronze badges

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

Collectives™ on Stack Overflow

How to cycle through SQL query on multiple datasets?

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