pythonpandasgroup-byto-json

Pandas to_json with groupby

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.

Solution

  • 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'}]}