How can .mean() exclude NaN values inside aggregate function?

Question

My Dataset has many columns. Here are two:

Index  Graduated  Age
0      College    24
1      HighSch    18
2      College    26
3      College    Nan
4      HighSch    20

The mean of Age is simple enough:

df.Age.mean()

However, I have many other columns, therefore I'm using agg():

df.groupby('Graduated').agg({'Age':'mean'})

The error I get:

No numeric types to aggregate If I insert a number instead of NaN, it works!!

Does the agg() function not allow us to run the mean if column has NaN values? Is there a way around that?

By the looks of it, it is not the "number" nan but instead a string "Nan". Change it to np.nan from numpy and it should work. — user2285236
– user2285236, Commented Jul 17, 2017 at 0:06
See stackoverflow.com/questions/25039328/… for an answer to your particular question not including the "Nan" issue mentioned by @ayhan — AGN Gazer
– AGN Gazer, Commented Jul 17, 2017 at 0:11

otayeby · Accepted Answer · 2017-07-17 00:29:53Z

1

As @ayhan said, the Nan values look like strings. One possible solution is that you can replace the Nan strings you have with actual NaN values using either of those two lines:

df['Age'] = df['Age'].replace(r'Nan', np.nan, regex=True)

@ayhan's suggestion is to use to_numeric method.

df['Age'] = pd.to_numeric(df['Age'], errors='coerce')

Then execute the aggregation that you mentioned in your question. And I would do the same for all columns to avoid confusion and get things straight from the beginning for analysis purposes in the future.

edited Jul 17, 2017 at 0:29

answered Jul 17, 2017 at 0:15

otayeby

3028 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2285236 Over a year ago

You might still need df["age"] = pd.to_numeric(df["age"], errors="coerce") if the dtype is object.

otayeby Over a year ago

I tried it and gave me, ValueError: Unable to parse string "Nan" at position 3

otayeby Over a year ago

That worked, would you like me to add it to the answer?

user2285236 Over a year ago

Sure that would be nice.

otayeby Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

How can .mean() exclude NaN values inside aggregate function?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related