[Tutor] DataFrame to Nested JSON Conversion issue

[Anitha Kothandan]

Hi Guys,

I am trying to convert a DataFrame into Nested json. The Policy column value should be an json object instead of an array(square brackets should not be there). Mentioned the code, expected output and screenshots below. Kindly help me out here. Thanks in Advance!!

Code:
import pandas as pd
import json
Data=pd.DataFrame()
x='{"Specversion":"1.0","PolicyID":"0123456789","EventID":"20201124030503-1000","Division":"LID"}'
Data=pd.io.json.json_normalize(json.loads(x))
Data=Data.groupby(['Specversion','PolicyID'])[['EventID','Division']].apply(lambda g: g.to_dict(orient="records")).reset_index(name="Policy")
Data['Final_json']=Data.apply(lambda x: x.to_json(date_format='iso'), axis=1)
print(json.dumps(json.loads(Data['Final_json'][0]),indent=2,sort_keys=False))

Expected Output:
{
  "Specversion": "1.0",
  "PolicyID": "0123456789",
  "Policy":
    {
      "EventID": "20201124030503-1000",
      "Division": "LID"
    }
}

Screenshot
[cid:image001.png at 01D6C323.74E7D370]


Thanks,
Anitha