You can also use (at least v1.9.3) of rbindlist in the data.table package:
library(data.table)
rbindlist(mylist, fill=TRUE)
## Hit Project Year Rating Launch ID Dept Error
## 1: True Blue 2011 4 26 Jan 2012 19 1, 2, 4 NA
## 2: False NA NA NA NA NA NA Record not found
## 3: True Green 2004 8 29 Feb 2004 183 6, 8 NA
You can also use (at least v1.9.3) of rbindlist in the data.table package:
library(data.table)
rbindlist(mylist, fill=TRUE)
## Hit Project Year Rating Launch ID Dept Error
## 1: True Blue 2011 4 26 Jan 2012 19 1, 2, 4 NA
## 2: False NA NA NA NA NA NA Record not found
## 3: True Green 2004 8 29 Feb 2004 183 6, 8 NA
You could create a list of data.frames:
dfs <- lapply(mylist, data.frame, stringsAsFactors = FALSE)
Then use one of these:
library(plyr)
rbind.fill(dfs)
or the faster
library(dplyr)
bind_rows(dfs) # in earlier versions: rbind_all(dfs)
In the case of dplyr::bind_rows, I am surprised that it chooses to use "" instead of NA for missing data. If you remove stringsAsFactors = FALSE, you will get NA but at the cost of a warning... So suppressWarnings(rbind_all(lapply(mylist, data.frame))) would be an ugly but fast solution.
Converting elements of a nested list into individual data frames
[Q] an efficient way to convert lists nested within a list to data.frame?
Problems with Column headers in converting a nested list into a dataframe
r - Extracting from Nested list to data frame - Stack Overflow
I have a deeply nested list like this:
results <- list()
results[[1]] <- list()
results[[2]] <- list()
results[[1]][["records"]] <- list()
results[[1]][["records"]][[1]] <- list(
"ID" = "askdhgk",
"vars" = list(
"V1" = "Value 2",
"V2" = "Value 3"
)
)
results[[1]][["records"]][[2]] <- list(
"ID" = "adsfaw3w",
"vars" = list(
"V1" = "Value 5",
"V2" = "Value 6"
)
)
results[[2]][["records"]][[1]] <- list(
"ID" = "rtyu",
"vars" = list(
"V1" = "Value 8",
"V2" = "Value 9"
)
)
results[[2]][["records"]][[2]] <- list(
"ID" = "324564",
"vars" = list(
"V1" = "Value 11",
"V2" = "Value 12"
)
)I want a dataframe, and I've written this code to get it to the format I want:
library(dplyr)
for(i in seq_along(results)){
for(n in seq_along(results[[i]][["records"]])){
# overwrite the the record list with a dataframe of records
results[[i]][["records"]][[n]] <- data.frame(as.list(unlist(results[[i]][["records"]][[n]])))
}
# overwrite the record entry with one dataframe of all records
results[[i]] <- dplyr::bind_rows(results[[i]][["records"]])
}
# return a dataframe of all the records
df1 <- dplyr::bind_rows(results)I can't help but feel like this is overly complicated and not 'R-like' way of achieving this, but can't find a one-liner that does it. Any ideas?
Hi all,
Say I have a list object, where each element is a 'person' who is identifiable by their ID number. Then within each person is another list object which contains the actual vector of data.
Example: say I have ID numbers for person "AA001", "BB001", "CC001"
my_list = list( AA001 = list(x = 1:5, y = 1:5), BB001 = list(x = -1:-5, y = -1:-5), CC001 = list(x = rep(99, 5), y = rep(0, 5)) ) print(my_list)
My desired output is something like
my_df = data.frame(
ID = rep(c("AA001", "BB001", "CC001"), each = 5),
x = c(1:5, -1:-5, rep(99, 5)),
y = c(1:5, -1:-5, rep(0, 5))
)
print(my_df)If possible, I would prefer efficiency over readability as I will be running some simulations with thousands for IDs and thousands of runs.
As a side question, the way my_list is structured. Is that even a good way to store data?
Thank you in advance!
Hi all,
I am trying to convert a nested list (tibbles) output from a db query into a regular data frame or matrix.
The nested list information is here.
db query output as nested listI have found various ways to turn the elements of the nested list into columns in the df.
The difficulty is having each tibble's name assigned as the column header. Usually, with all the approaches, R puts as the column header …."value", "value.1" and "value.2".
I have gotten closest in an approach that resulted in the following as column headers...
“Lab_Test.value”, “LS.value”, “TW_Analyte_Comment.value”
I would like the column headers to be the names of the nested tibbles or “Lab_Test”, “LS” and “TW_Analyte_Comment”.
I would guess my error is that I am not referring to the proper part of each of the nested lists in the approach I take to do the conversion.
Any ideas?
Jose
Maybe this can help
library(dplyr)
library(tidyr)
a <- unlist(a)
df <- data.frame(a=a, b=names(a)) %>% mutate(key=cumsum(b=="experience.duration")) %>%
split(.$key) %>% lapply(function(x) x %>% select(-key) %>% spread(b, a)) %>%
do.call(rbind, .) %>% t %>% data.frame
df$key <- rownames(df)
Then you can filter in on the rows of interest
The above would be equivalent to
rbind(unlist(a)[1:8], unlist(a)[9:16],unlist(a)[17:24]) %>% t
Update
try this for dput2
a <- unlist(dput2)
library(dplyr)
library(tidyr)
dummydf <- data.frame(b=c("experience.start", "experience.end", "experience.roleName", "experience.summary",
"experience.org", "experience.org.name", "experience.org.url",
"_meta.weight", "_meta._sources._origin", "experience.duration"), key=1:10)
df <- data.frame(a=a, b=names(a))
df2 <- left_join(df, dummydf)
df2$key2 <- as.factor(cumsum(df2$key < c(0, df2$key[-length(df2$key)])) +1)
df_split <- split(df2, df2$key2)
df3 <- lapply(df_split, function(x){
x %>% select(-c(key, key2)) %>% spread(b, a)
}) %>% data.table::rbindlist(fill=TRUE) %>% t
df3 <- data.frame(df3)
i <- sapply(seq_along(dput2), function(y) rep(y, sapply(dput2, function(x) length(x))[y])) %>% unlist
names(df3) <- paste0(names(df3), "_", i)
View(df3)
Managed to figure something out, using dput3 above:
a <- dput3
aa <- lapply(1:length(a), function(y){tryCatch(lapply(1:length(a[[y]]),
function(i){if(is.null(a[[y]][[i]]$experience$start)){"Null"}else{a[[y]][[i]]$experience$start}}),error=function(e) print(list()))})
for(i in 1:length(aa)){for(y in 1:length(aa[[i]])){tryCatch(for(z in length(aa[[i]][[y]]))
{test <- rbind(test, data.frame(key = i, key2= y))},error=function(e) print(0))}}
aaa <- unlist(aa)
df <- data.frame(a=aaa)
df2 <- cbind(df, test)
i <- sapply(seq_along(aa), function(y) rep(y, sapply(aa, function(x) length(x))[y])) %>% unlist
df5 <- data.frame(dates = df2$a)
df5 <- t(df5)
df5 <- data.frame(df5)
names(df5) <- paste0(names(df5), "_", i)
df5[] <- lapply(df5[], as.character)
l1 <- lapply(split(stack(df5), as.numeric(sub('.*_', '', stack(df5)[,2]))), '[', 1)
df6 <- t(do.call(cbindPad, l1))
df6 <- data.frame(df6)
Will try and expand it so it works with more than one vertical (as currently in aa I isolate start)
Borrowing from Spacedman and flodel here, we can define the following pair of recursive functions:
library(tidyverse) # I use dplyr and purrr here, plus tidyr further down below
depth <- function(this) ifelse(is.list(this), 1L + max(sapply(this, depth)), 0L)
bind_at_any_depth <- function(l) {
if (depth(l) == 2) {
return(bind_rows(l))
} else {
l <- at_depth(l, depth(l) - 2, bind_rows)
bind_at_any_depth(l)
}
}
We can now bind any arbitrary depth list into a single data.frame:
bind_at_any_depth(x)
# A tibble: 2 × 2 a b <dbl> <dbl> 1 1 2 2 3 4
bind_at_any_depth(x_ext) # From P Lapointe
# A tibble: 5 × 2 a b <dbl> <dbl> 1 1 2 2 5 6 3 7 8 4 1 2 5 3 4
If you want to keep track of the origin of each row, you can use this version:
bind_at_any_depth2 <- function(l) {
if (depth(l) == 2) {
l <- bind_rows(l, .id = 'source')
l <- unite(l, 'source', contains('source'))
return(l)
} else {
l <- at_depth(l, depth(l) - 2, bind_rows, .id = paste0('source', depth(l)))
bind_at_any_depth(l)
}
}
This will add a source column:
bind_at_any_depth2(x_ext)
# A tibble: 5 × 3 source a b * <chr> <dbl> <dbl> 1 X_x_1 1 2 2 X_y_z 5 6 3 X_y_zz 7 8 4 Y_x_1 1 2 5 Y_y_1 3 4
Note: At some point you can use purrr::depth, and will need to change at_depth to modify_depth when their new version rolls out to CRAN (thanks @ManuelS).
UPDATE
Here's a way to flatten more deeply nested lists simply with unlist. Since the structure is now uneven, the result will not be a data.frame.
x_ext <- list(X = list(x = list(a = 1,
b = 2),
y = list(z=list(a = 5,
b = 6),
zz=list(a = 7,
b = 8))),
Y = list(x = list(a = 1,
b = 2),
y = list(a = 3,
b = 4)))
unlist(x_ext)
X.x.a X.x.b X.y.z.a X.y.z.b X.y.zz.a X.y.zz.b Y.x.a Y.x.b Y.y.a Y.y.b
1 2 5 6 7 8 1 2 3 4
My initial answer was unlist first and rbind aftrerwards. However, it works only with the example in the question.
x_unlist <- unlist(x, recursive = FALSE)
do.call("rbind", x_unlist)
a b
X.x 1 2
X.y 3 4
Y.x 1 2
Y.y 3 4
Hi all,
I am trying to convert a nested list (tibbles) output from a db query into a regular data frame or matrix.
The nested list information is here.
db query output as nested listI have found various ways to turn the elements of the nested list into columns in the df.
The difficulty is having each tibble's name assigned as the column header. Usually, with all the approaches, R puts as the column header …."value", "value.1" and "value.2".
I have gotten closest in an approach that resulted in the following as column headers...
“Lab_Test.value”, “LS.value”, “TW_Analyte_Comment.value”
I would like the column headers to be the names of the nested tibbles or “Lab_Test”, “LS” and “TW_Analyte_Comment”.
I would guess my error is that I am not referring to the proper part of each of the nested lists in the approach I take to do the conversion.
Any ideas?
Jose
Like this?
library(tibble)
library(tidyr)
enframe(l) %>%
unnest(value) %>%
unnest_wider(value)
name x y ...3 ...4
<int> <dbl> <dbl> <dbl> <dbl>
1 1 1 2 3 4
2 1 3 4 5 6
3 2 1 2 3 4
4 3 2 3 4 5
This is a bit awkward because of the inconsisteny nesting levels, but we could write a recursive function to extract the lists that have "x" in their name. For example
find_x <- function(x) {
if (is.list(x) && !"x" %in% names(x)) {
return(do.call("rbind", lapply(x, find_x)))
} else if ( !is.list(x)) {
return(NULL)
} else {
return(x)
}
}
find_x(l)
# x y
# [1,] 1 2 3 4
# [2,] 3 4 5 6
# [3,] 1 2 3 4
# [4,] 2 3 4 5
You can change the "x" part to whatever marker you have for your own data of interest