One-liner
df.fillna(df.select_dtypes(np.number).mean(), inplace=True)
df.select_dtypes(np.number)
selects only the numeric columns of the dataframe
.mean()
computes the mean of each column, returning a new dataframe
df.fillna()
accepts a dataframe (or other forms) to impute NaNs in named columns
inplace
just means it happens in the original dataframe itself, without making a copy
You can probably use this to accomplish many more variations of imputation - replacing .mean()
with whatever you need.
Update with df
from OP
The example dataframe you provided has columns with mixtures of data types. Every column contains strings (e.g. '49'
is a string). Only column 2
contains integer types.
When a pandas column contains strings, the column's dtype becomes object
. This type is not part of np.number
, meaning you cannot select any columns with the method in my one-liner
solution above.
Note: OP originally showed a CSV being loaded, which pandas likely loaded into the correct data types. The example snippet for df = pd.DataFrame(...)
gives nearly all values as strings. There is a difference then between the original question and the updated snippet.
Solution
I will walk you through the steps required based on your example snippet.
In general, you need to ensure that all your column types are correct. E.g. the Age
column should have type int
, whereas Region
is str
. You need to convert your column types.
In [1]: import pandas as pd, numpy as np
In [2]: df = pd.DataFrame({'0': ['Region', 'India','Brazil', 'USA','Brazil','USA','India','Brazil','India','USA','India'],
...: '1': ['Age', '49', '32', '35','43','45','40','NaN','53','55','42'],
...: '2': ['Income', 86400, 57600, 64800,73200,'NaN',69600,62400,94800,99600,80400],
...: '3': ['Online Shopper','No','Yes',' No',' No','Yes','Yes','No','Yes','No','Yes']},
...: index=['0', '1', '2', '3','4','5','6','7','8','9','10'])
...:
In [3]: df
Out[3]:
0 1 2 3
0 Region Age Income Online Shopper
1 India 49 86400 No
2 Brazil 32 57600 Yes
3 USA 35 64800 No
4 Brazil 43 73200 No
5 USA 45 NaN Yes
6 India 40 69600 Yes
7 Brazil NaN 62400 No
8 India 53 94800 Yes
9 USA 55 99600 No
10 India 42 80400 Yes
In [4]: df.dtypes
Out[4]:
0 object
1 object
2 object
3 object
dtype: object
Column names stored as the first row, so make them actual column names and remove that first row:
In [5]: column_names = df.iloc[0].tolist()
In [6]: df = df.iloc[1:]
In [7]: df.columns = column_names
The missing NaN values are stored as string: replace with numpy.nan
:
In [8]: df[df == "NaN"] = np.nan
Convert the types of all columns, using a column names to type mapping - not all object
. Note that np.nan is actually a float
- so we can't use int
:
In [9]: df = df.astype({"Region": str, "Age": float, "Income": float, "Online Shopper": bool})
In [10]: df
Out[10]:
Region Age Income Online Shopper
1 India 49.0 86400.0 True
2 Brazil 32.0 57600.0 True
3 USA 35.0 64800.0 True
4 Brazil 43.0 73200.0 True
5 USA 45.0 NaN True
6 India 40.0 69600.0 True
7 Brazil NaN 62400.0 True
8 India 53.0 94800.0 True
9 USA 55.0 99600.0 True
10 India 42.0 80400.0 True
In [11]: df.dtypes
Out[11]:
Region object
Age float64
Income float64
Online Shopper bool
dtype: object
The one-liner solution now works:
In [12]: imputed_df = df.fillna(df.select_dtypes(np.number).mean())
In [13]: imputed_df
Out[13]:
Region Age Income Online Shopper
1 India 49.000000 86400.000000 True
2 Brazil 32.000000 57600.000000 True
3 USA 35.000000 64800.000000 True
4 Brazil 43.000000 73200.000000 True
5 USA 45.000000 76533.333333 True
6 India 40.000000 69600.000000 True
7 Brazil 43.777778 62400.000000 True
8 India 53.000000 94800.000000 True
9 USA 55.000000 99600.000000 True
10 India 42.000000 80400.000000 True
You might want to convert the columns to new types, e.g. making Age
of type int
. I will leave this is an exercise for you. I think this shows you many of the tools you will need to work it out.