Home > Back-end >  Finding minimum value with groupby() by comparing two columns
Finding minimum value with groupby() by comparing two columns

Time:10-23

I am trying to find a minimum number using groupby() by comparing two columns (Each containing a time duration)

Sample data:

Ambulance_ID    Centroid_ID  Hospital_ID   Regular_Ambu_TT     MSU_TT
37                 1               6         1,871884861      0,459994444
39                 2               13        1,599971112      0,372125
6                  3               6         1,307165278      0,080163889
42                 4               12        1,411554445      0,285008333
37                 5               14        1,968138334      0,424172222

Sample code: (It works for 1 column only)

Test_TT = pd.DataFrame()
Test_TT = df_A2C_TT_temp.loc[df_A2C_TT_temp.groupby('Centroid_ID').Regular_Ambu_TT.idxmin()]

I want to group my data by groupby('Centroid_ID') by comparing Regular_Ambu_TT and MSU_TT to get a minimum value.

CodePudding user response:

Here is one approach

  1. Get the min per column
  2. Get a min per row to get the final minimum .
df_mins = pd.DataFrame()
df_mins[['Centroid_ID', 'min_Regular_Ambu_TT']] = df_A2C_TT_temp.groupby('Centroid_ID')['Regular_Ambu_TT'].min().reset_index()
df_mins[['Centroid_ID', 'min_MSU_TT']] = df_A2C_TT_temp.groupby('Centroid_ID')['MSU_TT'].min().reset_index()
df_mins['min_per_group'] = df_mins[['min_Regular_Ambu_TT','min_MSU_TT']].min(axis=1)
df_mins
  • Related