Pandas: Coerce errors while reading CSV

Question

The pandas.to_datetime function has an errors keyword argument, that if set to 'coerce' will replace any values that it fails to cast with NaT.

Is there a way to replicate that functionality in pandas.read_csv while it's casting the columns?

For example, if I have the following data in a CSV file:

a,c
0,a
1,b
2,c
a,d

And I try:

pd.read_csv("file.csv", dtype={"a":"int64", "c":'object'})

It throws an error saying that it was unable to convert column a to type int64.

Is there a way to read a CSV with pandas so that if it fails while casting a column to fill a failed value with NaN or something that I specify?

Answer 1

Here is a solution that might work for you; or at least get you going in a direction.

Caveat:

AFIK what you're after, is not possible - i.e.: an int64 column with a NaN value because NaN is a float data type. Additionally, there is no need to convert column c to object, as this is implied.

Suggested Solution:

First, read your CSV without casting data types. Then, clean your data / convert your data types.

import numpy as np
import pandas as pd

# Just pretend this is reading from a CSV.
data = {'a': [0, 1, 2, 'a'],
        'c': ['a', 'b', 'c', 'd']}
df = pd.DataFrame(data)

Original Dataset:

   a  c
0  0  a
1  1  b
2  2  c
3  a  d 

a    object
c    object
dtype: object 

Convert column a:
Using the pd.to_numeric function, you can do something similar to to_datetime by coercing any errors to NaN. However, this converts your column to float64, as NaN is a float data type.

df['a'] = pd.to_numeric(df['a'], errors='coerce')

Output:

     a  c
0  0.0  a
1  1.0  b
2  2.0  c
3  NaN  d 

a    float64
c     object
dtype: object 

Convert column a to int64:
If you must have column a as an integer, you can do this:

df['a'] = df['a'].replace(np.nan, 0).astype(np.int64)

Output:

   a  c
0  0  a
1  1  b
2  2  c
3  0  d 

a     int64
c    object
dtype: object

Hope this gets you started.

Answer 2

Here's another solution that does it at read time. You can pass manual conversion function to csv reading as pd.read_csv(..., converters=...).

For your case, you should pass converters={'a': convert_to_none_coerce_if_not} where convert_to_none_coerce_if_not can be:

import numpy as np

def convert_to_none_coerce_if_not(val: str):
    try:
        if int(str) == float(str):
            # string is int
            return np.int16(str)
        else:
            # string is numeric, but a float
            return np.nan
    except ValueError as e:
        # string cannot be parsed as a number, return nan
        return np.nan