How to read Panda's DataFrames from json file?

New to Julia
21

I’m not sure you can by changing the for loop, but you can use it in the last line like
df = permutedims(reduce(vcat,rows), :KeyID)
although I’m sure that’s occurred to you.

1 Like
22 
df=DataFrame(keys=[k for k in keys(jsobj.Var)]) # Get list of row keys from anywhere. 
# "Var" is the first easy symbol to write 
:grinning:

# Then take all the values to put in the column
for sym in keys(jsobj)
    df[:,sym]=[getproperty(jsobj,sym)[k] for k in keys(jsobj[sym])]
end

put in a more general form

df=DataFrame(keys=collect(keys(getproperty(jsobj,first(keys(jsobj))))))

foreach(s->df[:,s]=collect(values(getproperty(jsobj,s))), keys(jsobj))
1 Like
23

Perfect!

24

How would you write it in order to have it transposed? (i.e. the columns changed to rows)

25

You mean this?

dff=DataFrame(keys=collect(keys(jsobj)))
dfr=DataFrame(NamedTuple.(values(jsobj)))
[dff dfr]

or

hcat(DataFrame.([(keys=collect(keys(jsobj)),), NamedTuple.(values(jsobj))])...)
1 Like
26

Exactly! Thank you.

27

I’ve been meaning to make a package for this for ages, and this post has prompted me to start one: GitHub - cjdoris/PandasIO.jl: Read/write Pandas dataframes

Example:

julia> using PandasIO, DataFrames

julia> df = PandasIO.read_json("00_stats.json", DataFrame)
32×105 DataFrame
 Row │ dRBYd        dTD/RZ      dYd/G      PuntRt      ToPLead    Con%     ⋯
     │ Float64      Float64     Float64    Float64     Float64    Float64  ⋯
─────┼──────────────────────────────────────────────────────────────────────
   1 │ -0.00361946   2.02676    -0.20186   -0.712688    1.32705    1.24077 ⋯
   2 │ -1.18115      0.29747    -0.664647   1.71805    -0.551793   0.23976
   3 │  0.826444     1.24938     2.45037    1.8372      0.611967   0.23976
   4 │ -0.177354    -1.73326    -0.929148   0.288203    1.02793    1.64227
   5 │ -0.988114    -0.511649   -0.657791   0.574172    1.00456    1.09227 ⋯
   6 │  1.54069     -0.0674266   1.46255    0.550341   -1.78566   -1.50924
   7 │ -0.351088    -0.495784   -0.496853  -1.26079    -0.103114  -0.75573
  ⋮  │      ⋮           ⋮           ⋮          ⋮           ⋮          ⋮    ⋱
  26 │  0.208722    -0.162617    0.969991   1.98018    -1.40241   -0.25523
  27 │  1.0967      -0.210212   -0.342591  -0.402888   -0.304085   0.23976 ⋯
  28 │  2.29353     -0.654435   -0.109844   0.717156    0.541861   0.74026
  29 │ -0.814379     0.884479    0.431427   0.0498956   0.471755   0.74026
  30 │ -0.505518    -0.860681   -1.2411    -0.212242   -0.308759  -0.50823
  31 │ -0.486215    -1.01933    -1.2005     0.598002   -1.13601   -0.25523 ⋯
  32 │ -2.04982     -0.130887   -1.56045    1.19377     2.07485    1.74127
                                             100 columns and 18 rows omitted
4 Likes
28

Also see GitHub - JuliaPy/Pandas.jl: A Julia front-end to Python's Pandas package. especially if you need some edgecase exactly like it is in pandas.

2 Likes