Slicing Pandas Dataframe with a sort in the condition

Question

this is a bit tricky to me. Let's say I have this dataframe:

Group    Mag    Morph
  1     -21.1     S
  1     -20.3     E
  1     -22.0     E
  2     -18.1     E
  2     -23.6     S
  2     -21.9     S
  3     -19.4     S
  3     -19.1     E

I would like to select only the groups (i.e. same 'Group') for which the lowest 'Mag' corresponds to a 'S' for 'Morph'.

For example, here:

• in group 1, the lowest is -22.0 corresponding to an 'E' Morph (so group not taken),

• for group 2 it's -23.6, so 'S' (group taken),

• and for group 3, -19.4, so 'S', so group taken.

Hence it would select

Group    Mag    Morph
  2     -18.1     E
  2     -23.6     S
  2     -21.9     S
  3     -19.4     S
  3     -19.1     E

Could someone help me?

Answer 1

You can use filter with idxmin for check value of column Morph per group:

print (df.groupby('Group').filter(lambda x: x.loc[x.Mag.idxmin(), 'Morph'] == 'S'))
   Group   Mag Morph
3      2 -18.1     E
4      2 -23.6     S
5      2 -21.9     S
6      3 -19.4     S
7      3 -19.1     E