Merging different data frames depending on column value

Question

I have a data frame df1

df1<- data.frame(ID = c("A","B","A","A","B"),CLASS = c(1,1,2,1,4))
 ID CLASS
1  A     1
2  B     1
3  A     2
4  A     1
5  B     4

and another two data frames A and B

   > A<- data.frame(CLASS = c(1,2,3), DESCRIPTION = c("Unknown", "Tall", "Short"))
  CLASS DESCRIPTION
1     1     Unknown
2     2        Tall
3     3       Short

> B <- data.frame(CLASS = c(1,2,3,4), DESCRIPTION = c("Big", "Small", "Medium", "Very Big"))
  CLASS DESCRIPTION
1     1         Big
2     2       Small
3     3      Medium
4     4    Very Big

I want to merge these three data frames depending on the ID and class of df1 to have something like this:

      ID CLASS DESCRIPTION
1  A     1     Unknown
2  B     1         Big
3  A     2        Tall
4  A     1     Unknown
5  B     4    Very Big

I know I can merge it as df1 <- merge(df1, A, by = "CLASS") but I can't find a way to add the conditional (maybe an "if" is too much) to also merge B according to the ID. I need to have an efficient way to do this as I am applying it to over 2M rows.

Could you explain row 5 from df1? How does it end up with CLASS = 4? — coffeinjunky
– coffeinjunky, Commented Jul 31, 2018 at 21:12
@coffeinjunky Sorry, it was meant to be 4 in df1. Already edited. — janf
– janf, Commented Jul 31, 2018 at 21:14

bouncyball · Accepted Answer · 2018-07-31 21:20:51Z

2

Add the ID variable to A and B, rbind A and B together, and use ID and CLASS to merge:

A$ID = 'A'
B$ID = 'B'

AB <- rbind(A, B)

merge(df1, AB, by = c('ID', 'CLASS'))

  ID CLASS DESCRIPTION
1  A     1     Unknown
2  A     1     Unknown
3  A     2        Tall
4  B     1         Big
5  B     4    Very Big

I would suggest using stringsAsFactors = FALSE when creating the data:

df1 <- data.frame(ID = c("A","B","A","A","B"),CLASS = c(1,1,2,1,4),
                  stringsAsFactors = FALSE)
A <- data.frame(CLASS = c(1,2,3), 
                DESCRIPTION = c("Unknown", "Tall", "Short"),
                stringsAsFactors = FALSE)
B <- data.frame(CLASS = c(1,2,3,4), 
                DESCRIPTION = c("Big", "Small", "Medium", "Very Big"),
                stringsAsFactors = FALSE)

answered Jul 31, 2018 at 21:20

bouncyball

10.8k23 silver badges32 bronze badges

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

Comments

coffeinjunky · Accepted Answer · 2018-07-31 21:31:52Z

To merge multiple dataframes in one go, Reduce is often helpful:

out <- Reduce(function(x,y) merge(x,y, by = "CLASS", all.x=T), list(df1, A, B))
out
  CLASS ID DESCRIPTION.x DESCRIPTION.y
1     1  A       Unknown           Big
2     1  B       Unknown           Big
3     1  A       Unknown           Big
4     2  A          Tall         Small
5     4  B          <NA>      Very Big

As you can see, columns that were present in all dataframes were added a suffix (default merge behavior). This allows you to apply whatever logic you want to get the final column you wish for. For instance,

out$Description <- ifelse(out$ID == "A", as.character(out$DESCRIPTION.x), as.character(out$DESCRIPTION.y))
> out
  CLASS ID DESCRIPTION.x DESCRIPTION.y Description
1     1  A       Unknown           Big     Unknown
2     1  B       Unknown           Big         Big
3     1  A       Unknown           Big     Unknown
4     2  A          Tall         Small        Tall
5     4  B          <NA>      Very Big    Very Big

Note that ifelse is vectorized and quite efficient.

moodymudskipper · Accepted Answer · 2018-07-31 22:03:32Z

1

A dplyr solution:

library(dplyr)
bind_rows(lst(A,B),.id="ID") %>% inner_join(df1)
#   ID CLASS DESCRIPTION
# 1  A     1     Unknown
# 2  A     1     Unknown
# 3  A     2        Tall
# 4  B     1         Big
# 5  B     4    Very Big

answered Jul 31, 2018 at 22:03

moodymudskipper

47.7k12 gold badges131 silver badges185 bronze badges

Collectives™ on Stack Overflow

Merging different data frames depending on column value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related