Groupby and Count the % of occurrence

Question

the following is my data, I want to count % occurrence of Flag=1 and Value.

df=pd.DataFrame({'ID':['A','B','C','D'],
                 'Group':['group1','group1','group2','group2'],
                 'Flag_1':[1,0,0,1],
                 'Flag_2':[1,1,0,1],
                 'Value':[30,40,60,70]
                })

These four are the outputs I expect:

df_count: The first one just counts the % of total occurrence. df_value: The second one counts the % of value when Flag=1. df_count_group and df_value_group are repeated operations but groupby Group.

df_count=pd.DataFrame({
                 'Flag_1':[2/4],
                 'Flag_2':[3/4],
                })

df_value=pd.DataFrame({
                 'Flag_1':[(30+70)/(30+40+60+70)],
                 'Flag_2':[(30+40+70)/(30+40+60+70)],
                })

df_count_group=pd.DataFrame({
                 'Flag_1':[0.5,0.5],
                 'Flag_2':[1,0.5]},index=['group1','group2'])

df_value_group=pd.DataFrame({
                 'Flag_1':[30/(30+40),70/(60+70)],
                 'Flag_2':[(30+40)/(30+40),70/(60+70)]},index=['group1','group2'])

I have tried the following function:

def func(df,group_bool):
    
    value_sum = df['Value'].sum()
    
    if group_bool==False:
        return (df[['Flag_1', 'Flag_2']]
            .agg([np.mean,lambda col: df.loc[col.eq(1), 'Value'].sum() / value_sum]))
    else:
        return (df.groupby('Group')[['Flag_1', 'Flag_2']]
            .agg([np.mean,lambda col: df.loc[col.eq(1), 'Value'].sum() / value_sum]))

It works when I call func(df,False) but when I call func(df,True),it throws an error.

user_14627505 · Answer

Firsly, define 2 functions:

def get_flag_percentages(df):
    return df[['Flag_1', 'Flag_2']].agg(np.mean).to_frame().T

def get_val_percentages(df):
    value_sum = df['Value'].sum()
    return (df[['Flag_1', 'Flag_2']]
            .agg(lambda col: df.loc[col.eq(1), 'Value'].sum() / value_sum)
            .to_frame().T)

Now use them:

df_count = get_flag_percentages(df)
df_value = get_val_percentages(df)
df_count_group = df.groupby('Group').apply(get_flag_percentages).droplevel(1)
df_value_group = df.groupby('Group').apply(get_val_percentages).droplevel(1)

If you want to get rid of the index name which is created by default ('Group'):

df_count_group.index.name = None
df_value_group.index.name = None

Results

df_count
   Flag_1  Flag_2
0     0.5     0.75


df_value
   Flag_1  Flag_2
0     0.5     0.7


df_count_group
        Flag_1  Flag_2
group1     0.5     1.0
group2     0.5     0.5


df_value_group
          Flag_1    Flag_2
group1  0.428571  1.000000
group2  0.538462  0.538462

Groupby and Count the % of occurrence

1 Answers