ENH/PERF: Add cache='infer' to to_datetime

[mroeschke]

xref PR #17077

Now that a cache keyword has been added to to_datetime, ideally the default should be set to cache='infer' which would inspect the input data to determine whether caching would be a more efficient conversion.

From some research (here and here), date strings, especially ones with timezones offsets, can benefit from conversion with a cache of dates. The rules of thumb of whether to convert with a cache should be based on a combination of input data type, proportion of duplicate values, and number of dates to convert.

Additionally, I'd be nice to resolve existing to_datetime performance issues (e.g. #17410) just so the rules of thumb informing the inference step are not misguided by these issues.

[jorisvandenbossche]

For me the main question is: what would be the cost of inferring on a typical all unique strings case compared to parsing it.
Is suppose to know the number of unique ones we would need to call unique?

In [58]: idx = pd.date_range("1990-01-01", periods=100000, freq='H')

In [60]: idx_string = idx.strftime('%Y-%m-%d %H:%M:%S')

In [62]: %timeit pd.to_datetime(idx_string)
31.8 ms ± 318 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [64]: %timeit pd.unique(idx_string)
26.3 ms ± 1.03 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

So that would give quite a slowdown in that case. Or can this inferring step be done more performantly?

[jreback]

a good heuristic here are

• if < N1, just cache=False
• take first N2 values and check ratio = nuniques / N2
• if a good ratio, then go ahead and cache=True

N1 ~ 50000
N2 may depend on the dtype as well.

you already did some work on figuring out the experimental data, so could just set based on those parameters.

[jbrockmendel]

@mroeschke to_datetime got a cache kwarg a little while ago. Did that handle this issue or is there more?

[mroeschke]

There's a bit more. Currently cache only accepts True/False, and the idea would to add infer that could determine from some light analysis of the incoming data if cache should be True or False.

[spatbord]

I just updated pandas to 0.25.1, and noticed this new behaviour. I would much prefer an option to override the 'infer', since the current behaviour causes a dramatic slowdown in my specific case, from less than 0.5 seconds to almost 2 minutes.
I retrieve a list of stock prices from a database: about 3 years of data for 20k stocks, sorted by first stock and then date (as julianday in the database). When converting to datetime, it infers whether to use the cache in this case based on the first 500 entries, and it decides not to use the cache since the first 500 are unique dates (that's about 2 years of data for the first stock).

timeit.timeit(stmt='pd.to_datetime(prices["Date"], origin="julian", unit="D", cache=True)', globals=globals(), number=1)
107.68747780000001

prices = prices.sort_values(by='Date')
timeit.timeit(stmt='pd.to_datetime(prices["Date"], origin="julian", unit="D", cache=True)', globals=globals(), number=1)
0.4012885999999867

[mroeschke]

Looking back on this issue, I am not convinced this feature is a good idea. I think pandas should aim to be "less smart" in general and developing constant heuristics on when to use the cache may not be realistic given changes in performance in other operations.

Going to close this issue out, but happy to reopen if there is a resurgence of interest