Read .json file in pandas (python)

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.

>Solution :

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])

Leave a Reply