How to sort out this data using multiple dataframes in R

January 25, 2023

Here is a short sample of data frames:

dat1<-read.table(text="ID1  Score
12  2
10  3
14  4
",h=T)
dat2<-read.table(text="ID2  Value   Time    Age
22  2   12  60
24  4   80  44
14  6   18  45
16  8   88  40
",h=T)
dat3<-read.table(text="ID1  ID2 Class   Color   Status
10  24  M   B   P
14  16  N   P   Q
12  14  N   P   Q
19  16  M   P   Q
",h=T)

We have three data frames. The following table is expected. If dat1 and dat2 are seen in dat3 for each row, the data of dat1 and dat2 are entered in the table.

ID1 ID2 Class   Color   Status  Score   Value   Time    Age
10  24  M   B   P   3   4   80  44
14  16  N   P   Q   4   8   88  40
12  14  N   P   Q   2   6   18  45
19  16  M   P   Q   NA  8   88  40

>Solution :

Place it in a list and do a join

library(dplyr)
library(purrr)
list(dat3, dat2, dat1) %>%
   reduce(left_join)

-output

  ID1 ID2 Class Color Status Value Time Age Score
1  10  24     M     B      P     4   80  44     3
2  14  16     N     P      Q     8   88  40     4
3  12  14     N     P      Q     6   18  45     2
4  19  16     M     P      Q     8   88  40    NA

Or use join_all