CODEWITHSUNDEEP
rloopsmerge
catin
catin

Reputation: 401

Merge and create tables using a loop

I tried to search for how to merge and create multiple tables using a loop but couldn't find exactly what I was looking for.

I have four tables: tables 1.1, 1.2, 1.3, and table 2.

Tables 1.1, 1.2, and 1.3 share the same column but have different rows.

Table 2 shares the same first column as tables 1.1, 1.2, and 1.3.

To be specific, the tables look as follows.

table 1.1

A B
1 a
2 b
3 c

table 1.2

A B
4 d
5 e
6 f

table 1.3

A B
7 g
8 h
9 i

table 2

A C
1 x
2 y
3 Z
4 p
5 q
6 r
7 s
8 t
9 u

I would like to merge table 1.1 with table 2, table 1.2 with table2, and table 1.3 with table 2 using a loop so that there are three different tables in the end.

To be specific, I would like to get as follows.

table 1.1'

A B C
1 a x
2 b y
3 c z

table 1.2'

A B C
4 d p
5 e q
6 f r

table 1.3'

A B C
7 g s
8 h t
9 i u

What would be the best way to get this? I would like to know how to use loops in this situation because my actual data contains tables 1.1 to 1.100 and table 2, and I will have to merge 100 times and create 100 tables.

Thanks in advance for all your help!

Upvotes: 2

Views: 232

Answers (1)

akrun
akrun

Reputation: 887213

We may use lapply after placing the 'table1.1', 'table1.2', 'table1.3', ... in a list (with mget) to loop over the list and then do an inner join with merge from base R and return a list

lst1 <- mget(paste0("table1.", 1:100))
# or if there is space in the object names
# lst1 <- mget(paste0("table 1.", 1:100))
lst2 <- lapply(lst1, function(x) merge(x, table2))

Upvotes: 4

Related Questions