Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Shifting the values of a column in pandas dataframe one month forward

Is there a way to shift the values of a column in pandas dataframe one month forward? (note that I want to thift the column value and not the date value). For example, if I have:

           ColumnA   ColumnB
2016-10-01       1  0
2016-09-30       2  1
2016-09-29       5  1
2016-09-28       7  1
.
.
2016-09-01       3  1
2016-08-31       4  7
2016-08-30       4  7
2016-08-29       9  7
2016-08-28       10  7

Then I want to be able to shift the values in ColumnB one month forward, to get the desired output:

           ColumnA   ColumnB
2016-10-01       1  1
2016-09-30       2  7
2016-09-29       5  7
2016-09-28       7  7
.
.
2016-09-01       3  7
2016-08-31       3  X
2016-08-30       4  X
2016-08-29       9  x
2016-08-28       10  x

In the data I have, the value if fixed for each month (for example, the value in ColumnB was 1 during september), so the fact that the number of days is a bit different each month should not be a problem.

This seems related Python/Pandas - DataFrame Index - Move one month forward, but in the linked question the OP wanted to shift the whole frame, and I want to shift only selected columns.

like image 396
Miriam Farber Avatar asked Aug 31 '25 18:08

Miriam Farber


1 Answers

It is not too elegant, but you can do something like that:

df=df.reset_index()
df['index']=pd.to_datetime(df['index'],infer_datetime_format=True)
df['offset']=df['index']-pd.DateOffset(months=1)
res=df.merge(df,right_on='index',left_on='offset',how='left')

and just take from res the columns you want

like image 78
Binyamin Even Avatar answered Sep 02 '25 06:09

Binyamin Even