[Python] overlap in the datetime column of a pandas dataframe plus additional complication

the datetime index of a pandas dataframe have groups with the same datetime (they also have the same Id number). Plus additional problem as explained below

here are some stock market tick data

2025-02-14 00:00:01.192429,6143.25,2,32178,6143.25,6143.50,271611387,C,43,01,2,14
2025-02-14 00:00:01.317655,6143.25,1,32179,6143.25,6143.50,271611388,C,43,01,2,14
2025-02-14 00:00:01.317655,6143.25,1,32180,6143.25,6143.50,271611388,C,43,01,2,14
2025-02-14 00:00:01.317655,6143.25,1,32181,6143.25,6143.50,271611388,C,43,01,2,14
2025-02-14 00:00:20.222990,6143.50,1,32182,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222990,6143.50,1,32183,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222990,6143.50,1,32184,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222990,6143.50,1,32185,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32186,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32187,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32188,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:23.891463,6143.50,1,32189,6143.50,6143.75,271611391,C,43,01,2,14
2025-02-14 00:00:23.891463,6143.50,1,32190,6143.50,6143.75,271611391,C,43,01,2,14
2025-02-14 00:00:23.891463,6143.50,1,32191,6143.50,6143.75,271611391,C,43,01,2,14
2025-02-14 00:01:00.046733,6143.50,1,32192,6143.25,6143.50,271611392,C,43,01,1,14


as you can see there are several groups that have the same datetime (datetime is the first column), and also have the same Id number (Id is in the 7-th column).

What I want to do is to add the smallest time increment (which is microseconds) when the time is the same.

This can be done using:

ttt = df.groupby(df['id']).cumcount()
df.index += pd.to_timedelta(ttt,unit='us')


The problem is that the difference between 2 groups with the same time is sometimes only 1 microsecond.

for instance here:

2025-02-14 00:00:20.222990,6143.50,1,32182,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222990,6143.50,1,32183,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222990,6143.50,1,32184,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222990,6143.50,1,32185,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32186,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32187,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32188,6143.25,6143.50,271611390,C,43,01,1,14


if I would execute the code above I would get this:

2025-02-14 00:00:20.222990,6143.50,1,32182,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32183,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222992,6143.50,1,32184,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222993,6143.50,1,32185,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32186,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222992,6143.50,1,32187,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222993,6143.50,1,32188,6143.25,6143.50,271611390,C,43,01,1,14


Then I would have a datetime overlap. I want to get this. Then there is no overlapping datetime.

2025-02-14 00:00:20.222990,6143.50,1,32182,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222991,6143.50,1,32183,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222992,6143.50,1,32184,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222993,6143.50,1,32185,6143.25,6143.50,271611389,C,43,01,1,14
2025-02-14 00:00:20.222994,6143.50,1,32186,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222995,6143.50,1,32187,6143.25,6143.50,271611390,C,43,01,1,14
2025-02-14 00:00:20.222996,6143.50,1,32188,6143.25,6143.50,271611390,C,43,01,1,14


any ideas how to get it done? Thanks

Continue reading...