ValueError: offset must be a timedelta strictly between -timedelta(hours=24) and timedelta(hours=24)-CodePudding

I have a dataframe whose rows 34180 to 34221 look like this for column 'MYTIME'.

df_fl2['MYTIME'].iloc[34180:34221]

34180    2022-03-02 1900-10-22 23:00:00
34181    2022-03-02 1900-10-23 00:00:00
34182    2022-03-02 1900-10-23 01:00:00
34183    2022-03-02 1900-10-23 02:00:00
34184    2022-03-02 1900-10-23 03:00:00
34185    2022-03-02 1900-10-23 04:00:00
34186    2022-03-02 1900-10-23 05:00:00
34187    2022-03-02 1900-10-23 06:00:00
34188    2022-03-02 1900-10-23 07:00:00
34189    2022-03-02 1900-10-23 08:00:00
34190    2022-03-02 1900-10-23 09:00:00
34191    2022-03-02 1900-10-23 10:00:00
34192    2022-03-02 1900-10-23 11:00:00
34193    2022-03-02 1900-10-23 12:00:00
34194    2022-03-02 1900-10-23 13:00:00
34195    2022-03-02 1900-10-23 14:00:00
34196    2022-03-02 1900-10-23 15:00:00
34197    2022-03-02 1900-10-23 16:00:00
34198    2022-03-02 1900-10-23 17:00:00
34199    2022-03-02 1900-10-23 18:00:00
34200    2022-03-02 1900-10-23 19:00:00
34201    2022-03-02 1900-10-23 20:00:00
34202    2022-03-02 1900-10-23 21:00:00
34203    2022-03-02 1900-10-23 22:00:00
34204    2022-03-02 1900-10-23 23:00:00
34205    2022-03-02 1900-10-24 00:00:00
34206    2022-03-02 1900-10-24 01:00:00
34207    2022-03-02 1900-10-24 02:00:00
34208    2022-03-02 1900-10-24 03:00:00
34209    2022-03-02 1900-10-24 04:00:00
34210    2022-03-02 1900-10-24 05:00:00
34211    2022-03-02 1900-10-24 06:00:00
34212    2022-03-02 1900-10-24 07:00:00
34213    2022-03-02 1900-10-24 08:00:00
34214    2022-03-02 1900-10-24 09:00:00
34215    2022-03-02 1900-10-24 10:00:00
34216    2022-03-02 1900-10-24 11:00:00
34217    2022-03-02 1900-10-24 12:00:00
34218    2022-03-02 1900-10-24 13:00:00
34219    2022-03-02 1900-10-24 14:00:00
34220    2022-03-02 1900-10-24 15:00:00
Name: MYTIME, dtype: object

However, when trying to force a conversion to datetime.

pd.to_datetime(df_fl2['MYTIME'].iloc[34180:34221], errors='coerce')

This error shows:

ValueError: offset must be a timedelta strictly between -timedelta(hours=24) and timedelta(hours=24).

How could I ignore all the errors raised in the conversion? I thought errors='coerce' did always the trick.

CodePudding user response：

strict parsing

Provide the expected format, everything that doesn't match will become NaT:

pd.to_datetime(df['MYTIME'], format='%Y-%m-%d %H:%M:%S', errors='coerce')

conversion

Assuming it is a mistake to have the 1900-... dates in the middle of the string, you could remove them:

pd.to_datetime(df['MYTIME'].str.replace(r' \S  ', ' ', regex=True),
               errors='coerce')

output:

34180   2022-03-02 23:00:00
34181   2022-03-02 00:00:00
34182   2022-03-02 01:00:00
...
34219   2022-03-02 14:00:00
34220   2022-03-02 15:00:00
Name: MYTIME, dtype: datetime64[ns]

If you rather want to remove the 2022 dates:

pd.to_datetime(df['MYTIME'].str.replace('^\S  ', ' ', regex=True),
               errors='coerce')