7

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.

Improve this question
2
  • What package are you using? I see three packages on CRAN with a fromJSON function (rjson, RJSONIO, and jsonlite). Commented Jan 31, 2016 at 22:28
  • @MichaelChirico Apologies, I'll edit. For this it is RJSONIO but I've tried all three with no success. Commented Jan 31, 2016 at 22:30

4 Answers 4

Reset to default
11

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().

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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.

Improve this answer

2 Comments

Thanks @Spacedman. It sounds like the issue is with me not mapping the JSON -> data frame process (I didn't know one had to do so). I don't know how to yet but will investigate. Thanks!
Well you only have to do so if the JSON is structured deeper than a simple table, which it is here. Quite what the optimal way of structuring the data in R (keep as a list, convert to two data frames with a linking ID) depends on what you want to do with it.
0

RJson and Jsonlite have similar commands, like fromJSON but depending on the order you load them, they will override each other. For my purposes, rJson structures data much better than JsonLite, so I make sure to load in the correct order/only load Rjson

Improve this answer

1 Comment

Welcome to Stack Overflow! Your answer seems more like a comment than a solution. (I know new users aren't able to post comments.)
0

jsonlite is load

library(jsonlite)

Definition of quandl_url

quandl_url <- "https://www.quandl.com/api/v3/datasets/WIKI/FB/data.json?auth_token=i83asDsiWUUyfoypkgMz"

Import Quandl data:

quandl_data <- fromJSON(quandl_url)

quandl_data in list type

quandl_data
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.