pandas pivot_table with dates as values

Question

let's say I have the following table of customer data

df = pd.DataFrame.from_dict({"Customer":[0,0,1], 
        "Date":['01.01.2016', '01.02.2016', '01.01.2016'], 
        "Type":["First Buy", "Second Buy", "First Buy"], 
        "Value":[10,20,10]})

which looks like this:

Customer |   Date   |   Type   |   Value
-----------------------------------------
       0 |01.01.2016|First Buy |     10 
-----------------------------------------
       0 |01.02.2016|Second Buy|     20 
-----------------------------------------
       1 |01.01.2016|First Buy |     10 

I want to pivot the table by the Type column. However, the pivoting only gives the numeric Value columns as a result. I'd desire a structure like:

 Customer | First Buy Date | First Buy Value | Second Buy Date | Second Buy Value
---------------------------------------------------------------------------------

where the missing values are NAN or NAT Is this possible using pivot_table. If not, I can imagine some workarounds, but they are quite lenghty. Any other suggestions?

Answer 1

Use unstack:

df1 = df.set_index(['Customer', 'Type']).unstack()
df1.columns = ['_'.join(cols) for cols in df1.columns]
print (df1)
         Date_First Buy Date_Second Buy  Value_First Buy  Value_Second Buy
Customer                                                                  
0            01.01.2016      01.02.2016             10.0              20.0
1            01.01.2016            None             10.0               NaN

If need another order of columns use swaplevel and sort_index:

df1 = df.set_index(['Customer', 'Type']).unstack()

df1.columns = ['_'.join(cols) for cols in df1.columns.swaplevel(0,1)]
df1.sort_index(axis=1, inplace=True)
print (df1)
         First Buy_Date  First Buy_Value Second Buy_Date  Second Buy_Value
Customer                                                                  
0            01.01.2016             10.0      01.02.2016              20.0
1            01.01.2016             10.0            None               NaN