Pandas Groupby - Calculating Distance From Relative Point

Lets say I have something that looks like this df = pd.DataFrame({'Event':['A','A','A','A', 'A' ,'B','B','B','B','B'], 'Number':[1,2,3,4,5,6,7,8,9,10],'Ref':[False,False,False,Fa

Solution 1:

In your case

df['new']=(df.set_index('Event').Number-df.query('Ref').set_index('Event').Number).to_numpy()
df
  Event  Number    Refnew0     A       1False-41     A       2False-32     A       3False-23     A       4False-14     A       5True05     B       6False-36     B       7False-27     B       8False-18     B       9True09     B      10False1

Solution 2:

You could do the following:

df["new"] = df.Number - df.Number[df.groupby('Event')['Ref'].transform('idxmax')].reset_index(drop=True)
print(df)

Output

  Event  Number    Refnew0     A       1False-41     A       2False-32     A       3False-23     A       4False-14     A       5True05     B       6False-36     B       7False-27     B       8False-18     B       9True09     B      10False1

This: df.groupby('Event')['Ref'].transform('idxmax') fill find the indices by group where Ref is True. Basically it finds the indices of the max values, so given that True = 1, and False = 0, it find the indices of the True values.

Solution 3:

Try where and grouby transform first

s = df.Number.where(df.Ref).groupby(df.Event).transform('first')
df.Number - s

Out[319]:
0   -4.01   -3.02   -2.03   -1.040.05   -3.06   -2.07   -1.080.091.0Name: Number, dtype: float64