Efficiently Read JSON and Create DataFrame

General Usage Performance
,
22

YFinance forces you to use the JSON3 Object, and this technique wont work for that setup. I could not find a way to convert the JSON3 Object to a Dict. There is a copy method, but that uses Dict{Symbol, Any}() and a dict of symbols wont work. You would have to modify YFinance or JSON3 in order for this to work. Or write a conversion routine from Dict(Symbol, Any) to Dict(String, Any)

23

I did a quick hack

using YFinance, JSON, DataFrames, TidierData

# There is a tiny modification to YFinance, to return the JSON string instead of a JSON3 Object
aapl_json=get_quoteSummary("AAPL")

json_res=JSON.parse(String(aapl_json))

apple=DataFrame(json_res)

apple_quote_summary = @unnest_wider(apple, quoteSummary)

apple_quote_summary_result = @unnest_longer(apple_quote_summary, quoteSummary_result)


summary_detail = @unnest_wider(apple_quote_summary_result, quoteSummary_result)

summary_detail_only_price = select(summary_detail, :quoteSummary_result_price)

priceDF_unstacked = @unnest_wider(summary_detail_only_price, quoteSummary_result_price)

result = stack(priceDF_unstacked,1:ncol(priceDF_unstacked))

result.variable.=replace.(result.variable, "quoteSummary_result_price_"=>"")

result

It will look like this:

Row │ variable value
│ String Any
─────┼────────────────────────────────────────────────────
1 │ exchange NMS
2 │ regularMarketChange -5.95001
3 │ exchangeDataDelayedBy 0
4 │ exchangeName NasdaqGS
5 │ quoteSourceName Nasdaq Real Time Price
6 │ regularMarketDayLow 217.68
7 │ postMarketTime 1743206395
8 │ currency USD
9 │ regularMarketPrice 217.9
10 │ longName Apple Inc.
11 │ regularMarketChangePercent -0.0265804
12 │ regularMarketDayHigh 223.8
13 │ averageDailyVolume10Day 47802000
14 │ regularMarketSource FREE_REALTIME
15 │ regularMarketOpen 221.649
16 │ postMarketChange -0.679993
17 │ priceHint 2
18 │ maxAge 1
19 │ regularMarketPreviousClose 223.85
20 │ quoteType EQUITY
21 │ symbol AAPL
22 │ currencySymbol $
23 │ shortName Apple Inc.
24 │ regularMarketVolume 39525987
25 │ fromCurrency
26 │ lastMarket
27 │ marketCap 3273315581952
28 │ regularMarketTime 1743192001
29 │ underlyingSymbol
30 │ averageDailyVolume3Month 52532506
31 │ postMarketPrice 217.22
32 │ preMarketSource FREE_REALTIME
33 │ postMarketChangePercent -0.00312066
34 │ postMarketSource FREE_REALTIME
35 │ marketState CLOSED
36 │ toCurrency

2 Likes
24

I had a similar challenge. Perhaps you can use a sink function and maybe this thread may provide some help:

25

I made a “template” for creating a DataFrame from JSON

using HTTP
using JSON
using DataFrames
using Plots
using Statistics


# Download the JSON data
url = "https://raw.githubusercontent.com/altair-viz/vega_datasets/master/vega_datasets/_data/wheat.json"
response = HTTP.get(url)

# Check for successful request
if response.status == 200
   # Parse the JSON data, note that you must specify that null should be interpreted as missing and inttype should be Float64, otherwise Ints and Floats can be mixed in the same column
    data = JSON.parse(String(response.body);  null=missing, inttype=Float64)

    # Convert JSON to DataFrame. The Tables.dictrowtable is necessary for any data which does not have fields for all data. In this example wages are not specified for years 1815 and 1820
    df = DataFrame(Tables.dictrowtable(data))

    # Assuming 'year' is a column in your data
    years = df.year
    wheat = df.wheat
    wages = df.wages

    # Calculate a sliding window mean (example window size 5)
    window_size = 5::Int64
    wheat_rolling = [mean(wheat[i:(i + window_size - 1)]) for i in 1:(length(wheat) - window_size + 1)]
    wages_rolling = [mean(wages[i:(i + window_size - 1)]) for i in 1:(length(wages) - window_size + 1)]
    rolling_years = years[window_size÷2:(length(years) - window_size÷2 - 1)]


    # Plotting
    thePlot=plot(rolling_years, wheat_rolling, label="Wheat (Rolling Mean)",xlabel="Year", ylabel="Value")
    plot!(rolling_years, wages_rolling, label="Wages (Rolling Mean)")
    display(thePlot)

else
println("Error downloading the file: ", response.status)
end