How to pop out the error-causing date records using pandas?

I have a dataframe like as shown below

df = pd.DataFrame({'date': ['45:42.7','11/1/2012 0:00','20/1/2012 2:48','15/1/2012 0:00',np.nan]})

I would like to convert the date column to type datetime.

So, I tried the below

df['date'] = pd.to_datetime(df['date'])

I get the below error

ValueError: hour must be in 0..23

As we can see from the sample dataframe NA is not causing this error but the 1st record which is 45:42.7.

While the raw excel file displays this date value 45:42.7 when I open the file but when I double click the cell, it displays correctly the actual date.

How can I filter the dataframe to pop-out the first record as output (which is the error causing record)?

I expect my output to be like shown in sample dataframe below

df = pd.DataFrame({‘error_date’: [’45:42.7′]})

Answer

First if need to see wrong values convert to datetimes and filter missing values like:

print(df[pd.to_datetime(df['date'], format='%d/%m/%Y %H:%M',errors='coerce').isna()])

I think None is no problem, you need specify column format and for not matched rows are generated NaNs if add errors='coerce' parameter:

df['date'] = pd.to_datetime(df['date'], format='%d/%m/%Y %H:%M',errors='coerce')
print (df)
                 date
0 2012-03-06 08:57:00
1 2012-01-11 00:00:00
2 2012-01-20 02:48:00
3 2012-01-15 00:00:00
4                 NaT