Get delta values of each quarter from cumulated income statement reports with pandas

Question

Get value of each quarter from cumulated income statement reports with pandas

Is there any way to do it with python/pandas?

I have an example dataset like below. (please suppose that this company's fiscal year is from Jan to Dec)

qend    revenue profit
2015-03-31  2,453   298
2015-06-30  5,076   520
2015-09-30  8,486   668
2015-12-31  16,724  820
2016-03-31  1,880   413
2016-06-30  3,989   568
2016-09-30  7,895   621
2016-12-31  16,621  816

I want to know how much revenue and profit that this company earns per each quarter. But the report is only showing the number in cumulative.

In this case, Q1 is fine. But from Q2-Q4, I have to get the difference from each last quarter.

This is my expecting results.

qend    revenue profit  mycommment
2015-03-31  2,453   298 copy from Q1  
2015-06-30  2,623   222 delta of Q1 and Q2
2015-09-30  3,410   148 delta of Q2 and Q3
2015-12-31  8,238   152 delta of Q3 and Q4
2016-03-31  1,880   413 copy from Q1  
2016-06-30  2,109   155 delta of Q1 and Q2
2016-09-30  3,906   53  delta of Q2 and Q3
2016-12-31  8,726   195 delta of Q3 and Q4

The difficulty is it is not simply getting delta from last row, because each Q1 needs no delta value while rest of Q2-4 needs delta value. If there is no easy way in pandas, I'll code it with python.

Answer 1

I think you need quarter for find first and then add value of diff by condition:

m = df['qend'].dt.quarter == 1
df['diff_profit'] = np.where(m, df['profit'], df['profit'].diff())
#same as
#df['diff_profit'] = df['profit'].where(m, df['profit'].diff())
print (df)
        qend revenue  profit  diff_profit
0 2015-03-31   2,453     298        298.0
1 2015-06-30   5,076     520        222.0
2 2015-09-30   8,486     668        148.0
3 2015-12-31  16,724     820        152.0
4 2016-03-31   1,880     413        413.0
5 2016-06-30   3,989     568        155.0
6 2016-09-30   7,895     621         53.0
7 2016-12-31  16,621     816        195.0

Or:

df['diff_profit'] = np.where(m, df['profit'], df['profit'].shift() - df['profit'])
print (df)
        qend revenue  profit  diff_profit
0 2015-03-31   2,453     298        298.0
1 2015-06-30   5,076     520       -222.0
2 2015-09-30   8,486     668       -148.0
3 2015-12-31  16,724     820       -152.0
4 2016-03-31   1,880     413        413.0
5 2016-06-30   3,989     568       -155.0
6 2016-09-30   7,895     621        -53.0
7 2016-12-31  16,621     816       -195.0

Detail:

print (df['qend'].dt.quarter)
0    1
1    2
2    3
3    4
4    1
5    2
6    3
7    4
Name: qend, dtype: int64