Python group by one column and calculate percentage of another column

user_4246716 0
Viewed 1302

I have a dataframe as follows:

    Frame   SizeOfExposure
0    1         5
1    1         5
2    2         7
3    3         2
4    3         8

I am trying to calculate the percentage of SizeOfExposure for each row group by Frame number.

I tried the following:

df.groupby("Frame")["SizeOfExposure"].sum()

This prints the sum of SizeOfExposure for each frame. However, when I tried

df['SizeOfExpPerc']=df["SizeOfExposure"]/df.groupby("Frame")["SizeOfExposure"].sum()

I am getting 'Nan' filled for most rows. How do I calculate? The desired output is

    Frame   SizeOfExposure  SizeOfExpPerc
0    1         5                  0.5
1    1         5                  0.5
2    2         7                  1.0
3    3         2                  0.2
4    3         8                  0.8
pandas group-by python
2 Answers

Use GroupBy.transform for return Series with same size like original DataFrame filled by aggregated values, so possible division:

df['SizeOfExpPerc']=df["SizeOfExposure"]/df.groupby("Frame")["SizeOfExposure"].transform('sum')
print (df)
   Frame  SizeOfExposure  SizeOfExpPerc
0      1               5            0.5
1      1               5            0.5
2      2               7            1.0
3      3               2            0.2
4      3               8            0.8

Detail:

print (df.groupby("Frame")["SizeOfExposure"].transform('sum'))
0    10
1    10
2     7
3    10
4    10
Name: SizeOfExposure, dtype: int64
user_29010020

The method by @jezrael is probably more efficient, but here is an alternative solution using groupby and apply.

>>> df.assign(SizeOfExpPerc=df.groupby('Frame')['SizeOfExposure'].apply(
        lambda group: group / group.sum()))
   Frame  SizeOfExposure  SizeOfExpPerc
0      1               5            0.5
1      1               5            0.5
2      2               7            1.0
3      3               2            0.2
4      3               8            0.8
user_24118020
Related