I am using R's bigrquery library to load data from a BQ database into R. bigrquery works fine, and a short example of the output I am receiving is as such:
dput(my_df_from_bq)
structure(list(season = c("2019", "2019", "2017", "2018", "2018"
), o_or_d = c("Offense", "Defense", "Offense", "Offense", "Defense"
), chances = list(list(num_ato_chances = 6L, ato_pts_scored = 4L,
ato_ppp = 0.66667, num_ato_chances_pctile = 0.272955974842767,
ato_ppp_pctile = 0.335849056603774), list(num_ato_chances = 7L,
ato_pts_scored = 2L, ato_ppp = 0.28571, num_ato_chances_pctile = 0.534591194968553,
ato_ppp_pctile = 0.913207547169811), list(num_ato_chances = 5L,
ato_pts_scored = 2L, ato_ppp = 0.4, num_ato_chances_pctile = 0.147118921127912,
ato_ppp_pctile = 0.177768696362893), list(num_ato_chances = 1L,
ato_pts_scored = 0L, ato_ppp = 0, num_ato_chances_pctile = 0,
ato_ppp_pctile = 0), list(num_ato_chances = 6L, ato_pts_scored = 8L,
ato_ppp = 1.33333, num_ato_chances_pctile = 0.70093839249286,
ato_ppp_pctile = 0.165646674826601)), dribbles = list(list(
dribbles = 928L, dribbles_pctile = 0.437735849056604), list(
dribbles = 1040L, dribbles_pctile = 0.113207547169811), list(
dribbles = 771L, dribbles_pctile = 0.0469963220269718), list(
dribbles = 735L, dribbles_pctile = 0.00489596083231334),
list(dribbles = 1049L, dribbles_pctile = 0.103223174214606))), row.names = c(NA,
-5L), class = c("tbl_df", "tbl", "data.frame"))
> my_df_from_bq
# A tibble: 5 x 4
season o_or_d chances dribbles
<chr> <chr> <list> <list>
1 2019 Offense <named list [5]> <named list [2]>
2 2019 Defense <named list [5]> <named list [2]>
3 2017 Offense <named list [5]> <named list [2]>
4 2018 Offense <named list [5]> <named list [2]>
5 2018 Defense <named list [5]> <named list [2]>
The table I am loading from BQ contains many nested structs, and as a result, this structure for the dataframe is as expected, as the bigrquery docs themselves indicate that Nested values become list-columns containing named lists.
However, I would like to now flatten this out. You'll notice that the list my_df_from_bq$chances[[1]]$ has values including num_ato_chances, ato_pts_scored, ato_ppp, and more. I would therefore like to flatten out this dataframe, such that the column names are:
...where the list name is concatenated with the values within each list.
You can use unnest_wider but I think it does not unnest multiple columns in one go as referenced in this open Github issue.
library(tidyr)
my_df_from_bq %>%
unnest_wider(chances, names_sep = "_") %>%
unnest_wider(dribbles, names_sep = "_")
# season o_or_d chances_num_ato… chances_ato_pts… chances_ato_ppp chances_num_ato…
# <chr> <chr> <int> <int> <dbl> <dbl>
#1 2019 Offen… 6 4 0.667 0.273
#2 2019 Defen… 7 2 0.286 0.535
#3 2017 Offen… 5 2 0.4 0.147
#4 2018 Offen… 1 0 0 0
#5 2018 Defen… 6 8 1.33 0.701
# … with 3 more variables: chances_ato_ppp_pctile <dbl>, dribbles_dribbles <int>,
# dribbles_dribbles_pctile <dbl>