Read .json file in pandas (python) [duplicate]

Question

I am trying to read a .json file in pandas and couldn't figure out how to. I was hoping if anyone can assist me with this.

My json file: [[{"Auction_Day":31,"Auction_Month":1,"Auction_Year":2023,"Unit":"PQR-1","Provider":"PQR Energy","Volume":8.0,"Price":10.0,"EFA":2},{"Auction_Day":31,"Auction_Month":1,"Auction_Year":2023,"Unit":"PQR-2","Provider":"PQR Energy","Volume":14.0,"Price":71.0,"EFA":4},{"Auction_Day":1,"Auction_Month":2,"Auction_Year":2023,"Unit":"PQR-3","Provider":"PQR Energy","Volume":19.0,"Price":30.0,"EFA":6}]]

I am expecting something like this.

enter image description here

Also, I tried the read_json command but it does not work. Thanks.

Tranbi · Accepted Answer · 2023-01-16 12:29:06Z

0

It seems that your json is actually a list of dictionaries inside a list. So you should first extract the nested list (at the first position, i.e. at index 0) to convert it to a dataframe:

import pandas as pd
import json

with open("file.json", "r") as f:
    data = json.load(f)
    df = pd.DataFrame(data[0])

answered Jan 16, 2023 at 12:29

Tranbi

12.8k6 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read .json file in pandas (python) [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related