Transform a nested list to dataframe

Question

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

Sotos · Accepted Answer · 2020-03-10 12:24:52Z

3

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

edited Mar 10, 2020 at 12:24

answered Mar 10, 2020 at 11:23

Sotos

51.6k6 gold badges35 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wimpel · Accepted Answer · 2020-03-10 10:57:36Z

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

henrik_ibsen · Accepted Answer · 2020-03-11 06:49:20Z

2

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

edited Mar 11, 2020 at 6:49

answered Mar 10, 2020 at 12:07

henrik_ibsen

8231 gold badge8 silver badges16 bronze badges

2 Comments

Lucas Over a year ago

How can I cbind "model" to the data frame?

henrik_ibsen Over a year ago

See edit. I've included an example with rapply. It assumes that the only character class in My_list are the model names.

s__ · Accepted Answer · 2020-03-10 11:11:39Z

1

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

edited Mar 10, 2020 at 11:11

answered Mar 10, 2020 at 11:01

s__

9,5333 gold badges30 silver badges48 bronze badges

Comments

GKi · Accepted Answer · 2020-03-10 12:21:39Z

1

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

edited Mar 10, 2020 at 12:21

answered Mar 10, 2020 at 12:16

GKi

40k3 gold badges36 silver badges56 bronze badges

Comments

ThomasIsCoding · Accepted Answer · 2020-03-10 11:10:02Z

0

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

answered Mar 10, 2020 at 11:10

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

Collectives™ on Stack Overflow

Transform a nested list to dataframe

6 Answers 6

Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related