CODEWITHSUNDEEP
rfor-loopsqldf
Chris L
Chris L

Reputation: 338

How to use fn$ within a for loop

I am unable to use fn$ within a for loop. When I test it with one variable, (x = "a_ind") I am able to successfully run a loop. However, when I add multiple variables via (c("a_ind",...)) it no longer works. I have been unable to find a solution online or on the forum

Please help!

library("sqldf")
library("gsubfn")

df <- data.frame( a_ind = c("1","0","0","0"),
            b_ind = c("0","1","0","0"),
            c_ind = c("0","0","1","0"),
            d_ind = c("0","0","0","1"),
            var1  = c(11,22,33,44),
            var2  = c(50,30,75,100))


    x <- c("a_ind","b_ind","c_ind","d_ind")
    for (i in x){

    product <- fn$sqldf('
                select 
                    $x,
                    var1 * var2 as var3
                from df
                where $x = "1" ')

    product <- rbind(product,product)
    product <- product[!duplicated(product),]
    }

    View(product)

Results in : Error in sqliteSendQuery(con, statement, bind.data) : error in statement: no such function: c

Desired Output would look like this:

name  var3
a_ind 550
b_ind 660
c_ind 2475
d_ind 4400

Upvotes: 0

Views: 255

Answers (1)

Chris L
Chris L

Reputation: 338

I wanted to close out this question after getting help from MrFlick and G. Grothendieck. In a for loop(i in x), use the "i" in your code. This gets the desired output. The additional step included was to create an empty dataset "new" so that my rbind can have something to add data to.

Thanks for all the help guys

library("sqldf")

new = data.frame(matrix(vector(),0,2,
               dimnames=list(c(),
             c("name","var3"))),
             stringsAsFactors=F)


df <- data.frame( a_ind = c("1","0","0","0"),
            b_ind = c("0","1","0","0"),
            c_ind = c("0","0","1","0"),
            d_ind = c("0","0","0","1"),
            var1  = c(11,22,33,44),
            var2  = c(50,30,75,100))

x <- c("a_ind","b_ind","c_ind","d_ind")
for (i in x){

product <- fn$sqldf('
           select 
                $i,
                var1 * var2 as var3
            from df
            where $i = "1" ')

new <- rbind.fill(new,product)
new<- new[!duplicated(new),]
}

View(new)

Upvotes: 1

Related Questions