Fix Pandas – OutOfBoundsDatetime: Out of bounds nanosecond timestamp Error

In this tutorial, we will look at how to fix the OutOfBoundsDatetime: Out of bounds nanosecond timestamp error when using the pandas.to_datetime() function with the help of an example.

Why does the OutOfBoundsDatetime error occur when using pandas.to_datetime()?

We use the pandas.to_datetime() function to convert a value or column to the pandas datetime type. The “OutOfBoundsDatetime: Out of bounds nanosecond timestamp” error occurs when trying to parse a date using the pandas.to_datetime() function that is outside the range of acceptable dates for the pandas.Timestamp type.

Pandas uses its own datetime type pandas.Timestamp which is different from the default datetime type, datetime.datetime in Python. Now, the pandas.Timestamp has a defined range of values that it can take. Let’s see that range for ourselves.

import pandas as pd

# min value for a pandas.Timestamp value
print(pd.Timestamp.min)
# max value for a pandas.Timestamp value
print(pd.Timestamp.max)

Output:

1677-09-21 00:12:43.145224193
2262-04-11 23:47:16.854775807

You can see that we get the minimum and the maximum values which the pandas.Timestamp type can take.

Now, if you try to parse a date (using the pandas.to_datetime() function) that lies outside this range, we get the above ParseError.

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-01-25", "1542-10-15", "1997-08-21", "2002-01-13", "2300-01-24"]
})

# 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 lie outside the above-displayed range of values for the pandas.Timestamp type – “0000-01-25”, “1542-10-15”, “2300-01-24”.

Note that all the dates in the above column are logically valid dates (it’s just that they lie outside the range of possible dates for the pandas.Timestamp type).

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:

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

TypeError                                 Traceback (most recent call last)

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/core/arrays/datetimes.py:2192, in objects_to_datetime64ns(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)
   2191 try:
-> 2192     values, tz_parsed = conversion.datetime_to_datetime64(data.ravel("K"))
   2193     # If tzaware, these values represent unix timestamps, so we
   2194     #  return them as i8 to distinguish from wall times

File ~/miniforge3/envs/dsp/lib/python3.8/site-packages/pandas/_libs/tslibs/conversion.pyx:359, in pandas._libs.tslibs.conversion.datetime_to_datetime64()

TypeError: Unrecognized value type: <class 'str'>

During handling of the above exception, another exception occurred:

OutOfBoundsDatetime                       Traceback (most recent call last)

Cell In[4], 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

...

OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 0-01-25 00:00:00

Since our dataframe had other dates that are not in the valid range of dates for the pandas.Timestamp type, we get an OutOfBoundsDatetime: Out of bounds nanosecond timestamp error.

How to fix the OutOfBoundsDatetime: Out of bounds nanosecond timestamp error in Pandas?

One way to fix this error 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.

# 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 outside the range of dates for the pandas.Timestamp type have been replaced with NaT (which represents “Not a Time”, it’s like the NaN equivalent for timestamp data).

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