Rolling difference between columns with period as parameter

user_13067389 0
Viewed 17

I have a dataframe:

df = pd.DataFrame({ 
    'BU': ['Total', 'Total', 'Total', 'ABC', 'ABC', 'ABC'], 
    'Line_Item': ['Revenues','EBT', 'Expenses', 'Revenues', 'EBT', 'Expenses'],
    '1Q16': [100, 120, 200, 200, 190, 210],
    '2Q16': [120, 120, 100, 200, 190, 210],
    '3Q16': [200, 250, 450, 120, 180, 190],
    '4Q16': [150, 850, 790, 410, 170, 990],
    '1Q17': [120, 240, 790, 10, 170, 990],
    '2Q17': [110, 140, 650, 50, 100, 120]})

I want new columns with the rolling difference between the columns. So I want the annual differences, which is, say, the difference in revenues in 1Q16 and 1Q17. I also want the differences between quarters. The period between the columns should a be parameter.

I know how to take the percent change by appling the pct_change function.

df_qtrs_YoY = df[['1Q16', '2Q16', '3Q16', '4Q16', '1Q17', '2Q17']].pct_change(periods=4, axis=1).dropna(1)

Could I also do so with difference? My dataset is large. So a vectorized solution might work best. The output should look like:

df1 = pd.DataFrame({ 
    'BU': ['Total', 'Total', 'Total', 'ABC', 'ABC', 'ABC'], 
    'Line_Item': ['Revenues','EBT', 'Expenses', 'Revenues', 'EBT', 'Expenses'],
    '1Q17': [20, 140, 590, -190, -20, 0],
    '2Q17': [-10, 20, -30, -150, -90, -90]})
rolling-computation dataframe
1 Answers

Maybe you're looking for .diff() function?

diff_qtrs_YoY = df[["1Q16", "2Q16", "3Q16", "4Q16", "1Q17", "2Q17"]].diff(
    periods=4, axis=1
)
print(df_qtrs_YoY)

Prints:

   1Q16  2Q16  3Q16  4Q16   1Q17   2Q17
0   NaN   NaN   NaN   NaN   20.0  -10.0
1   NaN   NaN   NaN   NaN  120.0   20.0
2   NaN   NaN   NaN   NaN  590.0  550.0
3   NaN   NaN   NaN   NaN -190.0 -150.0
4   NaN   NaN   NaN   NaN  -20.0  -90.0
5   NaN   NaN   NaN   NaN  780.0  -90.0

Or between each quarter:

print(
    df[["1Q16", "2Q16", "3Q16", "4Q16", "1Q17", "2Q17"]].diff(periods=1, axis=1)
)

   1Q16   2Q16   3Q16   4Q16   1Q17   2Q17
0   NaN   20.0   80.0  -50.0  -30.0  -10.0
1   NaN    0.0  130.0  600.0 -610.0 -100.0
2   NaN -100.0  350.0  340.0    0.0 -140.0
3   NaN    0.0  -80.0  290.0 -400.0   40.0
4   NaN    0.0  -10.0  -10.0    0.0  -70.0
5   NaN    0.0  -20.0  800.0    0.0 -870.0
user_100359850
Related