Subimago Subimago - 3 months ago 19
R Question

Weird sql statement in sqldf and R code

I have to maintain some R code and the guy that wrote it isn't here anymore.

I've found some weird sql statements in his code and i am not sure what they mean. This is one of them:

sqldf(paste("select ", i," i , * from simTypeFile union all select * from simTypeFiles", sep=""))


i is an index that is incremented from zero to x. I would expect that a select keyword will be followed by a column_name or an asterisk. In this case it is followed by an integer i, a space, a string i and an asterisk.

The table that is to be queried doesn't have any column with a number i or a string "i". What do those characters following the select statement mean?

Answer

I think they are preparing data for simulation, and adding i column for indexing and naming it as i (to make it explicit use AS to name a column, e.g.: select 1 AS i), consider below example:

# emprty data.frame to fill in
simTypeFiles <- data.frame(i = numeric(0), speed = numeric(0), dist = numeric(0))

# dummy data
simTypeFile <- cars[1:3,]

# then loop and rbind - union
for(i in 1:3){
  simTypeFiles <- 
    rbind(
      simTypeFiles,
      sqldf(paste("select ", i," i , * from simTypeFile union all select * from simTypeFiles", sep = ""))
    )
}

simTypeFiles
Comments