Pandas DataFrames column not being identified as numeric

Question

I was working with a Pandas dataframe, using the UCI repository credit screening file at http://archive.ics.uci.edu/ml/machine-learning-databases/credit-screening/crx.data

The data contains some missing values and I want to perform a different imputation strategy depending on the data type of the column. For example, if the column is numeric use median imputing, but if it is categorical replace for a category such as "No Value".

I run this code to identify the numeric columns:

#Import data
import pandas as pd
data = pd.read_csv('http://archive.ics.uci.edu/ml/machine-learning- 
databases/credit-screening/crx.data', header=None)

#Imputation
import numpy as np
data = data.replace('?', np.nan)
numeric_columns = data.select_dtypes(include=[np.number]).columns

And it returns:

Out[67]: Int64Index([2, 7, 10, 14], dtype='int64')

For some reason it is not identifying the column 1 (which is clearly numeric) as such. I believe the cause is that there are some NaN values in the column that are making it look like it is not numeric. Anyone know what's going on and what can I do to identify the column 1 as a numeric?

Thanks!

Answer 1

Use pd.to_numeric with error='ignore':

Before, df.info():

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 690 entries, 0 to 689
Data columns (total 16 columns):
0     678 non-null object
1     678 non-null object
2     690 non-null float64
3     684 non-null object
4     684 non-null object
5     681 non-null object
6     681 non-null object
7     690 non-null float64
8     690 non-null object
9     690 non-null object
10    690 non-null int64
11    690 non-null object
12    690 non-null object
13    677 non-null object
14    690 non-null int64
15    690 non-null object
dtypes: float64(2), int64(2), object(12)
memory usage: 86.3+ KB

Use pd.to_numeric:

df = df.replace('?',np.nan)
df = df.apply(lambda x: pd.to_numeric(x,errors='ignore'))

After output, df.info():

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 690 entries, 0 to 689
Data columns (total 16 columns):
0     678 non-null object
1     678 non-null float64
2     690 non-null float64
3     684 non-null object
4     684 non-null object
5     681 non-null object
6     681 non-null object
7     690 non-null float64
8     690 non-null object
9     690 non-null object
10    690 non-null int64
11    690 non-null object
12    690 non-null object
13    677 non-null float64
14    690 non-null int64
15    690 non-null object
dtypes: float64(4), int64(2), object(10)
memory usage: 86.3+ KB