dplyr join with three data frame

Question

I have 3 data frames as like this

 df1 <- structure(list(Vehicle = c("Car1", "Car2", "Car8"), Year = c(20L, 
21L, 20L), type = c("A", "A", "A")), class = "data.frame", row.names = c(NA, -3L)) 

df2 <- structure(list(Vehicle = c("Car1", "Car2", "Car7"), Year = c(20L, 
 21L, 90L), type = c("M", "M", "M")), class = "data.frame", row.names = c(NA, -3L))

df3 <- structure(list(Vehicle = c("Car1", "Car2", "Car9"), Year = c(20L, 
             21L, 92L), type = c("I", "I", "I")), class = "data.frame", row.names = c(NA, -3L))

And I need to make a new table as follows

Vehicle  Year type
Car1      20  A/M/I
Car2      21  A/M/I
Car7      90   M
Car8      20   A
Car9      92   I

for this purpose I used this code using dplyr as like this, but it is not working with 3 data frames:

 dplyr::full_join(df1, df2, df3, by = c('Vehicle', 'Year')) %>%
 tidyr::unite(type, type.x, type.y, sep = '/', na.rm = TRUE)

have you checked this https://stackoverflow.com/questions/32066402/how-to-perform-multiple-left-joins-using-dplyr-in-r/32066419 — AlexB, Oct 14 '20 at 12:38

score 1 · Answer 1 · answered Oct 14 '20 at 12:35

Generally, to merge >2 data.frame's/tibble's you'd use either base R's Reduce or purrr::reduce; for example using the latter:

list(df1, df2, df3) %>% 
    purrr::reduce(dplyr::full_join, by = c("Vehicle", "Year")) %>% 
    tidyr::unite(type, dplyr::starts_with("type"), sep = "/", na.rm = TRUE)
#  Vehicle Year  type
#1    Car1   20 A/M/I
#2    Car2   21 A/M/I
#3    Car8   20     A
#4    Car7   90     M
#5    Car9   92     I

Duck · Accepted Answer · 2020-10-14T12:55:55.097

Try this approach. Instead of merging it looks like you want to combine all dataframes and then aggregate. Here the code using dplyr:

library(dplyr)
#Code
newdf <- bind_rows(df1,df2,df3) %>%
  group_by(Vehicle,Year) %>%
  summarise(type=paste0(type,collapse='|'))

Output:

# A tibble: 5 x 3
# Groups:   Vehicle [5]
  Vehicle  Year type 
  <chr>   <int> <chr>
1 Car1       20 A|M|I
2 Car2       21 A|M|I
3 Car7       90 M    
4 Car8       20 A    
5 Car9       92 I

score 1 · Answer 3 · answered Oct 14 '20 at 13:03

1

Using base R

aggregate(type ~ Vehicle + Year, rbind(df1, df2, df3) ,
        FUN = paste, collapse="|")

-output

# Vehicle Year  type
#1    Car1   20 A|M|I
#2    Car8   20     A
#3    Car2   21 A|M|I
#4    Car7   90     M
#5    Car9   92     I

answered Oct 14 '20 at 13:03

akrun

874,273
37
540
662

dplyr join with three data frame

3 Answers3