Warm tip: This article is reproduced from serverfault.com, please click

Getting Out of bounds nanosecond timestamp error while using fillna in python?

发布于 2020-12-03 06:02:21

I am getting out of bounds nanosecond timestamp error while trying to pass default value to null value column

df3['check_date']=df3['eventDate0142'].fillna(df3['statusDateTi'].fillna(pd.to_datetime('9999-12-31')))

How can this be fixed.?

Questioner
LDF_VARUM_ELLAM_SHERIAAVUM
Viewed
0
jezrael 2020-12-03 14:57:06

Problem is in pandas maximal Timestamp is:

print (pd.Timestamp.max)
2262-04-11 23:47:16.854775807

So in pandas is raised error:

print (pd.to_datetime('9999-12-31'))
OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 9999-12-31 00:00:00

Sample:

df1 = pd.DataFrame({'eventDate0142': [np.nan,  np.nan, '2016-04-01'], 
                   'statusDateTi': [np.nan, '2019-01-01', '2017-04-01']})

df3 = df1.apply(pd.to_datetime)

print (df3)
  eventDate0142 statusDateTi  
0           NaT          NaT 
1           NaT   2019-01-01  
2    2016-04-01   2017-04-01  

Possible solution is use pure python, but then all pandas datetimelike methods failed - all data convert to dates:

from datetime import  date

print (date.fromisoformat('9999-12-31'))
9999-12-31


df3['check_date'] = (df3['eventDate0142'].dt.date
                        .fillna(df3['statusDateTi'].dt.date
                        .fillna(date.fromisoformat('9999-12-31'))))
print (df3)
  eventDate0142 statusDateTi  check_date
0           NaT          NaT  9999-12-31
1           NaT   2019-01-01  2019-01-01
2    2016-04-01   2017-04-01  2016-04-01

print (df3.dtypes)
eventDate0142    datetime64[ns]
statusDateTi     datetime64[ns]
check_date               object
dtype: object

Or convert timestamps to daily periods by Series.dt.to_period, and then use Periods for representing out of bounds spans:

print (pd.Period('9999-12-31'))
9999-12-31

df3['check_date'] = (df3['eventDate0142'].dt.to_period('d')
                        .fillna(df3['statusDateTi'].dt.to_period('d')
                        .fillna(pd.Period('9999-12-31'))))
print (df3)
  eventDate0142 statusDateTi  check_date
0           NaT          NaT  9999-12-31
1           NaT   2019-01-01  2019-01-01
2    2016-04-01   2017-04-01  2016-04-01

print (df3.dtypes)
eventDate0142    datetime64[ns]
statusDateTi     datetime64[ns]
check_date            period[D]
dtype: object

If assign back all columns:

df3['eventDate0142'] = df3['eventDate0142'].dt.to_period('d')
df3['statusDateTi'] = df3['statusDateTi'].dt.to_period('d')
df3['check_date'] = (df3['eventDate0142']
                        .fillna(df3['statusDateTi']
                        .fillna(pd.Period('9999-12-31'))))
print (df3)
  eventDate0142 statusDateTi  check_date
0           NaT          NaT  9999-12-31
1           NaT   2019-01-01  2019-01-01
2    2016-04-01   2017-04-01  2016-04-01

print (df3.dtypes)
eventDate0142    period[D]
statusDateTi     period[D]
check_date       period[D]
dtype: object