Pandas find indices where rows in a groupby exceed x-CodePudding

I have a data frame X

IDs Distance Threshold
 1     5          4
 3     6          9
 1     4          4
 3     10         9

Where I have IDs and each ID has a threshold. Now I want to find the row indices where the distance is larger than the threshold

idx=X.groupby(['IDs'])['Distance']>X['Threshold']

does not work.

idx=X.groupby(['IDs'])['Distance'].transform(???)>X['Threshold']

I thought that would work with a groupby and transform. However, the transform needs a function and I have not found out a function returning just the value. Can someone help me, I guess it should be easier than I think.

Thanks

CodePudding user response：

IIUC, are you looking for:

df.where(df['Distance'] > df['Threshold']).dropna()

output:

   IDs  Distance  Threshold
0  1.0       5.0        4.0
3  3.0      10.0        9.0

CodePudding user response：

Here are two approaches:

You can use a lambda function to check when the distance is larger then the threshold:

X['larger'] = X.apply(lambda x: x[1]>x[2],axis=1)
You can use np.where() to do the exact same thing.

X['larger'] = np.where(X['Distance']>X['Threshold'],True,False)

After using one of the above you can get the indices using .index():

indices = X['larger'].index[X['larger']].values