pandas pivot table giving me aggregation error

Question

Have a dataframe that looks like this:

      Unique_ID    Due_Date                     Provider
0           731   3/05/2019         Optus Mobile Pty Ltd
1           733   3/05/2019         Optus Mobile Pty Ltd
2           747  23/04/2019         Optus Mobile Pty Ltd
3           747  21/04/2019         Optus Mobile Pty Ltd
4           747  17/04/2019         Optus Mobile Pty Ltd

I'd like to perform a pivot_table to achieve the following:

      Unique_ID    Due_Date_1                   Provider_1    Due_Date_2  Provider_2              Due_Date_3  Provider_3
0           731    3/05/2019          Optus Mobile Pty Ltd
1           733    3/05/2019          Optus Mobile Pty Ltd
2           747    23/04/2019         Optus Mobile Pty Ltd    21/04/2019  Optus Mobile Pty Ltd    17/04/2019  Optus Mobile Pty Ltd

I am trying somethinng like the following:

df.pivot_table(index=['Unique_ID'], columns='Due_Date', values='Provider')

but I am getting returned a bunch of errors.

pandas.core.base.DataError: No numeric types to aggregate

Note, this is a snippet of a much larger dataset, and there will be many Unique_ID to perform on.

Do I need to perform a groupby first?

See: https://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape-by-two-variables. When you use `pivot_table` the default aggregation is to take a mean. You have only strings so it complains. Either create the helper and use pivot, or can use pivot_table with `aggfunc='first'` (though still need the cumcount helper) — ALollz, May 06 '19 at 17:26

score 3 · Answer 1 · answered May 06 '19 at 17:27

df['key']=(df.groupby('Unique_ID').cumcount()+1).astype(str)
s=df.pivot_table(index='Unique_ID',columns='key',values=['Due_Date','Provider'],aggfunc='first').sort_index(level=1,axis=1)
s.columns=s.columns.map('_'.join)
s
Out[54]: 
           Due_Date_1 Provider_1  Due_Date_2 Provider_2  Due_Date_3 Provider_3
Unique_ID                                                                     
731         3/05/2019      Optus         NaN        NaN         NaN        NaN
733         3/05/2019      Optus         NaN        NaN         NaN        NaN
747        23/04/2019      Optus  21/04/2019      Optus  17/04/2019      Optus

pandas pivot table giving me aggregation error

1 Answers1