1
$\begingroup$

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

In [1]: import pandas as pd                                                                                                                           
In [2]: df = pd.read_csv('c1.csv')                                                                                                                    
In [3]: df.head(2)                                                                                                                                    
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

Improve this question
$\endgroup$
3
  • $\begingroup$ 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. $\endgroup$
    – Alex L
    May 12, 2019 at 16:21
  • $\begingroup$ Clarify me once: The former will be average average temperature for all the years greater than 2000 ? $\endgroup$
    – ArnuldOnData
    May 13, 2019 at 14:26
  • 1
    $\begingroup$ 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. $\endgroup$
    – Ben Reiniger ♦
    May 13, 2019 at 16:54

1 Answer 1

Reset to default
1
$\begingroup$

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.

Improve this answer
$\endgroup$
5
  • $\begingroup$ 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 ? $\endgroup$
    – ArnuldOnData
    May 13, 2019 at 14:32
  • $\begingroup$ 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 $\endgroup$
    – ArnuldOnData
    May 20, 2019 at 17:08
  • $\begingroup$ 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 $\endgroup$
    – ArnuldOnData
    May 20, 2019 at 17:09
  • $\begingroup$ 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. $\endgroup$
    – secretive
    May 20, 2019 at 17:47
  • $\begingroup$ 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. $\endgroup$
    – secretive
    May 20, 2019 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.