Scan a column in one df using column in another df and if condition is met assign the create an extra column in the second df and assign value

Question

I got data like this

    structure(list(id = c(1, 1, 2, 2, 2, 3, 3, 3), code2 = c("24600", 
"2400", "718", "19C11", "2021", "41C200", "G8511", "2021")), class = "data.frame", row.names = c(NA, -8L))

I want to use the "code" column in the above df2 and scan it through the following df1, and if the code is matching then assign the value from the "score" column in df1

        structure(list(code = c("718", "E0012", "G8511", "209BF", "466D", 
    "2021"), score = c(1, 1, 3, 6, 1, 2)), class = "data.frame", row.names = c(NA, -6L))

and I want the final df to look like this

        structure(list(id = c(1, 1, 2, 2, 2, 3, 3, 3), code2 = c("24600", 
    "2400", "718", "19C11", "2021", "41C200", "G8511", "2021"), score2 = c(NA, 
    NA, 1, NA, 2, NA, 3, 2)), class = "data.frame", row.names = c(NA, -8L))

Does this answer your question? [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Martin Gal, Nov 15 '21 at 23:05
You can try `merge(df1, df2, by.x = "code2", by.y = "code", all.x = T)` — Ben, Nov 16 '21 at 01:18

score 0 · Answer 1 · answered Nov 15 '21 at 23:05

0

Here is a solution:

library(dplyr)
df1 <- structure(list(id = c(1, 1, 2, 2, 2, 3, 3, 3), code2 = c("24600", 
                                                         "2400", "718", "19C11", "2021", "41C200", "G8511", "2021")), class = "data.frame", row.names = c(NA, -8L))

df2 <- structure(list(code = c("718", "E0012", "G8511", "209BF", "466D", 
                        "2021"), score = c(1, 1, 3, 6, 1, 2)), class = "data.frame", row.names = c(NA, -6L))


res <- df1 %>% mutate(score2 = ifelse(code2 %in% df2$code, df2$score, NA))

answered Nov 15 '21 at 23:05

Bloxx

1,495
1
9
21

Sorry, I don't have the ID column in the first df. In the first df I only got the code and score and In the second df for each id , when their code column matches the code in the df1, create a score2 column and assign the value in the df1 score column. – kam Nov 15 '21 at 23:15
I tried this `df2$score <- with(df2, ifelse(code2 %in% df$code, df$score, NA))` but it gives the wring results. – kam Nov 15 '21 at 23:19
I mean I don't have the id column in the second df. Id column only present in one. Therefore, the code columns have to be matched and the value from the score column, need to inserted in a new score column in the first df. – kam Nov 15 '21 at 23:24

Scan a column in one df using column in another df and if condition is met assign the create an extra column in the second df and assign value

1 Answers1