Shifting column values in pandas dataframes forward one month
Is there a way to shift pandas-formatted column values one month forward? (note that I want the column value, 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 column B forward one month to get the desired result:
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 is fixed for each month (for example, the value in column B was 1 during September), so the problem that the number of days is slightly different each month shouldn't be a problem.
This is similar to Python / Pandas - DataFrame index - move forward one month , but in a related question, the OP wanted to shift the entire frame and I only want to shift the selected columns.
source to share
It's not too elegant, but you can do something like this:
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 grab the columns you want from res
source to share
You can first create a new pandas index Periods for each month and then find the value of each month and use the pandas auto-alignment to create a new column.
df1 = df.copy() orig_idx = df.index df1.index = orig_idx.to_period('M') col_b_new = df1.groupby(level=0)['ColumnB'].first().tshift(1) df1['ColumnB_new'] = col_b_new df1.index = orig_idx
Output
ColumnA ColumnB ColumnB_new
2016-10-01 1 0 1.0
2016-09-30 2 1 7.0
2016-09-29 5 1 7.0
2016-09-28 7 1 7.0
2016-09-01 3 1 7.0
2016-08-31 4 7 NaN
2016-08-30 4 7 NaN
2016-08-29 9 7 NaN
2016-08-28 10 7 NaN
source to share