0

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
onr
  • 296
  • 4
  • 18
  • It will be easier to reproduce the [mre] if you post the raw dataframe and the code used to produce the `groupby`. Please see [How to provide a reproducible copy of your DataFrame using `df.head(30).to_clipboard(sep=',')`](https://stackoverflow.com/questions/52413246), then **[edit] your question**, and paste the clipboard into a code block. Always provide a [mre] **with code, data, errors, current output, and expected output, as text**. If relevant, plot images are okay. – Trenton McKinney Sep 30 '20 at 18:26
  • You probably have to reset the index first, and then `shift`, but I'm just guessing, w/o a reproducible dataframe to run code on. – Trenton McKinney Sep 30 '20 at 18:28

0 Answers0