Using pandas to calculate over-the-month and over-the-year change

user_5103569 0
Viewed 8026

I can't wrap head around how to do this, but I want to go from this DataFrame:

Date    Value
Jan-15  300
Feb-15  302
Mar-15  303
Apr-15  305
May-15  307
Jun-15  307
Jul-15  305
Aug-15  306
Sep-15  308
Oct-15  310
Nov-15  309
Dec-15  312
Jan-16  315
Feb-16  317
Mar-16  315
Apr-16  315
May-16  312
Jun-16  314
Jul-16  312
Aug-16  313
Sep-16  316
Oct-16  316
Nov-16  316
Dec-16  312

To this one by calculating over-the-month and over-the-year change:

Date    Value  otm  oty
Jan-15  300    na   na
Feb-15  302    2    na
Mar-15  303    1    na
Apr-15  305    2    na
May-15  307    2    na
Jun-15  307    0    na
Jul-15  305    -2   na
Aug-15  306    1    na
Sep-15  308    2    na
Oct-15  310    2    na
Nov-15  309    -1   na
Dec-15  312    3    na
Jan-16  315    3    15
Feb-16  317    2    15
Mar-16  315    -2   12
Apr-16  315    0    10
May-16  312    -3   5
Jun-16  314    2    7
Jul-16  312    -2   7
Aug-16  313    1    7
Sep-16  316    3    8
Oct-16  316    0    6
Nov-16  316    0    7
Dec-16  312    -4   0

So otm is calculated from the value of the field above and oty is calculated from 12 fields above.

pandas python
3 Answers

More correct solution is to shift by month frequency:

#Create datetime column
df['DateTime'] = pd.to_datetime(df['Date'], format='%b-%y')

#Set it as index
df.set_index('DateTime', inplace=True)

#Then shift by month frequency:
df['otm'] = df['Value'] - df['Value'].shift(1, freq='MS')
df['oty'] = df['Value'] - df['Value'].shift(12, freq='MS')
user_109383520
Related