Frank B.
Reputation: 1873
Flatten Lists in JSON file with R
I have a JSON file from a data provider in the following format (at the end of post).
I can use jsonlite to read the JSON into R but the last to columns are lists and I can't figure out how to unpack the lists. The second to last column is null and the last column is a list with 15 entries.
example <- fromJSON('example.json')
summary(example)
ScoreId Season SeasonType Week Day DateTime
Min. :16878 Min. :2018 Min. :1 Min. :16 Length:16 Length:16
1st Qu.:16882 1st Qu.:2018 1st Qu.:1 1st Qu.:16 Class :character Class :character
Median :16886 Median :2018 Median :1 Median :16 Mode :character Mode :character
Mean :16886 Mean :2018 Mean :1 Mean :16
3rd Qu.:16889 3rd Qu.:2018 3rd Qu.:1 3rd Qu.:16
Max. :16893 Max. :2018 Max. :1 Max. :16
...
PregameOdds.Length PregameOdds.Class PregameOdds.Mode
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
0 -none- list
LiveOdds.Length LiveOdds.Class LiveOdds.Mode
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
15 data.frame list
I'm not wedded to jsonlite though it seems to be the preferred library.
File:
[{
"ScoreId": 16881,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-22T00:00:00",
"DateTime": "2018-12-22T16:30:00",
"AwayTeamId": 35,
"HomeTeamId": 34,
"AwayTeamName": "WAS",
"HomeTeamName": "TEN",
"GlobalGameId": 16881,
"GlobalAwayTeamId": 35,
"GlobalHomeTeamId": 34,
"HomeTeamScore": 28,
"AwayTeamScore": 18,
"TotalScore": 46,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 95703,
"Sportsbook": null,
"ScoreId": 16881,
"Created": "2018-12-22T19:04:18",
"Updated": "2018-12-22T19:24:55",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 0.0,
"AwayPointSpread": 0.0,
"HomePointSpreadPayout": -281,
"AwayPointSpreadPayout": 238,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16889,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-22T00:00:00",
"DateTime": "2018-12-22T20:20:00",
"AwayTeamId": 3,
"HomeTeamId": 29,
"AwayTeamName": "BAL",
"HomeTeamName": "LAC",
"GlobalGameId": 16889,
"GlobalAwayTeamId": 3,
"GlobalHomeTeamId": 29,
"HomeTeamScore": 11,
"AwayTeamScore": 24,
"TotalScore": 36,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 95933,
"Sportsbook": null,
"ScoreId": 16889,
"Created": "2018-12-22T23:12:29",
"Updated": "2018-12-22T23:24:59",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 13.0,
"AwayPointSpread": -13.0,
"HomePointSpreadPayout": 132,
"AwayPointSpreadPayout": -172,
"OverUnder": 36.6,
"OverPayout": 117,
"UnderPayout": -147
}]
}, {
"ScoreId": 16878,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 7,
"HomeTeamId": 8,
"AwayTeamName": "CIN",
"HomeTeamName": "CLE",
"GlobalGameId": 16878,
"GlobalAwayTeamId": 7,
"GlobalHomeTeamId": 8,
"HomeTeamScore": 29,
"AwayTeamScore": 20,
"TotalScore": 49,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 98674,
"Sportsbook": null,
"ScoreId": 16878,
"Created": "2018-12-23T14:22:38",
"Updated": "2018-12-23T15:54:51",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": -20.8,
"AwayPointSpread": 20.8,
"HomePointSpreadPayout": -153,
"AwayPointSpreadPayout": 119,
"OverUnder": 40.0,
"OverPayout": -148,
"UnderPayout": 116
}]
}, {
"ScoreId": 16879,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 33,
"HomeTeamId": 9,
"AwayTeamName": "TB",
"HomeTeamName": "DAL",
"GlobalGameId": 16879,
"GlobalAwayTeamId": 33,
"GlobalHomeTeamId": 9,
"HomeTeamScore": 30,
"AwayTeamScore": 22,
"TotalScore": 52,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 99556,
"Sportsbook": null,
"ScoreId": 16879,
"Created": "2018-12-23T15:49:51",
"Updated": "2018-12-23T16:09:54",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": -15.2,
"AwayPointSpread": 15.2,
"HomePointSpreadPayout": -362,
"AwayPointSpreadPayout": 258,
"OverUnder": 45.6,
"OverPayout": 118,
"UnderPayout": -148
}]
}, {
"ScoreId": 16880,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 20,
"HomeTeamId": 11,
"AwayTeamName": "MIN",
"HomeTeamName": "DET",
"GlobalGameId": 16880,
"GlobalAwayTeamId": 20,
"GlobalHomeTeamId": 11,
"HomeTeamScore": 10,
"AwayTeamScore": 30,
"TotalScore": 40,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 99448,
"Sportsbook": null,
"ScoreId": 16880,
"Created": "2018-12-23T15:36:07",
"Updated": "2018-12-23T16:19:49",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 16.3,
"AwayPointSpread": -16.3,
"HomePointSpreadPayout": 200,
"AwayPointSpreadPayout": -231,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16882,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 23,
"HomeTeamId": 14,
"AwayTeamName": "NYG",
"HomeTeamName": "IND",
"GlobalGameId": 16882,
"GlobalAwayTeamId": 23,
"GlobalHomeTeamId": 14,
"HomeTeamScore": 31,
"AwayTeamScore": 30,
"TotalScore": 61,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 99596,
"Sportsbook": null,
"ScoreId": 16882,
"Created": "2018-12-23T15:58:43",
"Updated": "2018-12-23T16:09:54",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 0.0,
"AwayPointSpread": 0.0,
"HomePointSpreadPayout": -273,
"AwayPointSpreadPayout": 233,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16883,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 15,
"HomeTeamId": 19,
"AwayTeamName": "JAX",
"HomeTeamName": "MIA",
"GlobalGameId": 16883,
"GlobalAwayTeamId": 15,
"GlobalHomeTeamId": 19,
"HomeTeamScore": 7,
"AwayTeamScore": 19,
"TotalScore": 27,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 99402,
"Sportsbook": null,
"ScoreId": 16883,
"Created": "2018-12-23T15:28:43",
"Updated": "2018-12-23T15:59:57",
"HomeMoneyLine": 169,
"AwayMoneyLine": -236,
"DrawMoneyLine": null,
"HomePointSpread": 2.8,
"AwayPointSpread": -2.8,
"HomePointSpreadPayout": 139,
"AwayPointSpreadPayout": -178,
"OverUnder": 28.7,
"OverPayout": 130,
"UnderPayout": -165
}]
}, {
"ScoreId": 16884,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 4,
"HomeTeamId": 21,
"AwayTeamName": "BUF",
"HomeTeamName": "NE",
"GlobalGameId": 16884,
"GlobalAwayTeamId": 4,
"GlobalHomeTeamId": 21,
"HomeTeamScore": 27,
"AwayTeamScore": 13,
"TotalScore": 40,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 98928,
"Sportsbook": null,
"ScoreId": 16884,
"Created": "2018-12-23T14:50:56",
"Updated": "2018-12-23T16:09:54",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": -19.7,
"AwayPointSpread": 19.7,
"HomePointSpreadPayout": 160,
"AwayPointSpreadPayout": -206,
"OverUnder": 42.3,
"OverPayout": -120,
"UnderPayout": -135
}]
}, {
"ScoreId": 16885,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 12,
"HomeTeamId": 24,
"AwayTeamName": "GB",
"HomeTeamName": "NYJ",
"GlobalGameId": 16885,
"GlobalAwayTeamId": 12,
"GlobalHomeTeamId": 24,
"HomeTeamScore": 42,
"AwayTeamScore": 49,
"TotalScore": 92,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 99828,
"Sportsbook": null,
"ScoreId": 16885,
"Created": "2018-12-23T16:37:09",
"Updated": "2018-12-23T16:59:50",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 0.0,
"AwayPointSpread": 0.0,
"HomePointSpreadPayout": 145,
"AwayPointSpreadPayout": -189,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16886,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 13,
"HomeTeamId": 26,
"AwayTeamName": "HOU",
"HomeTeamName": "PHI",
"GlobalGameId": 16886,
"GlobalAwayTeamId": 13,
"GlobalHomeTeamId": 26,
"HomeTeamScore": 36,
"AwayTeamScore": 33,
"TotalScore": 69,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 99727,
"Sportsbook": null,
"ScoreId": 16886,
"Created": "2018-12-23T16:19:15",
"Updated": "2018-12-23T16:24:59",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 0.0,
"AwayPointSpread": 0.0,
"HomePointSpreadPayout": -786,
"AwayPointSpreadPayout": 579,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16887,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T13:00:00",
"AwayTeamId": 2,
"HomeTeamId": 5,
"AwayTeamName": "ATL",
"HomeTeamName": "CAR",
"GlobalGameId": 16887,
"GlobalAwayTeamId": 2,
"GlobalHomeTeamId": 5,
"HomeTeamScore": 11,
"AwayTeamScore": 27,
"TotalScore": 38,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 98708,
"Sportsbook": null,
"ScoreId": 16887,
"Created": "2018-12-23T14:24:55",
"Updated": "2018-12-23T16:14:49",
"HomeMoneyLine": 113,
"AwayMoneyLine": -158,
"DrawMoneyLine": null,
"HomePointSpread": 2.8,
"AwayPointSpread": -2.8,
"HomePointSpreadPayout": -112,
"AwayPointSpreadPayout": -142,
"OverUnder": 49.0,
"OverPayout": -116,
"UnderPayout": -137
}]
}, {
"ScoreId": 16888,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T16:05:00",
"AwayTeamId": 32,
"HomeTeamId": 1,
"AwayTeamName": "LAR",
"HomeTeamName": "ARI",
"GlobalGameId": 16888,
"GlobalAwayTeamId": 32,
"GlobalHomeTeamId": 1,
"HomeTeamScore": 10,
"AwayTeamScore": 34,
"TotalScore": 45,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 100259,
"Sportsbook": null,
"ScoreId": 16888,
"Created": "2018-12-23T18:37:44",
"Updated": "2018-12-23T19:09:59",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 25.4,
"AwayPointSpread": -25.4,
"HomePointSpreadPayout": -274,
"AwayPointSpreadPayout": 233,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16890,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T16:05:00",
"AwayTeamId": 6,
"HomeTeamId": 31,
"AwayTeamName": "CHI",
"HomeTeamName": "SF",
"GlobalGameId": 16890,
"GlobalAwayTeamId": 6,
"GlobalHomeTeamId": 31,
"HomeTeamScore": 10,
"AwayTeamScore": 15,
"TotalScore": 25,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 100317,
"Sportsbook": null,
"ScoreId": 16890,
"Created": "2018-12-23T19:00:17",
"Updated": "2018-12-23T19:19:59",
"HomeMoneyLine": 1350,
"AwayMoneyLine": -2819,
"DrawMoneyLine": null,
"HomePointSpread": null,
"AwayPointSpread": null,
"HomePointSpreadPayout": null,
"AwayPointSpreadPayout": null,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16891,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T16:25:00",
"AwayTeamId": 28,
"HomeTeamId": 22,
"AwayTeamName": "PIT",
"HomeTeamName": "NO",
"GlobalGameId": 16891,
"GlobalAwayTeamId": 28,
"GlobalHomeTeamId": 22,
"HomeTeamScore": 34,
"AwayTeamScore": 31,
"TotalScore": 66,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 100417,
"Sportsbook": null,
"ScoreId": 16891,
"Created": "2018-12-23T19:43:32",
"Updated": "2018-12-23T19:59:58",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": 0.0,
"AwayPointSpread": 0.0,
"HomePointSpreadPayout": -244,
"AwayPointSpreadPayout": 211,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16892,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-23T00:00:00",
"DateTime": "2018-12-23T20:20:00",
"AwayTeamId": 16,
"HomeTeamId": 30,
"AwayTeamName": "KC",
"HomeTeamName": "SEA",
"GlobalGameId": 16892,
"GlobalAwayTeamId": 16,
"GlobalHomeTeamId": 30,
"HomeTeamScore": 42,
"AwayTeamScore": 34,
"TotalScore": 77,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 100950,
"Sportsbook": null,
"ScoreId": 16892,
"Created": "2018-12-23T23:41:01",
"Updated": "2018-12-23T23:54:58",
"HomeMoneyLine": -2257,
"AwayMoneyLine": 1193,
"DrawMoneyLine": null,
"HomePointSpread": -1.7,
"AwayPointSpread": 1.7,
"HomePointSpreadPayout": -1688,
"AwayPointSpreadPayout": 741,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}, {
"ScoreId": 16893,
"Season": 2018,
"SeasonType": 1,
"Week": 16,
"Day": "2018-12-24T00:00:00",
"DateTime": "2018-12-24T20:15:00",
"AwayTeamId": 10,
"HomeTeamId": 25,
"AwayTeamName": "DEN",
"HomeTeamName": "OAK",
"GlobalGameId": 16893,
"GlobalAwayTeamId": 10,
"GlobalHomeTeamId": 25,
"HomeTeamScore": 30,
"AwayTeamScore": 15,
"TotalScore": 46,
"PregameOdds": [],
"LiveOdds": [{
"GameOddId": 101804,
"Sportsbook": null,
"ScoreId": 16893,
"Created": "2018-12-24T22:58:47",
"Updated": "2018-12-24T23:24:51",
"HomeMoneyLine": null,
"AwayMoneyLine": null,
"DrawMoneyLine": null,
"HomePointSpread": -10.7,
"AwayPointSpread": 10.7,
"HomePointSpreadPayout": -110,
"AwayPointSpreadPayout": -127,
"OverUnder": null,
"OverPayout": null,
"UnderPayout": null
}]
}]
Upvotes: 1
Views: 1432
Answers (2)
igorkf
Reputation: 3565
I know that you can use unlist() or flatten() from the purrr package.
Upvotes: 0
akrun
Reputation: 887541
One option would be to unnest
library(tidyverse)
library(jsonlite)
out <- fromJSON("example.json") %>%
select(-PregameOdds) %>% # remove the column with empty list
unnest(LiveOdds)
-structure
str(out)
#'data.frame': 16 obs. of 31 variables:
# $ ScoreId : int 16881 16889 16878 16879 16880 16882 16883 16884 16885 16886 ...
# $ Season : int 2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 ...
# $ SeasonType : int 1 1 1 1 1 1 1 1 1 1 ...
# $ Week : int 16 16 16 16 16 16 16 16 16 16 ...
# $ Day : chr "2018-12-22T00:00:00" "2018-12-22T00:00:00" "2018-12-23T00:00:00" "2018-12-23T00:00:00" ...
# $ DateTime : chr "2018-12-22T16:30:00" "2018-12-22T20:20:00" "2018-12-23T13:00:00" "2018-12-23T13:00:00" ...
# $ AwayTeamId : int 35 3 7 33 20 23 15 4 12 13 ...
# $ HomeTeamId : int 34 29 8 9 11 14 19 21 24 26 ...
# $ AwayTeamName : chr "WAS" "BAL" "CIN" "TB" ...
# $ HomeTeamName : chr "TEN" "LAC" "CLE" "DAL" ...
# $ GlobalGameId : int 16881 16889 16878 16879 16880 16882 16883 16884 16885 16886 ...
# $ GlobalAwayTeamId : int 35 3 7 33 20 23 15 4 12 13 ...
# $ GlobalHomeTeamId : int 34 29 8 9 11 14 19 21 24 26 ...
# $ HomeTeamScore : int 28 11 29 30 10 31 7 27 42 36 ...
# $ AwayTeamScore : int 18 24 20 22 30 30 19 13 49 33 ...
# $ TotalScore : int 46 36 49 52 40 61 27 40 92 69 ...
# $ GameOddId : int 95703 95933 98674 99556 99448 99596 99402 98928 99828 99727 ...
# $ Sportsbook : logi NA NA NA NA NA NA ...
# $ ScoreId1 : int 16881 16889 16878 16879 16880 16882 16883 16884 16885 16886 ...
# $ Created : chr "2018-12-22T19:04:18" "2018-12-22T23:12:29" "2018-12-23T14:22:38" "2018-12-23T15:49:51" ...
# $ Updated : chr "2018-12-22T19:24:55" "2018-12-22T23:24:59" "2018-12-23T15:54:51" "2018-12-23T16:09:54" ...
# $ HomeMoneyLine : int NA NA NA NA NA NA 169 NA NA NA ...
# $ AwayMoneyLine : int NA NA NA NA NA NA -236 NA NA NA ...
# $ DrawMoneyLine : logi NA NA NA NA NA NA ...
# $ HomePointSpread : num 0 13 -20.8 -15.2 16.3 0 2.8 -19.7 0 0 ...
# $ AwayPointSpread : num 0 -13 20.8 15.2 -16.3 0 -2.8 19.7 0 0 ...
# $ HomePointSpreadPayout: int -281 132 -153 -362 200 -273 139 160 145 -786 ...
# $ AwayPointSpreadPayout: int 238 -172 119 258 -231 233 -178 -206 -189 579 ...
# $ OverUnder : num NA 36.6 40 45.6 NA NA 28.7 42.3 NA NA ...
# $ OverPayout : int NA 117 -148 118 NA NA 130 -120 NA NA ...
# $ UnderPayout : int NA -147 116 -148 NA NA -165 -135 NA NA ...
Upvotes: 2
Related Questions
- Convert a list to json in R
- How can I best flatten a nested list to a data.frame in R?
- Create Json from list in R with brackets
- List as JSON string in R
- Flattening nested JSON in R
- Flatten JSON list into data frame
- How to flatten a deeply and irregularly nested list/dataframe/JSON in R
- Json list not 'flattening' properly
- How to construct a JSON with nested lists
- list conversion of jsonlite in R