API deprecate date_parser, add date_format

[MarcoGorelli]

TLDR the conversation here goes on for a bit, but to summarise, the suggestion is:

• deprecate date_parser, because it always hurts performance (counter examples welcome!)
• add date_format, because that can boost performance
• for anything else, amend docs to make clear that users should first in the data as object, and then apply their parsing

Performance-wise, this would only be an improvement to the status quo

---

As far as I can tell, date_parser is a net negative and only ever slows things down

In the best case, it only results in a slight degradation:

timestamp_format = '%Y-%d-%m %H:%M:%S'

date_index = pd.date_range(start='1900', end='2000')

dates_df = date_index.strftime(timestamp_format).to_frame(name='ts_col')
data = dates_df.to_csv()

In [6]: %%timeit
   ...: df = pd.read_csv(io.StringIO(data),
   ...:     date_parser=lambda x: pd.to_datetime(x, format=timestamp_format),
   ...:     parse_dates=['ts_col']
   ...: )
   ...: 
   ...: 
111 ms ± 3.02 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [7]: %%timeit
   ...: df = pd.read_csv(io.StringIO(data),
   ...:     #date_parser=lambda x: pd.to_datetime(x, format=timestamp_format),
   ...:     #parse_dates=['ts_col']
   ...: )
   ...: df['ts_col'] = pd.to_datetime(df['ts_col'], format=timestamp_format)
   ...: 
   ...: 
75.8 ms ± 1.98 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Parsing element-by-element is also slower than just using .apply:

In [21]: %%timeit
    ...: df = pd.read_csv(io.StringIO(data),
    ...:     #date_parser=lambda x: pd.to_datetime(x, format=timestamp_format),
    ...:     #parse_dates=['ts_col']
    ...: )
    ...: df['ts_col'].apply(lambda x: du_parse(x, dayfirst=True))
    ...: 
    ...: 
1.13 s ± 33.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [22]: %%timeit
    ...: df = pd.read_csv(io.StringIO(data),
    ...:     date_parser=lambda x: du_parse(x, dayfirst=True),
    ...:     parse_dates=['ts_col']
    ...: )
    ...: 
    ...: 
1.19 s ± 3.43 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In the worst case, it results in 65x performance degradation, see #50586 (comment) (and this gets way worse for larger datasets)

My suggestion is:

• deprecate date_parser
• introduce date_format, which would actually deliver a performance improvement:

In [1]: timestamp_format = '%Y-%d-%m %H:%M:%S'
   ...: 
   ...: date_index = pd.date_range(start='1900', end='2000')
   ...: 
   ...: dates_df = date_index.strftime(timestamp_format).to_frame(name='ts_col')
   ...: data = dates_df.to_csv()

In [2]: %%timeit
   ...: df = pd.read_csv(io.StringIO(data),
   ...:     date_format=timestamp_format,
   ...:     parse_dates=['ts_col']
   ...: )
   ...: 
   ...: 
19.5 ms ± 397 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

That's over 3 times as fast!

[sm-Fifteen]

The main drawback I can see of switching from a callback to a format string is that the callback enables you to use Series.name to use different patterns for each date column, though that can be remedied by allowing a dict to map column names to date formats.

Is there concern that the other parameters of to_datetime may end up having to be added to read_csv, like utc=, unit=, or origin=, or are these not a problem for datetime column parsing (at read time, anyway)?

At any rate, I certainly feel that this would be a pretty sizable improvement to date parsing, not just in terms of performance gains but also in terms of usability.

[MarcoGorelli]

Thanks for your input, much appreciated

how about to_datetime_kwargs? then dayfirst could be deprecated as well. so then someone can run

read_csv(data, parser_dates=['ts_col'], to_datetime_kwargs={'format': datetime_format, 'utc': False, 'dayfirst': True})

and it would be performant, because to_datetime wouldn't need to be called twice in

pandas/pandas/io/parsers/base_parser.py

Lines 1133 to 1135 in 3bc2203

	result = tools.to_datetime(
	date_parser(*date_cols), errors="ignore", cache=cache_dates
	)

, the kwargs could directly go to

pandas/pandas/io/parsers/base_parser.py

Lines 1124 to 1130 in 3bc2203

	return tools.to_datetime(
	ensure_object(strs),
	utc=False,
	dayfirst=dayfirst,
	errors="ignore",
	cache=cache_dates,
	).to_numpy()

[MarcoGorelli]

The main drawback I can see of switching from a callback to a format string is that the callback enables you to use Series.name to use different patterns for each date column

not sure I see what you mean - is this currently supported? do you have an example please?

[sm-Fifteen]

The main drawback I can see of switching from a callback to a format string is that the callback enables you to use Series.name to use different patterns for each date column

not sure I see what you mean - is this currently supported? do you have an example please?

Something like this:

import pandas as pd
from io import StringIO
from tempfile import TemporaryFile

date_euro = pd.date_range(start='1900', end='2000', freq='1D').strftime("%d/%m/%Y %H:%M:%S").to_list()
date_us = pd.date_range(start='1900', end='2000', freq='1D').strftime("%m/%d/%Y %H:%M:%S").to_list()

date_df_pre = pd.DataFrame({
    'date_euro': date_euro,
    'date_us': date_us,
})

csv_buf = StringIO()
date_df_pre.to_csv(csv_buf)
csv_buf.seek(0)

date_df_pre

(day first vs month-first)

0      01/01/1900 00:00:00  01/01/1900 00:00:00
1      02/01/1900 00:00:00  01/02/1900 00:00:00
2      03/01/1900 00:00:00  01/03/1900 00:00:00
3      04/01/1900 00:00:00  01/04/1900 00:00:00
4      05/01/1900 00:00:00  01/05/1900 00:00:00
...                    ...                  ...
36520  28/12/1999 00:00:00  12/28/1999 00:00:00
36521  29/12/1999 00:00:00  12/29/1999 00:00:00
36522  30/12/1999 00:00:00  12/30/1999 00:00:00
36523  31/12/1999 00:00:00  12/31/1999 00:00:00
36524  01/01/2000 00:00:00  01/01/2000 00:00:00

[36525 rows x 2 columns]

from typing import Union

def parse_all_dates(col: Union[str,pd.Series]):
    if not isinstance(col, pd.Series): return col
    if not pd.api.types.is_string_dtype(col.dtype): return col

    if col.name == 'date_euro': return pd.to_datetime(col, format="%d/%m/%Y %H:%M:%S")
    if col.name == 'date_us': return pd.to_datetime(col, format="%m/%d/%Y %H:%M:%S")
    return col

new_df = pd.read_csv(csv_buf, usecols=['date_euro', 'date_us'], parse_dates=['date_euro', 'date_us'], date_parser=parse_all_dates)
new_df

       date_euro    date_us
0     1900-01-01 1900-01-01
1     1900-02-01 1900-01-02
2     1900-03-01 1900-01-03
3     1900-04-01 1900-01-04
4     1900-05-01 1900-01-05
...          ...        ...
36520 1999-12-28 1999-12-28
36521 1999-12-29 1999-12-29
36522 1999-12-30 1999-12-30
36523 1999-12-31 1999-12-31
36524 2000-01-01 2000-01-01

[36525 rows x 2 columns]

A bit contrived, but I'm sure there are real-world use-cases that would need something like this, where different date formats are used per column.

[MarcoGorelli]

IIUC that's not currently supported by date_parser either right?

if so, let's keep that to a separate discussion (you're welcome to open a new issue and I'll gladly take a look)

[sm-Fifteen]

Thanks for your input, much appreciated

how about to_datetime_kwargs? then dayfirst could be deprecated as well. so then someone can run

I can't say, I haven't had to use these parameters before and can't tell whether they're actually useful for CSV parsing, I'm just pointing it out for the record, in case there's a use case with these that I'm missing.

If the other args for to_datetime actually matter for file parsing, then I guess kwargs is probably the most extensible and least "future-intrusive" solution.

[sm-Fifteen]

IIUC that's not currently supported by date_parser either right?

if so, let's keep that to a separate discussion (you're welcome to open a new issue and I'll gladly take a look)

Oh, no, that code snippet actually works and uses date_parser to parse both column differently. It strikes me as a potentially useful feature (on paper anyway, I can't recall ever needing to do this) that would be lost if date_format were to only accept a flat string, and not, say, a dict of formats mapped by column names as well.

[MarcoGorelli]

ah I see, sorry, I should've actually tried running it. and if it's possible, then there's definitely someone out there using it 😄

a dict mapping column names to kwargs would allow for that functionality to be kept though, right? like

to_datetime(data, to_datetime_kwargs={'date_euro': {'dayfirst': True}, 'date_us': {'dayfirst': False}}

So, to_datetime_kwargs could either be a dict, or a dict of dicts

[sm-Fifteen]

I can't speak for the ergonomics of such a solution (the list of parameters for read_csv is already pretty long and complex), but it would at least preserve the functionnality of the callback, as far as I can tell. Looking at the current docs, there are cases where specific use of to_datetime is needed (such as mixed timezone columns), so that wouldn't be lost, at least.

---

Truth be told, the more I look at the extended use-cases, the less I'm confident about having this sort of highly tunable functionnality (date parsing kwargs, that is) be baked directly in the read_csv function instead of having the user do the conversion as a second pass. Pola-rs, for instance, only has a switch for automatic date parsing where it tries to figure out your date format in an undocumented way based on the first value of a column by testing a bunch of big-endian date formats, and the guide saying you should leave that switch off and do the string-to-date conversion yourself if you have anything more complicated than that.

Leaving read_csv with just the simple knobs (i.e: just parse_dates= + date_format= and that's it) and having the doc reccomend a two-pass approach for the rest (like I was doing in #35296) might be preferable, but I'm far from having sufficent knowledge of the user expectations for Pandas to tell if that would be a good change or not.

[MarcoGorelli]

Thanks for your thoughts

Yes, I think you're right, I'd be +1 for simple knobs, and doc change

So, to summarise:

• deprecate date_parser, because it always hurts performance (counter examples welcome!)
• add date_format, because that can boost performance
• for anything else, amend docs to make clear that users should first in the data as object, and then apply their parsing

Performance-wise, this would only be an improvement to the status quo