Very likely, you're using the wrong type for the year. I imagine these are integers.
You should try:
df.loc[(df['Granularity'].isin(['Total', 'Urban'])) & df['Year'].eq(2017)]
output (for the Year 2018 as 2017 is missing from the provided data):
Zone Granularity Year Value
20909 Zimbabwe Total 2018 14438.802
20913 Zimbabwe Urban 2018 5447.513
Answer from mozway on Stack Overflowpython - Selecting with complex criteria from pandas.DataFrame - Stack Overflow
Pandas - Filter based on multiple conditions
How to "pass through" multiple conditions in a pandas dataframe with query?
How to remove rows with multiple conditions?
Sure! Setup:
>>> import pandas as pd
>>> from random import randint
>>> df = pd.DataFrame({'A': [randint(1, 9) for x in range(10)],
'B': [randint(1, 9)*10 for x in range(10)],
'C': [randint(1, 9)*100 for x in range(10)]})
>>> df
A B C
0 9 40 300
1 9 70 700
2 5 70 900
3 8 80 900
4 7 50 200
5 9 30 900
6 2 80 700
7 2 80 400
8 5 80 300
9 7 70 800
We can apply column operations and get boolean Series objects:
>>> df["B"] > 50
0 False
1 True
2 True
3 True
4 False
5 False
6 True
7 True
8 True
9 True
Name: B
>>> (df["B"] > 50) & (df["C"] != 900)
or
>>> (df["B"] > 50) & ~(df["C"] == 900)
0 False
1 False
2 True
3 True
4 False
5 False
6 False
7 False
8 False
9 False
[Update, to switch to new-style .loc]:
And then we can use these to index into the object. For read access, you can chain indices:
>>> df["A"][(df["B"] > 50) & (df["C"] != 900)]
2 5
3 8
Name: A, dtype: int64
but you can get yourself into trouble because of the difference between a view and a copy doing this for write access. You can use .loc instead:
>>> df.loc[(df["B"] > 50) & (df["C"] != 900), "A"]
2 5
3 8
Name: A, dtype: int64
>>> df.loc[(df["B"] > 50) & (df["C"] != 900), "A"].values
array([5, 8], dtype=int64)
>>> df.loc[(df["B"] > 50) & (df["C"] != 900), "A"] *= 1000
>>> df
A B C
0 9 40 300
1 9 70 700
2 5000 70 900
3 8000 80 900
4 7 50 200
5 9 30 900
6 2 80 700
7 2 80 400
8 5 80 300
9 7 70 800
Another solution is to use the query method:
import pandas as pd
from random import randint
df = pd.DataFrame({'A': [randint(1, 9) for x in xrange(10)],
'B': [randint(1, 9) * 10 for x in xrange(10)],
'C': [randint(1, 9) * 100 for x in xrange(10)]})
print df
A B C
0 7 20 300
1 7 80 700
2 4 90 100
3 4 30 900
4 7 80 200
5 7 60 800
6 3 80 900
7 9 40 100
8 6 40 100
9 3 10 600
print df.query('B > 50 and C != 900')
A B C
1 7 80 700
2 4 90 100
4 7 80 200
5 7 60 800
Now if you want to change the returned values in column A you can save their index:
my_query_index = df.query('B > 50 & C != 900').index
....and use .iloc to change them i.e:
df.iloc[my_query_index, 0] = 5000
print df
A B C
0 7 20 300
1 5000 80 700
2 5000 90 100
3 4 30 900
4 5000 80 200
5 5000 60 800
6 3 80 900
7 9 40 100
8 6 40 100
9 3 10 600
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!
Users can use the where or query function with pandas dataframes to select rows/columns of the dataframe that match certain conditions, e.g.
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.query.html
>>> from numpy.random import randn
>>> from pandas import DataFrame
>>> df = DataFrame(randn(10, 2), columns=list('ab'))
>>> df.query('a > b')In my case, it may be better to think of a simple conditional, all rows satisfying a==1
df.query('a==1')Let's say I had a numpy array/Python list of values, and I would like to do an "OR" query for each item in the list.
list1 = [10, 20, 50]
# the query
df.query('a==10 | a==20 | a==50')Is this possible? The idea would be I would write a function whereby users input a list of values to query, and it performs an OR query for each.
For where, the idea is similar:
temperatures = [80, 90, 100]
# reads in temperatures
# performs this query:
rows = df.where('(temperature == 80) | (temperature == 90) | (temperature == 100)')