Question :
Most operations in pandas can be accomplished with operator chaining (groupby, aggregate, apply, etc), but the only way I’ve found to filter rows is via normal bracket indexing
df_filtered = df[df['column'] == value]
This is unappealing as it requires I assign df to a variable before being able to filter on its values. Is there something more like the following?
df_filtered = df.mask(lambda x: x['column'] == value)
Answer #1:
I’m not entirely sure what you want, and your last line of code does not help either, but anyway:
“Chained” filtering is done by “chaining” the criteria in the boolean index.
In [96]: df
Out[96]:
A B C D
a 1 4 9 1
b 4 5 0 2
c 5 5 1 0
d 1 3 9 6
In [99]: df[(df.A == 1) & (df.D == 6)]
Out[99]:
A B C D
d 1 3 9 6
If you want to chain methods, you can add your own mask method and use that one.
In [90]: def mask(df, key, value):
....: return df[df[key] == value]
....:
In [92]: pandas.DataFrame.mask = mask
In [93]: df = pandas.DataFrame(np.random.randint(0, 10, (4,4)), index=list('abcd'), columns=list('ABCD'))
In [95]: df.ix['d','A'] = df.ix['a', 'A']
In [96]: df
Out[96]:
A B C D
a 1 4 9 1
b 4 5 0 2
c 5 5 1 0
d 1 3 9 6
In [97]: df.mask('A', 1)
Out[97]:
A B C D
a 1 4 9 1
d 1 3 9 6
In [98]: df.mask('A', 1).mask('D', 6)
Out[98]:
A B C D
d 1 3 9 6
Answer #2:
Filters can be chained using a Pandas query:
df = pd.DataFrame(np.random.randn(30, 3), columns=['a','b','c'])
df_filtered = df.query('a > 0').query('0 < b < 2')
Filters can also be combined in a single query:
df_filtered = df.query('a > 0 and 0 < b < 2')
Answer #3:
The answer from @lodagro is great. I would extend it by generalizing the mask function as:
def mask(df, f):
return df[f(df)]
Then you can do stuff like:
df.mask(lambda x: x[0] < 0).mask(lambda x: x[1] > 0)
Answer #4:
Since version 0.18.1 the .loc method accepts a callable for selection. Together with lambda functions you can create very flexible chainable filters:
import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('ABCD'))
df.loc[lambda df: df.A == 80] # equivalent to df[df.A == 80] but chainable
df.sort_values('A').loc[lambda df: df.A > 80].loc[lambda df: df.B > df.A]
If all you’re doing is filtering, you can also omit the .loc.
Answer #5:
I offer this for additional examples. This is the same answer as https://stackoverflow.com/a/28159296/
I’ll add other edits to make this post more useful.
pandas.DataFrame.query
query was made for exactly this purpose. Consider the dataframe df
import pandas as pd
import numpy as np
np.random.seed([3,1415])
df = pd.DataFrame(
np.random.randint(10, size=(10, 5)),
columns=list('ABCDE')
)
df
A B C D E
0 0 2 7 3 8
1 7 0 6 8 6
2 0 2 0 4 9
3 7 3 2 4 3
4 3 6 7 7 4
5 5 3 7 5 9
6 8 7 6 4 7
7 6 2 6 6 5
8 2 8 7 5 8
9 4 7 6 1 5
Let’s use query to filter all rows where D > B
df.query('D > B')
A B C D E
0 0 2 7 3 8
1 7 0 6 8 6
2 0 2 0 4 9
3 7 3 2 4 3
4 3 6 7 7 4
5 5 3 7 5 9
7 6 2 6 6 5
Which we chain
df.query('D > B').query('C > B')
# equivalent to
# df.query('D > B and C > B')
# but defeats the purpose of demonstrating chaining
A B C D E
0 0 2 7 3 8
1 7 0 6 8 6
4 3 6 7 7 4
5 5 3 7 5 9
7 6 2 6 6 5
Answer #6:
pandas provides two alternatives to Wouter Overmeire’s answer which do not require any overriding. One is .loc[.] with a callable, as in
df_filtered = df.loc[lambda x: x['column'] == value]
the other is .pipe(), as in
df_filtered = df.pipe(lambda x: x['column'] == value)
Answer #7:
I had the same question except that I wanted to combine the criteria into an OR condition. The format given by Wouter Overmeire combines the criteria into an AND condition such that both must be satisfied:
In [96]: df
Out[96]:
A B C D
a 1 4 9 1
b 4 5 0 2
c 5 5 1 0
d 1 3 9 6
In [99]: df[(df.A == 1) & (df.D == 6)]
Out[99]:
A B C D
d 1 3 9 6
But I found that, if you wrap each condition in (... == True) and join the criteria with a pipe, the criteria are combined in an OR condition, satisfied whenever either of them is true:
df[((df.A==1) == True) | ((df.D==6) == True)]
Answer #8:
My answer is similar to the others. If you do not want to create a new function you can use what pandas has defined for you already. Use the pipe method.
df.pipe(lambda d: d[d['column'] == value])
