Replace values in column of a dataframe when matching to column in another dataframe in R

Question

I just read through comparable questions, but found no answering my specific problem. I have two dataframes,

df1 <- data.frame("name" = c("11-24", "Tim", "Anna", "67-14", "A0839", "A4b", "Lisa", "Selina"))
df2 <- data.frame("abbreviation" = c("11-24", "67-14", "A0839", "A4b"),
                  "name" = c("Charles", "Nick", "Harry", "Lola"))

Looking like this:

> df1
    name
1  11-24
2    Tim
3   Anna
4  67-14
5  A0839
6    A4b
7   Lisa
8 Selina

> df2
  abbreviation    name
1        11-24 Charles
2        67-14    Nick
3        A0839   Harry
4          A4b    Lola

I want to replace the abbreviations found in the column "name" of df1 by the matching name in df2. So that 11-24 is replaced by Charles or A4b by Lola.

What I tried was:

df1 <- df1 %>% 
       mutate(name = ifelse(name %in% df2$abbreviation, df2$name, name))

But this give not the result I want. I want:

> df1
        name
    1Charles
    2    Tim
    3   Anna
    4   Nick
    5  Harry
    6   Lola
    7   Lisa
    8 Selina

My dataframes have a different length. I am looking for a tidyverse-solution, maybe one of you has something in mind..

This would help me a lot :) Best, Kathrin

Ronak Shah · Accepted Answer · 2021-03-24 11:08:58Z

2

Using join and coalesce.

library(dplyr)

df1 %>%
  left_join(df2, by = c('name' = 'abbreviation')) %>%
  transmute(name = coalesce(name.y, name))

#     name
#1 Charles
#2     Tim
#3    Anna
#4    Nick
#5   Harry
#6    Lola
#7    Lisa
#8  Selina

answered Mar 24, 2021 at 11:08

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

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

1 Comment

Kathrin Over a year ago

Perfect, thank you! I don't know why but I thought that left_join will not.. This helped me :)

hello_friend · Accepted Answer · 2021-03-24 11:46:43Z

1

Base R solution:

idx <- match(df1$name, df2$abbreviation)
transform(df1, name = ifelse(!is.na(idx), df2$name[idx], name))

answered Mar 24, 2021 at 11:46

hello_friend

5,8381 gold badge13 silver badges18 bronze badges

Comments

GKi · Accepted Answer · 2021-03-24 11:18:10Z

1

In base you can use match to make this update join.

idx <- match(df1$name, df2$abbreviation)
idxn <- which(!is.na(idx))
#idxn <- !is.na(idx) #Alternative
df1$name[idxn] <- df2$name[idx[idxn]]
df1
#     name
#1 Charles
#2     Tim
#3    Anna
#4    Nick
#5   Harry
#6    Lola
#7    Lisa
#8  Selina

answered Mar 24, 2021 at 11:18

GKi

40.1k3 gold badges36 silver badges56 bronze badges

Collectives™ on Stack Overflow

Replace values in column of a dataframe when matching to column in another dataframe in R

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related