Merging specific lists of lists together in R

Question

I have a list of lists of lists of lists (I know, a lot of lists, in total around 6000 DataFrames). The first list specifies the startmonth (January through December), the second one specifies the year (2002 - 2018), the third one contains the different sectors (for example Discretionary or Consumer Staples, in total 10) and the last one the quantile (1 through 5).
To be more clear: sector_prtf[["StartMonth"]][["Year"]][["Sector"]][["Quantile"]]
Here is an example of how the DataFrames look:

sector_prtf[[1]][[1]][[1]][[1]]
              Growth quantile                 Sector
2002-01-31 0.2278331        1 Consumer Discretionary  

sector_prtf[[1]][[1]][[2]][[1]]
               Growth quantile           Sector
2002-01-31 0.09700046        1 Consumer Staples  

sector_prtf[[1]][[2]][[1]][[1]]
               Growth quantile                 Sector
2003-01-31 -0.1081433        1 Consumer Discretionary  

sector_prtf[[2]][[1]][[1]][[1]]
              Growth quantile                 Sector
2002-02-28 0.3596547        1 Consumer Discretionary

The goal is to merge the lists together in a way that the sectors are together with the quantile and the corresponding startdate.

              Growth quantile                 Sector
2002-01-31 0.2278331        1 Consumer Discretionary  
               Growth quantile                 Sector
2003-01-31 -0.1081433        1 Consumer Discretionary  
              Growth quantile                 Sector
2004-01-30 0.6446954        1 Consumer Discretionary  
.
.
.
              Growth quantile                 Sector
2017-01-31 0.1824898        1 Consumer Discretionary

Like I mentioned this should be done for every sector and every startdate.

I tried to merge the lists together by performing simple rbinds:

merged_sector <- lapply(sector_prtf, function(a) lapply(a, function(b) lapply(b, function(c) do.call("rbind", c))))
merged_sector <- lapply(merged_sector, function(a) lapply(a, function(b) do.call("rbind", b)))
merged_sector <- lapply(merged_sector, function(a) do.call("rbind", a))
merged_sector <- do.call("rbind", merged_sector)

After that the merged lists look like:

.
.
.
2012-01-319   -1.030502e-02        1              Materials
2012-01-3117   3.039239e-02        2              Materials
2012-01-3127   6.278972e-02        3              Materials
2012-01-3110   1.150880e-01        1            Real Estate
2012-01-3118   9.337119e-02        2            Real Estate
2012-01-3128   3.242025e-02        3            Real Estate
2012-01-3119   6.044756e-02        1              Utilities
2012-01-31110  1.154916e-01        2              Utilities
2012-01-3129   1.156366e-01        3              Utilities
2013-01-31     2.797345e-01        1 Consumer Discretionary
2013-01-311    1.875079e-01        2 Consumer Discretionary
2013-01-312    3.652037e-01        3 Consumer Discretionary
.
.
.

My idea was now to filter the merged DF by sector and quantile, but the dates are a huge problem (unique rownames).
Is there maybe a simpler way to solve this problem? Thanks in advance

*Update: Here's a link to the requested dput file. It includes only the first startmonth (January): https://ufile.io/y80fb

**Edit 2: I'm sorry for the inconvenience I caused with not providing a reproducible example.

list(list(list(structure(list(Growth = 0.227833070205427, quantile = structure(1L, .Label = "1", class = "factor"), 
    Sector = structure(1L, .Label = "Consumer Discretionary", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
    structure(list(Growth = 0.00580189434527657, quantile = structure(1L, .Label = "2", class = "factor"), 
        Sector = structure(1L, .Label = "Consumer Discretionary", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
    structure(list(Growth = 0.280654630370414, quantile = structure(1L, .Label = "3", class = "factor"), 
        Sector = structure(1L, .Label = "Consumer Discretionary", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.0970004606893047, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Consumer Staples", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.054821203483339, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Consumer Staples", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.00837169953085215, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Consumer Staples", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = -0.078767963284149, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Energy", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.069104950106169, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Energy", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.27207135756175, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Energy", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.009642535558954, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Financials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.0117244867054771, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Financials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.185284889832411, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Financials", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.239390715659085, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Health Care", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.0162271493055311, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Health Care", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.067303679327545, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Health Care", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.0620349870410483, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Industrials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.0821803720980501, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Industrials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.137729664907273, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Industrials", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = -0.0843930112785794, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Information Technology", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.172018997118367, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Information Technology", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.298718947065689, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Information Technology", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.0170747596874905, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Materials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.190415482682349, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Materials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.221341415148432, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Materials", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.168638361539387, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Real Estate", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.0810611988754563, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Real Estate", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.0365040437639329, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Real Estate", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
    list(structure(list(Growth = 0.111350872628164, quantile = structure(1L, .Label = "1", class = "factor"), 
        Sector = structure(1L, .Label = "Utilities", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = -0.0978660942657028, quantile = structure(1L, .Label = "2", class = "factor"), 
            Sector = structure(1L, .Label = "Utilities", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
        structure(list(Growth = 0.112770511307641, quantile = structure(1L, .Label = "3", class = "factor"), 
            Sector = structure(1L, .Label = "Utilities", class = "factor")), class = "data.frame", row.names = "2002-01-31"))))

Can you show a small example with `dput` and update the post so that it is reproducible — akrun, Nov 21 '18 at 20:30
Putting raw data on other sites that we need to download isn't helpful. See [how to make a good reproducible](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). It doesn't have to be your real data, just something we can copy/paste from the question to make it easier to help. — MrFlick, Nov 21 '18 at 20:47
I was given an excel file, with nine different sheets containing stock prices, Price to book, price earnings etc. I had to create a ranking for each stock every year based on those sheets and create an object from those containing the stock prices. Calculate log ret, % growth and again combine them (in the corresponing quantile group) to a portfolio. I ended up with this because I wantet to split not only for quantile, but also for start month and sector — Nico, Nov 22 '18 at 09:43

Anders Ellern Bilgrau · Accepted Answer · 2018-11-21T21:08:14.513

I wrote a recursive Reduce which should do what you want. So please read ?Reduce carefully.

Assuming your list of lists is named ll:

rReduce <- function(x) {
  y <- Reduce("rbind", x)
  if (is.list(y)) {
    return(rReduce(y))
  } else {
    return(x)
  }
}

res <- rReduce(ll)
print(res)
#                   Growth quantile                 Sector
#2002-01-31    0.227833070        1 Consumer Discretionary
#2002-01-311   0.097000461        1       Consumer Staples
#2002-01-312  -0.078767963        1                 Energy
#2002-01-313   0.009642536        1             Financials
#2002-01-314   0.239390716        1            Health Care
#2002-01-315   0.062034987        1            Industrials
#2002-01-316  -0.084393011        1 Information Technology
#...

This is equivalent to

Reduce("rbind", Reduce("rbind", Reduce("rbind", ll)))

if I am not mistaken. Now, you still have the date issue in the rownames, but that is easily solve by something like:

res$Date <- as.Date(substr(rownames(res), 1, 10))
rownames(res) <- NULL
print(res)
#         Growth quantile                 Sector       Date
#1   0.227833070        1 Consumer Discretionary 2002-01-31
#2   0.097000461        1       Consumer Staples 2002-01-31
#3  -0.078767963        1                 Energy 2002-01-31
#4   0.009642536        1             Financials 2002-01-31
#5   0.239390716        1            Health Care 2002-01-31
#6   0.062034987        1            Industrials 2002-01-31
#7  -0.084393011        1 Information Technology 2002-01-31
#8   0.017074760        1              Materials 2002-01-31

The data used in the above is:

ll <- list(list(list(structure(list(Growth = 0.227833070205427, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Consumer Discretionary", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.00580189434527657, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Consumer Discretionary", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.280654630370414, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Consumer Discretionary", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.0970004606893047, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Consumer Staples", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.054821203483339, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Consumer Staples", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.00837169953085215, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Consumer Staples", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = -0.078767963284149, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Energy", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.069104950106169, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Energy", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.27207135756175, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Energy", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.009642535558954, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Financials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.0117244867054771, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Financials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.185284889832411, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Financials", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.239390715659085, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Health Care", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.0162271493055311, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Health Care", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.067303679327545, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Health Care", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.0620349870410483, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Industrials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.0821803720980501, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Industrials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.137729664907273, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Industrials", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = -0.0843930112785794, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Information Technology", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.172018997118367, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Information Technology", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.298718947065689, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Information Technology", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.0170747596874905, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Materials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.190415482682349, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Materials", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.221341415148432, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Materials", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.168638361539387, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Real Estate", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.0810611988754563, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Real Estate", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.0365040437639329, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Real Estate", class = "factor")), class = "data.frame", row.names = "2002-01-31")), 
                list(structure(list(Growth = 0.111350872628164, quantile = structure(1L, .Label = "1", class = "factor"), 
                                    Sector = structure(1L, .Label = "Utilities", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = -0.0978660942657028, quantile = structure(1L, .Label = "2", class = "factor"), 
                                    Sector = structure(1L, .Label = "Utilities", class = "factor")), class = "data.frame", row.names = "2002-01-31"), 
                     structure(list(Growth = 0.112770511307641, quantile = structure(1L, .Label = "3", class = "factor"), 
                                    Sector = structure(1L, .Label = "Utilities", class = "factor")), class = "data.frame", row.names = "2002-01-31"))))

Merging specific lists of lists together in R

1 Answers1