Convert result from groupby on multiple columns to list of dictionaries

Question

I am trying to convert result from pandas groupby function to a array of dictionary which can then be used by recharts in the frontend.

Code run:

data.groupby(['date','board_type'])['count'].sum()

Output from groupby:

date        board_type
2021-01-02  B1        11
            B2       105
2021-01-03  B1        30
            B2        73
2021-01-04  B1       188

expected output after conversion:

[
  {
    date: 2021-01-02, B1: 11, B2: 105
  },
  {
    date: 2021-01-03, B1: 30, B2: 73
  },
  {
    date: 2021-01-04, B1: 188, B2: 0
  }
]

I tried using .to_dict but that doesn't seem to work. Please suggest how this can be achieved.

Answer 1

You are close. The key is to convert the result of that groupby back to a DataFrame with date as index and B1 and B2 as columns (it was a Series). This can be done using unstack. Then you can get what you want using DataFrame's to_dict this way:

data.groupby(['date', 'board_type'])['count'].sum().unstack(fill_value=0).reset_index().to_dict(orient='records')

Output:

[{'date': '2021-01-02', 'B1': 11, 'B2': 105},
 {'date': '2021-01-03', 'B1': 30, 'B2': 73},
 {'date': '2021-01-04', 'B1': 188, 'B2': 0}]