Using Simple imputer replace NaN values with mean error

Question

I am trying to replace 2 missing NaN values in data using the SimpleImputer. I load my data as follow;

import pandas as pd
import numpy as np
df = pd.read_csv('country-income.csv', header=None)
df.head(20)

As we can see I have 2 NaN values which I am trying to replace with mean() values using SimpleImputer and I get the following error:

imputer = SimpleImputer(missing_values=np.nan, strategy='mean', fill_value=None)
imputer.fit(df)

Because I have some categorical data (hence the error), I tried to take only the numeric columns so I tried this method:

missing_vars_numeric = [var for var in df.columns
                          if df[var].isnull().mean() > 0 and df[var].dtype != "0"]
missing_vars_numeric


Output: [1,2]

But when I use `missing_vars_numeric in the imputer I get the following error:

imputer = SimpleImputer(missing_values=np.nan, strategy='mean', fill_value=None)
imputer.fit(missing_vars_numeric)

ValueError: Expected 2D array, got 1D array instead:
array=[1. 2.].
Reshape your data either using array.reshape(-1, 1) if your data has a single feature or array.reshape(1, -1) if it contains a single sample.

I also tried using astype() and It did not work for me. What am I missing?

Sample of the DataFrame

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'])

Answer 1

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.