Match two dataframes and replace the corresponding entries in one of them

user_1306892 0
Viewed 62

I have the following two dataframes:

df = data.frame(From=c("Mike","Elena","Mike","Mark","Alice","Joana"),
                To=c("Jasmine","Mariah","Erik","Jack","Joana","Mike"),
                Number=1:6, stringsAsFactors = FALSE)
df2 = data.frame(ID=c("1738799","657940","13253","97980","6569874","64839","8494","2773"), 
                 Name=c("Mike","Elena","Mark","Alice","Joana","Mariah","Erik","Jack"),
                 stringsAsFactors = FALSE)

Dataframe df2 contains the ID associated to most of the names in df. I would like to replace the names in df with the corresponding ID. In this way:

> df
  From             To   Number
  1738799     Jasmine        1
  657940        64839        2
  1738799        8494        3
  13253          2773        4
  97980       6569874        5
  6569874     1738799        6
matching dataframe r
4 Answers

We can use coalesce with deframe in tidyverse

library(dplyr)
library(tibble)
df %>%
    mutate(across(From:To, ~ coalesce(deframe(df2[2:1])[.], .)))
#     From      To Number
#1 1738799 Jasmine      1
#2  657940   64839      2
#3 1738799    8494      3
#4   13253    2773      4
#5   97980 6569874      5
#6 6569874 1738799      6

Or using base R (R 4.1.0)

df[1:2] <- setNames(df2$ID, df2$Name)[as.matrix(df[1:2])] |>
       {\(x) ifelse(is.na(x), as.matrix(df[1:2]), x)}()

-output

df
#     From      To Number
#1 1738799 Jasmine      1
#2  657940   64839      2
#3 1738799    8494      3
#4   13253    2773      4
#5   97980 6569874      5
#6 6569874 1738799      6
user_37322710

In base R you could do:

df3 <- df
nms <- do.call(setNames, unname(df2))
df3[1:2]<- lapply(df[1:2], function(x) ifelse(is.na(a<-nms[x]), x, a))
df3
     From      To Number
1 1738799 Jasmine      1
2  657940   64839      2
3 1738799    8494      3
4   13253    2773      4
5   97980 6569874      5
6 6569874 1738799      6
user_83802720

You can also use the following tidyverse solution:

library(dplyr)
library(purrr)


df %>%
  map_if(~ is.character(.x), ~ ifelse(!is.na(match(.x, df2$Name)),
                                      str_replace(., .x, df2$ID[match(.x, df2$Name)]), 
                                      .x)) %>%
  bind_cols()


# A tibble: 6 x 3
  From    To      Number
  <chr>   <chr>    <int>
1 1738799 Jasmine      1
2 657940  64839        2
3 1738799 8494         3
4 13253   2773         4
5 97980   6569874      5
6 6569874 1738799      6
user_143145200

A base R option

transform(
  df,
  To = with(df2, {
    m <- ID[match(To, Name)]
    ifelse(is.na(m), To, m)
  })
)

gives

   From      To Number
1  Mike Jasmine      1
2 Elena   64839      2
3  Mike    8494      3
4  Mark    2773      4
5 Alice 6569874      5
6 Joana 1738799      6
user_121587570
Related