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

user_5015569 0
Viewed 3691

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...

pandas null python
4 Answers

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
user_29010020

The problem is your custom date parser - it can't handle the NaNs. Instead you can use the pandas.to_datetime function as a parser:

from functools import partial

date_parser = partial(pd.to_datetime, format='%m/%d/%Y')
user_37672390

Make use of infer_datetime_format=True parameter:

In [24]: pd.read_csv(StringIO(data), parse_dates=['date'], infer_datetime_format=True, na_values=['null', '(null)'])
Out[24]:
   value       date
0      7        NaT
1      7 2008-10-18
2    621        NaT

PS I guess your second option might be faster

user_57412050

pd.to_datetime can handle NaN by default, so, you simply need to replace the date_parser that you created with pd.to_datetime.

Here's how the solution looks like:

In [10]: pd.read_csv(pd.io.common.StringIO(data), parse_dates=['date'], 
    ...:             date_parser=pd.to_datetime, na_values=['null', '(null)'])
Out[10]: 
   value       date
0      7        NaT
1      7 2008-10-18
2    621        NaT
user_17622110
Related