Transform a nested list to dataframe

user_12010216 0
Viewed 425

I have a list of items, each of them has two items, one is a list and the other one is a character expression We generate de lists

My_list <- list()
My_list$'product1' <- list()
My_list$'product1'$'sales' <- c(1,2,3)
My_list$'product1'$'model' <- "arima"
My_list$'product2'$'sales' <- c(4,5,6)
My_list$'product2'$'model' <- "prophet"

This is the desired output shape

df1 <- data.frame(product=c("product1"),sales1 = 1, sales2 = 2, sales3 = 3)
df2 <- data.frame(product=c("product2"),sales1 = 4, sales2 = 5, sales3 = 6)
solution <- rbind (df1,df2)

I have tried something like this

solution <- lapply(My_list, function(x) do.call(rbind, lapply(x, as.data.frame)))
solution <- do.call(rbind, Map(cbind, product = names(My_list), My_list))
```7
do.call rbind dataframe r list
6 Answers

Here is a simple version via base R,

as.data.frame(matrix(unlist(My_list), nrow = length(My_list), byrow = TRUE))
#  V1 V2 V3      V4
#1  1  2  3   arima
#2  4  5  6 prophet

You can easily make amendments to fit your expected output (change the names and convert V4 to product1 and product2), i.e.

#save the data frame
d1 <- as.data.frame(matrix(unlist(My_list), nrow = length(My_list), byrow = TRUE))
#Set the column names
d1 <- setNames(d1, c(paste0('sales', seq(ncol(d1) - 1)), 'Product'))
#Change the variable under `Product`
d1$Product <- paste0('Product', seq(nrow(d1)))

d1
#  sales1 sales2 sales3  Product
#1      1      2      3 Product1
#2      4      5      6 Product2
user_56355800

Here is a data.table solution. I added explanation and in-between-results in de code below as comment...

library(data.table)
#bind list, using name as id
DT <- rbindlist( My_list, idcol = "product" )
#     product sales   model
# 1: product1     1   arima
# 2: product1     2   arima
# 3: product1     3   arima
# 4: product2     4 prophet
# 5: product2     5 prophet
# 6: product2     6 prophet

#create rowid's by product-group, used for casting in the next line
DT[, row_id := rowid(product) ]
#     product sales   model row_id
# 1: product1     1   arima      1
# 2: product1     2   arima      2
# 3: product1     3   arima      3
# 4: product2     4 prophet      1
# 5: product2     5 prophet      2
# 6: product2     6 prophet      3

#cast to wide format
dcast( DT, product ~ paste0( "sales", row_id ), value.var = "sales" )
#     product sales1 sales2 sales3
# 1: product1      1      2      3
# 2: product2      4      5      6
user_63562780

In my opinion a quite intuitive and easy to maintain method:

data.frame(product=names(My_list), 
           do.call(rbind, lapply(My_list, FUN=function(x) unlist(x["sales"]))), row.names = NULL)

   product sales1 sales2 sales3
1 product1      1      2      3
2 product2      4      5      6

It iterates through the list of lists using lapply and unlists all sales entries (which automatically names them). Then it rbinds the vectors together using do.call.

Quick way to add model name to the table is to use rapply which by default unlists the result (see ?rapply and the argument how)

data.frame(model=rapply(My_list, f=paste, classes="character"),
           product=names(My_list), 
           do.call(rbind, lapply(My_list, FUN=function(x) unlist(x["sales"]))), row.names = NULL)

    model  product sales1 sales2 sales3
1   arima product1      1      2      3
2 prophet product2      4      5      6
user_71621310

Here a base R solution:

# transpose and fetch the sales arguments putting them in a df
sales <-t(do.call(cbind,
        lapply(My_list, function(x) data.frame(x[names(x)=="sales"]))))

# rename the rows with products
rownames(sales) <- names(My_list)

# rename columns 
colnames(sales) <- paste0("sales",c(1:ncol(sales)))
sales

         sales1 sales2 sales3
product1      1      2      3
product2      4      5      6

In case you need a data.frame with product column:

sales <- data.frame(sales)
sales$product <- rownames(sales)
rownames(sales) <- 1:nrow(sales)
sales
  sales1 sales2 sales3  product
1      1      2      3 product1
2      4      5      6 product2
user_64986500

You can use [[ in lapply to get the first item sales from My_list, which you can rbind with do.call. From the result the colnames are set.

tt <- do.call(rbind, lapply(My_list, "[[", 1))
#tt <- do.call(rbind, lapply(My_list, "[[", "sales")) #Alternative
colnames(tt) <- paste0("sales",seq_len(ncol(tt)))
tt
#         sales1 sales2 sales3
#product1      1      2      3
#product2      4      5      6
user_104885040

A base R option

solution <- cbind(Product = names(My_list),
                  `names<-`(r <- as.data.frame(do.call(rbind,sapply(My_list, `[`,-2)),row.names = FALSE),
                            paste0("Sale",seq(ncol(r)))))

which gives

> solution
   Product Sale1 Sale2 Sale3
1 product1     1     2     3
2 product2     4     5     6
user_121587570
Related