Why do I get 'ValueError: NaTType does not support strftime' even though it's not empty?

Question

Forgive me if I am wrong, but AFAIK ValueError: NaTType does not support strftime will occur when the data is null or empty. But my data isn't.

Let's say I have this dataframe.

df = pd.DataFrame({'personnel_number': ['123', '345', '567', '789', '000', '4444'],
                 'expiry_date': ['2020-12-07', '2099-12-04', '2019-08-30', '2022-03-19', '2020-09-06', '9999-12-31']})

And I want to convert it to date type format with the following code.

for exp_date in df['expiry_date']:
    date = pd.to_datetime(exp_date, errors='coerce').strftime('%Y-%m-%d')
    print(date)

But I somehow always get this error when the looping reaches the last data (the '9999-12-31' date one).

ValueError: NaTType does not support strftime

I figured the year 9999 doesn't sound like it's making sense, but that's the data I have, I can't change it. So, what can I do?

Answer 1

I think loop here is not necessary, use pd.to_datetime with column and then Series.dt.strftime:

df['expiry_date'] = pd.to_datetime(df['expiry_date'], errors='coerce').dt.strftime('%Y-%m-%d')
print(df)
  personnel_number expiry_date
0              123  2020-12-07
1              345  2099-12-04
2              567  2019-08-30
3              789  2022-03-19
4              000  2020-09-06
5             4444         NaT

Reason for error is parameter errors='coerce' create missing values NaT for 'wrong' datetimes, because here are out of limitation, timestamp limitations:

In [92]: pd.Timestamp.min
Out[92]: Timestamp('1677-09-21 00:12:43.145225')

In [93]: pd.Timestamp.max
Out[93]: Timestamp('2262-04-11 23:47:16.854775807')