Handle Perpetual Maturity Bonds with Maturity date of 31-12-9999 12:00:00 AM

Question

I have a number of records in a dataframe where the maturity date column is 31-12-9999 12:00:00 AM as the bonds never mature. This naturally raises the error:

Out of bounds nanosecond timestamp: 9999-12-31 00:00:00

I see the max date is:

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

I just wanted to clarify what the best approach to clean all date columns in the datframe and fix my bug? My code modelled off the docs:

df_Fix_Date = df_Date['maturity_date'].head(8)
display(df_Fix_Date)
display(df_Fix_Date.dtypes)

0    2020-08-15 00:00:00.000
1    2022-11-06 00:00:00.000
2    2019-03-15 00:00:00.000
3    2025-01-15 00:00:00.000
4    2035-05-29 00:00:00.000
5    2027-06-01 00:00:00.000
6    2021-04-01 00:00:00.000
7    2022-04-03 00:00:00.000
Name: maturity_date, dtype: object

def conv(x):
        return pd.Period(day = x%100, month = x//100 % 100, year = x // 10000, freq='D')

df_Fix_Date['maturity_date'] = pd.to_datetime(df_Fix_Date['maturity_date'])               # convert to datetype
df_Fix_Date['maturity_date'] = pd.PeriodIndex(df_Fix_Date['maturity_date'].apply(conv))   # fix error
display(df_Fix_Date)

Output:

KeyError: 'maturity_date'

Answer 1

There is problem you cannot convert to out of bounds datetimes.

One solution is replace 9999 to 2261:

df_Fix_Date['maturity_date'] = df_Fix_Date['maturity_date'].replace('^9999','2261',regex=True)
df_Fix_Date['maturity_date'] = pd.to_datetime(df_Fix_Date['maturity_date']) 
print (df_Fix_Date)
  maturity_date
0    2020-08-15
1    2022-11-06
2    2019-03-15
3    2025-01-15
4    2035-05-29
5    2027-06-01
6    2021-04-01
7    2261-04-03

Another solution is replace all dates with year higher as 2261 to 2261:

m = df_Fix_Date['maturity_date'].str[:4].astype(int) > 2261
df_Fix_Date['maturity_date'] = df_Fix_Date['maturity_date'].mask(m, '2261' + df_Fix_Date['maturity_date'].str[4:])
df_Fix_Date['maturity_date'] = pd.to_datetime(df_Fix_Date['maturity_date']) 
print (df_Fix_Date)
  maturity_date
0    2020-08-15
1    2022-11-06
2    2019-03-15
3    2025-01-15
4    2035-05-29
5    2027-06-01
6    2021-04-01
7    2261-04-03

---

Or replace problematic dates to NaTs by parameter errors='coerce':

df_Fix_Date['maturity_date'] = pd.to_datetime(df_Fix_Date['maturity_date'], errors='coerce') 
print (df_Fix_Date)
  maturity_date
0    2020-08-15
1    2022-11-06
2    2019-03-15
3    2025-01-15
4    2035-05-29
5    2027-06-01
6    2021-04-01
7           NaT