How To Create A Nested Json From Pandas Dataframe?

I am trying to generate a nested JSON from a DataFrame, where attributes of a car are distributed in several rows. DataFrame cars = {'brand': ['Honda','Toyota','Ford','Audi','Honda

Solution 1:

In your solution is added rename with reset_index():

d = {'attributeName':'name','attributeValue':'value'}
j = df.rename(columns=d).groupby(['brand','model']).apply(lambda x: x[['name','value']].to_dict('records')).reset_index(name='attributes').to_json(orient='records')
print (j)
[{"brand":"Audi","model":"A4","attributes":[{"name":"color","value":"red"},{"name":"doors","value":2}]},{"brand":"Ford","model":"Focus","attributes":[{"name":"color","value":"black"},{"name":"doors","value":4}]},{"brand":"Honda","model":"Civic","attributes":[{"name":"color","value":"red"},{"name":"doors","value":2}]},{"brand":"Toyota","model":"Corolla","attributes":[{"name":"color","value":"blue"},{"name":"doors","value":4}]}]

Or:

d = {'attributeName':'name','attributeValue':'value'}
j = df.rename(columns=d).groupby(['brand','model']).apply(lambda x: x[['name','value']].to_dict('records')).explode().apply(lambda x: [x]).reset_index(name='attributes').to_json(orient='records')
print (j)
[{"brand":"Audi","model":"A4","attributes":[{"name":"color","value":"red"}]},{"brand":"Audi","model":"A4","attributes":[{"name":"doors","value":2}]},{"brand":"Ford","model":"Focus","attributes":[{"name":"color","value":"black"}]},{"brand":"Ford","model":"Focus","attributes":[{"name":"doors","value":4}]},{"brand":"Honda","model":"Civic","attributes":[{"name":"color","value":"red"}]},{"brand":"Honda","model":"Civic","attributes":[{"name":"doors","value":2}]},{"brand":"Toyota","model":"Corolla","attributes":[{"name":"color","value":"blue"}]},{"brand":"Toyota","model":"Corolla","attributes":[{"name":"doors","value":4}]}]

---

df['attributes'] = df.apply(lambda x: [{'name': x['attributeName'], 'value': x['attributeValue']}], axis=1)
df = df.drop(['attributeName','attributeValue'], axis=1)
print (df)
    brand    model                             attributes
0   Honda    Civic    [{'name': 'color', 'value': 'red'}]
1  Toyota  Corolla   [{'name': 'color', 'value': 'blue'}]
2    Ford    Focus  [{'name': 'color', 'value': 'black'}]
3    Audi       A4    [{'name': 'color', 'value': 'red'}]
4   Honda    Civic        [{'name': 'doors', 'value': 2}]
5  Toyota  Corolla        [{'name': 'doors', 'value': 4}]
6    Ford    Focus        [{'name': 'doors', 'value': 4}]
7    Audi       A4        [{'name': 'doors', 'value': 2}]

j = df.to_json(orient='records')
print (j)
[{"brand":"Honda","model":"Civic","attributes":[{"name":"color","value":"red"}]},{"brand":"Toyota","model":"Corolla","attributes":[{"name":"color","value":"blue"}]},{"brand":"Ford","model":"Focus","attributes":[{"name":"color","value":"black"}]},{"brand":"Audi","model":"A4","attributes":[{"name":"color","value":"red"}]},{"brand":"Honda","model":"Civic","attributes":[{"name":"doors","value":2}]},{"brand":"Toyota","model":"Corolla","attributes":[{"name":"doors","value":4}]},{"brand":"Ford","model":"Focus","attributes":[{"name":"doors","value":4}]},{"brand":"Audi","model":"A4","attributes":[{"name":"doors","value":2}]}]