Pandas Relative Time Pivot

Question

I have the last eight months of my customers' data, however these months are not the same months, just the last months they happened to be with us. Monthly fees and penalties are stored in rows, but I want each of the last eight months to be a column.

What I have:

Customer Amount Penalties Month
123      500    200       1/7/2017
123      400    100       1/6/2017
   ...
213      300    150       1/4/2015
213      200    400       1/3/2015

What I want:

Customer Month-8-Amount Month-7-Amount ... Month-1-Amount Month-1-Penalties ...
123      500            400                450            300
213      900            250                300            200
...

What I've tried:

df = df.pivot(index=num, columns=[amount,penalties])

I got this error:

ValueError: all arrays must be same length

Is there some ideal way to do this?

Answer 1

You can do it with unstack and set_index

# assuming all date is sort properly , then we do cumcount
df['Month']=df.groupby('Customer').cumcount()+1 

# slice the most recent 8 one 
df=df.loc[df.Month<=8,:]# slice the most recent 8 one 

# doing unstack to reshape your df 
s=df.set_index(['Customer','Month']).unstack().sort_index(level=1,axis=1)

# flatten multiple index to one 
s.columns=s.columns.map('{0[0]}-{0[1]}'.format) 
s.add_prefix("Month-")
Out[189]: 
          Month-Amount-1  Month-Penalties-1  Month-Amount-2  Month-Penalties-2
Customer                                                                      
123                  500                200             400                100
213                  300                150             200                400