I have the following table in pandas:
Index | Ingredient | Dish |
---|---|---|
1 | Potato | Pie |
2 | Potato | Juice |
3 | Potato | Fries |
4 | Potato | Pure |
5 | Apple | Pie |
6 | Apple | Juice |
7 | Apple | Fries |
8 | Apple | Pure |
and want to apply the following filter:
If Ingredient==’Apple’ then only keep Dish=[‘Pie’,’Juice’]
The result should look like this:
Index | Ingredient | Dish |
---|---|---|
1 | Potato | Pie |
2 | Potato | Juice |
3 | Potato | Fries |
4 | Potato | Pure |
5 | Apple | Pie |
6 | Apple | Juice |
How can I do that?
2
You want:
- to keep any row that does not equal ‘Apple’ (‘Ingredient’), via
Series.ne
. - or (
|
) keep any row that is in['Pie', 'Juice']
(‘Dish’), viaSeries.isin
.
<code>m = df['Ingredient'].ne('Apple')
n = df['Dish'].isin(['Pie', 'Juice'])
df[m | n]
</code>
<code>m = df['Ingredient'].ne('Apple')
n = df['Dish'].isin(['Pie', 'Juice'])
df[m | n]
</code>
m = df['Ingredient'].ne('Apple')
n = df['Dish'].isin(['Pie', 'Juice'])
df[m | n]
Output:
<code> Index Ingredient Dish
0 1 Potato Pie
1 2 Potato Juice
2 3 Potato Fries
3 4 Potato Pure
4 5 Apple Pie
5 6 Apple Juice
</code>
<code> Index Ingredient Dish
0 1 Potato Pie
1 2 Potato Juice
2 3 Potato Fries
3 4 Potato Pure
4 5 Apple Pie
5 6 Apple Juice
</code>
Index Ingredient Dish
0 1 Potato Pie
1 2 Potato Juice
2 3 Potato Fries
3 4 Potato Pure
4 5 Apple Pie
5 6 Apple Juice
Logic:
<code>df.assign(**{'m': m,
'n': n,
'm | n': m | n
}
)
Index Ingredient Dish m n m | n
0 1 Potato Pie True True True
1 2 Potato Juice True True True
2 3 Potato Fries True False True
3 4 Potato Pure True False True
4 5 Apple Pie False True True
5 6 Apple Juice False True True
6 7 Apple Fries False False False
7 8 Apple Pure False False False
# also possible: 'any': lambda x: x[['m', 'n']].any(axis=1)
</code>
<code>df.assign(**{'m': m,
'n': n,
'm | n': m | n
}
)
Index Ingredient Dish m n m | n
0 1 Potato Pie True True True
1 2 Potato Juice True True True
2 3 Potato Fries True False True
3 4 Potato Pure True False True
4 5 Apple Pie False True True
5 6 Apple Juice False True True
6 7 Apple Fries False False False
7 8 Apple Pure False False False
# also possible: 'any': lambda x: x[['m', 'n']].any(axis=1)
</code>
df.assign(**{'m': m,
'n': n,
'm | n': m | n
}
)
Index Ingredient Dish m n m | n
0 1 Potato Pie True True True
1 2 Potato Juice True True True
2 3 Potato Fries True False True
3 4 Potato Pure True False True
4 5 Apple Pie False True True
5 6 Apple Juice False True True
6 7 Apple Fries False False False
7 8 Apple Pure False False False
# also possible: 'any': lambda x: x[['m', 'n']].any(axis=1)
1
You can select values with Apple
if not match list
and then invert mask by ~
for expected ouput:
<code>out = df[ ~(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice']))]
print (out)
Index Ingredient Dish
0 1 Potato Pie
1 2 Potato Juice
2 3 Potato Fries
3 4 Potato Pure
4 5 Apple Pie
5 6 Apple Juice
</code>
<code>out = df[ ~(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice']))]
print (out)
Index Ingredient Dish
0 1 Potato Pie
1 2 Potato Juice
2 3 Potato Fries
3 4 Potato Pure
4 5 Apple Pie
5 6 Apple Juice
</code>
out = df[ ~(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice']))]
print (out)
Index Ingredient Dish
0 1 Potato Pie
1 2 Potato Juice
2 3 Potato Fries
3 4 Potato Pure
4 5 Apple Pie
5 6 Apple Juice
How it working:
<code>print (df.assign(m1=df['Ingredient'].eq('Apple'),
m2 = ~df['Dish'].isin(['Pie','Juice']),
both =(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice'])),
invert= ~(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice']))))
Index Ingredient Dish m1 m2 both invert
0 1 Potato Pie False False False True
1 2 Potato Juice False False False True
2 3 Potato Fries False True False True
3 4 Potato Pure False True False True
4 5 Apple Pie True False False True
5 6 Apple Juice True False False True
6 7 Apple Fries True True True False
7 8 Apple Pure True True True False
</code>
<code>print (df.assign(m1=df['Ingredient'].eq('Apple'),
m2 = ~df['Dish'].isin(['Pie','Juice']),
both =(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice'])),
invert= ~(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice']))))
Index Ingredient Dish m1 m2 both invert
0 1 Potato Pie False False False True
1 2 Potato Juice False False False True
2 3 Potato Fries False True False True
3 4 Potato Pure False True False True
4 5 Apple Pie True False False True
5 6 Apple Juice True False False True
6 7 Apple Fries True True True False
7 8 Apple Pure True True True False
</code>
print (df.assign(m1=df['Ingredient'].eq('Apple'),
m2 = ~df['Dish'].isin(['Pie','Juice']),
both =(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice'])),
invert= ~(df['Ingredient'].eq('Apple') & ~df['Dish'].isin(['Pie','Juice']))))
Index Ingredient Dish m1 m2 both invert
0 1 Potato Pie False False False True
1 2 Potato Juice False False False True
2 3 Potato Fries False True False True
3 4 Potato Pure False True False True
4 5 Apple Pie True False False True
5 6 Apple Juice True False False True
6 7 Apple Fries True True True False
7 8 Apple Pure True True True False
1