Pivot a DataFrame having dictionary as values in columns

Question

I have a dataframe as shown below:

Code    Description Date    Quantity
dasd2132    dummy 1 2023-06 {'prev_value': 0.0, 'new_value': 90.0}
312dasda    dummy 2 2023-07 0
das432cs    dummy 3 2023-08 0
31231dsa    dummy 4 2023-09 0

It can contain a dictionary as a value in its columns.

I want to pivot this dataframe and have the Date column values be displayed as column headers and the quantity to be the column values:

Code    Description 2023-06                                 2023-07 2023-08 2023-09
dasd2132    dummy 1 {'prev_value': 0.0, 'new_value': 90.0}  0   0   0

Is there any way to do this in Python?

Please suggest an optimized solution. Thank you.

I tried to do this using pivot_table:

pivoted_df = pd.pivot_table(df, index = ['Code', 'Description'], values = 'Quantity', columns = 'Date', sort = False)

However, this gives error: TypeError: unhashable type: 'dict'

Answer 1

Using a custom pivot:

# identify non numeric values
m = pd.to_numeric(df['Quantity'], errors='coerce').isna()

out = (df  # keep only first Code/Description
   .assign(Code=lambda d: d['Code'].where(m).ffill(),
           Description=lambda d: d['Description'].where(m).ffill(),
           cols=lambda d: d.groupby(m.cumsum()).cumcount(),
          )
   .pivot(index=['Code', 'Description'], columns=['Date', 'cols'],
          values='Quantity')
   .droplevel(1, axis=1).reset_index().rename_axis(columns=None)
)

Output:

       Code Description                                 2023-06 2023-06 2023-06 2023-06
0  dasd2132     dummy 1  {'prev_value': 0.0, 'new_value': 90.0}       0       0       0