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'

I suggest you use the JSON dumps() method to turn the dictionary into a string. — binaryescape, Aug 01 '23 at 14:09
So you want 4 columns with the same column name? Why shouldn't each code form it's own row? — not_speshal, Aug 01 '23 at 14:23
@not_speshal, no that is my mistake; the dates are supposed to be different like 2023-06, 2023-07, 2023-08 and so on. — Noor Fatimah, Aug 01 '23 at 15:11
@NoorFatimah - Please edit your post to reflect the correct input and expected output. — not_speshal, Aug 01 '23 at 15:15

score 1 · Answer 1 · answered Aug 01 '23 at 14:18

Try using pd.pivot() instead. It doesn't give the same error as pivot_table. For example:

qwe = pd.DataFrame({"Code": ['abc', 'xyz'], "Date": ["dummy 1", "dummy2"], "Quantity": [{'prev_value': 0.0, 'new_value': 90.0},1]})
qwe.pivot(["Code"],["Date"], ["Quantity"] )

score 0 · Accepted Answer · answered Aug 01 '23 at 14:18

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

Pivot a DataFrame having dictionary as values in columns

2 Answers2