CODEWITHSUNDEEP
pandas
panda
panda

Reputation: 625

How to filter the dataframe on multiple condition?

I have the following dataframe.

 import pandas as pd
    import numpy as np
    d ={
        'ID1':['abc1','abc2','abc3','abc4','abc5','abc1','abc1','abc1','abc1','abc1','abc2','abc2','abc2','abc3'],
        'Item':['orange','mango','jack','cucumber','banana','pineapple','sapota','grapes','papaya','watermelon','guava','pomogranate','mosambi','apple'],
        'Type':['A','B','A','B','A','B','A','B','A','B','A','B','A','B'],
        'Price':[25,30,15,20,25,30,15,20,25,30,15,20,25,30]
    }
    df = pd.DataFrame(data = d)
    df

For groupby condition following is the code:

df.groupby('ID1').filter(lambda s: s.Price.sum()>=80).sort_values(by='ID1',ascending = True)

How to filter ID's on the following multiple condition:

  1. Sum of Price> 90 and
  2. It should have 3 times A and 3 times B as Type and
  3. 2 id can have price between 15, 20 and
  4. 4 ids can have price greater or equal to 25

Expected Output:

    ID1     Item            Type    Price
0   abc1    orange              A   25
5   abc1    pineapple           B   30
6   abc1    sapota              A   15
7   abc1    grapes              B   20
8   abc1    papaya              A   25
9   abc1    watermelon          B   30

Upvotes: 1

Views: 89

Answers (1)

jezrael
jezrael

Reputation: 863751

You can use GroupBy.transform with sum - from second condition for count of True values per conditions by Series.eq,Series.ge and Series.between and last chain conditions by & for bitwise AND and filter by boolean indexing:

m1 = df.groupby('ID1')['Price'].transform('sum') > 90
m2 = df['Type'].eq('A').groupby(df['ID1']).transform('sum') == 3
m3 = df['Type'].eq('B').groupby(df['ID1']).transform('sum') == 3
m4 = df['Price'].between(15, 20).groupby(df['ID1']).transform('sum') == 2
m5 = df['Price'].ge(25).groupby(df['ID1']).transform('sum') == 4

Or:

m1 = df.groupby('ID1')['Price'].transform('sum').gt(90)
m2 = df['Type'].eq('A').groupby(df['ID1']).transform('sum').eq(3)
m3 = df['Type'].eq('B').groupby(df['ID1']).transform('sum').eq(3)
m4 = df['Price'].between(15, 20).groupby(df['ID1']).transform('sum').eq(2)
m5 = df['Price'].ge(25).groupby(df['ID1']).transform('sum').eq(4)

df = df[m1 & m2 & m3 & m4 & m5]
print (df)
    ID1        Item Type  Price
0  abc1      orange    A     25
5  abc1   pineapple    B     30
6  abc1      sapota    A     15
7  abc1      grapes    B     20
8  abc1      papaya    A     25
9  abc1  watermelon    B     30

Upvotes: 2

Related Questions