How to Wrangle JSON Data in R with jsonlite, purr and dplyr

Working with modern APIs you will often have to wrangle with data in JSON format.

This article presents some tools and recipes for working with JSON data with R in the tidyverse.

We’ll use purrr::map functions to extract and transform our JSON data. And we’ll provide intuitive examples of the cross-overs and differences between purrr and dplyr.

library(tidyverse)
library(here)
library(kableExtra)

pretty_print <- function(df, num_rows) {
  df %>%
  head(num_rows) %>%
    kable() %>%
    kable_styling(full_width = TRUE, position = 'center') %>%
    scroll_box(height = '300px')
}

Load JSON as nested named lists

This data has been converted from raw JSON to nested named lists using jsonlite::fromJSON with the simplify argument set to FALSE (that is, all elements are converted to named lists).

The data consists of market data for SPY options with various strikes and expiries. We got it from the options data vendor Orats, whose data API I enjoy almost as much as their orange website.

If you want to follow along, you can sign-up for a free trial of the API, and load the data directly from the Orats API with the following code (just define your API key in the ORATS_token variable):

library(httr)

ORATS_token <- 'YOUR_KEY_HERE'
res <- GET('https://api.orats.io/data/strikes?tickers=SPY', add_headers(Authorization = ORATS_token))

if (http_type(res) == 'application/json') {
  strikes <- jsonlite::fromJSON(content(res, 'text'), simplifyVector = FALSE)
} else {
  stop('No json returned')
}

if (http_error(res)) {
  stop(paste('API request error:',status_code(res), odata$message, odata$documentation_url))
} 

Now, if you want to read this data directly into a nicely formatted dataframe, replace the line:

strikes <- jsonlite::fromJSON(content(res, 'text'), simplifyVector = FALSE)

with

strikes <- jsonlite::fromJSON(content(res, 'text'), simplifyVector = TRUE, flatten = TRUE)

However, you should know that it isn’t always possible to coerce JSON into nicely shaped dataframes this easily – often the raw JSON won’t contain primitive types, or will have nested key-value pairs on the same level as your desired dataframe columns, to name a couple of obstacles.

In that case, it’s useful to have some tools – like the ones in this post – for wrangling your source data.

So let’s look at that strikes object, and show how we can wrangle it into something useful…

Look inside JSON lists

str(strikes, max.level = 1)  

## List of 1
##  $ data:List of 2440

This tells us we have a component named “data”. Let’s look at that a little more closely:

str(strikes$data, max.level = 1, list.len = 10)

## List of 2440
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##  $ :List of 40
##   [list output truncated]

This suggests we have homogenous lists of 40 elements each (an assumption we’ll check shortly).

Let’s look at one of those lists:

str(strikes$data[[1]])

## List of 40
##  $ ticker          : chr "SPY"
##  $ tradeDate       : chr "2020-05-19"
##  $ expirDate       : chr "2020-05-29"
##  $ dte             : int 11
##  $ strike          : int 140
##  $ stockPrice      : num 293
##  $ callVolume      : int 0
##  $ callOpenInterest: int 0
##  $ callBidSize     : int 20
##  $ callAskSize     : int 23
##  $ putVolume       : int 0
##  $ putOpenInterest : int 2312
##  $ putBidSize      : int 0
##  $ putAskSize      : int 7117
##  $ callBidPrice    : num 152
##  $ callValue       : num 153
##  $ callAskPrice    : num 153
##  $ putBidPrice     : int 0
##  $ putValue        : num 1.12e-25
##  $ putAskPrice     : num 0.01
##  $ callBidIv       : int 0
##  $ callMidIv       : num 0.98
##  $ callAskIv       : num 1.96
##  $ smvVol          : num 0.476
##  $ putBidIv        : int 0
##  $ putMidIv        : num 0.709
##  $ putAskIv        : num 1.42
##  $ residualRate    : num -0.00652
##  $ delta           : int 1
##  $ gamma           : num 9.45e-16
##  $ theta           : num -0.00288
##  $ vega            : num 2e-11
##  $ rho             : num 0.0384
##  $ phi             : num -0.0802
##  $ driftlessTheta  : num -6.07e-09
##  $ extSmvVol       : num 0.478
##  $ extCallValue    : num 153
##  $ extPutValue     : num 1.77e-25
##  $ spotPrice       : num 293
##  $ updatedAt       : chr "2020-05-19 20:02:33"

All these elements look like they can be easily handled. For instance, I don’t see any more deeply nested lists, weird missing values, or anything else that looks difficult.

So now I’ll pull out the interesting bit:

strikes <- strikes[["data"]]

How many observations do we have?

length(strikes)

## [1] 2440

Are all strike sublists identically named?

This is where we’ll check that our sublists are indeed homogeneously named, as we assumed above:

strikes %>%
  map(names) %>%  # this applies the base R function names to each sublist, and returns a list of lists with the output
  unique() %>%
  length() == 1

## [1] TRUE

Make a dataframe

We should also check the variable types are consistent as we need single types in each column of a dataframe (although R will warn if it is forced to coerce one type to another).

Here’s an interesting thing. It uses a nested purrr::map to get the variable types for each element of each sublist. They’re actually not identical according to this:

strikes %>%
  map(.f = ~{map_chr(.x, .f = class)}) %>%
  unique() %>%
  length()

## [1] 39

This is actually a little puzzling. Inspecting the individual objects suggests that we do have identical types. If anyone has anything to say about this, I’d love to hear about it in the comments. In any event, after we make our dataframe, we should check that the variable types are as expected.

Now, to that dataframe…

purrr::flatten removes one level of hierarchy from a list (unlist removes them all). Here, flatten is applied to each sub-list in strikes via purrr::map_df.

We use the variant flatten_df which returns each sublist as a dataframe, which makes it compatible with purrr::map_df,which requires a function that returns a dataframe.

strikes_df <- strikes %>%
  map_df(flatten_df)

strikes_df %>%
  pretty_print(30)

Cross-over and differences between purrr and dplyr

Here are some other interesting things that we can do with the nested lists via purrr, and their equivalent operation on the strikes_df dataframe using dplyr.

The intent is to gain some intuition for purrr using what you already know about dplyr.

Get vector of column names

strikes %>%
  map(names) %>%
  unique() %>%
  unlist()

##  [1] "ticker"           "tradeDate"        "expirDate"        "dte"              "strike"           "stockPrice"      
##  [7] "callVolume"       "callOpenInterest" "callBidSize"      "callAskSize"      "putVolume"        "putOpenInterest" 
## [13] "putBidSize"       "putAskSize"       "callBidPrice"     "callValue"        "callAskPrice"     "putBidPrice"     
## [19] "putValue"         "putAskPrice"      "callBidIv"        "callMidIv"        "callAskIv"        "smvVol"          
## [25] "putBidIv"         "putMidIv"         "putAskIv"         "residualRate"     "delta"            "gamma"           
## [31] "theta"            "vega"             "rho"              "phi"              "driftlessTheta"   "extSmvVol"       
## [37] "extCallValue"     "extPutValue"      "spotPrice"        "updatedAt"

This is equivalent to the following dplyr operation on the strikes_df dataframe:

strikes_df %>%
  names

##  [1] "ticker"           "tradeDate"        "expirDate"        "dte"              "strike"           "stockPrice"      
##  [7] "callVolume"       "callOpenInterest" "callBidSize"      "callAskSize"      "putVolume"        "putOpenInterest" 
## [13] "putBidSize"       "putAskSize"       "callBidPrice"     "callValue"        "callAskPrice"     "putBidPrice"     
## [19] "putValue"         "putAskPrice"      "callBidIv"        "callMidIv"        "callAskIv"        "smvVol"          
## [25] "putBidIv"         "putMidIv"         "putAskIv"         "residualRate"     "delta"            "gamma"           
## [31] "theta"            "vega"             "rho"              "phi"              "driftlessTheta"   "extSmvVol"       
## [37] "extCallValue"     "extPutValue"      "spotPrice"        "updatedAt"

You can see the connection: map(strikes, names) applies names to each sublist in strikes, returning a list of names for each sublist, which we then check for a single unique case and convert to a charcter vector via unlist.

In the dataframe version, we’ve already mapped each sublist to a dataframe row. We can get the column names of the dataframe by calling names directly on this object.

Check that all elements have the same ticker

strikes %>%
  map_chr("ticker") %>%  # this makes a character vector of list elements "ticker"
  unique()

## [1] "SPY"

Calling the purrr::map functions on a list with the name of a common sub-element returns the value associated with each sub-element. map returns a list; here we use map_chr to return a character vector.

This only works if the thing being returned from the sub-element is indeed a character.

This is equivalent to the following dplyr operation on the strikes_df dataframe:

strikes_df %>%
  distinct(ticker) %>%
  pull()

## [1] "SPY"

In the dplyr dataframe version, we’ve already mapped our tickers to their own column. So we simply call distinct on that column to get the unique values. A pull converts the resulting tibble to a vector.

Get the strike prices, expiries and call and put mid-prices

In this case, the purrr solution is somewhat convoluted:

callBids <- strikes %>%
  map_dbl("callBidPrice")

callAsks <- strikes %>%
  map_dbl("callAskPrice")

putBids <- strikes %>%
  map_dbl("putBidPrice")

putAsks <- strikes %>%
  map_dbl("putAskPrice")

data.frame(
  strike = strikes %>% map_dbl("strike"),
  expirDate = strikes %>% map_chr("expirDate"),
  callMid = map2_dbl(.x = callBids, .y = callAsks, ~{(.x + .y)/2}),
  putMid = map2_dbl(.x = putBids, .y = putAsks, ~{(.x + .y)/2})
) %>%
  pretty_print(10)

Since our mapping function requires two inputs, we need to use the map2 functions, and must set up the inputs as a first step.

The dplyr equivalent on the dataframe object is much more succinct:

strikes_df %>%
  mutate(
    callMid = (callBidPrice + callAskPrice)/2,
    putMid = (putBidPrice + putAskPrice)/2
  ) %>%
  select(strike, expirDate, callMid, putMid) %>%
  pretty_print(10)

Leverage the dataframe’s structure

We can also leverage the fact that a dataframe is represented as a list of columns to use purrr functions directly on dataframes. These recipes are quite useful for quickly getting to know a dataframe.

For instance, we can get the type of each column:

strikes_df %>%
  map_chr(class)

##           ticker        tradeDate        expirDate              dte           strike       stockPrice       callVolume 
##      "character"      "character"      "character"        "integer"        "numeric"        "numeric"        "integer" 
## callOpenInterest      callBidSize      callAskSize        putVolume  putOpenInterest       putBidSize       putAskSize 
##        "integer"        "integer"        "integer"        "integer"        "integer"        "integer"        "integer" 
##     callBidPrice        callValue     callAskPrice      putBidPrice         putValue      putAskPrice        callBidIv 
##        "numeric"        "numeric"        "numeric"        "numeric"        "numeric"        "numeric"        "numeric" 
##        callMidIv        callAskIv           smvVol         putBidIv         putMidIv         putAskIv     residualRate 
##        "numeric"        "numeric"        "numeric"        "numeric"        "numeric"        "numeric"        "numeric" 
##            delta            gamma            theta             vega              rho              phi   driftlessTheta 
##        "numeric"        "numeric"        "numeric"        "numeric"        "numeric"        "numeric"        "numeric" 
##        extSmvVol     extCallValue      extPutValue        spotPrice        updatedAt 
##        "numeric"        "numeric"        "numeric"        "numeric"      "character"

Which is equivalent to a dplyr::summarise_all, except that this returns a tibble rather than a vector:

strikes_df %>%
  summarise_all(~class(.x)) 

## # A tibble: 1 x 40
##   ticker tradeDate expirDate dte   strike stockPrice callVolume callOpenInterest callBidSize callAskSize putVolume
##   <chr>  <chr>     <chr>     <chr> <chr>  <chr>      <chr>      <chr>            <chr>       <chr>       <chr>    
## 1 chara~ character character inte~ numer~ numeric    integer    integer          integer     integer     integer  
## # ... with 29 more variables: putOpenInterest <chr>, putBidSize <chr>, putAskSize <chr>, callBidPrice <chr>,
## #   callValue <chr>, callAskPrice <chr>, putBidPrice <chr>, putValue <chr>, putAskPrice <chr>, callBidIv <chr>,
## #   callMidIv <chr>, callAskIv <chr>, smvVol <chr>, putBidIv <chr>, putMidIv <chr>, putAskIv <chr>, residualRate <chr>,
## #   delta <chr>, gamma <chr>, theta <chr>, vega <chr>, rho <chr>, phi <chr>, driftlessTheta <chr>, extSmvVol <chr>,
## #   extCallValue <chr>, extPutValue <chr>, spotPrice <chr>, updatedAt <chr>

We can also get the number of distinct values in each column using purrr functions:

strikes_df %>%
  map_dbl(n_distinct)

##           ticker        tradeDate        expirDate              dte           strike       stockPrice       callVolume 
##                1                1               31               31              151                1              162 
## callOpenInterest      callBidSize      callAskSize        putVolume  putOpenInterest       putBidSize       putAskSize 
##             1097              144              165              498             1691             1054              926 
##     callBidPrice        callValue     callAskPrice      putBidPrice         putValue      putAskPrice        callBidIv 
##             2151             2228             2199             1281             2419             1311             1907 
##        callMidIv        callAskIv           smvVol         putBidIv         putMidIv         putAskIv     residualRate 
##             2423             2430             2044             2345             2425             2428             2039 
##            delta            gamma            theta             vega              rho              phi   driftlessTheta 
##             2137             2238             2192             2211             2188             2133             2196 
##        extSmvVol     extCallValue      extPutValue        spotPrice        updatedAt 
##             2016             2168             2413                1                1

Again, this is equivalent to a dplyr::summarise_all, different return objects aside:

strikes_df %>%
  summarise_all(~n_distinct(.x))

## # A tibble: 1 x 40
##   ticker tradeDate expirDate   dte strike stockPrice callVolume callOpenInterest callBidSize callAskSize putVolume
##    <int>     <int>     <int> <int>  <int>      <int>      <int>            <int>       <int>       <int>     <int>
## 1      1         1        31    31    151          1        162             1097         144         165       498
## # ... with 29 more variables: putOpenInterest <int>, putBidSize <int>, putAskSize <int>, callBidPrice <int>,
## #   callValue <int>, callAskPrice <int>, putBidPrice <int>, putValue <int>, putAskPrice <int>, callBidIv <int>,
## #   callMidIv <int>, callAskIv <int>, smvVol <int>, putBidIv <int>, putMidIv <int>, putAskIv <int>, residualRate <int>,
## #   delta <int>, gamma <int>, theta <int>, vega <int>, rho <int>, phi <int>, driftlessTheta <int>, extSmvVol <int>,
## #   extCallValue <int>, extPutValue <int>, spotPrice <int>, updatedAt <int>

If we wanted to put both of these things together, there’s an elegant purrr solution:

strikes_df %>%
  map_df(
    ~data.frame(num_distinct = n_distinct(.x), type = class(.x)),
      .id = "variable"
  )

##            variable num_distinct      type
## 1            ticker            1 character
## 2         tradeDate            1 character
## 3         expirDate           31 character
## 4               dte           31   integer
## 5            strike          151   numeric
## 6        stockPrice            1   numeric
## 7        callVolume          162   integer
## 8  callOpenInterest         1097   integer
## 9       callBidSize          144   integer
## 10      callAskSize          165   integer
## 11        putVolume          498   integer
## 12  putOpenInterest         1691   integer
## 13       putBidSize         1054   integer
## 14       putAskSize          926   integer
## 15     callBidPrice         2151   numeric
## 16        callValue         2228   numeric
## 17     callAskPrice         2199   numeric
## 18      putBidPrice         1281   numeric
## 19         putValue         2419   numeric
## 20      putAskPrice         1311   numeric
## 21        callBidIv         1907   numeric
## 22        callMidIv         2423   numeric
## 23        callAskIv         2430   numeric
## 24           smvVol         2044   numeric
## 25         putBidIv         2345   numeric
## 26         putMidIv         2425   numeric
## 27         putAskIv         2428   numeric
## 28     residualRate         2039   numeric
## 29            delta         2137   numeric
## 30            gamma         2238   numeric
## 31            theta         2192   numeric
## 32             vega         2211   numeric
## 33              rho         2188   numeric
## 34              phi         2133   numeric
## 35   driftlessTheta         2196   numeric
## 36        extSmvVol         2016   numeric
## 37     extCallValue         2168   numeric
## 38      extPutValue         2413   numeric
## 39        spotPrice            1   numeric
## 40        updatedAt            1 character

But the best I can do with dplyr is somewhat less elegant:

strikes_df %>%
  summarise_all(
    list(~n_distinct(.x), ~class(.x))
  ) 

## # A tibble: 1 x 80
##   ticker_n_distin~ tradeDate_n_dis~ expirDate_n_dis~ dte_n_distinct strike_n_distin~ stockPrice_n_di~ callVolume_n_di~
##              <int>            <int>            <int>          <int>            <int>            <int>            <int>
## 1                1                1               31             31              151                1              162
## # ... with 73 more variables: callOpenInterest_n_distinct <int>, callBidSize_n_distinct <int>,
## #   callAskSize_n_distinct <int>, putVolume_n_distinct <int>, putOpenInterest_n_distinct <int>,
## #   putBidSize_n_distinct <int>, putAskSize_n_distinct <int>, callBidPrice_n_distinct <int>, callValue_n_distinct <int>,
## #   callAskPrice_n_distinct <int>, putBidPrice_n_distinct <int>, putValue_n_distinct <int>, putAskPrice_n_distinct <int>,
## #   callBidIv_n_distinct <int>, callMidIv_n_distinct <int>, callAskIv_n_distinct <int>, smvVol_n_distinct <int>,
## #   putBidIv_n_distinct <int>, putMidIv_n_distinct <int>, putAskIv_n_distinct <int>, residualRate_n_distinct <int>,
## #   delta_n_distinct <int>, gamma_n_distinct <int>, theta_n_distinct <int>, vega_n_distinct <int>, rho_n_distinct <int>,
## #   phi_n_distinct <int>, driftlessTheta_n_distinct <int>, extSmvVol_n_distinct <int>, extCallValue_n_distinct <int>,
## #   extPutValue_n_distinct <int>, spotPrice_n_distinct <int>, updatedAt_n_distinct <int>, ticker_class <chr>,
## #   tradeDate_class <chr>, expirDate_class <chr>, dte_class <chr>, strike_class <chr>, stockPrice_class <chr>,
## #   callVolume_class <chr>, callOpenInterest_class <chr>, callBidSize_class <chr>, callAskSize_class <chr>,
## #   putVolume_class <chr>, putOpenInterest_class <chr>, putBidSize_class <chr>, putAskSize_class <chr>,
## #   callBidPrice_class <chr>, callValue_class <chr>, callAskPrice_class <chr>, putBidPrice_class <chr>,
## #   putValue_class <chr>, putAskPrice_class <chr>, callBidIv_class <chr>, callMidIv_class <chr>, callAskIv_class <chr>,
## #   smvVol_class <chr>, putBidIv_class <chr>, putMidIv_class <chr>, putAskIv_class <chr>, residualRate_class <chr>,
## #   delta_class <chr>, gamma_class <chr>, theta_class <chr>, vega_class <chr>, rho_class <chr>, phi_class <chr>,
## #   driftlessTheta_class <chr>, extSmvVol_class <chr>, extCallValue_class <chr>, extPutValue_class <chr>,
## #   spotPrice_class <chr>, updatedAt_class <chr>

Intuitively, you’d reach for something like this:

try(
  strikes_df %>%
    summarise_all(
      ~data.frame(num_distinct = n_distinct(.x), type = class(.x))
    )
)

## Error : Column `ticker` must be length 1 (a summary value), not 2

But we get an error related to the fact summarise wants to return a single value for each variable being summarised, that is, a dataframe with a single row.

There are probably better dplyr solutions out there, but this illustrates an important point: the purrr::map functions are highly customisable, able to apply a function to individual elements in a collection, returning a data object of your choosing. dplyr::summarise really shines when you need to aggregate or reduce variables to a single value.

Conclusion

In this post we explored the purrr::map functions for wrangling a data set consisting of nested lists, as you might have if you were reading in JSON data to R.

We also explored the cross-over and differences in use-cases for purrr and dplyr functions.