How to remove for

[Christophe]

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

[mara]

Hi Christophe,

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

Thanks

FAQ: How to format your code

Formatting Code: the Basics All code or console output you include in your posts should be formatted properly. Luckily, this is very easy to do! Just use the code formatting button at the top of the post editing box: [image] Select some code Click the </> button! The code formatting button automatically adds special Markdown formatting symbols to whatever you selected. If you check the preview pane to the right of your post, you'll see that the forum software now outputs nicely formatted code . [04] The code formatting button is pretty smart! It can tell the difference between snippets of code that are part of a sentence and blocks of code that stand alone. …

[jcblum]

For the first question:

@Christophe

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')

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., < instead of <. Only properly formatted code is safe from this)

[jcblum]

For the second question:

@Christophe

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.

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 `$<-.data.frame`(`*tmp*`, "iField2", value = 5:6): replacement has 2 rows, data has 3

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

DF1
#>   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)}
Notes:

• See the documentation for match()
• field1 and field2 are factors, not character vectors, because the code does not set stringsAsFactors = FALSE while creating the data frame. This doesn't really make a difference here, but it's good to keep in mind.

[Christophe]

Thank you very much for your answer