CODEWITHSUNDEEP
rdataframematch
TNAU
TNAU

Reputation: 25

Comparing two dataframes in R and extract the values from one dataframe

I have two dataframes which have different number of rows and columns. one dataframe is with two columns and other dataframe with multiple columns. The first dataframes looks like,

The dataframe with item name and the value

Second dataframe is like

Dataframe with multiple columns not arranged in regular manner

Actually, i need to replace the second dataframe which contains A,B,C etc with the values of 2nd column of first dataframe.

I need the output in below format.

output format

Help me to solve this problem.

dput:

df

structure(list(col1 = c("A", "B", "C", "D", "E", "F", "G", "H", 
"I", "J", "K", "L"), col2 = c(10, 1, 2, 3, 4, 3, 1, 8, 19, 200, 
12, 112)), row.names = c(NA, -12L), class = c("tbl_df", "tbl", 
"data.frame"))

df2

structure(list(col1 = c("A", "F", "W", "E", "F", "G"), col2 = c(NA, 
NA, "J", "K", "L", NA), col3 = c(NA, "H", "I", NA, "A", "B")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

Upvotes: 1

Views: 670

Answers (4)

GKi
GKi

Reputation: 39727

Another short one liner in base. You can use match and assign the result to df2[]:

df2[] <- df[match(unlist(df2), df[,1]), 2]
df2
#  col1 col2 col3
#1   10   NA   NA
#2    3   NA    8
#3   NA  200   19
#4    4   12   NA
#5    3  112   10
#6    1   NA    1

Upvotes: 0

slava-kohut
slava-kohut

Reputation: 4233

one-liner in base R:

df2 <- as.data.frame(lapply(df2, function(x) ifelse(!is.na(x), setNames(df$col2, df$col1)[x], NA)))

Output

> df2
  col1 col2 col3
1   10   NA   NA
2    3   NA    8
3   NA  200   19
4    4   12   NA
5    3  112   10
6    1   NA    1

Upvotes: 1

Allan Cameron
Allan Cameron

Reputation: 174556

A one-liner:

as_tibble(`colnames<-`(matrix(df1$col2[match(as.matrix(df2),df1$col1)], ncol=3), names(df2)))
#> # A tibble: 6 x 3
#>    col1  col2  col3
#>   <dbl> <dbl> <dbl>
#> 1    10    NA    NA
#> 2     3    NA     8
#> 3    NA   200    19
#> 4     4    12    NA
#> 5     3   112    10
#> 6     1    NA     1

Upvotes: 2

bouncyball
bouncyball

Reputation: 10781

You can accomplish this with a little data manipulation. Make the data in df2 long, then join to df, then make the data wide again.

The rowid_to_column is necessary to make the transition from long to wide work. You can easily remove that column by adding select(-rowid) at the end of the chain.

library(tidyverse)

df2 %>%
    rowid_to_column() %>%
    pivot_longer(cols = -rowid) %>%
    left_join(df, by = c("value" = "col1")) %>%
    select(-value) %>%
    pivot_wider(names_from = name, values_from = col2)

#   rowid  col1  col2  col3
#   <int> <dbl> <dbl> <dbl>
# 1     1    10    NA    NA
# 2     2     3    NA     8
# 3     3    NA   200    19
# 4     4     4    12    NA
# 5     5     3   112    10
# 6     6     1    NA     1

Upvotes: 1

Related Questions