1

I am trying to replace column a1 of dataframe df1 by value b1 of dataframe df1, if a1 matches. Please help me achieve this using basic R functions 

df1 <- data.frame(a1 = c("b2","c2","abc2"),b1 = c("bb2","cc2","d2"))
df2 <- data.frame(d1 = c("sale","sale2","sale3"),a1 = c("b2","c2","d2"))

> df2
     a1 b1
1  sale b2
2 sale2 c2
3 sale3 d2

Expected output:

     a1  b1
1  sale bb2
2 sale2 cc2
3 sale3  d2
B--rian
  • 5,578
  • 10
  • 38
  • 89
prog
  • 1,073
  • 5
  • 17
  • 1
    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) – NelsonGon Feb 23 '20 at 12:32

2 Answers2

3

Personally I like to add the reference table to the original table. For example, (using dplyr) you might do something like:

df2 %>% left_join(df1, by = "a1") %>% mutate(new_b1 = ifelse(!is.na(b1), b1, a1))

Which results in 

    d1 a1   b1 new_b1
1  sale b2  bb2    bb2
2 sale2 c2  cc2    cc2
3 sale3 d2 <NA>     d2

In order to get your stated desired result, although it feels a bit weird to change the merging column but everything is possible, you might do something like:

df2 %>% left_join(df1, by = "a1") %>% 
  mutate(b1 = ifelse(!is.na(b1), b1, a1)) %>% 
  select(-a1) %>% 
  rename(a1 = d1)

which results in 

     a1  b1
1  sale bb2
2 sale2 cc2
3 sale3  d2
Annet
  • 846
  • 3
  • 14
2

One base R possibility could be:

pmax(df2$a1, df1$b1[match(df2$a1, df1$a1)], na.rm = TRUE)

[1] "bb2" "cc2" "d2"

It requires to import your data using stringsAsFactors = FALSE.

Or:

ifelse(is.na(matching), df2$a1, matching); matching <- df1$b1[match(df2$a1, df1$a1)]
tmfmnk
  • 38,881
  • 4
  • 47
  • 67