[Tutor] DataFrame to Nested JSON Conversion issue

[Mats Wichmann]

On 11/24/20 11:37 PM, Anitha Kothandan wrote:
> 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))

you're doing so many things in one go that there's no way to see what's 
going wrong:

Data=Data.groupby(['Specversion','PolicyID'])[['EventID','Division']].apply(lambda 
g: g.to_dict(orient="records")).reset_index(name="Policy")

something there is making a list with the dict as an element (which then 
has to be constructed in json in the way you don't want), rather than 
just the dict. Simplify into discrete steps with examination at each 
step if possible, so you can watch the data get transformed.