How to remove for

Hello all,

I don't want to have a for in my code so I have two questions for you.

The first one is : I want to construct a SQL sentence dynamically. I have a list
Liste1 <- c("AAA","ABB","ACC")
and I want to introduce it in a SQL sentence like select * from table1 where field1 in ('AAA','ABB','ACC')
For the moment I have do that

LA <- paste0("'",Liste1[1],"'")
for (i in 2:length(Liste1)) {
    LA <- paste0(LA,"',",Liste1[i],"'")

Is there another solution without for ?

The second one is:
I have a list
Liste1 <- c("AAA","AAB","AAC","AAD","BBA","BAB")
and a dataframe
DF1 <- data.frame(field1 = c("AAA","AAC","AAD"), field2 = c("BBA","BBA","BAB"))
I want to have 2 other columns with the index from Liste1 of the values of the columns field1 and field2.
So I have done

DF1 <- transform(DF1,iField1=0)
DF1 <- transform(DF1,iField2=0)
for (i in 1:nrow(DF1)) {
    DF1$iField1[i] <- which(Liste1==DF1$field1[i])
    DF1$iField2[i] <- which(Liste1==DF1$field2[i]) 

Is there another solution without for ?

Thank you

Hi Christophe,

It's really hard to read your code as is. Would you mind adding formatting to your post for easier reading?


For the first question:

paste() and paste0() are vectorized functions, so it’s as simple as this:

Liste1 <- c("AAA","ABB","ACC")

paste0("'", Liste1, "'", collapse = ",")
#> [1] "'AAA','ABB','ACC'"

But if you’re doing a lot of SQL statement creation, take a look at glue_sql() from the very helpful glue package.

(please do fix the code formatting! In addition to being hard to read, in some circumstances the forum will copy-paste unformatted code with special characters converted to HTML entities — e.g., &lt; instead of <. Only properly formatted code is safe from this)


For the second question:

Liste1 <- c("AAA","AAB","AAC","AAD","BBA","BAB")
DF1 <- data.frame(field1 = c("AAA","AAC","AAD"), field2 = c("BBA","BBA","BAB"))

# This works for the first case...
DF1$iField1 <- which(Liste1 %in% DF1$field1)

# But fails for the second
DF1$iField2 <- which(Liste1 %in% DF1$field2)
#> Error in `$<`(`*tmp*`, "iField2", value = 5:6): replacement has 2 rows, data has 3

# This will work for both cases
DF1$iField2 <- match(DF1$field2, Liste1)

#>   field1 field2 iField1 iField2
#> 1    AAA    BBA       1       5
#> 2    AAC    BBA       3       5
#> 3    AAD    BAB       4       6

Created on 2018-10-09 by the reprex package (v0.2.1)


Thank you very much for your answer