BUG: tz_localize(ambiguous="infer") breaks on DST with missing data near DST change.

[joshdunnlime]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df = pd.concat([
    pd.DataFrame({'timestamp': pd.date_range('2021-10-30 22:00', '2021-10-31 01:30', freq='30T')}),
    pd.DataFrame({'timestamp': pd.date_range('2021-10-31 10:00', '2021-11-01', freq='30T')}),
]).reset_index(drop=True)

df.timestamp.dt.tz_localize(
    "Europe/London",
    ambiguous='infer',
)

## Raise AmbiguousTimeError: 2021-10-31 01:00:00

Issue Description

We can see in the example above that there is missing data between 2021-10-31 01:30 and 2021-10-31 10:00 - essentually, dropping the 2nd 2021-10-31 01:30 and 2021-10-31 01:30 of the DST day. When this happens pandas assumes the time is ambiguous, which in some respects it is, but as a human I can infer that the data stopped at this point and therefore the 2021-10-31 01:30 and 2021-10-31 01:30 timestamps are almost certainly the first instances of the day.

This kind of error, in this place (near a clock change) is going to be somewhat common but not quite common enough to be able to find it easily. This is because IRL metering systems and physical logging systems are most likely break around edge cases precisely like a clock change. (This above reproducable example is using the exact dates for the data I have. I assume that someone had to manually restart the device at 9 am when they started work the day of the clock change).

Expected Behavior

Something similar to the following but with the missing datetimes infered correctly and not with NaTs. Otherwise, something that returns NaTs would be great because currently it just breaks and can REALLY mess up data pipelines and be extrememly difficult to debug!

def tz_localize_with_missing(S, nonexistent='shift_forward'):
    """Missing data around a DST change can break `infer` in this case return `NaT`."""
    try:
        return (
            S.dt.tz_localize(
                "Europe/London",
                ambiguous='infer',
                nonexistent=nonexistent,
            )
            .dt.tz_convert("UTC")
        )
    except AmbiguousTimeError:
        return (
            S.
            dt.tz_localize(
                "Europe/London",
                ambiguous='NaT',
                nonexistent=nonexistent,
            )
            .dt.tz_convert("UTC")
        )

df.timestamp.transform(tz_localize_with_missing)

Possibly and option that allows you to decide how it fails:

S.dt.tz_localize(
    "Europe/London",
    ambiguous='infer',
    nonexistent=nonexistent,
    ambiguous_error='NaT', # raises, default_first, default_last, removes... etc etc
)

Installed Versions

INSTALLED VERSIONS

commit : 4bfe3d0
python : 3.8.12.final.0
python-bits : 64
OS : Darwin
OS-release : 20.6.0
Version : Darwin Kernel Version 20.6.0: Tue Oct 12 18:33:42 PDT 2021; root:xnu-7195.141.8~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 1.4.2
numpy : 1.21.4
pytz : 2021.3
dateutil : 2.8.2
pip : 21.2.4
setuptools : 58.0.4
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.9.2
jinja2 : 3.0.2
IPython : 7.26.0
pandas_datareader: None
bs4 : None
bottleneck : 1.3.4
brotli :
fastparquet : None
fsspec : 2021.10.1
gcsfs : None
markupsafe : 2.0.1
matplotlib : 3.4.3
numba : 0.55.1
numexpr : 2.8.1
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : 7.0.0
pyreadstat : None
pyxlsb : None
s3fs : 0.4.2
scipy : 1.7.3
snappy : None
sqlalchemy : 1.4.28
tables : None
tabulate : None
xarray : 0.21.1
xlrd : None
xlwt : None
zstandard : None

[joshdunnlime]

Temporary work around/hack:

def tz_localize_with_missing(S, nonexistent='shift_forward'):
    """Missing data around a DST change can break `infer` in this case return `NaT`."""
    try:
        return (
            S.dt.tz_localize(
                "Europe/London",
                ambiguous='infer',
                nonexistent=nonexistent,
            )
            .dt.tz_convert("UTC")
        )
    except AmbiguousTimeError:
        return (
            S.
            dt.tz_localize(
                "Europe/London",
                ambiguous='NaT',
                nonexistent=nonexistent,
            )
            .dt.tz_convert("UTC")
        )

df.timestamp.transform(tz_localize_with_missing)

#or for multiple timeseries examples:
df.groupby('U_ID')['timestamp'].transform(tz_localize_with_missing)

# where U_ID is the unique identifier column in your df.

This allowed me to get around the raising of the errors and simply return NaT at the first timestamp.

[simonjayhawkins]

Thanks @joshdunnlime for the report.

The current implementation attempts to infer and raises otherwise.

also sort of related #45905. (limitation of inference logic)

I'll also label this issue as an enhancement rather than a bug.

I guess what could be useful for data pipelines is another option for a "infer" that doesn't raise.

Contributions and PRs welcome.

[joshdunnlime]

I guess what could be useful for data pipelines is another option for a "infer" that doesn't raise.

This is exactly what I was thinking. Maybe a 3rd kwarg (ewwww) called something like backup_error_handling with options of 'raise', 'NaT' and maybe 'warn'?

[simonjayhawkins]

rather than adding additional kwargs, and since the current 'infer' is effectively 'infer' and 'raise', could instead maybe just allow, say, 'infer_safe' as an additional option to cover the 'infer' and 'NaT'.

not sure about 'warn' as could also be considered as a "top-level" option rather than just for the inference failure case.

[adamLutPhi]

`
def tz_localize_with_missing(S, nonexistent='shift_forward'):

try:
    return (
        #S.dt.
        S.tz_localize(
            "Europe/London",
            ambiguous='infer',
            nonexistent=nonexistent,
        )
     
    )
except AmbiguousTimeError: 
    return (
        S.
        dt.tz_localize(
            "Europe/London",
           # ambiguous='NaT', #<---- take this one [Reason: Does not exist, anymore]
          nonexistent=nonexistent,
        )
        .dt.tz_convert("UTC")
    )

`

Demo

`
rng_hourly2 = pd.DatetimeIndex( ['11/06/2022 00:00', '11/06/2022 1:00'] )

print( tz_localize_with_missing(rng_hourly2) )
`

Note: the ambiguous parameter has been already removed.

But, If S is different from the datatype assigned above, then please do not mind this implementation, if that doesn't seem to help much.