Groupby category then do divisions for new year over old year

user_8635767 0
Viewed 91

Sample dataset:

import pandas as pd
  
df = pd.DataFrame({'Year':[1995, 1996, 1997, 1998, 1999, 2000,1995, 1996, 1997, 1998, 1999, 2000],
                   'Group':['A', 'A', 'A','A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B'],
                         'Col_1':[100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 1100, 1200],
                         'Col_2':[11, 22, 33, 44, 55, 66, 77, 88, 99, 199, 299, 399]})

I'd like to create a new column by dividing one year by its previous year in Col_1 and Col_2 respectively for each group. Then, divide the two divisions. See example below:

Year Group Col_1 Col_2 New Column
1995 A 100 11 NA
1996 A 200 22 (200/100)/(22/11)
1997 A 300 33 (300/200)/(33/22)
1998 A 400 44 .............
1999 A 500 55 .............
2000 A 600 66 .............
1995 B 700 77 NA
1996 B 800 88 (800/700)/(88/77)
1997 B 900 99 (900/800)/(99/88)
1998 B 1000 199 .............
1999 B 1100 299 .............
2000 B 1200 399 .............
rolling-computation pandas division group-by python
1 Answers

The pre-requisite is that Year is sorted and that no years are missing. You can groupby "Group" compute the diff and divide the resulting columns using eval:

division of division

df['New Column'] = (df.groupby('Group')[['Col_1','Col_2']]
                      .pct_change().add(1)
                      .eval('Col_1/Col_2')
                   )

output:

    Year Group  Col_1  Col_2  New Column
0   1995     A    100     11         NaN
1   1996     A    200     22    1.000000
2   1997     A    300     33    1.000000
3   1998     A    400     44    1.000000
4   1999     A    500     55    1.000000
5   2000     A    600     66    1.000000
6   1995     B    700     77         NaN
7   1996     B    800     88    1.000000
8   1997     B    900     99    1.000000
9   1998     B   1000    199    0.552764
10  1999     B   1100    299    0.732107
11  2000     B   1200    399    0.817498

division of difference (initial question)

df['New Column'] = (df.groupby('Group')[['Col_1','Col_2']]
                      .diff()
                      .eval('Col_1/Col_2')
                   )

output:

    Year Group  Col_1  Col_2  New Column
0   1995     A    100     11         NaN
1   1996     A    200     22    9.090909
2   1997     A    300     33    9.090909
3   1998     A    400     44    9.090909
4   1999     A    500     55    9.090909
5   2000     A    600     66    9.090909
6   1995     B    700     77         NaN
7   1996     B    800     88    9.090909
8   1997     B    900     99    9.090909
9   1998     B   1000    199    1.000000
10  1999     B   1100    299    1.000000
11  2000     B   1200    399    1.000000
user_163434640
Related