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 from Wouter Overmeire on Stack Overflowpython - pandas: filter rows of DataFrame with operator chaining - Stack Overflow
Pandas - Filter based on multiple conditions
python - How to filter Pandas dataframe using 'in' and 'not in' like in SQL - Stack Overflow
python - Efficient way to apply multiple filters to pandas DataFrame or Series - Stack Overflow
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
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')
Hi,
I have a csv file with approx. 100 columns and I want to filter rows if two of the columns are set to a value of X and the other columns are blank / Nan values. In order to make the code more readable, I would like to specify the column names in a list and then use the variable name within the Pandas query e.g. something like the following:
my_file=/home/test.csv my_df=pd.read_csv(my_file) control_fields=['Is_Active','Is_Valid'] data_fields=['Age','DOB','Country','City']
As a starting point, I have tried the following but this isn't filtering the data at all:
my_new_df=my_df[my_df['control_fields']==1]
Can someone please explain why the above isn't working and also advise if there is a better way of achieving my requirement?
Thanks!
You can use pd.Series.isin.
For "IN" use: something.isin(somewhere)
Or for "NOT IN": ~something.isin(somewhere)
As a worked example:
>>> df
country
0 US
1 UK
2 Germany
3 China
>>> countries_to_keep
['UK', 'China']
>>> df.country.isin(countries_to_keep)
0 False
1 True
2 False
3 True
Name: country, dtype: bool
>>> df[df.country.isin(countries_to_keep)]
country
1 UK
3 China
>>> df[~df.country.isin(countries_to_keep)]
country
0 US
2 Germany
Alternative solution that uses .query() method:
In [5]: df.query("country in @countries_to_keep")
Out[5]:
countries
1 UK
3 China
In [6]: df.query("country not in @countries_to_keep")
Out[6]:
countries
0 US
2 Germany
Pandas (and numpy) allow for boolean indexing, which will be much more efficient:
In [11]: df.loc[df['col1'] >= 1, 'col1']
Out[11]:
1 1
2 2
Name: col1
In [12]: df[df['col1'] >= 1]
Out[12]:
col1 col2
1 1 11
2 2 12
In [13]: df[(df['col1'] >= 1) & (df['col1'] <=1 )]
Out[13]:
col1 col2
1 1 11
If you want to write helper functions for this, consider something along these lines:
In [14]: def b(x, col, op, n):
return op(x[col],n)
In [15]: def f(x, *b):
return x[(np.logical_and(*b))]
In [16]: b1 = b(df, 'col1', ge, 1)
In [17]: b2 = b(df, 'col1', le, 1)
In [18]: f(df, b1, b2)
Out[18]:
col1 col2
1 1 11
Update: pandas 0.13 has a query method for these kind of use cases, assuming column names are valid identifiers the following works (and can be more efficient for large frames as it uses numexpr behind the scenes):
In [21]: df.query('col1 <= 1 & 1 <= col1')
Out[21]:
col1 col2
1 1 11
Chaining conditions creates long lines, which are discouraged by PEP8.
Using the .query method forces to use strings, which is powerful but unpythonic and not very dynamic.
Once each of the filters is in place, one approach could be:
import numpy as np
import functools
def conjunction(*conditions):
return functools.reduce(np.logical_and, conditions)
c_1 = data.col1 == True
c_2 = data.col2 < 64
c_3 = data.col3 != 4
data_filtered = data[conjunction(c_1,c_2,c_3)]
np.logical operates on and is fast, but does not take more than two arguments, which is handled by functools.reduce.
Note that this still has some redundancies:
- Shortcutting does not happen on a global level
- Each of the individual conditions runs on the whole initial data
Still, I expect this to be efficient enough for many applications and it is very readable. You can also make a disjunction (wherein only one of the conditions needs to be true) by using np.logical_or instead:
import numpy as np
import functools
def disjunction(*conditions):
return functools.reduce(np.logical_or, conditions)
c_1 = data.col1 == True
c_2 = data.col2 < 64
c_3 = data.col3 != 4
data_filtered = data[disjunction(c_1,c_2,c_3)]