Merge three data sets with tidyr::unite

Question

I have 3 data as like this

D1

 Name  Item  January Type
 E001  Phone  45.6    T
 S253  Car    765.7   -
 Q78   Wood   65.1    T

D2

 Name  Item   March  May  Type
 E001  Phone  5.6    23    F
 S253  Car    65.7   78    F
 Q78   Wood   5.1    965   -

D3

 Name  Item   Type
 E001  Phone  A
 S253  Car    A
 Q78   Wood   A

and need a output as like this

 Name  Item  January March   May     Type
 E001  Phone  45.6    5.6     23    T/F/A
 S253  Car    765.7   65.7    78    -/F/A
 Q78   Wood   65.1    5.1     965   T/-/A

for this I tried of this command

merge(D1,D2,D3, by = c("Name","Item"))#%>% tidyr::unite(type, dplyr::starts_with("Type"), sep = "/", na.rm = TRUE)

But it is not serving the purpose

Functions of the merge/join family only applies to **2** dataframes. You can't merge(D1, D2, D3) all at once. If you really want to do so, try `Reduce(function(x, y) merge(x, y, by = c("Name", "Item")), list(D1, D2, D3))`. Also, you may want to read this [post](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right?rq=1). — ekoam, Dec 09 '20 at 17:46

score 0 · Answer 1 · answered Dec 09 '20 at 17:50

This will work:

library(dplyr)
library(tidyr)

D1 <- 
  data.frame(
    Name = c("E001", "S253"),
    Item = c("Phone", "Car"),
    January = c(45.6, 765),
    Type = c("T", "-")
  )

D2 <- 
  data.frame(
    Name = c("E001", "S253"),
    Item = c("Phone", "Car"),
    March = c(424, 465),
    May = c(49, 76),
    Type = c("F", "F")
  )

D3 <- 
  data.frame(
    Name = c("E001", "S253"),
    Item = c("Phone", "Car"),
    Type = c("A", "A")
  )

D1 %>%
  left_join(D2, by = c("Name", "Item")) %>%
  left_join(D3, by = c("Name", "Item")) %>%
  unite("Type", starts_with("Type"), sep = "/")

Merge three data sets with tidyr::unite

1 Answers1