# How to read Panda's DataFrames from json file?

**URL:** <https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749>\
**Category:** New to Julia\
**Tags:** dataframes\
**Created:** [January 30, 2023, 12:40am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749 "2023-01-30T00:40:21Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 12:40am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/1 "2023-01-30T00:40:21Z")

</div>

I have a huge collection of json files containing a single panda’s dataframe each. I would like to open them in julia but I haven’t found the solution. A caveat is that the dataframes have strings as row indexes.  
Any help would be appreciated!

---

<div class="post-metadata">

**Author:** ![TheCedarPrince](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/thecedarprince/32/17323_2.png) [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)\
**Post date:** [January 30, 2023, 12:55am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/2 "2023-01-30T00:55:51Z")

</div>

This is an interesting format - didn’t realize pandas serialized to JSON but doesn’t surprise me. Could you share an example of what this JSON looks like? First thought is to do something like:

1. Use JSON3 to convert JSON files to Julia Dict objects
2. Write your own parser in how the data should look
3. Convert data to a DataFrame via DataFrames.jl
4. Repeat steps 1 - 3 until you have loaded in all your dataframes
5. `vcat` each dataframe from this loading – via something like `vcat(df1, df2, ...)`.
6. Sort on your column that has string based indices (I don’t think you have to convert to Int here – if you do, should just be like `df.rownum = parse.(,Int,df.rownum)`)
7. Off to the rest of your analysis!

Without an example, it is rather hard to imagine exactly how this would work but this is what comes to mind. Does that help?

P.S. Also, welcome to the Julia community! 👋

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 1:09am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/3 "2023-01-30T01:09:34Z")

</div>

Thank you very much for your kind response, @TheCedarPrince!

 ![json_dataframe_2](https://global.discourse-cdn.com/julialang/original/3X/2/2/22146dadd43661dcaedcc7781981b098a0c94980.jpeg)  
In this first picture, we can see the partial contents of one of my json files, which is a long dataframe (32 rows, 105 columns).  
I didn’t understand what you mean by sort my column that has string based indices. The whole dataframe has string indices instead of numbers.  
I really don’t have a clue of how to proceed, any further help will be of great help.

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 1:14am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/4 "2023-01-30T01:14:11Z")

</div>

![json_dataframe](https://global.discourse-cdn.com/julialang/original/3X/c/d/cdb54860b24336eb189973865ee28d8b43f1d810.jpeg)  
I already managed to read the json file by converting it to a string, as shown in the picture above. But I don’t know how to have it opened as the Dataframe that it is.

---

<div class="post-metadata">

**Author:** ![TheCedarPrince](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/thecedarprince/32/17323_2.png) [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)\
**Post date:** [January 30, 2023, 1:17am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/5 "2023-01-30T01:17:04Z")

</div>

Ah got it! So, actually, I think the simplest way may be this:

1. Open up your files again as pandas dataframes (within Python)
2. Write these dataframes to CSVs instead
3. Use CSV.jl to open the CSV into a DataFrame via DataFrames.jl (within Julia)
4. Continue with your analysis.

Does that work for your needs?

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 1:34am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/6 "2023-01-30T01:34:16Z")

</div>

I converted one of the json files to csv within python to test your solution. My problem now is that I do not know how to open it as a dataframe as you suggest? Do you have the code for that? Thanks again!

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [January 30, 2023, 2:07am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/7 "2023-01-30T02:07:30Z")

</div>

I wonder if [JSONTables.jl](https://github.com/JuliaData/JSONTables.jl) might work here…

```julia
using JSONTables, DataFrames

json_string = read("00_stats.json", String)
jtable = JSONTables.jsontable(json_string)
df = DataFrame(jtable)

```

(Of course, I haven’t tested this against your file format.)

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 2:11am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/8 "2023-01-30T02:11:20Z")

</div>

> [@jd-foster](#):
>
> ```julia
> using JSONTables, DataFrames
> 
> json_string = read("00_stats.json", String)
> jtable = JSONTables.jsontable(json_string)
> df = DataFrame(jtable)
> 
> ```

Thanks for your help. I got the following error:

ArgumentError: input `JSON3.Object` must only have `JSON3.Array` values to be considered a table

Stacktrace:  
[1] jsontable(x::JSON3.Object{Base.CodeUnits{UInt8, String}, Vector{UInt64}})  
@ JSONTables C:\Users\sERRa.julia\packages\JSONTables\EZWPP\src\JSONTables.jl:26  
[2] jsontable(source::String)  
@ JSONTables C:\Users\sERRa.julia\packages\JSONTables\EZWPP\src\JSONTables.jl:15  
[3] top-level scope  
@ In[31]:4

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [January 30, 2023, 2:16am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/9 "2023-01-30T02:16:11Z")

</div>

I guess the file format is not

> a JSON object of arrays, or a JSON array of objects,

as the JSONTable readme says. It was a bit of a low-probability that this would work directly.

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [January 30, 2023, 2:19am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/10 "2023-01-30T02:19:41Z")

</div>

Can you post a link to an example file you’re using?

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 2:23am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/11 "2023-01-30T02:23:48Z")

</div>

> **[00\_stats.json](https://drive.google.com/file/d/1rxf4O_RXi6_m1NmZXVPDtbz7N3lGW7LH/view?usp=share_link)**
>
> Google Drive file.

This is the json file containing a single Dataframe consisting of 32 rows and 105 columns.

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [January 30, 2023, 2:57am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/12 "2023-01-30T02:57:51Z")

</div>

Ok, this basically follows what @TheCedarPrince outlined above:

```julia
using JSON3
using DataFrames

json_string = read("00_stats.json");
jsobj = JSON3.read(json_string);

rows = DataFrame[];

for sym in keys(jsobj)
    # sym = :Var # For example.
    row = DataFrame([k => jsobj[sym][k] for k in keys(jsobj[sym])])
    insertcols!(row, 1, :KeyID => String(sym))
    push!(rows, row)
end

df = reduce(vcat,rows)

```

(The `KeyID`s seem unintelligible to me but trust you can figure it out!)

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 3:14am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/13 "2023-01-30T03:14:24Z")

</div>

> [@jd-foster](#):
>
> ```julia
> using JSON3
> using DataFrames
> 
> json_string = read("00_stats.json");
> jsobj = JSON3.read(json_string);
> 
> rows = DataFrame[];
> 
> for sym in keys(jsobj)
> # sym = :Var # For example.
> row = DataFrame([k => jsobj[sym][k] for k in keys(jsobj[sym])])
> insertcols!(row, 1, :KeyID => String(sym))
> push!(rows, row)
> end
> 
> df = reduce(vcat,rows)
> 
> ```

This worked perfectly!! Only thing, rows and columns are inverted, how can I transpose them?

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [January 30, 2023, 3:17am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/14 "2023-01-30T03:17:35Z")

</div>

Try `permutedims(df, :KeyID)`

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 3:33am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/15 "2023-01-30T03:33:04Z")

</div>

Do you know the equivalent of df.iloc[1] to extract a row?

---

<div class="post-metadata">

**Author:** ![mrufsvold](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mrufsvold/32/31600_2.png) [@mrufsvold](https://discourse.julialang.org/u/mrufsvold)\
**Post date:** [January 30, 2023, 3:42am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/16 "2023-01-30T03:42:34Z")

</div>

[ExpandNestedData.jl](https://github.com/mrufsvold/ExpandNestedData.jl) might help you.

I haven’t registered it yet, so you need to install it with the GitHub link (see the docs)

---

<div class="post-metadata">

**Author:** ![mrufsvold](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mrufsvold/32/31600_2.png) [@mrufsvold](https://discourse.julialang.org/u/mrufsvold)\
**Post date:** [January 30, 2023, 3:43am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/17 "2023-01-30T03:43:45Z")

</div>

```julia
df[1, :]

```

Should do it!

---

<div class="post-metadata">

**Author:** ![mrufsvold](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mrufsvold/32/31600_2.png) [@mrufsvold](https://discourse.julialang.org/u/mrufsvold)\
**Post date:** [January 30, 2023, 3:48am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/18 "2023-01-30T03:48:33Z")

</div>

I moved the solution mark back to @jd-foster since his response is the solution to OP 😁

---

<div class="post-metadata">

**Author:** ![TheCedarPrince](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/thecedarprince/32/17323_2.png) [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)\
**Post date:** [January 30, 2023, 3:56am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/19 "2023-01-30T03:56:19Z")

</div>

Glad things were resolved! Got distracted by hanging out with my family. 😄

That said, welcome to the Julia fam! Glad you got your questions answered and see you around!

---

<div class="post-metadata">

**Author:** ![sERRa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/serra/32/46358_2.png) [@sERRa](https://discourse.julialang.org/u/sERRa)\
**Post date:** [January 30, 2023, 4:21am UTC](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749/20 "2023-01-30T04:21:50Z")

</div>

How can I fix the for loop so that the columns and rows are switched here, instead of having to use permutedims(df, :KeyID) later?

[Next page](https://discourse.julialang.org/t/how-to-read-pandas-dataframes-from-json-file/93749.md?page=2)