How to increment duplicate time axis

Question

I have a pd.DataFrame which has duplicate time in its index.

Example:

from datetime import datetime

import pandas as pd

time_index = [
    datetime(2017, 1, 1, 0, 4, 1, 80000), datetime(2017, 1, 1, 0, 4, 1, 80000), datetime(2017, 1, 1, 0, 4, 1, 80000),
    datetime(2017, 1, 1, 0, 4, 2), datetime(2017, 1, 1, 0, 4, 3)
]
df_i_have = pd.DataFrame([1, 2, 3, 4, 5], columns=['A'], index=time_index)

Notice that the first three rows have the exact same time.

My goal is to convert this data frame to one with unique time index. More specifically, I would like to add to each duplicated time on the dataframe index, one some small timedelta such that I end up with a dataframe like this one:

new_time_index = [
    datetime(2017, 1, 1, 0, 4, 1, 80000), datetime(2017, 1, 1, 0, 4, 1, 81000), datetime(2017, 1, 1, 0, 4, 1, 82000),
    datetime(2017, 1, 1, 0, 4, 2), datetime(2017, 1, 1, 0, 4, 3)
]
df_i_want = pd.DataFrame([1, 2, 3, 4, 5], columns=['A'], index=new_time_index)

Can anyone please help with an efficient way to do this?

score 3 · Accepted Answer · answered Apr 24 '21 at 15:36

You can groupby and cumcount and then add milliseconds:

t = df_i_have.groupby(level=0).cumcount()
df_i_have.index += pd.to_timedelta(t,unit='ms')

print(df_i_have)

                         A
2017-01-01 00:04:01.080  1
2017-01-01 00:04:01.081  2
2017-01-01 00:04:01.082  3
2017-01-01 00:04:02.000  4
2017-01-01 00:04:03.000  5

How to increment duplicate time axis

1 Answers1