Missing values in pandas using errors='coerce'

user_13317119 0
Viewed 646

I wanted to count the missing values for the following data using the following command. 

df.isnull().sum()

It failed to detect missing values. Then I have used the following command

cols = df.columns
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce')
missing = df.isnull().sum()
print(missing)

It worked , but it also transformed the 'BinaryObject" categorical response variable to NAN as well. If I have categorical variables in multiple columns and missing values are marked as ? or other weird symbol, how can I identify those missing values? I appreciate your suggestions!

pandas python
1 Answers

You can use np.isreal() to see if your columns contain string representations of numbers. 

cols = [c for c in df.columns if df[c].map(np.isreal).all()]
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce')
missing = df.isnull().sum()
print(missing)

Here's a toy example.

import pandas as pd
import numpy as np

dict = {'Name': ['Alex', 'Alex', '', 'Donna'],
        'Number_1': ['1', np.nan, '3', '4'],
        'Number_2': [np.nan,np.nan, '3', '4']}

df = df.replace('?', np.nan)

df = pd.DataFrame(dict)


print(df)

Name    Number_1    Number_2
0   Alex    1        NaN
1   Alex    NaN      NaN
2           3        3
3   Donna   4        4

cols = [c for c in df.columns.tolist() if df[c].map(np.isreal).all()]
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce')
missing = df.isnull().sum()
print(missing)

Name        0
Number_1    1
Number_2    2
dtype: int64

print(df.dtypes)

Name         object
Number_1    float64
Number_2    float64
dtype: object

print(df)
    Name    Number_1    Number_2
0   Alex    1.0         NaN
1   Alex    NaN         NaN
2           3           3
3   Donna   4           4

Going back to your original code, we can see the difference.

cols = df.columns
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce')
missing = df.isnull().sum()
print(missing)

Name        4
Number_1    1
Number_2    2
dtype: int64

print(df.dtypes)

Name        float64
Number_1    float64
Number_2    float64
dtype: object

print(df)
    Name    Number_1    Number_2
0   NaN     1.0         NaN
1   NaN     NaN         NaN
2   NaN     3.0         3.0
3   NaN     4.0         4.0
user_73274830
Related