Multiple relational tables to nested JSON format using Python
Use merge with left join first, then groupby with lambda function for dictionaries and convert to_dict, last add top key value and convert to json:
d = (df1.merge(df2, on='Emp_id', how='left') .groupby(['Emp_id','Gender','Age'])['Month','Incentive'] .apply(lambda x: [dict(x.values)]) .reset_index(name='Incentive') .to_dict(orient='records'))#print (d)import jsonjson = json.dumps({'data':d})print (json){ "data": [{ "Emp_id": 1, "Gender": "M", "Age": 32, "Incentive": [{ "Aug": 3000, "Sep": 3500, "Oct": 2000 }] }, { "Emp_id": 2, "Gender": "M", "Age": 35, "Incentive": [{ "Aug": 1500 }] }, { "Emp_id": 3, "Gender": "F", "Age": 31, "Incentive": [{ "Aug": 5000, "Sep": 2400 }] }]}