Taking column headers from column values while performing a pivot

user_10816754 0
Viewed 361

I have a DataFrame that looks like this:

Customer    ProductCategory    TimeFrame    Sales    Returns
Greg        Soap               CY           5        1
Phil        Towels             PY           2        3
Greg        Soap               PY           7        
Greg        Towels             CY           3

I would like to pivot this DataFrame so that I get something like this:

Customer    ProductCategory    Measure    CY    PY
Greg        Soap               Sales      5     7
Greg        Soap               Returns    1      
Greg        Towels             Sales      3     
Greg        Towels             Returns           
Phil        Towels             Sales            2
Phil        Towels             Returns          3

The missing values above should be null. I don't want to fill them.

I've tried some different methods. The closest I've gotten is by using pivot_table along with melt, groupby and stack:

pd.pivot_table(df, values=['Sales', 'Returns'], index=['Customer', 'ProductCategory'], columns=['TimeFrame'], aggfunc=np.sum) \
.reset_index() \
.melt(id_vars=['Customer', 'ProductCategory']) \
.rename(columns={None: 'Measure'}) \
.groupby(by=['Customer', 'ProductCategory', 'Measure', 'TimeFrame'])['value'] \
.aggregate('first') \
.unstack() \
.reset_index()

This results in the following:

TimeFrame   Customer    ProductCategory    Measure    CY    PY
0           Greg        Soap               Returns    1.0   0.0
1           Greg        Soap               Sales      5.0   7.0
2           Greg        Towels             Returns    0.0   NaN
3           Greg        Towels             Sales      3.0   NaN
4           Phil        Towels             Returns    NaN   3.0
5           Phil        Towels             Sales      NaN   2.0

The issue here is that some of the null numeric values have been filled with zeros. Also, the index is now named "TimeFrame" even though I applied reset_index?

Finally, I'm curious if there is a more efficient way to do this that I'm missing. Since pivot_table gets me most of the way there, is there a parameter I'm missing or something like that?

pandas pivot-table dataframe python
1 Answers

You just need melt and pivot_table:

(df.melt(['Customer','ProductCategory','TimeFrame'], var_name='Measure')
   .pivot_table(index=['Customer','ProductCategory','Measure'],
                columns='TimeFrame',values='value')
   .reset_index()
)

Output:

TimeFrame Customer ProductCategory  Measure   CY   PY
0             Greg            Soap  Returns  1.0  NaN
1             Greg            Soap    Sales  5.0  7.0
2             Greg          Towels    Sales  3.0  NaN
3             Phil          Towels  Returns  NaN  3.0
4             Phil          Towels    Sales  NaN  2.0

To remove the name of index/column, chain the above with rename_axis(index=None,columns=None)

user_42384080
Related