API allow for `format='mixed'` in to_datetime

[MarcoGorelli]

This was brought up in the community call.

#50939 adds support for mixed ISO8601 strings. But people may have mixed not-necessarily-ISO8601 strings, and want them parsed in a more performant manner than apply.

So, format='mixed' could accomplish that. This would risk parsing ['12/01/2000', '13/01/2000'] in different formats, but because users have to opt-in to it by explicitly writing format='mixed', then it should be clear to them that they will need to accept whatever risks come from this. In any case, they can always use dayfirst.

Alternatives would be to add a new keyword argument, or un-deprecate infer_datetime_format and change its behaviour. However:

• bringing back infer_datetime_format but changing behaviour might be confusing
• mixed non-ISO formats in a large dataset strikes me a pretty unusual use-case, and not one that would justify extra keyword arguments

Docs and error messages should be updated to direct users towards this option

It's probably easiest to tackle this as part of #50939

[jorisvandenbossche]

Just adding for future reference as argument for allowing this: currently we point people towards using apply(pd.to_datetime) if you have mixed strings and want to parse element-wise. But the overhead of calling pd.to_datetime on scalars is really huge. A small example:

s = pd.Series(["12 Jan 2000", "2000-01-13"]*50_000)

# using pandas 1.5
In [2]: %timeit pd.to_datetime(s)
5.41 ms ± 56.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# using current main
In [2]: %time s.apply(pd.to_datetime)
CPU times: user 40 s, sys: 16.1 ms, total: 40 s
Wall time: 40.1 s

# with the open PR
In [3]: %timeit pd.to_datetime(s, format="mixed")
6.76 ms ± 423 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

[MarcoGorelli]

that's a great example, thanks!