Fix Pandas – ParseError: year 0 is out of range 0000-00-00 Error

In this tutorial, we will look at how to fix the ParseError: year 0 is out of range 0000-00-00 error when using the pandas.to_datetime() function with the help of an example.

Why does this occur when using pandas.to_datetime()?

We use the pandas.to_datetime() function to convert a value or column to datetime type. The “pandas ParserError: year 0 is out of range: 0000-00-00” error occurs when trying to parse an invalid date using the pandas.to_datetime() function.

Let’s reproduce this error.

import pandas as pd

# create a dataframe
df = pd.DataFrame({
    "Name": ["John", "Akbar", "Steve", "Emma", "Rohan"],
    "Gender": ["Male", "Male", "Male", "Female", "Male"],
    "Date Of Birth": ["0000-00-00", "1997-12-35", "2002-01-13", "1890-01-24", "1971-12-25"]
})

# display the dataframe
df

Output:

Here, we created a dataframe with the columns “Name”, “Gender”, and “Date Of Birth”. Notice that the “Date Of Birth” column contains some dates that are logically incorrect – “0000-00-00”, this date has a logically incorrect value for the month and the day, the date “1997-12-35” has an incorrect date value (a date in December can only be from 1 to 31).

Let’s check the data type of the columns in the above dataframe.

# get information about the dataframe's columns
df.info()

Output:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 3 columns):
 #   Column         Non-Null Count  Dtype 
---  ------         --------------  ----- 
 0   Name           5 non-null      object
 1   Gender         5 non-null      object
 2   Date Of Birth  5 non-null      object
dtypes: object(3)
memory usage: 248.0+ bytes

Now, let’s try to convert the “Date of Birth” column to datetime using the pandas.to_datetime() function.

# convert column to datetime
df["Date Of Birth"] = pd.to_datetime(df["Date Of Birth"])
# display the dataframe
df

Output:

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:649, in parser.parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    648 try:
--> 649     ret = self._build_naive(res, default)
    650 except ValueError as e:

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:1235, in parser._build_naive(self, res, default)
   1233         repl['day'] = monthrange(cyear, cmonth)[1]
-> 1235 naive = default.replace(**repl)
   1237 if res.weekday is not None and not res.day:

ValueError: year 0 is out of range

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

ParserError                               Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslib.pyx:534, in pandas._libs.tslib.array_to_datetime()

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslibs/parsing.pyx:257, in pandas._libs.tslibs.parsing.parse_datetime_string()

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:1368, in parse(timestr, parserinfo, **kwargs)
   1367 else:
-> 1368     return DEFAULTPARSER.parse(timestr, **kwargs)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:651, in parser.parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    650 except ValueError as e:
--> 651     six.raise_from(ParserError(str(e) + ": %s", timestr), e)
    653 if not ignoretz:

File <string>:3, in raise_from(value, from_value)

ParserError: year 0 is out of range: 0000-00-00

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslib.pyx:545, in pandas._libs.tslib.array_to_datetime()

TypeError: invalid string coercion to datetime

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:649, in parser.parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    648 try:
--> 649     ret = self._build_naive(res, default)
    650 except ValueError as e:

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:1235, in parser._build_naive(self, res, default)
   1233         repl['day'] = monthrange(cyear, cmonth)[1]
-> 1235 naive = default.replace(**repl)
   1237 if res.weekday is not None and not res.day:

ValueError: year 0 is out of range

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

ParserError                               Traceback (most recent call last)

Cell In[55], line 2
      1 # convert column to datetime
----> 2 df["Date Of Birth"] = pd.to_datetime(df["Date Of Birth"])
      3 # display the dataframe
      4 df

...

ParserError: year 0 is out of range: 0000-00-00

We get the ParserError: year 0 is out of range: 0000-00-00

The PareseError here comes from the date “0000-00-00”. We cannot have a date with the year 0 in Pandas (although it’s logically correct year value).

What if we change the year to a valid value but keep the month and the day values as invalid for the date “0000-00-00” and then try to convert the column to datetime using the pandas.to_datetime() function?

# create a dataframe
df = pd.DataFrame({
    "Name": ["John", "Akbar", "Steve", "Emma", "Rohan"],
    "Gender": ["Male", "Male", "Male", "Female", "Male"],
    "Date Of Birth": ["0001-00-00", "1997-12-35", "2002-01-13", "1890-01-24", "1971-12-25"]
})

# convert column to datetime
df["Date Of Birth"] = pd.to_datetime(df["Date Of Birth"])
# display the dataframe
df

Output:

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:649, in parser.parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    648 try:
--> 649     ret = self._build_naive(res, default)
    650 except ValueError as e:

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:1235, in parser._build_naive(self, res, default)
   1233         repl['day'] = monthrange(cyear, cmonth)[1]
-> 1235 naive = default.replace(**repl)
   1237 if res.weekday is not None and not res.day:

ValueError: month must be in 1..12

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

ParserError                               Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslib.pyx:534, in pandas._libs.tslib.array_to_datetime()

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslibs/parsing.pyx:257, in pandas._libs.tslibs.parsing.parse_datetime_string()

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:1368, in parse(timestr, parserinfo, **kwargs)
   1367 else:
-> 1368     return DEFAULTPARSER.parse(timestr, **kwargs)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:651, in parser.parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    650 except ValueError as e:
--> 651     six.raise_from(ParserError(str(e) + ": %s", timestr), e)
    653 if not ignoretz:

File <string>:3, in raise_from(value, from_value)

ParserError: month must be in 1..12: 0001-00-00

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslib.pyx:545, in pandas._libs.tslib.array_to_datetime()

TypeError: invalid string coercion to datetime

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:649, in parser.parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    648 try:
--> 649     ret = self._build_naive(res, default)
    650 except ValueError as e:

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/dateutil/parser/_parser.py:1235, in parser._build_naive(self, res, default)
   1233         repl['day'] = monthrange(cyear, cmonth)[1]
-> 1235 naive = default.replace(**repl)
   1237 if res.weekday is not None and not res.day:

ValueError: month must be in 1..12

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

ParserError                               Traceback (most recent call last)

Cell In[56], line 9
      2 df = pd.DataFrame({
      3     "Name": ["John", "Akbar", "Steve", "Emma", "Rohan"],
      4     "Gender": ["Male", "Male", "Male", "Female", "Male"],
      5     "Date Of Birth": ["0001-00-00", "1997-12-35", "2002-01-13", "1890-01-24", "1971-12-25"]
      6 })
      8 # convert column to datetime
----> 9 df["Date Of Birth"] = pd.to_datetime(df["Date Of Birth"])
     10 # display the dataframe
     11 df

...

ParserError: month must be in 1..12: 0001-00-00

We again get a ParseError but notice that the error message here is different, “month must be in 1..12: 0001-00-00”, we get an error because the month value is not in the range of 1 to 12 making it an invalid date.

We would get a similar error for the date “1997-12-35” which has an invalid date.

How to fix ParserError: year 0 is out of range: 0000-00-00 in the pandas.to_datetime() function?

One way to fix the above errors is to set the errors parameter to 'coerce' when using the pandas.to_datetime() function. This will convert invalid dates to NaT (Not a Time) values instead of raising an error.

Let’s recreate the original dataframe.

# create the original dataframe
df = pd.DataFrame({
    "Name": ["John", "Akbar", "Steve", "Emma", "Rohan"],
    "Gender": ["Male", "Male", "Male", "Female", "Male"],
    "Date Of Birth": ["0000-00-00", "1997-12-35", "2002-01-13", "1890-01-24", "1971-12-25"]
})

# display the dataframe
df

Output:

Let’s now use the pandas.to_datetime() function with the errors='coerce' parameter.

# convert column to datetime
df["Date Of Birth"] = pd.to_datetime(df["Date Of Birth"], errors="coerce")
# display the dataframe
df

Output:

Here, we didn’t get an error. Notice that all the dates in the dataframe that were invalid have been replaced with NaT (which represents “Not a Time”, it’s like the NaN equivalent for timestamp data).

Additionally note that the data type resulting from the pandas.to_datetime() function can only handle values inside a given range. If you try to parse date values outside this range, you’ll get a ParseError if you do not use the errors='coerce' argument (in which case it will replace such dates with NaT). See this tutorial for more details.

You might also be interested in –

• Check if a DataFrame column is of datetime dtype in Pandas
• Pandas – Get only date from datetime
• Pandas – Convert String Column to datetime