# Flattening YFinance.jl JSON result into a DataFrame

**URL:** <https://discourse.julialang.org/t/flattening-yfinance-jl-json-result-into-a-dataframe/96333>\
**Category:** New to Julia\
**Tags:** question\
**Created:** [March 20, 2023, 9:06am UTC](https://discourse.julialang.org/t/flattening-yfinance-jl-json-result-into-a-dataframe/96333 "2023-03-20T09:06:23Z")\
**Posts on this page:** 1\
**Showing post:** 24

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [April 11, 2023, 5:07pm UTC](https://discourse.julialang.org/t/flattening-yfinance-jl-json-result-into-a-dataframe/96333/24 "2023-04-11T17:07:38Z")

</div>

> **function's methods to read json file in nested DataFrame**
>
> ```julia
> 
> function allflatnt(vnt::Vector{Dict{Symbol, Any}})
> for i in eachindex(vnt)
> for k in keys(vnt[i])
> v=vnt[i][k]
> if v isa Vector{<:Dict} || v isa Dict
> return false
> end
> end
> end
> true
> end
> 
> function allflatnt(d::Dict{Symbol, Any})
> for k in keys(d)
> if d[k] isa Vector{<:Dict} || d[k] isa Dict
> return false
> end
> end
> 
> true
> end
> 
> function nestdf(vd::Vector{Dict{Symbol, Any}})
> for i in eachindex(vd)
> for (k,v) in vd[i]
> if v isa Vector{<:Dict} || v isa Dict
> if allflatnt(v)
> tdf=DataFrame(Tables.dictrowtable(v))
> if names(tdf)==["first","second"]
> rename!(tdf, [:first,:second].=>[:key,:value])
> end
> vd[i]=merge(vd[i], Dict(k=>tdf))
> else
> vd[i]=merge(vd[i], Dict(k=>nestdf(v)))
> end
> end
> end
> end
> DataFrame(Tables.dictrowtable(vd))
> end
> 
> function nestdf(d::Dict{Symbol, Any})
> for (k,v) in d
> if v isa Vector{<:Dict} || v isa Dict
> if allflatnt(v)
> tdf=DataFrame(Tables.dictrowtable(v))
> if names(tdf)==["first","second"]
> rename!(tdf, [:first,:second].=>[:key,:value])
> end
> d=merge(d, Dict(k=>tdf))
> else
> d=merge(d, Dict(k=>nestdf(v)))
> end
> end
> end
> rename!(DataFrame(Tables.dictrowtable(d)), [:first,:second].=>[:key,:value])
> end
> 
> nestdf(jsobj::JSON3.Object)=nestdf(copy(jsobj))
> nestdf(jsobj::JSON3.Array)=nestdf(copy(jsobj))
> 
> ```

I adapted the previous scripts to be able to handle nested structures of JSON.Array and JSON.Object.  
I only did some tests on the data corresponding to the “AAPL” ticker

```julia
using YFinance, JSON3, DataFrames

aapl_json=get_quoteSummary("AAPL")

```

Below are some possible “views” with the structure obtained by applying the `nestdf()` function

```julia
julia> ndf=nestdf(aapl_json)
31×2 DataFrame
 Row │ key value
     │ Symbol DataFrame      
─────┼───────────────────────────────────────────────────
   1 │ cashflowStatementHistoryQuarterly 2×2 DataFrame  
   2 │ industryTrend 3×2 DataFrame  
   3 │ earningsTrend 2×2 DataFrame  
   4 │ incomeStatementHistory 2×2 DataFrame  
   5 │ price 31×2 DataFrame 
   6 │ upgradeDowngradeHistory 2×2 DataFrame  
   7 │ institutionOwnership 2×2 DataFrame  
   8 │ fundOwnership 2×2 DataFrame
   9 │ summaryDetail 42×2 DataFrame
  10 │ netSharePurchaseActivity 12×2 DataFrame
  11 │ earnings 4×2 DataFrame
  12 │ insiderHolders 2×2 DataFrame
  13 │ calendarEvents 4×2 DataFrame
  14 │ summaryProfile 13×2 DataFrame
  15 │ majorDirectHolders 2×2 DataFrame
  16 │ assetProfile 20×2 DataFrame
  17 │ recommendationTrend 2×2 DataFrame
  18 │ balanceSheetHistory 2×2 DataFrame
  19 │ financialData 30×2 DataFrame
  20 │ indexTrend 5×2 DataFrame
  21 │ balanceSheetHistoryQuarterly 2×2 DataFrame
  22 │ insiderTransactions 2×2 DataFrame
  23 │ cashflowStatementHistory 2×2 DataFrame
  24 │ earningsHistory 2×2 DataFrame
  25 │ quoteType 13×2 DataFrame
  26 │ sectorTrend 3×2 DataFrame
  27 │ esgScores 36×2 DataFrame
  28 │ secFilings 2×2 DataFrame
  29 │ defaultKeyStatistics 39×2 DataFrame
  30 │ majorHoldersBreakdown 5×2 DataFrame
  31 │ incomeStatementHistoryQuarterly 2×2 DataFrame

julia> ndf.value[16]
20×2 DataFrame
 Row │ key value
     │ Symbol Any
─────┼──────────────────────────────────────────────────────────────
   1 │ auditRisk 4
   2 │ country United States
   3 │ overallRisk 1
   4 │ state CA
   5 │ phone 408 996 1010
   6 │ longBusinessSummary Apple Inc. designs, manufactures…
   7 │ city Cupertino
   8 │ zip 95014
   9 │ industry Consumer Electronics
  10 │ address1 One Apple Park Way
  11 │ shareHolderRightsRisk 1
  12 │ governanceEpochDate 1680307200
  13 │ maxAge 86400
  14 │ compensationRisk 5
  15 │ compensationAsOfEpochDate 1672444800
  16 │ boardRisk 1
  17 │ sector Technology
  18 │ fullTimeEmployees 164000
  19 │ website https://www.apple.com
  20 │ companyOfficers 10×9 DataFrame

julia> ndf.value[16].value[20]
10×9 DataFrame
 Row │ fiscalYear unexercisedValue age name title ⋯
     │ Int64? DataFrame Int64? String String ⋯
─────┼──────────────────────────────────────────────────────────────────────────────────────
   1 │ 2022 3×2 DataFrame 61 Mr. Timothy D. Cook CEO & Director ⋯
   2 │ 2022 3×2 DataFrame 59 Mr. Luca Maestri CFO & Sr. VP
   3 │ 2022 3×2 DataFrame 58 Mr. Jeffrey E. Williams Chief Operating Off  
   4 │ 2022 3×2 DataFrame 58 Ms. Katherine L. Adams Sr. VP, Gen. Counse  
   5 │ 2022 3×2 DataFrame 55 Ms. Deirdre O'Brien Sr. VP of Retail ⋯
   6 │ missing 3×2 DataFrame missing Mr. Chris Kondo Sr. Director of Cor  
   7 │ missing 3×2 DataFrame missing Mr. James Wilson Chief Technology Of  
   8 │ missing 3×2 DataFrame missing Ms. Mary Demby Chief Information O  
   9 │ missing 3×2 DataFrame missing Ms. Nancy Paxton Sr. Director of Inv ⋯
  10 │ missing 3×2 DataFrame missing Mr. Greg Joswiak Sr. VP of Worldwide  
                                                                           5 columns omitted

julia> ndf.value[16].value[20].totalPay[1:3]
3-element Vector{Union{Missing, DataFrame}}:
 3×2 DataFrame
 Row │ key value      
     │ Symbol Union…     
─────┼─────────────────────
   1 │ fmt 16.43M
   2 │ longFmt 16,425,933
   3 │ raw 16425933
 3×2 DataFrame
 Row │ key value     
     │ Symbol Union…    
─────┼────────────────────
   1 │ fmt 5.02M
   2 │ longFmt 5,019,783
   3 │ raw 5019783
 3×2 DataFrame
 Row │ key value     
     │ Symbol Union…    
─────┼────────────────────
   1 │ fmt 5.02M
   2 │ longFmt 5,018,337
   3 │ raw 5018337

```

> **some refactoring**
>
> ```julia
> allflatnt(vd::Vector{Dict{Symbol, Any}})= all(allflatnt, vd)
> 
> allflatnt(d::Dict{Symbol, Any})=all(e->!(last(e) isa Vector{<:Dict} || last(e) isa Dict),d)
> 
> function nestdf(vd::Vector{Dict{Symbol, Any}})
> DataFrame(Tables.dictrowtable(nestdict.(vd)))
> end
> 
> function nestdf(d::Dict{Symbol, Any})
> tdf=DataFrame(Tables.dictrowtable(nestdict(d)))
> if names(tdf)==["first","second"]
> rename!(tdf, [:first,:second].=>[:key,:value])
> end
> #tdf
> end
> 
> function nestdict(d::Dict{Symbol, Any})
> for (k,v) in d
> if v isa Vector{<:Dict} || v isa Dict
> if allflatnt(v)
> tdf=DataFrame(Tables.dictrowtable(v))
> if names(tdf)==["first","second"]
> rename!(tdf, [:first,:second].=>[:key,:value])
> end
> d=merge(d, Dict(k=>tdf))
> else
> d=merge(d, Dict(k=>nestdf(v)))
> end
> end
> end
> d 
> end
> 
> nestdf(jsobj::JSON3.Object)=nestdf(copy(jsobj))
> nestdf(jsobj::JSON3.Array)=nestdf(copy(jsobj))
> 
> using YFinance, JSON3, DataFrames
> 
> aapl_json=get_quoteSummary("AAPL")
> 
> ndf=nestdf(aapl_json)
> 
> ```

---

_[View the full topic](https://discourse.julialang.org/t/flattening-yfinance-jl-json-result-into-a-dataframe/96333)._