Pandas/python: Filter By Condition Within Same Column Grouping

i have a df that has multiple pairs of related items; example: fxr_dl2_rank.r1 and fxr_dl2_rank.r1_wp. Is it possible to fliter all the related pairs with both positive results? da

Solution 1:

First rework the 'item' to get the common part, use it to group the rows, check whether all elements are positive and use the output for slicing:

group = df['item'].str.replace('_wp$', '', regex=True)
df[df.groupby(group)['result'].transform(lambda s: all(s.ge(0)))]

output:

                 item  result
1     fxr_dl2_rank.r2    0.13
3     fxr_dl2_rank.r4    0.18
6  fxr_dl2_rank.r2_wp    0.16
8  fxr_dl2_rank.r4_wp    0.17

Solution 2:

You can use the following:

#create helper column
df["helper"] = df["item"].str[:15]
#filteroutall negative valuesinresult
df = df[df["result"] >=0]

#keep only duplicated rowsin helper column
df[df.duplicated(subset="helper", keep=False)]