R sqldf loop and create data frame named based on query name

Question

I have data frame of SQL queries I would like to execute on another data frame.

queries <- structure(list(Name = c("innovation", "foos", "testing"), A = c("select * from data WHERE `TEXT` RLIKE '[[:<:]]innovat[^[:space:]]+[[:>:]]'", "select * from data WHERE `TEXT` RLIKE '[[:<:]]foo[^[:space:]]+[[:>:]]'", "select * from data WHERE `TEXT` RLIKE '[[:<:]]test[^[:space:]]+[[:>:]]'"), B = c("", "b", "b"), C = c("c", "c", "c")), .Names = c("Name", "Query", "Q1_2", "Q1_3"), row.names = c(NA, -3L), class = "data.frame")

I would like to loop through these queries using the package sqldf, and name the data frame output from each query its correspond name found in the data frame queries. I also need to create a new variable in each dataframe that matches the data frame name.

Sample dataset

data <- structure(list(Participant = 1:3, A = c("and other foo things", "testing test and foo", "nothing here"), B = c("", "b", "b"), C = c("c", "c", "c")), .Names = c("Participant", "TEXT", "other", "another"), row.names = c(NA, -3L), class = "data.frame")

Something like:

for (i in queries[2]) {
  i<- as.data.frame(sqldf(i)
                      i$category <- i

}

But this doesn't work yet. Suggestions?

typo, corrected to the data dataframe

lmcshane
– lmcshane

2016-11-03 01:17:57 +00:00
Commented Nov 3, 2016 at 1:17 — lmcshane
– lmcshane, Commented Nov 3, 2016 at 1:17

seasmith · Accepted Answer · 2016-11-02 23:44:10Z

1

df <- lapply(queries[[2]], sqldf) names(df) <- queries[[1]]

EDIT: Should work.

edited Nov 2, 2016 at 23:44

answered Nov 2, 2016 at 23:38

seasmith

9199 silver badges16 bronze badges

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

Comments

Parfait · Accepted Answer · 2016-11-03 01:29:50Z

0

Consider iterating by rows, capturing each row's Query and Name to create a dataframe, all compiled into a list of dataframes. You can then output to your global environment (or better yet, keep in list):

dfList <- lapply(seq_len(nrow(queries)), function(i){
  df <- sqldf(queries$Query[i])
  df$Category <- queries$Name[i]
  return(df)
})    
names(dfList) <- queries$Name

list2env(dfList, envir = .GlobalEnv)

Alternatively, use mapply's wrapper Map, passing in each Query and Name column vector:

handler <- function(x, y) {
    df <- sqldf(x)
    df$Category <- y
    return(df)
}
dfList <- Map(handler, queries$Query, queries$Name)
names(dfList) <- queries$Name

list2env(dfList, envir = .GlobalEnv)

answered Nov 3, 2016 at 1:29

Parfait

108k19 gold badges103 silver badges138 bronze badges

Collectives™ on Stack Overflow

R sqldf loop and create data frame named based on query name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related