Pandas: Calculate difference with fixed intervals and change to varying intervals to use earliest records when no more records

user_11729738 0
Viewed 43

I got a DataFrame with monthly record, and I would like to calculate the difference between latest month with the one before 6 months. However, for those who didn't have completed 6 months record, can I calculate the difference with the one it has the earliest record like below.

Client       Month   CAV 
 A          2021-09   30  
 A          2021-08   20
 A          2021-07   10
 A          2021-06    5
 A          2021-05   10
 A          2021-04    5
 A          2021-03   10
 B          2021-08   50
 B          2021-07   10
 B          2021-06   30

I used df['CAV_diff'] = df.groupby('Client')['CAV'].diff(-5), it will get:

 Client        Month   CAV     CAV_diff  
   A          2021-09   30        25 (=30-5) 
   A          2021-08   20        10 (=20-10)
   A          2021-07   10        N/A
   A          2021-06    5        N/A
   A          2021-05   10        N/A
   A          2021-04    5        N/A
   A          2021-03   10        N/A
   B          2021-08   50        N/A
   B          2021-07   10        N/A
   B          2021-06   30        N/A

Can I get the result as below:

 Client        Month   CAV     CAV_diff  
   A          2021-09   30        25 (=30-5)  
   A          2021-08   20        10 (=20-10)
   A          2021-07   10        0  (=10-10)
   A          2021-06    5        -5 (=5-10)
   A          2021-05   10        0
   A          2021-04    5        -5
   A          2021-03   10        0
   B          2021-08   50        20
   B          2021-07   10        -20
   B          2021-06   30        0
pandas diff python
1 Answers

You can use .groupby() + shift() + fillna() + transform() on 'last':

Here, when there are no values for .shift(), we obtain the last entry within the group by using the .groupby() + transform() on 'last' to get the value for .fillna().

 df['CAV_diff'] = df['CAV'] - df.groupby('Client')['CAV'].shift(-5).fillna(df.groupby('Client')['CAV'].transform('last'))

Result:

print(df)

  Client    Month  CAV  CAV_diff
0      A  2021-09   30      25.0
1      A  2021-08   20      10.0
2      A  2021-07   10       0.0
3      A  2021-06    5      -5.0
4      A  2021-05   10       0.0
5      A  2021-04    5      -5.0
6      A  2021-03   10       0.0
7      B  2021-08   50      20.0
8      B  2021-07   10     -20.0
9      B  2021-06   30       0.0

Explanation:

Let's compare the values of shift(-5) with and without the .fillna() part:

df.groupby('Client')['CAV'].shift(-5)

0     5.0
1    10.0
2     NaN
3     NaN
4     NaN
5     NaN
6     NaN
7     NaN
8     NaN
9     NaN
Name: CAV, dtype: float64

Since there are only 7 and 3 rows of entries for Client A and Client B respectively, only the first 2 entries of shift(-5) of Client A have values. There is no value for Client B.

With fillna() with the last entry of the Client group, we get:

 df.groupby('Client')['CAV'].shift(-5).fillna(df.groupby('Client')['CAV'].transform('last'))


0     5.0
1    10.0
2    10.0
3    10.0
4    10.0
5    10.0
6    10.0
7    30.0
8    30.0
9    30.0
Name: CAV, dtype: float64

The NaN entries are fill-ed up with the last value within each group.

user_150706970
Related