Convert JSON URL to R Data Frame

Question

I'm having trouble converting a JSON file (from an API) to a data frame in R. An example is the URL http://api.fantasy.nfl.com/v1/players/stats?statType=seasonStats&season=2010&week=1&format=json

I've tried a few different suggestions from S/O, including convert json data to data frame in R and various blog posts such as http://zevross.com/blog/2015/02/12/using-r-to-download-and-parse-json-an-example-using-data-from-an-open-data-portal/

The closest I've been is using the code below which gives me a large matrix with 4 "rows" and a bunch of "varables" (V1, V2, etc.). I'm assuming that this JSON file is in a different format than "normal" ones.

library(RJSONIO)

raw_data <- getURL("http://api.fantasy.nfl.com/v1/players/stats?statType=seasonStats&season=2010&week=1&format=json")

data <- fromJSON(raw_data)

final_data <- do.call(rbind, data)

I'm pretty agnostic as to how to get it to work so any R packages/process are welcome. Thanks in advance.

Answer 1

There's nothing "abnormal" about this JSON, its just not a rectangular structure that fits trivially into a data frame. JSON can represent much richer data structures.

For example (using the rjson package, you've not said what you've used):

> data = rjson::fromJSON(file="http://api.fantasy.nfl.com/v1/players/stats?statType=seasonStats&season=2010&week=1&format=json")
> length(data[[4]][[10]]$stats)
[1] 14
> length(data[[4]][[1]]$stats)
[1] 21

(data[[1 to 3]] look like headers)

the "stats" of the 10th element of data[[4]] has 14 elements, the "stats" of the first has 21. How is that going to fit into a rectangular data frame? R has stored it in a list because that's R's best way of storing irregular data structures.

Unless you can define a way of mapping the irregular data into a rectangular data frame, you can't store it in a data frame. Do you understand the structure of the data? That's essential.

Answer 2

The jsonlite package automatically picks up the dataframe:

library(jsonlite)
mydata <- fromJSON("http://api.fantasy.nfl.com/v1/players/stats?statType=seasonStats&season=2010&week=1&format=json")

names(mydata$players)
# [1] "id"                 "esbid"              "gsisPlayerId"       "name"              
# [5] "position"           "teamAbbr"           "stats"              "seasonPts"         
# [9] "seasonProjectedPts" "weekPts"            "weekProjectedPts" 

head(mydata$players)
#        id     esbid gsisPlayerId                name position teamAbbr stats.1
# 1  100029     FALSE        FALSE San Francisco 49ers      DEF       SF      16
# 2     729 ABD660476   00-0025940     Husain Abdullah       DB       KC      15
# 3 2504171 ABR073003   00-0019546        John Abraham       LB               15
# 4 2507266 ADA509576   00-0025668       Michael Adams       DB               13
# 5 2505708 ADA515576   00-0022247          Mike Adams       DB      IND      15
# 6 1037889 ADA534252   00-0027610       Phillip Adams       DB      ATL      11

You can control this using the simplify arguments in jsonlite::fromJSON().