# Efficiently Read JSON and Create DataFrame

**URL:** <https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087>\
**Category:** Performance\
**Tags:** json, dataframes\
**Created:** [June 9, 2020, 6:25pm UTC](https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087 "2020-06-09T18:25:04Z")\
**Posts on this page:** 4\
**Page:** 2

<div class="post-metadata">

**Author:** ![stene](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stene/32/26044_2.png) [@stene](https://discourse.julialang.org/u/stene)\
**Post date:** [February 20, 2025, 10:13pm UTC](https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087/22 "2025-02-20T22:13:15Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![stene](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stene/32/26044_2.png) [@stene](https://discourse.julialang.org/u/stene)\
**Post date:** [March 30, 2025, 8:49am UTC](https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087/23 "2025-03-30T08:49:11Z")

</div>

> [@rocco\_sprmnt21](#):
>
> `aapl_json=get_quoteSummary("AAPL")`

I did a quick hack

```julia
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

---

<div class="post-metadata">

**Author:** ![JesperMartinsson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jespermartinsson/32/34098_2.png) [@JesperMartinsson](https://discourse.julialang.org/u/JesperMartinsson)\
**Post date:** [April 2, 2025, 3:59pm UTC](https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087/24 "2025-04-02T15:59:42Z")

</div>

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

> [@JSON3 to mutable Dict{String,Any}](https://discourse.julialang.org/t/json3-to-mutable-dict-string-any/93443):
>
> I’m using JSON3 to parse an input that can be either a json or an array of json string. I want to convert the output of JSON3.read to either a mutable Dict{String,Any} or Array{Dict{String,Any}}, respectively. I see that copy(JSON3.read(...)) always returns keys as Symbol and not String. The only solution I have so far to return keys as String is to using a sink argument that is a union: JSON3.read(input,Union{Dict{String,Any},Array{Dict{String,Any}}}). Anyone know of a preferred way to do this…

---

<div class="post-metadata">

**Author:** ![stene](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stene/32/26044_2.png) [@stene](https://discourse.julialang.org/u/stene)\
**Post date:** [April 3, 2025, 6:00am UTC](https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087/25 "2025-04-03T06:00:28Z")

</div>

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

```julia
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

```

[Previous page](https://discourse.julialang.org/t/efficiently-read-json-and-create-dataframe/41087.md?page=1)