Pandas - show percentage of values in one column, grouped by other column

user_9490787 0
Viewed 1930

So I have a Pandas DataFrame with two columns: first is Grade with values 0 to 9 second is Criteria, with values 0 or 1.

Grade (0-9/ Criteria(0/1)

   Grade  Criteria
0      0         1
1      1         0
2      2         1
3      2         0
4      5         1
5      2         1

etc

I need to count "Criteria rate", which is actually sum of "1"s in Criteria column, divided by appropriate number of rows in Criteria column, but grouped by Grade column values. For example, for Grade = 2 we count sum of 1 in Criteria column and divide it by number of rows with Grade 2: 2/3, so for Grade 2 we get 0.66 approx. In my example, the answer should look like:

Grade / Criteria rate

   Grade  Criteria
0      0  1.000000
1      1  0.000000
2      2  0.666667
3      5  1.000000

Any ideas, how to do this? Also the add. question - how to do this, if we have "yes/no" text values in Criteria column? I've searched here, but found only solutions to groupby's, divided by total rows count etc.

Thank you!

pandas percentage python
2 Answers

You can aggregate sum with size and then divide columns:

df = df.groupby('Grade')['Criteria'].agg(['sum','size'])
df['new'] = df['sum'] / df['size']
print (df)
       sum  size       new
Grade                     
0        1     1  1.000000
1        0     1  0.000000
2        2     3  0.666667
5        1     1  1.000000

Or use custom function:

#not exclude NaNs 
df = df.groupby('Grade')['Criteria'].agg(lambda x: x.sum() / len(x)).reset_index(name='new')

#exclude possible NaNs
df = df.groupby('Grade')['Criteria'].agg(lambda x: x.sum() / x.count()).reset_index(name='new')

For yes/no values working with boolean mask - Trues are processes like 1s:

print (df)
   Grade Criteria
0      0      yes
1      1       no
2      2      yes
3      2       no
4      5      yes
5      2      yes

df = (df['Criteria'] == 'yes').groupby(df['Grade']).agg(lambda x: x.sum() / len(x)).reset_index(name='new')
print (df)
   Grade       new
0      0  1.000000
1      1  0.000000
2      2  0.666667
3      5  1.000000
user_29010020

If criteria is 1 or 0, or even True or False

You can use mean

groupby

df.groupby('Grade').mean()

       Criteria
Grade          
0      1.000000
1      0.000000
2      0.666667
5      1.000000

set_index and mean

df.set_index('Grade').mean(level=0)

       Criteria
Grade          
0      1.000000
1      0.000000
2      0.666667
5      1.000000

In the case that 'Criteria' are 'yes' and 'no' strings

df

   Grade Criteria
0      0      yes
1      1       no
2      2      yes
3      2       no
4      5      yes
5      2      yes

You can group the boolean evaluation

df.Criteria.eq('yes').groupby(df.Grade).mean()

Grade
0    1.000000
1    0.000000
2    0.666667
5    1.000000
Name: Criteria, dtype: float64

Use reset_index on any of these answers to get the desired dataframe

user_23366540
Related