GroupBy results to list of dictionaries, Using the grouped by object in it

My DataFrame looks like so:

Date Column1 Column2
1.1       A      1
1.1       B      3
1.1       C      4
2.1       A      2
2.1       B      3
2.1       C      5
3.1       A      1
3.1       B      2
3.1       C      2

And I’m looking to group it by Date and extract that data to a list of dictionaries so it appears like this:

[
  {
    "Date": "1.1",
    "A": 1,
    "B": 3,
    "C": 4
  },
  {
    "Date": "2.1",
    "A": 2,
    "B": 3,
    "C": 5
  },
  {
    "Date": "3.1",
    "A": 1,
    "B": 2,
    "C": 2
  }
]

This is my code so far:

df.groupby('Date')['Column1', 'Column2'].apply(lambda g: {k, v for k, v in g.values}).to_list()

Using this method can’t use my grouped by objects in the apply method itself:

[
  {

    "A": 1,
    "B": 3,
    "C": 4
  },
  {
    "A": 2,
    "B": 3,
    "C": 5
  },
  {
    "A": 1,
    "B": 2,
    "C": 2
  }
]

Using to_dict() giving me the option to reach the grouped by object, but not to parse it to the way I need.

Anyone familiar with some elegant way to solve it?

Thanks!!

Answer

You could first reshape your data using df.pivot, reset the index, and then apply to_dict to the new shape with the orient parameter set to “records”. So:

import pandas as pd

data = {'Date': ['1.1', '1.1', '1.1', '2.1', '2.1', '2.1', '3.1', '3.1', '3.1'],
 'Column1': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C'],
 'Column2': [1, 3, 4, 2, 3, 5, 1, 2, 2]}

df = pd.DataFrame(data)

df_pivot = df.pivot(index='Date',columns='Column1',values='Column2')
    .reset_index(drop=False)
result = df_pivot.to_dict('records')

target = [{'Date': '1.1', 'A': 1, 'B': 3, 'C': 4},
 {'Date': '2.1', 'A': 2, 'B': 3, 'C': 5},
 {'Date': '3.1', 'A': 1, 'B': 2, 'C': 2}]

print(result == target)
# True