Shift vales based on the first level index (Timestamp)

57 Views Asked by At

Is there a better way how to shift the values of a dataframe column based only on the first level of index? For each week I have calculated the weekly sum (Column 1) and I want the second columns the sum shifted one week before.

I managed to do this by shifting with (5*length of the second index). (5-because weekends are excluded)

Is there a way to shift values based on timestamp indexes?

df_test.to_clipboard(sep=',', index=True)
pd.read_clipboard(sep=',')

a=len(df_test.index.get_level_values(1).unique())
df_test['1_weeks_ago_sum'] =  df_test['weekly_sum'].shift(5*a)



               weekly_sum 1_weeks_ago_sum
date    level_1     
2019-01-16  AMB 3888.0  NaN
            COL 4774.0  NaN
            DGC 203.0   NaN
            DGZ 297.0   NaN
            FRZ 164.0   NaN
2019-01-17  AMB 3888.0  NaN
            COL 4774.0  NaN
            DGC 203.0   NaN
            DGZ 297.0   NaN
            FRZ 164.0   NaN
2019-01-18  AMB 3888.0  NaN
            COL 4774.0  NaN
            DGC 203.0   NaN
            DGZ 297.0   NaN
            FRZ 164.0   NaN
2019-01-21  AMB 6407.0  NaN
            COL 7200.0  NaN
            DGC 436.0   NaN
            DGZ 412.0   NaN
            FRZ 322.0   NaN
2019-01-22  AMB 6407.0  NaN
            COL 7200.0  NaN
            DGC 436.0   NaN
            DGZ 412.0   NaN
            FRZ 322.0   NaN
2019-01-23  AMB 6407.0  3888.0
            COL 7200.0  4774.0
            DGC 436.0   203.0
            DGZ 412.0   297.0
            FRZ 322.0   164.0
2019-01-24  AMB 6407.0  3888.0
            COL 7200.0  4774.0
            DGC 436.0   203.0
            DGZ 412.0   297.0
            FRZ 322.0   164.0
2019-01-25  AMB 6407.0  3888.0
            COL 7200.0  4774.0
            DGC 436.0   203.0
            DGZ 412.0   297.0
            FRZ 322.0   164.0
2019-01-28  AMB 6253.0  6407.0
            COL 7440.0  7200.0
            DGC 535.0   436.0
            DGZ 429.0   412.0
            FRZ 397.0   322.0
2019-01-29  AMB 6253.0  6407.0
            COL 7440.0  7200.0
            DGC 535.0   436.0
            DGZ 429.0   412.0
            FRZ 397.0   322.0
2019-01-30  AMB 6253.0  6407.0
            COL 7440.0  7200.0
            DGC 535.0   436.0
            DGZ 429.0   412.0
            FRZ 397.0   322.0
0

There are 0 best solutions below