durbachit durbachit - 3 months ago 22
Python Question

Try Except block not working with datetime object?

I have a DataFrame with some datetime data in one column and whatever else in other columns. However, some of the data is messed up, e.g.:

11/11/2014 22:28 15.1
11/11/2014 22:29 16.1
11/11/2014 22:30 15.2
bollocks 10000
11/11/2014 22:32 15.4
:00
11/11/2014 22:34 15.3


I would like to get rid of the lines that are messed up. For now, I decided to just replace them with NaN values (but dropping them would also help, only it didn't work in the cycle so it's not an issue, in the next step I can just use
dropna()
). I'm doing this using
try()
, but the exception doesn't work. My code looks like this:

for line in df.ix[:,"DATETIME"]:
try:
line = datetime.datetime.strptime(line,"%d/%m/%Y %H:%M")
except ValueError:
line = 'NaN'
except TypeError:
line = 'NaN'


But in the end, I still get the
ValueError: time data '156004E00F455AA' does not match format '%d/%m/%Y %H:%M'
and the faulty lines are not replaced with
NaN
. What is wrong here?
(I also tried putting the errors on one line like this:
except (ValueError, TypeError):
and it didn't work either...)

Answer

pd.to_datetime can set to NaT ill-formed data while converting the column to datetime.

pd.to_datetime(df['DATETIME'], errors='coerce')

           DATETIME
0  11/11/2014 22:28
1  11/11/2014 22:29
2  11/11/2014 22:30
3               NaT
4  11/11/2014 22:32
5               NaT
6  11/11/2014 22:34