# What is the difference between these 2 Python Pandas queries

I am trying to make sense out of some data, here is the Pandas data frame:

In [1]: import pandas as pd
Out[3]:
year     city        country  avg_temp
0  1849  Abidjan  Côte D'Ivoire     25.58
1  1850  Abidjan  Côte D'Ivoire     25.52


All I want is to know if the year is greater than 2000 and avg_temp is less than 20, then what's the mean() and I get 2 different answers:

In [10]: (df.query('year > 2000')['avg_temp'] < 20).mean()
Out[10]: 0.5629877369007804

In [11]: ((df.year > 2000) & (df.avg_temp < 20)).mean()
Out[11]: 0.03540828203222504

In [12]: ((df['year'] > 2000) & (df['avg_temp'] < 20)).mean()
Out[12]: 0.03540828203222504


I can't figure out which one is correct

• To clarify, are you looking for the mean of avg_temp or the mean of the boolean truth vector? The former will be, as it seems, the average average temperature, the latter will be the number of rows that fit your query divided by the total number of rows. – Alex L May 12 '19 at 16:21
• Clarify me once: The former will be average average temperature for all the years greater than 2000 ? – Arnuld May 13 '19 at 14:26
• None of these returns an average temperature. Without .mean(), each line is a boolean Series, and applying mean to those gives the percentage of city-years with avg_temp<20. The two different answers are explained by @rajatkabra. For average temperatures instead, you need to use the binary series to slice the dataframe, then average the temperature column. – Ben Reiniger May 13 '19 at 16:54

Depends on what you are trying to do. What is the mean that you are trying to find. Your query return the mean of the number of correct matching rows divided by all the rows in the dataset.

Query 10: It first filters the data out on year > 2000 and then check the total number of rows in this filtered dataset which have ['avg_temp'] < 20. So if your whole dataset had 100 rows, your filtered dataset now will have lets say 10 rows. After the avg_temp condition, only 5 rows match that criterion. So the mean will be 0.5

Query 11 and 12:

It filters the original dataframe on both the conditions at the same time. SO from the sample example as above, your total number of rows in the dataset is 100 and the number of rows that match the criterion are still 5 so the mean will be 0.05.

So it depends on what you are trying to do here.

• What does my Query #10 does in contrast to yours. My point is all of them should return the mean of avg_temp for years greater than 2000. But they don't. What is the difference between what your query does and my #10 query ? – Arnuld May 13 '19 at 14:32
• You explained well now. And should they not give same answer ? I mean whether I filter years first and then match temperature or whether I filter both at same time. Answer should be same. But answers #10 and #11 (or #12) are different in my case – Arnuld May 20 '19 at 17:08
• Let's say there are 10 rows that have year > 2000. Whether you filter them first or not, rows will be 10 anyway. I hope you get my point – Arnuld May 20 '19 at 17:09
• thats what I have mentioned in the answer. The result is different because in one case you take mean by considering the whole dataframe but in other query you create a new temporary dataframe with less number of rows in it and take mean from the new dataframe. That is why the mean is different for these queries. – rajat kabra May 20 '19 at 17:47
• To better understand this, break your code. Instead of taking the mean, store these two dataframes in new variables and check their shape to see the number of rows in both of them. then perform mean operation on these filtered dataframe. You will see the difference in the shape. – rajat kabra May 20 '19 at 17:48