How to convert a list of tables to a data frame in R

Question

This question is related to: Count values in the columns separated by ":"

I have following list of tables:

ll = structure(list(`001` = structure(c(1L, 2L, 1L, 1L), .Dim = 4L, .Dimnames = structure(list(
    c("Active", "Com.Tent", "Perform", "Sport_Well")), .Names = ""), class = "table"), 
    `002` = structure(c(1L, 2L, 5L, 2L), .Dim = 4L, .Dimnames = structure(list(
        c("Active", "Com.Tent", "Perform", "Sport_Well")), .Names = ""), class = "table"), 
    `003` = structure(c(2L, 1L, 4L), .Dim = 3L, .Dimnames = structure(list(
        c("Active", "Com.Tent", "Perform")), .Names = ""), class = "table")), .Names = c("001", 
"002", "003"))

ll

$`001`

    Active   Com.Tent    Perform Sport_Well 
         1          2          1          1 

$`002`

    Active   Com.Tent    Perform Sport_Well 
         1          2          5          2 

$`003`

  Active Com.Tent  Perform 
       2        1        4

How can I convert this to following data frame:

user_id  Active Com.tent Perform  Sport_Well
001     1       2       1       1
002     1       2       5       2
003     2       1       4       0

Thanks everyone for your answers. You will agree this is an interesting problem. — rnso
– rnso, Commented Oct 13, 2014 at 14:09

hrbrmstr · Accepted Answer · 2014-10-13 12:56:42Z

4

rbindlist from the data.table package has a fill parameter which can handle varying columns in an rbinded list:

library(data.table)
tmp <- rbindlist(lapply(ll, function(x) as.data.frame.list(x)), fill=TRUE)
tmp$user_id <- names(ll)

##    Active Com.Tent Perform Sport_Well user_id
## 1:      1        2       1          1     001
## 2:      1        2       5          2     002
## 3:      2        1       4         NA     003

edited Oct 13, 2014 at 12:56

answered Oct 13, 2014 at 12:51

hrbrmstr

79.1k11 gold badges146 silver badges209 bronze badges

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

Comments

rnso · Accepted Answer · 2014-10-14 01:38:58Z

4

I found a simple method:

> library(reshape2)
> dcast(melt(ll), L1~Var1)
   L1 Active Com.Tent Perform Sport_Well
1 001      1        2       1          1
2 002      1        2       5          2
3 003      2        1       4         NA

answered Oct 14, 2014 at 1:38

rnso

24.7k26 gold badges127 silver badges270 bronze badges

Comments

A5C1D2H2I1M1N2O1R2T1 · Accepted Answer · 2014-10-13 13:15:56Z

Here's a very manual approach (but one that is generally efficient):

## Get the unique column names needed
colNames <- unique(unlist(lapply(ll, names)))

## Create an empty matrix to hold the data
M <- matrix(0, nrow = length(ll), ncol = length(colNames), 
            dimnames = list(names(ll), colNames))

## Match the matrix column names with the required values
matches <- lapply(ll, function(x) match(names(x), colNames))

## Use matrix indexing to replace the required values
M[cbind(rep(sequence(nrow(M)), sapply(matches, length)),
        unlist(matches))] <- unlist(ll)
M
#     Active Com.Tent Perform Sport_Well
# 001      1        2       1          1
# 002      1        2       5          2
# 003      2        1       4          0

The result is a matrix, so if you wanted a data.frame, you would need as.data.frame.

akrun · Accepted Answer · 2014-10-13 13:13:08Z

2

You could also use unnest from tidyr

 devtools::install_github("hadley/tidyr")
 library(tidyr)
 unnest(lapply(ll, as.data.frame.list), user_id)
 #  user_id Active Com.Tent Perform Sport_Well
 #1     001      1        2       1          1
 #2     002      1        2       5          2
 #3     003      2        1       4         NA

answered Oct 13, 2014 at 13:13

akrun

891k38 gold badges590 silver badges700 bronze badges

Comments

konvas · Accepted Answer · 2014-10-13 14:14:41Z

2

All the other answers work just fine, I just wanted to add the base R solution

max.length  <- max(sapply(ll, length))
ll <- lapply(ll, function(x) {length(x) <- max.length; x})
d <- data.frame(do.call(rbind, ll))
d$user_id <- rownames(d)

If you want to replace NAs by zeros like in your sample output, d[is.na(d)] <- 0 as you suggested yourself :)

edited Oct 13, 2014 at 14:14

answered Oct 13, 2014 at 13:15

konvas

14.4k2 gold badges43 silver badges46 bronze badges

2 Comments

rnso Over a year ago

For the last part of replacing NA with 0, why do you not do: d[is.na(d)]=0 ?

konvas Over a year ago

Hmm you're right.. I know this works for a matrix but I thought I had run into problems when doing that with a data frame before but I probably just remembered wrongly. thanks:)

Rich Scriven · Accepted Answer · 2014-10-14 03:05:33Z

1

dplyr::rbind_all also works.

library(dplyr)    
cbind(user_id = names(ll), rbind_all(lapply(ll, as.data.frame.list)))
#   user_id Active Com.Tent Perform Sport_Well
# 1     001      1        2       1          1
# 2     002      1        2       5          2
# 3     003      2        1       4         NA

edited Oct 14, 2014 at 3:05

answered Oct 14, 2014 at 1:50

Rich Scriven

99.7k11 gold badges190 silver badges252 bronze badges

Collectives™ on Stack Overflow

How to convert a list of tables to a data frame in R

6 Answers 6

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related