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

user_10829044 0
Viewed 97

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']})

pandas dataframe datetime python
2 Answers

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
user_29010020

The Error is caused by using something like 24:00. Testing with (note the change in the second entry to 24:00):

df = pd.DataFrame({'date': ['6/3/2012 8:57','11/1/2012 24:00','20/1/2012 2:48','15/1/2012 0:00',np.nan]})

We receive the same error as in your big dataframe. Going trough with a for loop maybe a bit slower but this way we can catch the errors.

wrong_datetime_list = []
for index, value in enumerate(df['date']):
    try:
        df.loc[index,'date']= pd.to_datetime(df.loc[index,'date'])
    except:
        wrong_datetime_list.append((index, value))
user_120516590
Related