Define a strategy of filling NaNs in pandas dataframe?-CodePudding

I have a dataframe with mixed types - strings, floats, integers, bool.

pd.DataFrame({'a': [6.6, -5.2, 2.1, float('NaN'), float('NaN')],
              'b': ['a', 'a', 'NaN', 'b', 'NaN'],
              'c': [True, True, False, float('NaN'), float('NaN')],
              'd': [1,2,3,None, None]})

Most of these columns have some NaNs. I want to impute the missing values according to some custom rules: For every float type column - take the median of this column and impute. For every string column - take the mode and impute. For every integer column - take the median, ceil and impute. For every bool column - impute missing values with False.

That is what I have done so far:

fill_na_policy = {'float64': np.median(),
                  'int': np.ceil(np.median()),
                  'string': scipy.stats.mode(),
                  'bool': False}
df.fillna(df.dtypes.replace(fill_na_policy), inplace=True)

Please advise how to make it work or should I create lambda functions for each type?

CodePudding user response：

You can distinguish and select the different types columns using select_dtypes , and impute using the technique you want each individual parts of the dataframe. Consider the below example:

# Select numeric columns
f = df.select_dtypes('float64')
i = df.select_dtypes('int64')

# Select string and boolean columns
o = df.select_dtypes('object')
b = df.select_dtypes(include='bool')

# Fill numeric
df[f.columns] = f.fillna(f.median())
df[i.columns] = i.fillna(np.ceil(np.median(i)))

# Fill object
df[o.columns] = o.fillna(o.agg(lambda x: x.mode().values[0]))
df[b.columns] = b.fillna(False)

Which will give you:

     a  b      c    d
0  6.6  a   True  1.0
1 -5.2  a   True  2.0
2  2.1  a  False  3.0
3  2.1  b   True  2.0
4  2.1  a   True  2.0

CodePudding user response：

Create a Series for the various dtypes:

# create more variables depending on the dtype
floats = df.select_dtypes(float).median()
strings = df.select_dtypes('object').mode().stack().droplevel(0)
fill_vals = pd.concat([floats, strings])

Now fill the dataframe (the columns are the index in fill_vals, the values will be replaced for each corresponding column):

df.fillna(fill_vals)