AS @dimitris_ps mentioned earlier, the answer could be:
do.call(rbind, listHolder)
Since do.call naturally "strips" 1 level of the "list of list", obtaining a list, not a list of lists.
After that, rbind can handle the elements on the list and create a matrix.
Answer from Camilo Abboud on Stack OverflowAS @dimitris_ps mentioned earlier, the answer could be:
do.call(rbind, listHolder)
Since do.call naturally "strips" 1 level of the "list of list", obtaining a list, not a list of lists.
After that, rbind can handle the elements on the list and create a matrix.
The value of nrow needs to be fixed. I fixed your code as follows:
dd <- as.data.frame(matrix(unlist(listHolder), nrow=length(unlist(listHolder[1]))))
I'm working with some large datasets in R consisting of Reddit comments for network analysis. A fairly small example file is available here: https://files.pushshift.io/reddit/comments/sample_data.json .
They're originally JSON files that are compressed, but I've been able to uncompress them, and to convert from JSON objects into R objects. But I'm now having trouble working with the data structure once it's in R. The file is a "large list", made up of 10000 smaller lists, and each smaller list is made up of 20 entries.
I want to convert the nested data to a tidy data frame, but can't quite figure out how to do it, and Google has not been able to solve my problem. Is there a command or script I should use to convert these multiple lists to a single data frame? Thanks for any help!
library(jsonlite)
library(purrr)
library(data.table)
# This isn't strictly json - it's a file where each line is json
# Read data line by line
raw <- readLines("https://files.pushshift.io/reddit/comments/sample_data.json")
# Convert each line from json to a list
json_list <- map(raw, fromJSON)
# Convert each list to a data.table
dt_list <- map(json_list, as.data.table)
# Harness the power of rbind list
dt <- rbindlist(dt_list, fill = TRUE)
# Done
dt
# Here is the same thing in a single pipeline
dt2 <- readLines("https://files.pushshift.io/reddit/comments/sample_data.json") %>%
map(fromJSON) %>%
map(as.data.table) %>%
rbindlist(fill = TRUE)
# Happy to explain any steps - enjoy
I think you can use bind_rows() or bind_cols() depending on whether you want 20 columns or 20 rows. According to their description, they'll take lists: lists of data frames and lists of vectors anyway. I'm not sure about lists of lists. You might have to flatten the internal lists to vectors with unlist() or whatever the tidy version is.
Hope that helps.
I created a list that contains 11356 nested list. I want to convert each of the 11356 to one dataframe with 11356 rows and 18 columns. Each of the 11356 list contains the same numbers of columns (18) (Image 1).
My problem is that some list are showing an error. (Image 2)
After the transformation in one dataframe I want to export this dataframe into excel.
I have read a lot of topics but still haven't found the right answer for my problem.
What about this?
do <- as.data.frame(do.call(rbind, lapply(my.stuff, as.vector)))
do <- cbind(my.var=rownames(do), do)
do[do == "NULL"] <- NA
Result
> do
my.var my.col1 my.col2 my.col3 my.col4
AA AA 1 4 NA NA
BB BB NA NA NA NA
CC CC 13 8 2 10
DD DD NA NA -5 7
Edit:
If we don't want lists as column objects as @akrun reasonably suggests, we could do it this way:
u <- as.character(unlist(my.stuff, recursive=FALSE))
u[u == "NULL"] <- NA
do <- matrix(as.integer(u), nrow=4, byrow=TRUE,
dimnames=list(NULL, names(my.stuff[[1]])))
do <- data.frame(my.var=names(my.stuff), do, stringsAsFactors=FALSE)
Test:
> all.equal(str(do), str(desired.object))
'data.frame': 4 obs. of 5 variables:
my.col1: int 1 NA 13 NA
$ my.col2: int 4 NA 8 NA
$ my.col3: int NA NA 2 -5
$ my.col4: int NA NA 10 7
'data.frame': 4 obs. of 5 variables:
my.col1: int 1 NA 13 NA
$ my.col2: int 4 NA 8 NA
$ my.col3: int NA NA 2 -5
$ my.col4: int NA NA 10 7
[1] TRUE
We can use a recursive map
library(tidyverse)
map_df(my.stuff, ~ map_df(.x, ~
replace(.x, is.null(.x), NA)), .id = "my.var")
# A tibble: 4 x 5
# my.var my.col1 my.col2 my.col3 my.col4
# <chr> <dbl> <dbl> <dbl> <dbl>
#1 AA 1 4 NA NA
#2 BB NA NA NA NA
#3 CC 13 8 2 10
#4 DD NA NA -5 7
With rbind
do.call(rbind.data.frame, your_list)
Edit: Previous version return data.frame of list's instead of vectors (as @IanSudbery pointed out in comments).
Update July 2020:
The default for the parameter stringsAsFactors is now default.stringsAsFactors() which in turn yields FALSE as its default.
Assuming your list of lists is called l:
df <- data.frame(matrix(unlist(l), nrow=length(l), byrow=TRUE))
The above will convert all character columns to factors, to avoid this you can add a parameter to the data.frame() call:
df <- data.frame(matrix(unlist(l), nrow=132, byrow=TRUE),stringsAsFactors=FALSE)
I couldn't reproduce your example, but what you're trying to do is simple. You could use do.call to call the rbind function on the list and what you get at the end is a pretty dataframe.
list <- getOptionChain("AAPL", "2019/2021")
data <- do.call(rbind, list)
It might be more flexible to work with a couple functions from dplyr and purrr. dplyr::bind_rows can take a list of data frames, and they can have different names, whereas the base rbind just works on 2 data frames at once. bind_rows also has an argument .id that will create a column of list item names. purrr::map_dfr calls a function over a list and returns a data frame of them all row-bound together; because it wraps around bind_rows, it also has an .id argument.
Having access to setting those IDs is helpful because you have 2 sets of IDs: one of dates, and one of calls vs puts. Setting one ID within the inner bind_rows and one within map_dfr gets both.
Written out with a function, to make it a little easier to see:
library(quantmod)
AAPL.2015 <- getOptionChain("AAPL", "2019/2021")
aapl_df <- purrr::map_dfr(AAPL.2015, function(d) {
dplyr::bind_rows(d, .id = "type")
}, .id = "date")
head(aapl_df)
#> date type Strike Last Chg Bid Ask Vol OI
#> 1 Sep.27.2019 calls 140 79.50 9.50 77.60 77.90 10 30
#> 2 Sep.27.2019 calls 145 75.85 0.00 72.70 73.30 NA 28
#> 3 Sep.27.2019 calls 150 72.22 0.00 67.85 67.90 10 91
#> 4 Sep.27.2019 calls 155 52.53 0.00 65.80 69.90 NA 10
#> 5 Sep.27.2019 calls 160 60.10 0.00 57.85 58.15 2 11
#> 6 Sep.27.2019 calls 165 54.40 15.95 52.65 52.90 9 16
Or in more common dplyr piping with function shorthand notation:
library(dplyr)
aapl_df <- AAPL.2015 %>%
purrr::map_dfr(~bind_rows(., .id = "type"), .id = "date")
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))
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