How to remove microseconds from DateTimeIndex in dataframe in Python?

Question

I want to remove the microseconds from index.

My index is like this:

DatetimeIndex(['2003-11-20 13:07:40.895000+00:00',
           '2003-11-20 13:16:13.039000+00:00',
           '2003-11-20 13:24:44.868000+00:00',
           '2003-11-20 13:33:17.013000+00:00',
           '2003-11-20 13:41:49.158000+00:00',
           '2003-11-20 13:50:20.987000+00:00',
           '2003-11-20 13:58:53.132000+00:00',
           '2003-11-20 14:07:24.961000+00:00',
           '2003-11-20 14:15:57.106000+00:00',
           '2003-11-20 14:24:28.935000+00:00',
           ...
           '2003-12-04 19:28:56.025000+00:00',
           '2003-12-04 19:37:27.854000+00:00',
           '2003-12-04 19:45:59.999000+00:00',
           '2003-12-04 19:54:32.143000+00:00',
           '2003-12-04 20:03:03.972000+00:00',
           '2003-12-04 20:11:36.117000+00:00',
           '2003-12-04 20:20:07.946000+00:00',
           '2003-12-04 20:28:40.091000+00:00',
           '2003-12-04 20:37:11.920000+00:00',
           '2003-12-04 20:45:44.065000+00:00'],
          dtype='datetime64[ns, UTC]'

And I want to remove the microseconds in order to have something like this only: '2003-12-04 20:45:44' I do not want to convert it to string, as it is needed to remain datetime because it is the index of the dataframe. I have been searching for this, but I only found this, which does not work:

df.index.replace(microsecond=0, inplace = True)

Can you help me please?

That +00:00 is timezone information, you don't need that then try: `didx.floor('S').tz_localize(None)` — Scott Boston, Aug 07 '20 at 16:57
also `idx.floor('S')'.tz_convert(None)`... so the difference is when you want to change timezones, `tz_convert` will change the time to what it would be in the new tz, `tz_localize` keeps times the same and just stamps the new tz — RichieV, Aug 07 '20 at 17:04
possibly a duplicate of [convert pandas tz aware to naive](https://stackoverflow.com/q/16628819/6692898) — RichieV, Aug 07 '20 at 17:11
you can keep the microseconds for later use with `idx.microsecond` which will return a numpy array of integers... [pandas docs](https://pandas.pydata.org/pandas-docs/stable/reference/indexing.html#datetimeindex) are a great source — RichieV, Aug 07 '20 at 17:21
@Scott Boston can you write your answer in a comment in order to be better visualized? — Ariadne R., Aug 07 '20 at 20:13

score 3 · Accepted Answer · answered Aug 08 '20 at 01:45

Given a pd.DateTimeIndex with timezone information and millisecond data like this:

didx = pd.DatetimeIndex(['2003-11-20 13:07:40.895000+00:00',
           '2003-11-20 13:16:13.039000+00:00',
           '2003-11-20 13:24:44.868000+00:00',
           '2003-11-20 13:33:17.013000+00:00',
           '2003-11-20 13:41:49.158000+00:00',
           '2003-11-20 13:50:20.987000+00:00',
           '2003-11-20 13:58:53.132000+00:00',
           '2003-11-20 14:07:24.961000+00:00',
           '2003-11-20 14:15:57.106000+00:00',
           '2003-11-20 14:24:28.935000+00:00',
           '2003-12-04 19:28:56.025000+00:00',
           '2003-12-04 19:37:27.854000+00:00',
           '2003-12-04 19:45:59.999000+00:00',
           '2003-12-04 19:54:32.143000+00:00',
           '2003-12-04 20:03:03.972000+00:00',
           '2003-12-04 20:11:36.117000+00:00',
           '2003-12-04 20:20:07.946000+00:00',
           '2003-12-04 20:28:40.091000+00:00',
           '2003-12-04 20:37:11.920000+00:00',
           '2003-12-04 20:45:44.065000+00:00'],
          dtype='datetime64[ns, UTC]')

You can use pd.DateTimeIndex.floor and tz_localize(None), to truncate timestamps to seconds and remove the timezone information.

didx.floor('S').tz_localize(None)

Output:

DatetimeIndex(['2003-11-20 13:07:40', '2003-11-20 13:16:13',
               '2003-11-20 13:24:44', '2003-11-20 13:33:17',
               '2003-11-20 13:41:49', '2003-11-20 13:50:20',
               '2003-11-20 13:58:53', '2003-11-20 14:07:24',
               '2003-11-20 14:15:57', '2003-11-20 14:24:28',
               '2003-12-04 19:28:56', '2003-12-04 19:37:27',
               '2003-12-04 19:45:59', '2003-12-04 19:54:32',
               '2003-12-04 20:03:03', '2003-12-04 20:11:36',
               '2003-12-04 20:20:07', '2003-12-04 20:28:40',
               '2003-12-04 20:37:11', '2003-12-04 20:45:44'],
              dtype='datetime64[ns]', freq=None)

score 0 · Answer 2 · edited Aug 07 '20 at 17:22

0

You should be able to use .strftime('%Y-%m-%d %H:%M:%S') on each.

edited Aug 07 '20 at 17:22

Ivan V.

7,593
2
36
53

answered Aug 07 '20 at 16:42

Joseph Hissong

3
4

1

Yes, it worked, but it transformed the index into String. – Ariadne R. Aug 07 '20 at 16:43
Can you try https://docs.python.org/3.4/library/datetime.html#datetime.datetime.strptime – Joseph Hissong Aug 07 '20 at 16:47
This would allow you to pass a datetime object, format as needed, and return as a datetime object. so datetime.strptime( {date_time object}, '%Y-%m-%d %H:%M:%S') – Joseph Hissong Aug 07 '20 at 16:51
1

it is great to have several options... this is not as efficient as Scott's comment, yet it gets the job done – RichieV Aug 07 '20 at 17:09

How to remove microseconds from DateTimeIndex in dataframe in Python?

2 Answers2