How to count NA's in aggregate function of a group by using pandas?-CodePudding

I have a dataframe like as shown below

df = pd.DataFrame(
    {'stud_id' : [101, 101, 101, 101, 
                  101, 101, 101, 101],
     'sub_code' : ['CSE01', 'CSE01', 'CSE01', 
                   'CSE01', 'CSE02', 'CSE02',
                   'CSE02', 'CSE02'],
     'ques_date' : ['13/11/2020', '10/1/2018','11/11/2017', '27/03/2016', 
                '13/05/2010',  '10/11/2008','11/1/2007', '27/02/2006'],
     'resp_date' : [np.nan, '11/1/2018','14/11/2017', '29/03/2016', 
                np.nan, np.nan,np.nan,'28/02/2006'],
     'marks' : [77, 86, 55, 90, 
                65, 90, 80, 67]}
)
df['ques_date'] = pd.to_datetime(df['ques_date'])
df['resp_date'] = pd.to_datetime(df['resp_date'])
df['date_diff'] = (df['resp_date'] - df['ques_date']).dt.days

I would like to do the below

a) For every stud_id and sub_code combination, get the avg date_diff.

b) For every stud_id and sub_code combination, get the avg number of NA. NAs indicate the lack of response. for ex: stud_id = 101 AND sub_code = CSE01 combination has `1 NA out of 4 records, resulting in 1/4 = 0.25.

I tried the below but not sure how to get the avg NA in aggregate function

df.groupby(['stud_id','sub_code']).agg(stud_total_records = ('stud_id','count'),
                                            avg_resp_time = ('date_diff','mean'),
                                            lack_resp_pct = (df.groupby(['stud_id','sub_code'])['resp_date'].isna().sum()).reset_index(name='NA_cnt')['NA_cnt']/stud_total_records)

I expect my output to be like as shown below

CodePudding user response：

Update

Use lazy groups:

grp = df.groupby(['stud_id', 'sub_code'])

out = grp.agg(stud_total_records = ('stud_id', 'count'),
              avg_resp_time = ('date_diff', 'mean'),
              lack_resp_pct = ('date_diff', lambda x: sum(x.isna()) / df['date_diff'].count())) \
         .reset_index()
print(out)

# Output
   stud_id sub_code  stud_total_records  avg_resp_time  lack_resp_pct
0      101    CSE01                   4           12.0           0.25
1      101    CSE02                   4            1.0           0.75

Old answer Try:

out = df.groupby(['stud_id','sub_code']).agg(stud_total_records = ('stud_id', 'count'),
                                            avg_resp_time = ('date_diff', 'mean'))

out['lack_resp_pct'] = df[df['date_diff'].isna()].value_counts(['stud_id', 'sub_code'], normalize=True)

Output:

>>> out.reset_index()
   stud_id sub_code  stud_total_records  avg_resp_time  lack_resp_pct
0      101    CSE01                   4           12.0           0.25
1      101    CSE02                   4            1.0           0.75