R replace values of a column based on exact match of another data frame

Question

I have 2 data frames in R

DF1 is

ColA ColB  observation
123  Text1 45
234  Text2 65
345  Text3 78

DF2 is

ColumnA ColumnB metric
123     Text1    7
123     Text2    65
345     Text1    102

I want to replace the observation column of DF1 with values of the metric column of DF2 but only for the rows that have an exact match on ColA=ColumnA and ColB=ColumnB. The above example shall output the following data frame:

ColA ColB  value
123  Text1 7
234  Text2 65
345  Text3 78

score 1 · Accepted Answer · answered Feb 24 '20 at 11:17

Here is a base R solution, using merge + ifelse

DF <- within(merge(DF1,DF2,by.x = c("ColA","ColB"),by.y = c("ColumnA","ColumnB"),all.x = TRUE),
             value <- ifelse(is.na(metric),observation,metric))[-(3:4)]

such that

> DF
  ColA  ColB value
1  123 Text1     7
2  234 Text2    65
3  345 Text3    78

score 0 · Answer 2 · answered Feb 24 '20 at 11:09

Solution using dplyr:

Since you did not provide a minimal reproducible example I could not test the code...

library(dplyr)
DF1 %>%
  left_join(DF2, by=("ColA"="ColumnA", "ColB"="ColumnB") %>%
  mutate(value = if_else(!is.na(metric), metric, observation)

R replace values of a column based on exact match of another data frame

2 Answers2