Understanding Group By's behavior with missing values and figuring out a discrepancy between .mean() and manually solving for the mean

Viewed 32

I have a multi-index df in the following form:

                      pid    time    delta_t
sess_id     vis_id      

id1         vis_id1    id1    t_0      1
            vis_id1    id2    t_1      5
            vis_id1    id1    t_2      NA 

id2         vis_id2    id3    t_3      6
            vis_id2    id2    t_4      7
            vis_id2    id1    t_5      NA   

Context: Delta_t is the time spent on a specific page, pid--instances where the times are NA is because there is no recorded time for that page because they had left the website and are no longer tracked from page to page

I want to find the average length of time (avg delta_t) for each specific page, so I do a group by and create another df to append to already existing df containing info about my page id pid:

df2 = df1.reset_index().groupby('pid')['delta_t'].mean().reset_index().set_index('pid')

For some odd reason, using the .mean() function after a group by yields a different average than manually adding up the averages and dividing it by the instances of pid

I think it has to do that group_by.mean() ignores NAs and a manual division includes them. Or vice versa. Is there anyway I can do group_by.mean() on my data frame such that if the value is NA it ignores it but still divides by total observations?

1 Answers

Fill NAs with 0, then it will be included in the mean, contributing to the denominator but not the numerator:

df1.fillna(0).reset_index().groupby('pid')['delta_t'].mean().reset_index().set_index('pid')
Related