Converting JSON to dataframe

[bzigterman]

Hi all,

I'm trying to convert a JSON file from the web to a usable dataframe, but am having trouble getting the data out of it. I can only get it into a lists of lists.
Here's where the JSON is: https://igpa.uillinois.edu/wp-json/wp/v2/pages/365
And I'd like to put it into a table like the one here: Flash Index (All Data) IGPA, which I would then put into a more tidy format.

Here's what I have so far:

flash_index_url <- "https://igpa.uillinois.edu/wp-json/wp/v2/pages/365"
flash_index_GET <- GET(flash_index_url)
flash_index_content <- content(flash_index_GET)
flash_index_data <- flash_index_content[["acf"]][["flash_index_data"]] 

I feel like I'm getting there, but can't quite figure out the last step or two. Thanks!

[FJCC]

I never work with JSON data, so this code may be an atrocity. Does it get you what you want? I had to average the monthly values when using pivot_wider() because July 1993 has two values. You may well want to handle that some other way.

library(jsonlite)
library(tidyverse)
FullData <- fromJSON("https://igpa.uillinois.edu/wp-json/wp/v2/pages/365")
YearlyList <- FullData$acf
DATA <- YearlyList$flash_index_data
Final <- unnest(DATA,year_values) |> 
  mutate(flash_index_value = as.numeric(flash_index_value))
FinalWide <- Final |> pivot_wider(names_from = "month", 
                                  values_from = "flash_index_value",
                                  values_fn = mean)

FinalWide
#> # A tibble: 43 × 13
#>    year    Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec
#>    <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1 1981    0     0     0     0     0    91.2  91.4  92     0    90.8  92    92.8
#>  2 1982   93.4  94.4  97.1  98.2  95.9  94.5  93.8  93.8  99.3  94.6  94.3  92.8
#>  3 1983   92.9  91.5  87.5  85.9  86.4  86.2  86.6  87.5  86.9  90.1  90.5  92.5
#>  4 1984   93.5  95.8 101.  106.  110.  112.  117.  118.  116.  119.  119.  118. 
#>  5 1985  120.  119   118.  118.  117.  113.  111.  109.  109.  105.  104.  105. 
#>  6 1986  102.  101.  100.   97.8  94.7 102.   96.6  96.9  98.1  98.4  99.4  99.5
#>  7 1987  102.  102.  103   103.  108.  102.  106.  107.  108.  107.  107.  107. 
#>  8 1988  104.  105.  104.  105.  103.  104   103.  103.  102.  102   102.  102. 
#>  9 1989  104.  103   102.  103.  103.  102.  102.  102.  100.  101.  101.   96.9
#> 10 1990   94.9  95.2  95.2  93.6  93.4  92.1  93.6  93.6  95    94.7  94.3  97.6
#> # ℹ 33 more rows

^{Created on 2024-01-29 with reprex v2.0.2}

[bzigterman]

Looks good to me, thanks! I was overcomplicating it with my httr calls.