Shift elements above the diagonal to the start of the row

Question

I have a matrix that was generated as a pivot table. I have included the data below. I need to turn the diagonal into the first column, which effectively re-orients the matrix so that the cell in the diagonal becomes the cell in the first column, for each row.

This is the matrix as rendered in Pandas

This is a representation of what the matrix should look like after.

df = pd.DataFrame({
    'exposure':[4500,2000, 2000, 2000, 2000, 
                6000,10000,3000,2000,1000,
                2000,3000,4000,6000], 
    'due_date':['2019-01-01', '2019-01-01', '2019-01-01', '2019-01-01', '2019-01-01', 
                '2019-01-02', '2019-01-02', '2019-01-02','2019-01-01','2019-01-04',
                '2019-01-03','2019-01-03','2019-01-03','2019-01-04'], 
    'repaid_date':['2019-01-01', '2019-01-04','2019-01-01', '2019-01-03', '2019-01-02', 
                   '2019-01-03','2019-01-04', '2019-01-02', '2019-01-03', '2019-01-04',
                   '2019-01-03','2019-01-04','2019-01-03','2019-01-04']})

pivot = df.pivot_table(values='exposure', index='due_date', columns='repaid_date', aggfunc=len)
pivot.fillna(0,inplace=True)
pivot.reset_index(inplace=True)

Apologies, forgot to follow up. Your solution was fatastic, thank you! — Stats DUB01, Jan 09 '19 at 12:27

score 1 · Accepted Answer · answered Jan 06 '19 at 16:29

Before filling or resetting the index, you can justify NaNs using Divakar's justify function.

pivot = df.pivot_table(values='exposure', 
                       index='due_date', 
                       columns='repaid_date', 
                       aggfunc='size')
pivot[:] = justify(pivot.values, invalid_val=np.nan, axis=1, side='left')
pivot.fillna(0, downcast='infer').reset_index()

repaid_date    due_date  2019-01-01  2019-01-02  2019-01-03  2019-01-04
0            2019-01-01           2           1           2           1
1            2019-01-02           1           1           1           0
2            2019-01-03           2           1           0           0
3            2019-01-04           2           0           0           0

Shift elements above the diagonal to the start of the row

1 Answers1