Python Pandas: conversion - moving values ​​from a diagonal

Question

given the following DataFrame, grouped with:

dataset = z.groupby( ['app', 'regmonth', 'loginsmonth']).sum().unstack().fillna( 0, inplace=False) cnt loginsmonth 2014-02-01 2014-03-01 2014-04-01 2014-05-01 app regmonth 1 2014-02-01 6069 1837 107 54 2014-03-01 0 10742 2709 1394 2014-04-01 0 0 5584 1107 2014-05-01 0 0 0 3044 2014-06-01 0 0 0 0 

I would like to convert it to:

  cnt loginsmonth 2014-02-01 2014-03-01 2014-04-01 2014-05-01 app regmonth 1 2014-02-01 6069 1837 107 54 2014-03-01 10742 2709 1394 0 2014-04-01 5584 1107 0 0 2014-05-01 3044 0 0 0 2014-06-01 0 0 0 0 

Thus, it moves the diagonal to the beginning of the line and fills the void with zeros. Does panda have any easy way to do this?

Answer 1

But you change your data in the process, right?

I don't know if pandas good way to do this, but np.diagnoal can do what you want here:

 In [96]: print df loginsmonth 2014-02-01 2014-03-01 2014-04-01 2014-05-01 app regmonth 1 2014-02-01 6069 1837 107 54 2014-03-01 0 10742 2709 1394 2014-04-01 0 0 5584 1107 2014-05-01 0 0 0 3044 2014-06-01 0 0 0 0 [5 rows x 4 columns] In [124]: print df*0+np.asarray([np.hstack((np.diagonal(df.values, i), np.zeros(i+1, int))) for i in range(df.shape[1])]).T loginsmonth 2014-02-01 2014-03-01 2014-04-01 2014-05-01 app regmonth 1 2014-02-01 6069 1837 107 54 2014-03-01 10742 2709 1394 0 2014-04-01 5584 1107 0 0 2014-05-01 3044 0 0 0 2014-06-01 0 0 0 0 [5 rows x 4 columns] 

here 1 in np.zeros(i+1, int) is df.shape[0]-df.shape[1] . I do not know if you will have a situation where df.shape[0] < df.shape[1] .

But if your DataFrame always looks like the one shown here, which looks like an upper diagonal matrix with no 0 inside, you can just take a short shot:

 In [134]: print df.apply(lambda x: sorted(x, key=lambda y: y==0), axis=1) cnt 2014-02-01 2014-03-01 2014-04-01 2014-05-01 app regmonth 1 2014-02-01 6069 1837 107 54 2014-03-01 10742 2709 1394 0 2014-04-01 5584 1107 0 0 2014-05-01 3044 0 0 0 2014-06-01 0 0 0 0 [5 rows x 4 columns]