How to use fn$ within a for loop

Question

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

What was your intention with `$x = "1"`? Did you want to check that every column was equal to 1? It would be helpful if you gave the desired output for your example. — MrFlick, Feb 25 '15 at 16:25
$x will change in the loop, example (for the first iteration, it will be a_ind = "1"). I will edit to show the desired output, good idea — Chris L, Feb 25 '15 at 16:27
`$x` doesn't change in the loop, `$i` would. Did you just mean to have `$i` instead of `$x`? — MrFlick, Feb 25 '15 at 16:29
`x` must be a string but in the example it is illegally a vector of strings. Also `library(gsubfn)` is unnecessary (though not wrong) since `library(sqldf)` already does that. — G. Grothendieck, Feb 25 '15 at 16:31
@ G.Grothendieck - could you please explain further? Can you not perform a loop with a list of strings? Is there an alternative? — Chris L, Feb 25 '15 at 16:33
Yes, you can perform a loop over a vector of strings but you cannot substitute a vector into a string. Maybe you meant `$i` instead of `$x` (as @MrFlick has already pointed out)? — G. Grothendieck, Feb 25 '15 at 16:35
substituting $x for $i corrects the issue, thank you both! The loop now works how I had envisioned! I still don't quite get what I want, it appears that I write over my table "product" each time, instead of appending new data to the first one each time. Is this an easy fix? — Chris L, Feb 25 '15 at 16:39

score 1 · Answer 1 · answered Feb 25 '15 at 18:35

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)

How to use fn$ within a for loop

1 Answers1