CODEWITHSUNDEEP
rdataframereplacematch
kin182
kin182

Reputation: 403

How to match and replace value from one dataframe to another?

I have a dataframe df

df = data.frame("A" = c("car",NA,"cat","cut"),
                "B" = c(NA,"car",NA,"cat"),
                "C" = c("cut","cat",NA,NA))

and another one df2

df2 = data.frame("Group" = c("car", "cat","cut"),
                 "Value" = c(1,2,3))

I would like to have the output like this

     A    B    C
1    1   NA    3
2   NA    1    2
3    2   NA   NA
4    3    2   NA

I am relative new to R so I need some help in solving this. Thanks!

Upvotes: 1

Views: 989

Answers (2)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193517

You can use factor:

df_new <- df # just in case you want to retain your original df
df_new[] <- lapply(df, factor, levels = df2$Group, labels = df2$Value)
df_new 
#      A    B    C
# 1    1 <NA>    3
# 2 <NA>    1    2
# 3    2 <NA> <NA>
# 4    3    2 <NA>

Note that the values are characters, though. To get numeric values, you can do lapply(df, function(x) as.integer(as.character(factor(x, levels = df2$Group, labels = df2$Value)))).

Alternatively, you can do:

df_new[] <- factor(as.matrix(df), levels = df2$Group, labels = df2$Value)

This will again return a data.frame with character columns. Get numeric values by using as.integer(as.character(factor(as.matrix(df), levels = df2$Group, labels = df2$Value))).

If your value column is always just a sequential integer, you can skip the "labels" argument and the as.character before using as.integer. Furthermore, you can simplify the extraction of numeric values by using data.matrix.

These have been benchmarked in this Gist.

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

You can unlist the dataframe and use match :

df[] <- df2$Value[match(unlist(df), df2$Group)]
df

#   A  B  C
#1  1 NA  3
#2 NA  1  2
#3  2 NA NA
#4  3  2 NA

To solve this using dplyr you can do :

library(dplyr)
df %>% mutate(across(.fns = ~df2$Value[match(., df2$Group)]))

Upvotes: 1

Related Questions