1

I have the following dataframe:

key         category    amount
2019-01     abc         123
2019-01     xyz         111
2019-02     abc         87
2019-02     xyz         34
2019-03     abc         15
2019-04     def         64

How would I go about transforming this data into the below list of dicts. Essentially each dict represents a category along with all dates and corresponding amounts for that category:

[
  {
    "category": "abc",
    "2019-01": 123,
    "2019-02": 87,
    "2019-03": 15
  },
  {
    "category": "xyz",
    "2019-01": 111,
    "2019-02": 34
  },
  {
    "category": "def,
    "2019-04": 64
  }
]
darkpool
  • 13,822
  • 16
  • 54
  • 89
  • Does this answer your question? [Pandas DataFrame to List of Dictionaries](https://stackoverflow.com/questions/29815129/pandas-dataframe-to-list-of-dictionaries) – anonymous Jan 30 '20 at 09:28

1 Answers1

6

Use lambda function in GroupBy.apply with merge 2 dictionaries:

f = lambda x: {**{'category': x.name}, **dict(zip(x['key'], x['amount']))}
L = df.groupby('category', sort=False).apply(f).tolist()
print (L)
[{'category': 'abc', '2019-01': 123, '2019-02': 87, '2019-03': 15}, 
 {'category': 'xyz', '2019-01': 111, '2019-02': 34},
 {'category': 'def', '2019-04': 64}]
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252