Replace column values based on column in another dataframe

Question

I would like to replace some column values in a df based on column in another data frame This is the head of the first df:

 df1
A tibble: 253 x 2
      id sum_correct
    <int>       <dbl>
 1 866093          77
 2 866097          95
 3 866101          37
 4 866102          65
 5 866103          16
 6 866104          72
 7 866105          99
 8 866106          90
 9 866108          74
10 866109          92

and some sum_correct need to be replaced by the correct values in another df using the id to trigger the replacement

df 2 
A tibble: 14 x  2
     id sum_correct
    <int>       <dbl>
 1 866103          61
 2 866124          79
 3 866152          85
 4 867101          24
 5 867140          76
 6 867146          51
 7 867152          56
 8 867200          50
 9 867209          97
10 879657          56
11 879680          61
12 879683          58
13 879693          77
14 881451          57

how I can achieve this in R studio? thanks for the help in advance.

GKi · Accepted Answer · 2019-12-02 08:24:35Z

2

You can make an update join using match to find where id matches and remove non matches (NA) with which:

idx <- match(df1$id, df2$id)
idxn <- which(!is.na(idx))
df1$sum_correct[idxn]  <- df2$sum_correct[idx[idxn]]
df1
       id sum_correct
1  866093          77
2  866097          95
3  866101          37
4  866102          65
5  866103          61
6  866104          72
7  866105          99
8  866106          90
9  866108          74
10 866109          92

answered Dec 2, 2019 at 8:24

GKi

40.1k3 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cettt · Accepted Answer · 2019-12-02 08:18:37Z

1

you can do a left_join and then use coalesce:

library(dplyr)
left_join(df1, df2, by = "id", suffix = c("_1", "_2")) %>%
  mutate(sum_correct_final = coalesce(sum_correct_2, sum_correct_1))

The new column sum_correct_final contains the value from df2 if it exists and from df1 if a corresponding entry from df2 does not exist.

answered Dec 2, 2019 at 8:18

Cettt

12k8 gold badges40 silver badges61 bronze badges

Collectives™ on Stack Overflow

Replace column values based on column in another dataframe

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related