Fix out of bounds datetime error in pandas

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:

📚 Data Science Programs By Skill Level

Introductory

Intermediate ⭐⭐⭐

Advanced ⭐⭐⭐⭐⭐

🔎 Find Data Science Programs 👨‍💻 111,889 already enrolled

Disclaimer: Data Science Parichay is reader supported. When you purchase a course through a link on this site, we may earn a small commission at no additional cost to you. Earned commissions help support this website and its team of writers.

the resulting dataframe with "Name", "Gender", and "Date Of Birth" columns

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:

dataframe after converting "Date Of Birth" column to datetime

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 –


Subscribe to our newsletter for more informative guides and tutorials.
We do not spam and you can opt out any time.


Author

  • Piyush Raj

    Piyush is a data professional passionate about using data to understand things better and make informed decisions. He has experience working as a Data Scientist in the consulting domain and holds an engineering degree from IIT Roorkee. His hobbies include watching cricket, reading, and working on side projects.

Scroll to Top