For a pandas
dataframe like this
key val1 val2
a a1 a2
a b1 b2
c c1 c2
How can I convert it to the following json
? Need to group on 'key' and use its values as the keys in the json:
{
"a":[{"val1":"a1",
"val2":"a2"},
{"val1":"b1",
"val2":"b2"}],
"c":[{"val1":"c1",
"val2":"c2"}]
}
Tried using df.groupby('key').apply(lambda x: x.to_json(orient='records'))
but it includes the key in the json array.
CodePudding user response:
Using .groupby and .to_dict() with "records"
df = df.groupby("key")[["val1", "val2"]].apply(lambda x: x.to_dict(orient="records")).to_json()
print(df)
Output:
{'a': [{'val1': 'a1', 'val2': 'a2'}, {'val1': 'b1', 'val2': 'b2'}], 'c': [{'val1': 'c1', 'val2': 'c2'}]}