Pandas Converting to Datetime, dateutilparser error [duplicate]-CodePudding

I am trying to combine two columns; one with date and other with time into a single datetime column.

df: 
      date         time
----  -----------  ------
   0  08 Sep 2021  00:00
   1  08 Sep 2021  00:00
   2  08 Sep 2021  00:00
   3  08 Sep 2021  00:05
   4  08 Sep 2021  00:30

The way i am doing it is:

df['datetime'] = df['date']   ' '   df['time']

df: 

      date         time    datetime
----  -----------  ------  -----------------
   0  08 Sep 2021  00:00   08 Sep 2021 00:00
   1  08 Sep 2021  00:00   08 Sep 2021 00:00
   2  08 Sep 2021  00:00   08 Sep 2021 00:00
   3  08 Sep 2021  00:05   08 Sep 2021 00:05
   4  08 Sep 2021  00:30   08 Sep 2021 00:30

I tried:

df['datetime'] = pd.to_datetime(df['datetime'])

And I also tried

df.loc[:, 'datetime'] = pd.to_datetime(df.date.astype(str)   ' '   df.time.astype(str))

However, I am getting the same error:

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 649, in parse
    ret = self._build_naive(res, default)
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 1235, in _build_naive
    naive = default.replace(**repl)
ValueError: hour must be in 0..23

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "pandas\_libs\tslib.pyx", line 534, in pandas._libs.tslib.array_to_datetime
  File "pandas\_libs\tslibs\parsing.pyx", line 257, in pandas._libs.tslibs.parsing.parse_datetime_string
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 1368, in parse
    return DEFAULTPARSER.parse(timestr, **kwargs)
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 651, in parse
    six.raise_from(ParserError(str(e)   ": %s", timestr), e)
  File "<string>", line 3, in raise_from
dateutil.parser._parser.ParserError: hour must be in 0..23: 09 Sep 2021 84'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "pandas\_libs\tslib.pyx", line 545, in pandas._libs.tslib.array_to_datetime
TypeError: invalid string coercion to datetime

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 649, in parse
    ret = self._build_naive(res, default)
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 1235, in _build_naive
    naive = default.replace(**repl)
ValueError: hour must be in 0..23

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\harsh\AppData\Roaming\JetBrains\PyCharmCE2021.2\scratches\scratch_17.py", line 40, in <module>
    df.loc[:, 'datetime'] = pd.to_datetime(df.date.astype(str)   ' '   df.time.astype(str))
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\tools\datetimes.py", line 883, in to_datetime
    cache_array = _maybe_cache(arg, format, cache, convert_listlike)
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\tools\datetimes.py", line 195, in _maybe_cache
    cache_dates = convert_listlike(unique_dates, format)
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\tools\datetimes.py", line 401, in _convert_listlike_datetimes
    result, tz_parsed = objects_to_datetime64ns(
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\arrays\datetimes.py", line 2193, in objects_to_datetime64ns
    raise err
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\arrays\datetimes.py", line 2175, in objects_to_datetime64ns
    result, tz_parsed = tslib.array_to_datetime(
  File "pandas\_libs\tslib.pyx", line 379, in pandas._libs.tslib.array_to_datetime
  File "pandas\_libs\tslib.pyx", line 611, in pandas._libs.tslib.array_to_datetime
  File "pandas\_libs\tslib.pyx", line 749, in pandas._libs.tslib._array_to_datetime_object
  File "pandas\_libs\tslib.pyx", line 740, in pandas._libs.tslib._array_to_datetime_object
  File "pandas\_libs\tslibs\parsing.pyx", line 257, in pandas._libs.tslibs.parsing.parse_datetime_string
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 1368, in parse
    return DEFAULTPARSER.parse(timestr, **kwargs)
  File "C:\Program Files\Python39\lib\site-packages\dateutil\parser\_parser.py", line 651, in parse
    six.raise_from(ParserError(str(e)   ": %s", timestr), e)
  File "<string>", line 3, in raise_from
dateutil.parser._parser.ParserError: hour must be in 0..23: 09 Sep 2021 84'

Process finished with exit code 1

How can I correctly combine the two columns into a single datetime value?

CodePudding user response：

There are some bad values in time column like 84, so use errors='coerce' for convert them to NaT.

df['datetime'] = pd.to_datetime(df['datetime'], errors='coerce')