Parsing dates in pandas.read_csv with null-value handling?

Question

Consider the following made-up CSV:

from io import StringIO

data = """value,date
7,null
7,10/18/2008
621,(null)"""

fake_file = StringIO(data)

I want to read this file using pandas.read_csv, handling nulls with the na_values parameter and dates with parse_dates and date_parser:

import pandas as pd

date_parser = lambda c: pd.datetime.strptime(c, '%m/%d/%Y')

df = pd.read_csv(fake_file,
                 parse_dates=['date'],
                 date_parser=date_parser,
                 na_values=['null', '(null)'])

Running this code in Python 3.5 gives me this:

  File "<ipython-input-11-aa5bcf0858b7>", line 1, in <lambda>
    date_parser = lambda c: pd.datetime.strptime(c, DATE_FMT)

TypeError: strptime() argument 1 must be str, not float

So it seems the nulls are handled first and then the dates are attempted to be parsed...

I know I can do this:

df = pd.read_csv(fake_file,
                 na_values=['null', '(null)'])
df['date'] = pd.to_datetime(df['date'],
                            format='%m/%d/%Y')

But my real question is how to both handle date formatting and NaN-handling in one fell swoop...

Answer 1

Use to_datetime with format and errors='coerce':

date_parser = lambda c: pd.to_datetime(c, format='%m/%d/%Y', errors='coerce')
df = pd.read_csv(fake_file, parse_dates=['date'], date_parser=date_parser)
print (df)
   value       date
0      7        NaT
1      7 2008-10-18
2    621        NaT