Merge two lists of data frames

Question

I have two lists of data frames. Each list has 6 data frames. The dataframes has the same columns, but in list1 the dataframes has info from 2015 to 2017 and list2 has info of 2018. Like below

List1$A

Name Value  Year
AAA  123   2015
BBB  456   2016
CCC  789   2017
AAA  543   2018

List2$A

Name Value  Year
AAA  543    2018
BBB  248    2018

I want to merge the dataframes from both lists. So I want in the end just one list of dataframes with all the info for all years.

Some dataframes from list1 has already info of 2018, so when I merge them with the others I want those 2018 values to be replaced.

Newlist$A

Name Value  Year
AAA  123    2015
BBB  456    2016
CCC  789    2017
AAA  543    2018
BBB  248    2018

I tried this but didn't work

data<- lapply(list1,list2, function (x,y) merge(x,y))

How can I do this?

Answer 1

It's always helpful to include a sample of data with dput, but here's an attempt without the data's confirmation:

library(tidyverse)

map2(list1, list2, ~bind_rows(.y, .x) %>% group_by(Name, Year) %>% slice(1))

We bind the rows (with list2 first), then grouping by Name and Year and taking the first occurrence with slice, which should take the first value for any Name/Year repeated measures from the 2nd data frame.

Answer 2

We could first bind everything into a long data frame and remove the entries for "2018" that first occur if there's an entry in list 2.

To do this we could list the lists and rbind them after adding an ID column that later helps to remove the duplicates of year "2018" that stem from list 1 with by/ave, but keep those which don't occur in list 2.

The trick of the latter is to us a rev(seq_along(x)).

To demonstrate I have created sample data that probably resembles your data.

# list the lists
L <- list(L1=L1, L2=L2)

# add id column to sublists
L <- lapply(seq(L), function(x) 
  Map(`[<-`, L[[x]], "list", value=substr(names(L)[x], 2, 2)))

# rbind lists to long data frame
d <- do.call(rbind, unlist(L, recursive=FALSE))

# remove 2018 duplicates of list L1, keep if no 2018 in list L2
do.call(rbind, by(d, d$name, function(y) {
  i <- cbind(y, id=ave(y$year, y$year, FUN=function(z) rev(seq_along(z))))
  i[!i$id == 2, ]
  }))

Result

#        name value year list id
# A.A.1     A   998 2015    1  1
# A.A.4     A   456 2016    1  1
# A.A.7     A   312 2017    1  1
# A.A.13    A   478 2018    2  1
# B.A.2     B  1592 2015    1  1
# B.A.5     B  1072 2016    1  1
# B.A.8     B   673 2017    1  1
# B.A.21    B   445 2018    2  1
# C.A.3     C   957 2015    1  1
# C.A.6     C   199 2016    1  1
# C.A.9     C  2165 2017    1  1
# C.A.31    C   342 2018    2  1
# D.B.1     D   877 2015    1  1
# D.B.4     D   876 2016    1  1
# D.B.7     D   482 2017    1  1
# D.B.13    D  1077 2018    2  1
# E.B.2     E   370 2015    1  1
# E.B.5     E  1475 2016    1  1
# E.B.8     E   768 2017    1  1
# E.B.11    E   385 2018    1  1  <- this stems from list 1!
# F.B.3     F   421 2015    1  1
# F.B.6     F   930 2016    1  1
# F.B.9     F  1105 2017    1  1
# F.B.31    F  1836 2018    2  1

Data

l1 <- list(A = structure(list(name = structure(c(1L, 2L, 3L, 1L, 2L, 
3L, 1L, 2L, 3L, 1L, 2L, 3L), .Label = c("A", "B", "C"), class = "factor"), 
    value = c(1371, 565, 363, 633, 404, 106, 1512, 95, 2018, 
    63, 1305, 2287), year = c(2015L, 2015L, 2015L, 2016L, 2016L, 
    2016L, 2017L, 2017L, 2017L, 2018L, 2018L, 2018L)), class = "data.frame", row.names = c(NA, 
-12L)), B = structure(list(name = structure(c(1L, 2L, 3L, 1L, 
2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L), .Label = c("D", "E", "F"), class = "factor"), 
    value = c(1389, 279, 133, 636, 284, 2656, 2440, 1320, 307, 
    1781, 172, 1215), year = c(2015L, 2015L, 2015L, 2016L, 2016L, 
    2016L, 2017L, 2017L, 2017L, 2018L, 2018L, 2018L)), class = "data.frame", row.names = c(NA, 
-12L)))

L2 <- list(A = structure(list(name = structure(1:3, .Label = c("A", 
"B", "C"), class = "factor"), value = c(1895, 430, 257), year = c(2018, 
2018, 2018)), class = "data.frame", row.names = c(NA, -3L)), 
    B = structure(list(name = structure(c(1L, 3L), .Label = c("D", 
    "E", "F"), class = "factor"), value = c(1763, 640), year = c(2018, 
    2018)), row.names = c(1L, 3L), class = "data.frame"))

L2$B <- L2$B[-2, ]  # remove intentionally value