How To Group And Apply Custom Aggregation Function To Get Mode Values Of A Column In Pandas?

I need to get the mode value of X variable, and maximum value of Y variable, grouped by (ID, Date), with two conditions: (1) If there are zeros and other values in X for same (ID,

Solution 1:

You should first sort the values according to time.

df = df.sort_values(['ID','Date', 'Time'],ascending=True)

Then, you can apply a custom aggregation function with your defined 2 conditions on X column.

defcustom_agg_func(x):
    x = x[x > 0] #Filter out all zero values from X#If there are no values then return 0 as mode else first value from sorted listreturn0if x.size < 1else x.mode().iloc[0]


result_df = (df.groupby(['ID','Date'], as_index=False)
        .agg({'Y':'max', 'X': custom_agg_func}))

print(result_df)

Outputs:

IDDate  Y    X
0108/27/201911231208/26/201914562308/27/201911233308/28/2019004408/28/20191789