CODEWITHSUNDEEP
r
rnso
rnso

Reputation: 24623

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

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

Upvotes: 4

Views: 1682

Answers (6)

Rich Scriven
Rich Scriven

Reputation: 99391

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

Upvotes: 1

rnso
rnso

Reputation: 24623

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

Upvotes: 4

konvas
konvas

Reputation: 14366

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 :)

Upvotes: 2

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193687

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.

Upvotes: 3

akrun
akrun

Reputation: 887981

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

Upvotes: 2

hrbrmstr
hrbrmstr

Reputation: 78842

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

Upvotes: 4

Related Questions