BUG: add date_format to read_csv / Date parsing mistake. read_csv

[John-Colvin]

date_format keyword could take the format, dict of columns to format, or list of formats (and could then obviate the need for parse_dates)

Sometimes months and days get mixed up.

E.g.
test.csv:

a,b
27.03.2003 14:55:00.000,1
03.08.2003 15:20:00.000,2

read_csv("/home/john/Documents/test.csv",index_col=0, parse_dates=True)
b
a
2003-03-27 14:55:00 1
2003-03-08 15:20:00 2

There doesn't appear to be any continuity in the date parsing over the rows. As well as meaning things can easily get switched around, this makes date parsing VERY slow. Once you know the format, using the datetime constructor with string slicing as a parser makes read_csv 20x faster on my machine.

I think there needs to be some more parameters for specifying date formats. seeing as in the general case dates a string of dates can be ambiguous (see above).

A possible approach: Have a few default formats to choose from, as well as a more general format string approach. Obviously the defaults could use the datetime constructor with string slicing, which is very fast.

Perhaps have a dayfirst and yearfirst flag that gets passed to dateutil.parser.parse to solve ambiguities when using automatic parsing.

[qwhelan]

Try using the dayfirst parameter (new in v0.10.0):

In [4]: read_csv('test.csv', index_col=0, parse_dates=True, dayfirst=True)
Out[4]: 
                     b
a                     
2003-03-27 14:55:00  1
2003-08-03 15:20:00  2

If you want to specify the datetime format you can use the date_parser parameter:

In [5]: read_csv('test.csv', index_col=0, date_parser=lambda x: datetime.strptime(x, '%d.%m.%Y %H:%M:%S.%f'))
Out[5]: 
                     b
a                     
2003-03-27 14:55:00  1
2003-08-03 15:20:00  2

[John-Colvin]

sorry, my bad for not looking at the API reference closely enough to spot the dayfirst flag.

the date_parser parameter is what I was using to test using the datetime constructor. In my experience strptime is very slow compared to using the constructor and string slicing. Having some default formats to choose from that then call an optimised parser would seem a good idea.

It would speed up parsing for those formats while also allowing the user to be strict with the format, without them having to write their own, potentially slow, parsing function.

It seems worthwhile, what with the recent efforts to make file parsing much faster the date parsing really dominates the runtime of read_csv

[qwhelan]

Here are the benchmarks I'm getting (50k of the format %d/%M/%Y):

In [68]: %timeit read_csv('dates.csv', parse_dates=[0], dayfirst=True)
1 loops, best of 3: 4.6 s per loop

In [69]: %timeit read_csv('dates.csv', parse_dates=[0], date_parser=lambda x: datetime.strptime(x, '%d/%M/%Y'), dayfirst=True)
1 loops, best of 3: 1.18 s per loop

And for passing directly to the constructor:

In [70]: %timeit data = read_csv('dates.csv')
10 loops, best of 3: 21.1 ms per loop

In [71]: %time data.Date = map(lambda x, y, z: datetime(x, y, z), data.Date.map(lambda a: int(a[4:])), data.Date.map(lambda a: int(a[2:3])), data.Date.map(lambda a: int(a[:1])))
CPU times: user 0.35 s, sys: 0.00 s, total: 0.35 s
Wall time: 0.35 s

So there's a ~3x speedup by specifying the format, and another ~3x from manually passing to the constructor.

I don't think the user should be forced to pass a date format to get faster performance, especially when there might be multiple columns with different formats. We're currently assuming that a date column is not of a homogeneous format (by calling parse() independently on every value). All those independent calls probably means we're seeing a lot of object creation/destruction (ie, compiled regexes that are used once).

If we have a homogeneity flag, we could call parse() on the first row, get the pattern it settled on (may not be currently available from parse()), and then construct an array of datetimes.

[wesm]

I actually added a format argument to to_datetime-- it might be worth adding to read_csv, maybe. Alternately you can parse without using parse_dates and use to_datetime yourself and it should be reasonably fast.

[dragoljub]

A 'format' argument would be a great feature in read_csv(). Would save you from writhing the 'fast' parser lambda function with strptime() while also handling the special non-parseable case where you want to just fill parsing errors with a np.nan or NaT. I'm assuming this would use datetime's format strings.

[jreback]

the new infer_datetime_format flag solves this problem (though leaving open because ideally you specify the format directly)

[maxgrenderjones]

Small point, but just to give an example of a similar use case that should be handled - I have a csv in which some columns are dates and some are datetimes. I need to be able to provide a different format for each column that I want to parse (as per the OP's suggested specification).

[jreback]

@maxgrenderjones interested in doing a PR for this?

as an aside, try infer_datetime_format kw (its in 0.13.1), should be able to figure out most common formats.