How join multiple dataframes with complementary information-CodePudding

I have the following dataframes: df1:

Animal Farm Year    y  
1      A      1    23
2      A      2    24    
3      B      1    13   
4      B      3    28 
5      C      1    25

df2

Farm Year Month  C   Mm  Pp
 A      1    1    23  15  4
 A      1    2    25  18  5
 A      1    3    13  35  7
 A      1    4    18  38  4

df3

Farm Year Month  C   Mm  Pp
 B      1    1    12  13  3
 B      1    2    22  11  2
 B      1    3    14  17  5
 B      1    4    18  38  6

df4

 Farm Year Month  C   Mm  Pp
     C      3    1    12  13  3
     C      3    2    22  11  2
     C      3    3    14  17  5
     C      3    4    18  38  6

I want the following for all animals and farms present in df1: ex:

df5

 Animal Farm  Year  month  C Mm  Pp y
    1   A      1    1    23  15  4 23
    1   A      1    2    25  18  5 23
    1   A      1    3    13  35  7 23
    1   A      1    4    18  38  4 23
    3   B      1    1    12  13  3 13
    3   B      1    2    22  11  2 13
    3   B      1    3    14  17  5 13
    3   B      1    4    18  38  6 13

I tried

df1 %>% full_join(df2, by= c("farm", "year")) %>%
full_join(df3, by = c("farm, "year", "month")) %>%
full_join(df4, by = c("farm, "year", "month")) --> df5

but it did't work correctly. So, I also tried:

library(gtools)

df5 <- smartbind(df1,df2, df3, df4)

CodePudding user response：

Here a way to go with data.table:

library(data.table)

df5 = merge(df1, rbindlist(list(df2,df3,df4)), by = c("Farm", "Year"))

Output:

> df5
   Farm Year Animal  y Month  C Mm Pp
1:    A    1      1 23     1 23 15  4
2:    A    1      1 23     2 25 18  5
3:    A    1      1 23     3 13 35  7
4:    A    1      1 23     4 18 38  4
5:    B    1      3 13     1 12 13  3
6:    B    1      3 13     2 22 11  2
7:    B    1      3 13     3 14 17  5
8:    B    1      3 13     4 18 38  6