list of named lists to data.frame

13
votes

I have a list of named lists of the following form from a JSON object: 

my_list = list(list(a = 10, b = "blah"), 
               list(a = 15, b = "stuff"))

Each element of the outer list is a named list and I want to convert it to a data.frame of the following form with the column names intact:

a   b 
10  "blah" 
15  "stuff"

On the surface, I can achieve this by doing to_df = data.frame(do.call(rbind, my_list)).

However, if I were to try to extract an individual column using to_df$a or to_df[,1] I would get a list instead of a vector as normally expected from a data.frame:

> to_df[,1]
[[1]]
[1] 10

[[2]]
[1] 15

Instead of:

> to_df[,1]
[1] 10 15

An old post on the R mailing list suggested the following solution: to_df = as.data.frame(t(sapply(my_list, rbind))). But not only does this not transfer over the column names, it still has the same issue of returning a list instead of a vector when looking at individual columns using to_df[,1].

What's the best way to achieve this? Is there a dplyr way?

EDIT: Thanks for all the solutions, it appears the trick is to lapply and transform each element of the list to a data.frame and then bind them together using dplyr or do.call. Alternatively, data.table does most of the work with a single call to rbindlist.

4
rdplyr
You could try lapply(my_list, data.frame) %>% bind_rows() – talat
You have several viable solutions below. But you should note that to_df = data.frame(do.call(rbind, my_list)) does not appear to give you a data.frame. It appears to give you a list based on the output you show. – Alex W
"... it appears the trick is to lapply and transform each element of the list to a data.frame". This is quite slow. You can do it way faster. – Benjamin Christoffersen

4 Answers

14
votes

I prefer rbindlist from the data.table package. It's simple, fast, and returns a data frame/table.

data.table::rbindlist(my_list)
#     a     b
# 1: 10  blah
# 2: 15 stuff

Another advantage of rbindlist() is that it will automatically fill in missing values with NA.

To remove the data.table class, you can just wrap in as.data.frame()

as.data.frame(data.table::rbindlist(my_list))
7
votes

In base R you may do

df<-do.call(rbind,lapply(my_list,data.frame))
7
votes

It looks like you can do this with bind_rows from the development version of dplyr, dplyr_0.4.2.9002, as of two days ago.

library(dplyr)
bind_rows(my_list)

Source: local data frame [2 x 2]

   a     b
1 10  blah
2 15 stuff
1
votes

Fast pure base R way to do it if the columns are of different types and you want to preserve the types

# sample data
set.seed(46823239)
list_of_lists <- 
  replicate(
    100, list(a = rnorm(100), b = sample.int(100, 100, replace = TRUE), 
              c = factor(sample(letters, 100, replace = TRUE))), 
    simplify = FALSE)
str( # show first two lists
  list_of_lists[1:2])
#R> List of 2
#R>  $ :List of 3
#R>   ..$ a: num [1:100] -0.0439 -0.4487 -0.5682 -0.8062 1.5074 ...
#R>   ..$ b: int [1:100] 59 91 63 87 61 72 92 77 62 41 ...
#R>   ..$ c: Factor w/ 26 levels "a","b","c","d",..: 4 16 5 14 25 17 25 4 4 20 ...
#R>  $ :List of 3
#R>   ..$ a: num [1:100] 0.356 1.239 -0.926 -0.673 -1.168 ...
#R>   ..$ b: int [1:100] 62 21 90 20 41 99 57 6 83 22 ...
#R>   ..$ c: Factor w/ 26 levels "a","b","c","d",..: 15 16 17 6 3 13 21 16 3 11 ...

# define functions to stack
f1 <- function(x){
  . <- function(...){
    args <- list(...)
    if(is.factor(args[[1]]))
      # see https://stackoverflow.com/a/3449403/5861244
      return(factor(do.call(c, lapply(args, as.character))))

    do.call(c, args)
  }

  out <- NULL
  for(i in 1:length(x[[1]]))
    out <- c(out, list(do.call(., lapply(x, "[[", i))))

  out <- data.frame(out)
  names(out) <- names(x[[1]])
  out
}

f2 <- function(x)
  # simple alternative from http://r.789695.n4.nabble.com/Convert-list-of-lists-lt-gt-data-frame-td860048.html
  do.call(rbind, lapply(x, data.frame))

# show output
all.equal( # yields the same
  f1(list_of_lists), f2(list_of_lists))
#R> [1] TRUE
all.equal(
  f1(list_of_lists), data.table::rbindlist(list_of_lists), 
  check.attributes = FALSE)
#R> [1] TRUE
out <- f1(list_of_lists)
head(out, 5)
#R>             a  b c
#R> 1 -0.04391595 59 d
#R> 2 -0.44866652 91 p
#R> 3 -0.56815817 63 e
#R> 4 -0.80622044 87 n
#R> 5  1.50736514 61 y
sapply(out, class)
#R>         a         b         c 
#R> "numeric" "integer"  "factor"

# benchmark
microbenchmark::microbenchmark(
  f1(list_of_lists), f2(list_of_lists), data.table::rbindlist(list_of_lists))
#R> Unit: microseconds
#R>                                  expr       min         lq      mean     median        uq        max neval
#R>                     f1(list_of_lists)  1259.850  1426.3685  1633.127  1531.0590  1643.257   7086.211   100
#R>                     f2(list_of_lists) 31348.099 34293.8720 61224.476 37003.7930 92775.162 153318.869   100
#R>  data.table::rbindlist(list_of_lists)   652.246   786.7645  1040.994   872.6905  1022.221   4063.994   100