How to update type of index in a data frame

Question

I have a data frame df which its index structure likes the following:

>> df.index.values
array(['2017-12-19T07:00:04.753422000', '2017-12-19T07:00:09.628389000',
       '2017-12-19T07:00:12.065880000', ...,
       '2018-01-22T15:29:52.462586000', '2018-01-22T15:29:54.900103000',
       '2018-01-22T15:29:56.728234000'], dtype='datetime64[ns]')

As you can see dtype of the index is datetime64[ns]. How can I update its dtype to datetime64[us]? (this post did not work for me).

Indeed the main problem comes from calling this function df.to_sql('table_name', engine) to insert df into a table in postgre. And I've gotten the following errors:

error: Cannot cast DatetimeIndex to dtype datetime64[us]

And I can't solve the problem using these posts (1 and 2), as they did not help on index dtype.

Answer 1

You can convert DatetimeIndex only separately, if assign back pandas floor it and convert to default ns:

idx = pd.DatetimeIndex(['2017-12-19T07:00:04.753422000', '2017-12-19T07:00:09.628389000',
           '2017-12-19T07:00:12.065880000', 
           '2018-01-22T15:29:52.462586000', '2018-01-22T15:29:54.900103000',
           '2018-01-22T15:29:56.728234000'])
df = pd.DataFrame({'a':range(6)}, index=idx)
print (df)
                            a
2017-12-19 07:00:04.753422  0
2017-12-19 07:00:09.628389  1
2017-12-19 07:00:12.065880  2
2018-01-22 15:29:52.462586  3
2018-01-22 15:29:54.900103  4
2018-01-22 15:29:56.728234  5

idx = df.index.values.astype('datetime64[us]')
print (idx.dtype)
datetime64[us]

df.index = df.index.values.astype('datetime64[us]')

print (df.index.dtype)
datetime64[ns]

If use some another value - e.g. to ms, then floor is seen better:

df.index = df.index.values.astype('datetime64[ms]')
print (df)
                         a
2017-12-19 07:00:04.753  0
2017-12-19 07:00:09.628  1
2017-12-19 07:00:12.065  2
2018-01-22 15:29:52.462  3
2018-01-22 15:29:54.900  4
2018-01-22 15:29:56.728  5