Canovice
Reputation: 10441
List of list of lists (from API call) into data frame in R
I know questions of this nature (convert lists into dataframes) have been asked before, however I'm running into a particular issue with a nested list of lists that I'd like to convert into a dataframe. The data I'm getting is from an API call in R, hence why I am dealing with this nested list of lists structure. Here's a small example of the API return object I am working with (5 games of sports data):
dput(soccer_data)
list(structure(list(id = 1603158L, league_id = 779L, season_id = 914L,
stage_id = 1810L, round_id = 29156L, group_id = NULL, aggregate_id = NULL,
venue_id = 139L, referee_id = 656L, localteam_id = 607L,
visitorteam_id = 3639L, weather_report = NULL, commentaries = TRUE,
attendance = NULL, pitch = NULL, winning_odds_calculated = FALSE,
formations = structure(list(localteam_formation = "4-2-3-1",
visitorteam_formation = "4-1-4-1"), .Names = c("localteam_formation",
"visitorteam_formation")), scores = structure(list(localteam_score = 5L,
visitorteam_score = 1L, localteam_pen_score = 0L, visitorteam_pen_score = 0L,
ht_score = "1-0", ft_score = "5-1", et_score = NULL), .Names = c("localteam_score",
"visitorteam_score", "localteam_pen_score", "visitorteam_pen_score",
"ht_score", "ft_score", "et_score")), time = structure(list(
status = "FT", starting_at = structure(list(date_time = "2017-03-04 05:30:00",
date = "2017-03-04", time = "05:30:00", timestamp = 1488605400L,
timezone = "UTC"), .Names = c("date_time", "date",
"time", "timestamp", "timezone")), minute = 90L, extra_minute = NULL,
injury_time = NULL), .Names = c("status", "starting_at",
"minute", "extra_minute", "injury_time")), coaches = structure(list(
localteam_coach_id = 429924L, visitorteam_coach_id = 429940L), .Names = c("localteam_coach_id",
"visitorteam_coach_id")), standings = structure(list(localteam_position = NULL,
visitorteam_position = NULL), .Names = c("localteam_position",
"visitorteam_position")), deleted = FALSE), .Names = c("id",
"league_id", "season_id", "stage_id", "round_id", "group_id",
"aggregate_id", "venue_id", "referee_id", "localteam_id", "visitorteam_id",
"weather_report", "commentaries", "attendance", "pitch", "winning_odds_calculated",
"formations", "scores", "time", "coaches", "standings", "deleted"
)), structure(list(id = 1603159L, league_id = 779L, season_id = 914L,
stage_id = 1810L, round_id = 29156L, group_id = NULL, aggregate_id = NULL,
venue_id = 113L, referee_id = 3614L, localteam_id = 577L,
visitorteam_id = 75L, weather_report = NULL, commentaries = FALSE,
attendance = NULL, pitch = NULL, winning_odds_calculated = FALSE,
formations = structure(list(localteam_formation = "4-2-3-1",
visitorteam_formation = "4-2-3-1"), .Names = c("localteam_formation",
"visitorteam_formation")), scores = structure(list(localteam_score = 1L,
visitorteam_score = 1L, localteam_pen_score = 0L, visitorteam_pen_score = 0L,
ht_score = "1-0", ft_score = "1-1", et_score = NULL), .Names = c("localteam_score",
"visitorteam_score", "localteam_pen_score", "visitorteam_pen_score",
"ht_score", "ft_score", "et_score")), time = structure(list(
status = "FT", starting_at = structure(list(date_time = "2017-03-04 22:00:00",
date = "2017-03-04", time = "22:00:00", timestamp = 1488664800L,
timezone = "UTC"), .Names = c("date_time", "date",
"time", "timestamp", "timezone")), minute = 90L, extra_minute = NULL,
injury_time = NULL), .Names = c("status", "starting_at",
"minute", "extra_minute", "injury_time")), coaches = structure(list(
localteam_coach_id = 455860L, visitorteam_coach_id = 176760L), .Names = c("localteam_coach_id",
"visitorteam_coach_id")), standings = structure(list(localteam_position = NULL,
visitorteam_position = NULL), .Names = c("localteam_position",
"visitorteam_position")), deleted = FALSE), .Names = c("id",
"league_id", "season_id", "stage_id", "round_id", "group_id",
"aggregate_id", "venue_id", "referee_id", "localteam_id", "visitorteam_id",
"weather_report", "commentaries", "attendance", "pitch", "winning_odds_calculated",
"formations", "scores", "time", "coaches", "standings", "deleted"
)), structure(list(id = 1603160L, league_id = 779L, season_id = 914L,
stage_id = 1810L, round_id = 29156L, group_id = NULL, aggregate_id = NULL,
venue_id = 28L, referee_id = 555L, localteam_id = 413L, visitorteam_id = 583L,
weather_report = NULL, commentaries = FALSE, attendance = 23554L,
pitch = NULL, winning_odds_calculated = FALSE, formations = structure(list(
localteam_formation = "4-4-1-1", visitorteam_formation = "4-4-2"), .Names = c("localteam_formation",
"visitorteam_formation")), scores = structure(list(localteam_score = 1L,
visitorteam_score = 2L, localteam_pen_score = 0L, visitorteam_pen_score = 0L,
ht_score = "0-0", ft_score = "1-2", et_score = NULL), .Names = c("localteam_score",
"visitorteam_score", "localteam_pen_score", "visitorteam_pen_score",
"ht_score", "ft_score", "et_score")), time = structure(list(
status = "FT", starting_at = structure(list(date_time = "2017-03-05 00:00:00",
date = "2017-03-05", time = "00:00:00", timestamp = 1488672000L,
timezone = "UTC"), .Names = c("date_time", "date",
"time", "timestamp", "timezone")), minute = 90L, extra_minute = NULL,
injury_time = NULL), .Names = c("status", "starting_at",
"minute", "extra_minute", "injury_time")), coaches = structure(list(
localteam_coach_id = 429914L, visitorteam_coach_id = 429917L), .Names = c("localteam_coach_id",
"visitorteam_coach_id")), standings = structure(list(localteam_position = NULL,
visitorteam_position = NULL), .Names = c("localteam_position",
"visitorteam_position")), deleted = FALSE), .Names = c("id",
"league_id", "season_id", "stage_id", "round_id", "group_id",
"aggregate_id", "venue_id", "referee_id", "localteam_id", "visitorteam_id",
"weather_report", "commentaries", "attendance", "pitch", "winning_odds_calculated",
"formations", "scores", "time", "coaches", "standings", "deleted"
)), structure(list(id = 1603161L, league_id = 779L, season_id = 914L,
stage_id = 1810L, round_id = 29156L, group_id = NULL, aggregate_id = NULL,
venue_id = 411L, referee_id = 274L, localteam_id = 1062L,
visitorteam_id = 111L, weather_report = NULL, commentaries = FALSE,
attendance = NULL, pitch = NULL, winning_odds_calculated = FALSE,
formations = structure(list(localteam_formation = "4-2-3-1",
visitorteam_formation = "3-5-2"), .Names = c("localteam_formation",
"visitorteam_formation")), scores = structure(list(localteam_score = 0L,
visitorteam_score = 0L, localteam_pen_score = 0L, visitorteam_pen_score = 0L,
ht_score = "0-0", ft_score = "0-0", et_score = NULL), .Names = c("localteam_score",
"visitorteam_score", "localteam_pen_score", "visitorteam_pen_score",
"ht_score", "ft_score", "et_score")), time = structure(list(
status = "FT", starting_at = structure(list(date_time = "2017-03-05 00:30:00",
date = "2017-03-05", time = "00:30:00", timestamp = 1488673800L,
timezone = "UTC"), .Names = c("date_time", "date",
"time", "timestamp", "timezone")), minute = 90L, extra_minute = NULL,
injury_time = NULL), .Names = c("status", "starting_at",
"minute", "extra_minute", "injury_time")), coaches = structure(list(
localteam_coach_id = 456638L, visitorteam_coach_id = 516577L), .Names = c("localteam_coach_id",
"visitorteam_coach_id")), standings = structure(list(localteam_position = NULL,
visitorteam_position = NULL), .Names = c("localteam_position",
"visitorteam_position")), deleted = FALSE), .Names = c("id",
"league_id", "season_id", "stage_id", "round_id", "group_id",
"aggregate_id", "venue_id", "referee_id", "localteam_id", "visitorteam_id",
"weather_report", "commentaries", "attendance", "pitch", "winning_odds_calculated",
"formations", "scores", "time", "coaches", "standings", "deleted"
)), structure(list(id = 1603162L, league_id = 779L, season_id = 914L,
stage_id = 1810L, round_id = 29157L, group_id = NULL, aggregate_id = NULL,
venue_id = 11573L, referee_id = 370L, localteam_id = 179L,
visitorteam_id = 641L, weather_report = NULL, commentaries = FALSE,
attendance = NULL, pitch = NULL, winning_odds_calculated = FALSE,
formations = structure(list(localteam_formation = "4-2-3-1",
visitorteam_formation = "4-3-1-2"), .Names = c("localteam_formation",
"visitorteam_formation")), scores = structure(list(localteam_score = 1L,
visitorteam_score = 0L, localteam_pen_score = 0L, visitorteam_pen_score = 0L,
ht_score = "0-0", ft_score = "1-0", et_score = NULL), .Names = c("localteam_score",
"visitorteam_score", "localteam_pen_score", "visitorteam_pen_score",
"ht_score", "ft_score", "et_score")), time = structure(list(
status = "FT", starting_at = structure(list(date_time = "2017-03-05 02:00:00",
date = "2017-03-05", time = "02:00:00", timestamp = 1488679200L,
timezone = "UTC"), .Names = c("date_time", "date",
"time", "timestamp", "timezone")), minute = 90L, extra_minute = NULL,
injury_time = NULL), .Names = c("status", "starting_at",
"minute", "extra_minute", "injury_time")), coaches = structure(list(
localteam_coach_id = 524071L, visitorteam_coach_id = 261458L), .Names = c("localteam_coach_id",
"visitorteam_coach_id")), standings = structure(list(localteam_position = NULL,
visitorteam_position = NULL), .Names = c("localteam_position",
"visitorteam_position")), deleted = FALSE), .Names = c("id",
"league_id", "season_id", "stage_id", "round_id", "group_id",
"aggregate_id", "venue_id", "referee_id", "localteam_id", "visitorteam_id",
"weather_report", "commentaries", "attendance", "pitch", "winning_odds_calculated",
"formations", "scores", "time", "coaches", "standings", "deleted"
)))
soccer_data has 5 games of MLS soccer data, and here is what I am currently doing to convert this into a dataframe:
# grab the "scores" info from the nested list $scores (from each game)
season_scores <- data.frame()
for(i in 1:length(soccer_data)) {
game_scores <- as.data.frame(t(unlist(soccer_data[[i]]$scores)), stringsAsFactors = FALSE)
game_scores$date <- as.Date(soccer_data[[i]]$time$starting_at$date)
season_scores <- rbind.fill(season_scores, game_scores)
}
season_scores <- season_scores %>% readr::type_convert()
# create df of the game scores, add the season scores, and drop the bad cols
season_boxscores <- as.data.frame(do.call(rbind, soccer_data), stringsAsFactors = FALSE) %>%
dplyr::select(-one_of(c('scores', 'group_id', 'aggregate_id', 'time', 'standings'))) %>%
cbind(season_scores) %>%
readr::type_convert()
Unfortunately, the issue with this approach is that the very last type_convert() function call doesn't do what I'd like, and the resulting season_boxscores dataframe has columns whose classes are mostly of class == list.
# check yourself
sapply(season_boxscores, class)
My questions then are:
- How can I do this so that the classes of all of the columns in season_boxscores are not all class == list? And also,
- Am I doing this (converting from list of lists of lists) in the best way possible, with the do.call, rbind, and as.data.frame?
Thanks in advance!
EDIT: It would be particularly nice if all of the nested lists (in this case, soccer_data has a few: formations, scores, time, coaches, standings) sort of unnested themselves, in the same way that I unnested them in the for loop for scores.
EDIT 2: Sorry for sharing such a large list object for only 5 games. In a list of lists, or big nested object like this, I actually do not know how to remove the same item from each nested list, which I would've done for this post. (ie remove league_id, round_id, etc. from each of soccer_data[[i]]). If anyone knows how to do that either, would be great to know!
EDIT 3: because soccer_data isnt simply a list of lists, but rather a list of list of lists (with other, non-list objects in each list of lists), none of the solutions here - Force list of lists into dataframe - work on soccer_data.
Upvotes: 3
Views: 648
Answers (2)
Maurits Evers
Reputation: 50718
How about the following base R only approach (using unlist):
Collapse list of list of list to
listofcharvectors:# Collapse list of list of list to list of character vectors lst <- lapply(soccer_data, unlist);Make sure that all list entries have the same keys. For example, only
listentry 3 of your sample data has keyattendance.# Make sure that all list entries have values for the same keys keys <- unique(unlist(lapply(lst, names)));Fill missing key entries with
NA# Fill missing entries with NULL lst <- lapply(lst, function(x) x[match(keys, names(x))]);rbindintodata.frame:# Combind in dataframe df <- do.call(rbind.data.frame, lst); colnames(df) <- keys; df; #id league_id season_id stage_id round_id venue_id referee_id #1 1603158 779 914 1810 29156 139 656 #2 1603159 779 914 1810 29156 113 3614 #3 1603160 779 914 1810 29156 28 555 #4 1603161 779 914 1810 29156 411 274 #5 1603162 779 914 1810 29157 11573 370 #localteam_id visitorteam_id commentaries winning_odds_calculated #1 607 3639 TRUE FALSE #2 577 75 FALSE FALSE #3 413 583 FALSE FALSE #4 1062 111 FALSE FALSE #5 179 641 FALSE FALSE #formations.localteam_formation formations.visitorteam_formation #1 4-2-3-1 4-1-4-1 #2 4-2-3-1 4-2-3-1 #3 4-4-1-1 4-4-2 #4 4-2-3-1 3-5-2 #5 4-2-3-1 4-3-1-2 #scores.localteam_score scores.visitorteam_score scores.localteam_pen_score #1 5 1 0 #2 1 1 0 #3 1 2 0 #4 0 0 0 #5 1 0 0 #scores.visitorteam_pen_score scores.ht_score scores.ft_score time.status #1 0 1-0 5-1 FT #2 0 1-0 1-1 FT #3 0 0-0 1-2 FT #4 0 0-0 0-0 FT #5 0 0-0 1-0 FT #time.starting_at.date_time time.starting_at.date time.starting_at.time #1 2017-03-04 05:30:00 2017-03-04 05:30:00 #2 2017-03-04 22:00:00 2017-03-04 22:00:00 #3 2017-03-05 00:00:00 2017-03-05 00:00:00 #4 2017-03-05 00:30:00 2017-03-05 00:30:00 #5 2017-03-05 02:00:00 2017-03-05 02:00:00 #time.starting_at.timestamp time.starting_at.timezone time.minute #1 1488605400 UTC 90 #2 1488664800 UTC 90 #3 1488672000 UTC 90 #4 1488673800 UTC 90 #5 1488679200 UTC 90 #coaches.localteam_coach_id coaches.visitorteam_coach_id deleted attendance #1 429924 429940 FALSE <NA> #2 455860 176760 FALSE <NA> #3 429914 429917 FALSE 23554 #4 456638 516577 FALSE <NA> #5 524071 261458 FALSE <NA>
If you remove all the excess text/explanations, this is quite short.
Update
Unfortunately, column types are lost as a result of unlist. You can convert factors back to numeric in the following way:
# Smart-convert to numeric
is.num <- apply(df, 2, function(x) {
x <- x[!is.na(x)];
all(suppressWarnings(!is.na(as.numeric(as.character(x)))));
})
df[, is.num] <- apply(df[, is.num], 2, function(x) as.numeric(as.character(x)));
It's a bit messy but works.
Upvotes: 1
twedl
Reputation: 1648
I'm still trying to learn this stuff too. I tested a million things and this was the simplest I could come up with:
library(tidyverse)
soccer_data %>%
map(unlist) %>%
map(t) %>%
map(as_tibble) %>%
bind_rows()
The idea: take your list soccer_data, map unlist to every element (so it unlists at the second level, which means it keeps all the games in separate elements of the top-most list). Then use map transpose t to turn the list to a thing that looks like a row, then convert it to a tibble, then bind_rows them altogether.
The result:
# A tibble: 5 x 30
id league_id season_id stage_id round_id venue_id referee_id localteam_id
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 1603158 779 914 1810 29156 139 656 607
2 1603159 779 914 1810 29156 113 3614 577
3 1603160 779 914 1810 29156 28 555 413
4 1603161 779 914 1810 29156 411 274 1062
5 1603162 779 914 1810 29157 11573 370 179
# ... with 22 more variables: visitorteam_id <chr>, commentaries <chr>,
# winning_odds_calculated <chr>, formations.localteam_formation <chr>,
# formations.visitorteam_formation <chr>, scores.localteam_score <chr>,
# scores.visitorteam_score <chr>, scores.localteam_pen_score <chr>,
# scores.visitorteam_pen_score <chr>, scores.ht_score <chr>, scores.ft_score <chr>,
# time.status <chr>, time.starting_at.date_time <chr>, time.starting_at.date <chr>,
# time.starting_at.time <chr>, time.starting_at.timestamp <chr>,
# time.starting_at.timezone <chr>, time.minute <chr>,
# coaches.localteam_coach_id <chr>, coaches.visitorteam_coach_id <chr>,
# deleted <chr>, attendance <chr>
Does that look right? Good luck!
Upvotes: 3
Related Questions
- In R, convert list from API result into dataframe
- Converting Lists into Data Frames
- Combining multiple lists to a data frame R
- How to parse a list of multiple list and convert it to data frame in R?
- Sourcing Dataframes and looping to create a list/dataframe
- Populating a data frame with lists in R
- Looping through API and putting the results into single a data.frame
- for loops for API calls and then inserting into a single data frame in R
- Adding a list of data frames to one of the variables in a data frame (R)
- Manipulating a List and a List of Lists of Data Frames