user5025141 user5025141 - 3 months ago 114
Python Question

Convert '%m/%d/%Y' string index into pandas datetime index

MY index is datetime string with format

'%m/%d/%Y' ('09/26/2007')


When I try to convert the index into datetime index using
pd.to_datetime
function
pd.to_datetime(df.index)
, I got error message
OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 1-01-01 00:00:00


It looks like pandas can't detect the right string format, how I can convert the index into datetime index?

Thanks

Answer

The the look of the error message, it appears you may have the string '1/1/0001' in your index. For example,

df = pd.DataFrame([1,2], index=['09/26/2007', '1/1/0001'])
pd.to_datetime(df.index)

raises

OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 1-01-01 00:00:00

This error arises because the DatetimeIndex uses an array of NumPy datetime64[ns]s which can not represent the date 0001-01-01. The datetime64[ns] dtype can only represent dates in the range [1678 AD, 2262 AD].

There is a pandas github issue discussing this limitation.

For now, the recommended solution is to use a PeriodIndex instead of a DatetimeIndex:

df = pd.DataFrame([1,2], index=['09/26/2007', '1/1/0001'])
df.index = pd.PeriodIndex(df.index, freq='D')

yields

            0
2007-09-26  1
1-01-01     2