merge multiple data.frames [r]

Question

There is a list l1 of data.frames:

head(lapply(l1,head,n=3),3)
[[1]]
   nu_pregao    pcVar
1       2371 7.224848
45      2372 2.797704
89      2373 3.947368

[[2]]
   nu_pregao    pcVar
2       2371 4.055709
46      2372 2.944882
90      2373 3.507937

[[3]]
   nu_pregao    pcVar
3       2371 4.011461
47      2372 3.679907
91      2373 4.693034

If one uses Reduce to merge them

l2=Reduce(function(x,y) merge(x,y, by='nu_pregao'),l1)
There were 41 warnings (use warnings() to see them)

gets a sequence of warnings like this:

1: In merge.data.frame(x, y, by = "nu_pregao") :
  column names ‘pcVar.x’, ‘pcVar.y’ are duplicated in the result

The result is ok, the only problem are the duplicated names. Is there a way to avoid this?
I´ve seen question How to merge multiple data.frames and sum and average columns at the same time in R but it seems it does rbind instead of merge.

You can change the name of `pcVar` in the data sets before merging. — svenhalvorson, Oct 19 '20 at 14:44
You can change the name e.g. with: `l1 <- lapply(seq_along(l1), function(i) {names(l1[[i]])[2] <- paste0("pcVar", i); l1[[i]]})` and then use `merge` in `Reduce`. — GKi, Oct 19 '20 at 15:53

Edo · Accepted Answer · 2020-10-19T17:04:45.767

What about something like this:

l2 <- Reduce(function(x, n) merge(x, l1[[n]], by='nu_pregao', suffixes = c("", n)),
             seq(2, length(l1)), init = l1[[1]])
l2
#>   nu_pregao    pcVar   pcVar2   pcVar3
#> 1      2371 7.224848 4.055709 4.011461
#> 2      2372 2.797704 2.944882 3.679907
#> 3      2373 3.947368 3.507937 4.693034

Final touch for names consistency:

names(l2)[match("pcVar", names(l2))] <- "pcVar1"
l2
#>   nu_pregao   pcVar1   pcVar2   pcVar3
#> 1      2371 7.224848 4.055709 4.011461
#> 2      2372 2.797704 2.944882 3.679907
#> 3      2373 3.947368 3.507937 4.693034

Your data:

l1 <- list(read.table(text = "nu_pregao    pcVar
1       2371 7.224848
45      2372 2.797704
89      2373 3.947368", header = TRUE),

read.table(text = "nu_pregao    pcVar
2       2371 4.055709
46      2372 2.944882
90      2373 3.507937", header = TRUE),

read.table(text = "nu_pregao    pcVar
3       2371 4.011461
47      2372 3.679907
91      2373 4.693034", header = TRUE))

merge multiple data.frames [r]

1 Answers1