How to convert date format when there are missing/NaT values in the column

Question

I have couple of date columns, I want to convert them to month/day/year format. Let's say test is one of the date columns - below code works.

dfq['test1'] = dfq['test1'].apply(lambda x: x.strftime('%m/%d/%Y'))

But when there are missing value in the column as 'NaT' it shows error ValueError: NaTType does not support strftime . I have created a sample data set and intentionally kept one missing value as ' ' . In that case also it shows error.

I want to keep the missing or NaT values, so can't remove them. Is there any other way around ?

Another question, if I want to convert all my date columns (say test1, test, test3) at the same time, - is there a way to do it while using lambda/strftime ?

Answer 1

You should use pd.Series.dt.strftime, which handles NaT gracefully:

import pandas as pd

s = pd.Series(['2018-01-01', 'hello'])

s = pd.to_datetime(s, errors='coerce')

# 0   2018-01-01
# 1          NaT
# dtype: datetime64[ns]

s = s.dt.strftime('%m/%d/%Y')

print(s)

# 0    01/01/2018
# 1           NaT
# dtype: object

---

For your second question, I do not believe datetime to str conversion can be vectorised. You can easily do this:

for col in ['col1', 'col2', 'col3']:
    df[col] = df[col].dt.strftime('%m/%d/%Y')

Or better:

for col in df.select_dtypes(include=['datetime']):
    df[col] = df[col].dt.strftime('%m/%d/%Y')