After Group By, How express multiple logical condition on a Column' different categorical value-CodePudding

My example df shown below:

dataframe-

     Product    Event    Sum    Count
  0  camera     basket   20     1
  1  camera     listing   0     0
  2  camera     product   0     0
  3  camera     sales    65     2
  4  keyboard   basket    0     0
  5  keyboard   listing   0     0
  6  keyboard   product  20     1
  7  keyboard   sales     0     0
  8  mouse      basket    0     0
  9  mouse      listing   5     1
 10  mouse      product  10     1
 11  mouse      sales     0     0
 12  saat       basket    0     0
 13  saat       listing   0     0
 14  saat       product  10     1
 15  saat       sales    10     1
 16  tv         basket   20     2
 17  tv         listing   0     0
 18  tv         product   0     0
 19  tv         sales    50     2
 20  watch      basket   30     2
 21  watch      listing   0     0
 22  watch      product   0     0
 23  watch      sales     0     0

I want to filter for each product, when (Event=='sales', count<1) and (Event=='basket', count>1) Based on this condition it should return only watch product line:

Output-

20  watch       basket   30     2

I have tried the below code, but it returns nothing.

result=df.groupby('Product').apply(lambda x: x[((x['Count'] < 1) & (x['Event']=='sales')) & ((x['Count'] > 1) & (x['Event']=='basket'))])

CodePudding user response：

Use boolean indexing:

# is there any sale with count>1 in the group?
m1 = df['Count'].where(df['Event'].eq('sales')).lt(1).groupby(df['Product']).transform('any')
# rows that have Event == basket and Count < 1
m2 = df['Event'].eq('basket') & df['Count'].gt(1)

df.loc[m1&m2]

output:

   Product   Event  Sum  Count
20   watch  basket   30      2