I have a pandas DataFrame like the following:
pd.DataFrame({'ID': {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'E'}, 'Count': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5}, 'Group': {0: 'A', 1: 'A', 2: 'A', 3: 'B', 4: 'B'}})
which looks like this:
Change Count Group
0 A 1 A
1 B 2 A
2 C 3 A
3 D 4 B
4 E 5 B
I would like to group by the Group column, find the sum of all the counts in the Count column for that subset and then create a new column Proportion which is calculated as the values in the Count column for that subset divided by the sum of that subset.
The result should look result in this:
pd.DataFrame({'Change': {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'E'}, 'Count': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5}, 'Group': {0: 'A', 1: 'A', 2: 'A', 3: 'B', 4: 'B'}, 'Proportion': {0: 0.167, 1: 0.333, 2: 0.5, 3: 0.444, 4: 0.555}})
and look like this:
Change Count Group Proportion
0 A 1 A 0.167
1 B 2 A 0.333
2 C 3 A 0.500
3 D 4 B 0.444
4 E 5 B 0.555
>Solution :
Let us try groupby with transform
df['new'] = df['Count'].div(df.groupby('Group')['Count'].transform('sum'))
df
Out[176]:
Change Count Group new
0 A 1 A 0.166667
1 B 2 A 0.333333
2 C 3 A 0.500000
3 D 4 B 0.444444
4 E 5 B 0.555556