pandas raises ValueError on DatetimeIndex Conversion

Question

I am converting all ISO-8601 formatted values into Unix Values. For some inexplicable reason this line

a_col = pd.DatetimeIndex(a_col).astype(np.int64)/10**6

raises the error

ValueError: Unable to convert 0 2001-06-29

... (Abbreviated Output of Column

Name: DateCol, dtype: datetime64[ns] to datetime dtype

This is very odd because I've guaranteed that each value is in datetime.datetime format as you can see here:

if a_col.dtypes is (np.dtype('object') or np.dtype('O')):
      a_col = a_col.apply(lambda x: x if isinstance(x, datetime.datetime) else epoch)
a_col = pd.DatetimeIndex(a_col).astype(np.int64)/10**6

Epoch is datetime.datetime.

When I check the dtypes of the column that gives me an error it's "object), exactly what I'm checking for. Is there something I'm missing?

Answer 1

Assuming that your time zone is US/Eastern (based on your dataset) and that your DataFrame is named df, please try the following:

import datetime as dt
from time import mktime
import pytz

df['Job Start Date'] = \
    df['Job Start Date'].apply(lambda x: mktime(pytz.timezone('US/Eastern').localize(x)
                                         .astimezone(pytz.UTC).timetuple()))

>>> df['Job Start Date'].head()
0     993816000
1    1080824400
2    1052913600
3    1080824400
4    1075467600
Name: Job Start Date, dtype: float64

You first need to make your 'naive' datetime objects timezone aware (to US/Eastern) and then convert them to UTC. Finally, pass your new UTC aware datetime object as a timetable to the mtkime function from the time module.