Calculating summation over months of pandas dataframe

user_18951121 0
Viewed 77

I have a pandas dataframe given below:

ID       Year       R1  R1_f
KAR1    20201001    1   5
KAR1    20201101    2   6
KAR1    20201201    3   7
KAR1    20210101    4   8
KAR1    20210201    5   9
KAR1    20210301    6   10
KAR1    20210401    7   11
KAR1    20210501    8   12
KAR1    20210601    9   13
KAR1    20210701    10  14
KAR1    20210801    11  15
KAR1    20210901    12  16
KAR2    20201001    4   9
KAR2    20201101    3   8
KAR2    20201201    2   7
KAR2    20210101    1   6
KAR2    20210201    9   5
KAR2    20210301    2   4
KAR2    20210401    6   3
KAR2    20210501    5   2
KAR2    20210601    3   1
KAR2    20210701    30  2
KAR2    20210801    34  3
KAR2    20210901    20  4

I need to transform above dataframe as given below:

ID Year      R1_sum 3m_R1 6m_R1 9m_R1 12m_R1 R1_f 3m_R1_f 6m_R1_f 9m_R1_f 12m_R1_f

KAR1 20210901 12 33 57 72 78 16 45 81 108 126 KAR2 20210901 20 84 98 110 119 4 9 15 30 54

In above output dataframe R1_sum is having value equal to value in year 20210901 for both Id's.

3m_R1 is the summation of values of 3 months 20210901 to 20210701 for column R1

6m_R1 is the summation of values of 6 months from 20210901 to 20210401 for column R1

9m_R1 is the summation of values of 9 months from 20210901 to 20210101 for column R1

12m_R1 is the summation of values of 12 months from 20210901 to 20201001 for column R1

R1_f is having value equal to value in year 20210901 for both Id's.

3m_R1_f is the summation of values of 3 months 20210901 to 20210701 for column R1_f

6m_R1_f is the summation of values of 6 months from 20210901 to 20210401 for column R1_f

9m_R1_f is the summation of values of 9 months from 20210901 to 20210101 for column R1_f

12m_R1_f is the summation of values of 12 months from 20210901 to 20201001 for column R1_f

Please help

data-science pandas time-series python
1 Answers

For count from last months to first months per groups ID first crate helper groups by GroupBy.cumcount and aggregate sum, then aggregate sum with GroupBy.cumsum, reshape by DataFrame.unstack, flatten MultiIndex in columns and add to DataFrame created by GroupBy.last:

Data:

df = pd.DataFrame({'ID': ['KAR1', 'KAR1', 'KAR1', 'KAR1', 'KAR1', 'KAR1', 'KAR1', 
                          'KAR1', 'KAR1', 'KAR1', 'KAR1', 'KAR1', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2', 'KAR2'], 
                   'Year': [20201001, 20201101, 20201201, 20210101, 20210201, 20210301,
                            20210401, 20210501, 20210601, 20210701, 20210801, 20210901, 
                            20201001, 20201101, 20201201, 20210101, 20210201, 20210301,
                            20210401, 20210501, 20210601, 20210701, 20210801, 20210901], 
                   'R1': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 4, 3, 2, 1, 9, 2, 6, 5, 3, 30, 34, 20], 
                   'R1_f': [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 9, 8, 7, 6, 5, 4, 3, 2, 1, 2, 3, 4]})
    
print (df)

cols = ['R1','R1_f']
g = df.groupby('ID').cumcount(ascending=False) // 3
df1 = df.groupby(['ID',g])[cols].sum().groupby(level=0).cumsum().unstack()
print (df1)
      R1               R1_f              
       0   1    2    3    0   1    2    3
ID                                       
KAR1  33  57   72   78   45  81  108  126
KAR2  84  98  110  119    9  15   30   54

df2 = df.groupby('ID')[['Year'] + cols].last()
df2.columns = pd.MultiIndex.from_product([df2.columns, [-1]])
print (df2)
            -1  -1   -1
ID                     
KAR1  20210901  12   16
KAR2  20210901  20    4

df = df2.join(df1).sort_index(axis=1)
df.columns = [f'{(b + 1) * 3}m_{a}' if b!=-1 else f'{a}_sum' for a, b in df.columns]
df = df.reset_index()
df.insert(1, 'Year', df.pop('Year_sum'))
print (df)
     ID      Year  R1_sum  3m_R1  6m_R1  9m_R1  12m_R1  R1_f_sum  3m_R1_f  \
0  KAR1  20210901      12     33     57     72      78        16       45   
1  KAR2  20210901      20     84     98    110     119         4        9   

   6m_R1_f  9m_R1_f  12m_R1_f  
0       81      108       126  
1       15       30        54
user_29010020
Related