Skip to content
Advertisement

extract key and values from nested jsons and put in a DataFrame

I have a json object that looks like this:

{
  "2022-06-05":{
    "revenue":"287.05",
    "returns_amount":"0.00",
    "date":"2022-06-05"
  },
  "2022-06-06":{
    "revenue":"229.76",
    "returns_amount":"0.00",
    "date":"2022-06-06"
  },
  "2022-06-07":{
    "revenue":"0.00",
    "returns_amount":"0.00",
    "date":"2022-06-07"
  }
}

Is it possible to extract all dates (key) along with their respective “revenue” values and put them into a dataframe that looks likethis:

date              revenue
2022-06-05        287.05
2022-06-06        229.76
2022-06-07        0.00

Advertisement

Answer

How about something like:

import pandas as pd
import json

data = []   # list to hold data
file_name = "file.json" # JSON file

with open(file_name) as f:
    doc = json.load(f)
    for key in doc:
        data.append(doc[key])   # just need object, not outer date "key"

    df = pd.DataFrame(data=data, columns=["date", "revenue"])
    print(df)
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement