How to combine multiple R data frames stored in multiple lists based on a common column?

To combine multiple R data frames stored in multiple lists based on a common column, we can follow the below steps −

• First of all, create two lists of data frames.
• Then, use map2_df function from purrr package and inner_join function from dplyr package to combine the data frames stored in lists based on a common column.

Create the two lists of data frames

Let's create a data frames as shown below −

 Live Demo

df1<-
data.frame(x=sample(LETTERS[1:5],10,replace=TRUE),y1=rpois(10,1),y2=rpois(10,4))
df2<-
data.frame(x=sample(LETTERS[1:5],10,replace=TRUE),y3=rpois(10,2),y4=rpois(10,5))
df3<-
data.frame(x=sample(LETTERS[1:5],10,replace=TRUE),y5=rpois(10,10),y6=rpois(10,1)
)
List1<-list(df1,df2,df3)
List1

On executing, the above script generates the below output(this output will vary on your system due to randomization) −

[[1]]
x y1 y2
1 C 0 3
2 D 0 1
3 A 1 6
4 C 2 1
5 B 1 7
6 A 1 5
7 D 0 1
8 E 1 5
9 B 3 1
10 B 2 7

[[2]]
x y3 y4
1 B 3 8
2 E 1 3
3 E 2 10
4 A 1 5
5 A 2 4
6 C 5 3
7 B 1 6
8 A 2 5
9 E 1 4
10 E 3 2

[[3]]
x y5 y6
1 A 12 0
2 E 10 3
3 D 11 0
4 A 11 2
5 C 6 1
6 E 9 1
7 B 11 3
8 E 9 0
9 E 6 0
10 B 11 1

Again, create another list of data frames as shown below −

 Live Demo

df2<-
data.frame(x=sample(LETTERS[1:3],10,replace=TRUE),y4=rpois(10,1),y5=rpois(10,4))
df3<-
data.frame(x=sample(LETTERS[1:3],10,replace=TRUE),y2=rpois(10,5),y3=rpois(10,2))
df4<-
data.frame(x=sample(LETTERS[1:3],10,replace=TRUE),y1=rpois(10,5),y2=rpois(10,1))
List2<-list(df2,df3,df4)
List2

On executing, the above script generates the below output(this output will vary on your system due to randomization) −

[[1]]
x y4 y5
1 C 1 4
2 A 4 6
3 B 1 3
4 A 2 2
5 A 2 1
6 A 1 6
7 A 0 7
8 C 1 4
9 B 1 9
10 A 2 4

[[2]]
x y2 y3
1 B 7 5
2 B 4 2
3 A 7 2
4 C 6 2
5 C 6 3
6 A 1 0
7 C 4 0
8 A 7 1
9 A 6 5
10 B 2 2

[[3]]
x y1 y2
1 C 7 0
2 A 5 1
3 C 5 0
4 B 9 1
5 B 7 1
6 B 4 0
7 C 2 2
8 C 5 1
9 A 5 0
10 A 7 0

Combine multiple data frames stored in multiple lists based on a common column

Loading purrr and dplyr package then using map2_df function and inner_join function to combine the data frames stored in lists based on a common column −

df1<-
data.frame(x=sample(LETTERS[1:5],10,replace=TRUE),y1=rpois(10,1),y2=rpois(10,4))
df2<-
data.frame(x=sample(LETTERS[1:5],10,replace=TRUE),y3=rpois(10,2),y4=rpois(10,5))
df3<-
data.frame(x=sample(LETTERS[1:5],10,replace=TRUE),y5=rpois(10,10),y6=rpois(10,1)
)
List1<-list(df1,df2,df3)
df2<-
data.frame(x=sample(LETTERS[1:3],10,replace=TRUE),y4=rpois(10,1),y5=rpois(10,4))
df3<-
data.frame(x=sample(LETTERS[1:3],10,replace=TRUE),y2=rpois(10,5),y3=rpois(10,2))
df4<-
data.frame(x=sample(LETTERS[1:3],10,replace=TRUE),y1=rpois(10,5),y2=rpois(10,1))
List2<-list(df2,df3,df4)
library(purrr)
library(dplyr)
map2_df(List1,List2,inner_join,by="x")

Output

x y1 y2 y4 y5 y3.x y3.y y6
1 C 0 3 1 4 NA NA NA
2 C 0 3 1 4 NA NA NA
3 A 1 6 4 6 NA NA NA
4 A 1 6 2 2 NA NA NA
5 A 1 6 2 1 NA NA NA
6 A 1 6 1 6 NA NA NA
7 A 1 6 0 7 NA NA NA
8 A 1 6 2 4 NA NA NA
9 C 2 1 1 4 NA NA NA
10 C 2 1 1 4 NA NA NA
11 B 1 7 1 3 NA NA NA
12 B 1 7 1 9 NA NA NA
13 A 1 5 4 6 NA NA NA
14 A 1 5 2 2 NA NA NA
15 A 1 5 2 1 NA NA NA
16 A 1 5 1 6 NA NA NA
17 A 1 5 0 7 NA NA NA
18 A 1 5 2 4 NA NA NA
19 B 3 1 1 3 NA NA NA
20 B 3 1 1 9 NA NA NA
21 B 2 7 1 3 NA NA NA
22 B 2 7 1 9 NA NA NA
23 B NA 7 8 NA 3 5 NA
24 B NA 4 8 NA 3 2 NA
25 B NA 2 8 NA 3 2 NA
26 A NA 7 5 NA 1 2 NA
27 A NA 1 5 NA 1 0 NA
28 A NA 7 5 NA 1 1 NA
29 A NA 6 5 NA 1 5 NA
30 A NA 7 4 NA 2 2 NA
31 A NA 1 4 NA 2 0 NA
32 A NA 7 4 NA 2 1 NA
33 A NA 6 4 NA 2 5 NA
34 C NA 6 3 NA 5 2 NA
35 C NA 6 3 NA 5 3 NA
36 C NA 4 3 NA 5 0 NA
37 B NA 7 6 NA 1 5 NA
38 B NA 4 6 NA 1 2 NA
39 B NA 2 6 NA 1 2 NA
40 A NA 7 5 NA 2 2 NA
41 A NA 1 5 NA 2 0 NA
42 A NA 7 5 NA 2 1 NA
43 A NA 6 5 NA 2 5 NA
44 A 5 1 NA 12 NA NA 0
45 A 5 0 NA 12 NA NA 0
46 A 7 0 NA 12 NA NA 0
47 A 5 1 NA 11 NA NA 2
48 A 5 0 NA 11 NA NA 2
49 A 7 0 NA 11 NA NA 2
50 C 7 0 NA 6 NA NA 1
51 C 5 0 NA 6 NA NA 1
52 C 2 2 NA 6 NA NA 1
53 C 5 1 NA 6 NA NA 1
54 B 9 1 NA 11 NA NA 3
55 B 7 1 NA 11 NA NA 3
56 B 4 0 NA 11 NA NA 3
57 B 9 1 NA 11 NA NA 1
58 B 7 1 NA 11 NA NA 1
59 B 4 0 NA 11 NA NA 1

Nizamuddin Siddiqui

Updated on: 13-Aug-2021

351 Views

Kickstart Your Career

Get certified by completing the course

Get Started