Filtering all rows in dataframe where at least one row meets a condition

user_4841936 0
Viewed 210

I have a dataframe that has a list of customers and the instances of products they have bought. I am trying to get a new dataframe that excludes all customers that have purchased a particular product at least once. For example:

d = {'Customer': ['Cust 1', 'Cust 1', 'Cust 2', 'Cust 1', 'Cust 2', 'Cust 2', 'Cust 3', 'Cust 3'], 
     'Product': [1, 1, 2, 1, 1, 2, 2, 1], 
     'PO': ['P1', 'P2', 'P3', 'P4', 'P5', 'P6', 'P7', 'P8']}

df = pd.DataFrame(data=d)
df

Output

| |Customer |Product   |PO        |
| |:--------|:---------|:---------|
|0| Cust 1  |  1       |  P1      |
|1| Cust 1  |  1       |  P2      |
|2| Cust 2  |  2       |  P3      |
|3| Cust 1  |  1       |  P4      |
|4| Cust 2  |  1       |  P5      |
|5| Cust 2  |  2       |  P6      |
|6| Cust 3  |  2       |  P7      |
|7| Cust 3  |  1       |  P8      |

I want to be able to filter out any customer that has purchased Product 2 at any stage, regardless of what else they have purchased, eg:

| |Customer |Product   |PO        |
| |:--------|:---------|:---------|
|0| Cust 1  |  1       |  P1      |
|1| Cust 1  |  1       |  P2      |
|2| Cust 1  |  1       |  P4      |

Is there a way to do this? Any help will be appreciated greatly!

pandas python
4 Answers

Use pandas.DataFrame.groupby.filter:

new_df = df.groupby("Customer").filter(lambda x: 2 not in set(x["Product"]))
print(new_df)

Output:

  Customer  Product  PO
0   Cust 1        1  P1
1   Cust 1        1  P2
3   Cust 1        1  P4
user_70937410 
k = df[df['PO']=='P2']['Customer']
df[df['Customer'].isin(k)]

desired result

    Customer    Product PO
0   Cust 1          1   P1
1   Cust 1          1   P2
3   Cust 1          1   P4
user_9926000 
df[~df.Customer.isin(df[df.Product == 2]['Customer'])]
user_107882430

You can do it a couple of ways:

  1. Using groupby with filter and all:

df.groupby('Customer').filter(lambda x: (x['Product'] != 2).all())

  1. Using groupby with transform and all with boolean indexing:

df[df.groupby('Customer')['Product'].transform(lambda x: (x != 2).all())]

Output:

  Customer  Product  PO
0   Cust 1        1  P1
1   Cust 1        1  P2
3   Cust 1        1  P4
user_63615310
Related