Merging multiple pairs of data-frame in R

Question

I am looking to merge multiple pairs of data frames. For example I have following two set of data frames Con_2014, Con_2015, Con_2016 and Income_2014, Income_2015, Income_2016. So I want to merge data for each year like the following but at once.

Con_Income_2014 <- merge(Income_2014,Con_2014)
Con_Income_2015 <- merge(Income_2015,Con_2015)
Con_Income_2016 <- merge(Income_2016,Con_2016)`

I tried the following way but it does not work.

for (i in 1: length(years)){
  assign(paste("Con_Income", as.character(years[i]),sep =""),merge(paste("Income_",as.character(years[i]),sep=""),paste("Cons_",as.character(years[i]),sep="")))}

It runs but it give me just name of each pairs. Its merging strings(the name of the file but not the data)

Could you provide a minimal set of your dataframes using `dput`? — Maël, Dec 06 '21 at 15:16
Have a look at the `get()` function. It allows you to call a variable, by its name in character. By the way, you don't have to use `as.character()` for integers. `R` converts it into character by default. — maydin, Dec 06 '21 at 15:20
Possibly this helps you: https://stackoverflow.com/questions/8091303/simultaneously-merge-multiple-data-frames-in-a-list — Albin, Dec 06 '21 at 15:25
@Albin, No this does not help much. If I am right, they are merging multiple files into one where as in my case I want to merge two files, and then merge another two files and continue for other pair of files. So here there will be multiple output merged files where as in that case there will be only one output merged files. — Vishnu Kant, Dec 06 '21 at 16:02
Combined with Albin's excellent link, see [How to make a list of data frames](https://stackoverflow.com/questions/17499013/how-do-i-make-a-list-of-data-frames). Putting all your data frames in a `list` will make whatever operations you want to do much easier. If, for example you had a `con` list and an `income` list, you would use `con_income = mapply(merge, con, income)` to get a list of results. — Gregor Thomas, Dec 06 '21 at 16:03

GuedesBF · Answer 1 · 2021-12-06T16:20:15.887

*Observation. I believe the last object should be con_... and not cons_... (a typo)

We do not neet for loops. If all years are associated with a pair of dataframes, and the naming is consistent, we can use mget(ls(pattern = ....)) to create two lists of dataframes, then do the pairwise merge with mapply:

mapply(merge,
       mget(ls(pattern = "Income_20\\d{2}")),
       mget(ls(pattern = "Con_20\\d{2}")),
       SIMPLIFY = FALSE) %>%
    setNames(paste0('income_con_', 2014:2016))

Data created by @DaveArmstrong:

set.seed(1)

Income_2014 = data.frame(id = 1:5, x = runif(5,-1,1))
Income_2015 = data.frame(id = 6:10, x = runif(5,-1,1))
Income_2016 = data.frame(id = 11:15, x = runif(5,-1,1))
Cons_2014 = data.frame(id = 1:5, y = runif(5,2,3))
Cons_2015 = data.frame(id = 6:10, y = runif(5,2,3))
Cons_2016 = data.frame(id = 11:15, y = runif(5,2,3))

Output:

$income_con_2014
  id          x        y
1  1 -0.4689827 2.497699
2  2 -0.2557522 2.717619
3  3  0.1457067 2.991906
4  4  0.8164156 2.380035
5  5 -0.5966361 2.777445

$income_con_2015
  id          x        y
1  6  0.7967794 2.934705
2  7  0.8893505 2.212143
3  8  0.3215956 2.651674
4  9  0.2582281 2.125555
5 10 -0.8764275 2.267221

$income_con_2016
  id          x        y
1 11 -0.5880509 2.386114
2 12 -0.6468865 2.013390
3 13  0.3740457 2.382388
4 14 -0.2317926 2.869691
5 15  0.5396828 2.340349

score 0 · Accepted Answer · answered Dec 06 '21 at 16:07

It's not the prettiest thing, but it works:

Income_2014 = data.frame(id = 1:5, x = runif(5,-1,1))
Income_2015 = data.frame(id = 6:10, x = runif(5,-1,1))
Income_2016 = data.frame(id = 11:15, x = runif(5,-1,1))
Cons_2014 = data.frame(id = 1:5, y = runif(5,2,3))
Cons_2015 = data.frame(id = 6:10, y = runif(5,2,3))
Cons_2016 = data.frame(id = 11:15, y = runif(5,2,3))
years <- 2014:2016
for (i in 1: length(years)){
  assign(paste("Cons_Income", as.character(years[i]),sep =""),
    merge(eval(parse(text=paste("Income_",as.character(years[i]),sep=""))),                  
          eval(parse(text=paste("Cons_",as.character(years[i]),sep="")))))
}

Merging multiple pairs of data-frame in R

2 Answers2