Group by, count and calculate proportions in pandas?

Question

I have a dataframe as follows:

d = {
  'id': [1, 2, 3, 4, 5], 
  'is_overdue': [True, False, True, True, False],
  'org': ['A81001', 'A81002', 'A81001', 'A81002', 'A81003']
}
df = pd.DataFrame(data=d)

Now I want to work for each organization, what percentage of rows are overdue and what percentage is not.

I know how to group by organization and expired status:

df.groupby(['org', 'is_overdue']).agg('count')

But how do I get a share in the organization? I want to get something like this:

org      is_overdue   not_overdue   proportion_overdue
A81001   2            0             100
A81002   1            1             50
A81003   0            1             0

Answer 1

You can use DataFrame.apply.

The first group of organizations and count the number of overdue / non-past due. Then calculate the percentage.

df_overdue = df.groupby(['org']).apply(lambda dft: pd.Series({'is_overdue': dft.is_overdue.sum(), 'not_overdue': (~dft.is_overdue).sum()}))
df_overdue['proportion_overdue'] = df_overdue['is_overdue'] / (df_overdue['not_overdue'] + df_overdue['is_overdue'])

print(df_overdue)

exits

        is_overdue  not_overdue  proportion_overdue
org                                                
A81001           2            0                 1.0
A81002           1            1                 0.5
A81003           0            1                 0.0

Answer 2

You can use pd.crosstabto create a frequency table, i.e. count the quantity is_overduefor each org.

import pandas as pd
d = {
  'id': [1, 2, 3, 4, 5], 
  'is_overdue': [True, False, True, True, False],
  'org': ['A81001', 'A81002', 'A81001', 'A81002', 'A81003']
}
df = pd.DataFrame(data=d)

result = pd.crosstab(index=df['org'], columns=df['is_overdue'], margins=True)
result = result.rename(columns={True:'is_overdue', False:'not overdue'})
result['proportion'] = result['is_overdue']/result['All']*100
print(result)

gives

is_overdue  not overdue  is_overdue  All  proportion
org                                                 
A81001                0           2    2       100.0
A81002                1           1    2        50.0
A81003                1           0    1         0.0
All                   2           3    5        60.0

Answer 3

, , :

df.is_overdue = df.is_overdue.map({True: 1, False: 0})
df.groupby(['org'])['is_overdue'].agg({'total_count':'count', 'is_overdue':   'sum'}).reset_index()

Now you can just easily calculate not_overdue and measure_overdue.