Dedupe duplicated datetime index values by adding fractional timedelta increments

user_8166819 0
Viewed 161

The data:

n = 8
np.random.seed(42)
df = pd.DataFrame(index=[dt.datetime(2020,3,31,9,25) + dt.timedelta(seconds=x) 
                         for x in np.random.randint(0,10000,size=n).tolist()],
                  data=np.random.randint(0,100,size=(n, 2)),
                  columns=['price', 'volume']).sort_index()
df.index.name = 'timestamp'
df = df.append(df.iloc[[3,6]]+1)
df = df.append(df.iloc[3]+1)
df = df.append(df.iloc[3]).sort_index()

                  price volume
timestamp       
2020-03-31 09:32:46 413 805
2020-03-31 09:39:20 372 99
2020-03-31 10:38:46 385 191
2020-03-31 10:51:31 130 661
2020-03-31 10:51:31 131 662
2020-03-31 10:51:31 131 662
2020-03-31 10:51:31 130 661
2020-03-31 10:54:50 871 663
2020-03-31 11:00:34 308 769
2020-03-31 11:09:25 343 491
2020-03-31 11:09:25 344 492
2020-03-31 11:26:10 458 87

using df.loc[df.index.duplicated(keep=False)] I can find the rows with a non unique index. For those rows I am looking to add 1sec/(number of rows) increments to the index in order to make the index monotonically increasing.

The desired output looks like this:

                          price volume
timestamp       
2020-03-31 09:32:46.000000  413 805
2020-03-31 09:39:20.000000  372 99
2020-03-31 10:38:46.000000  385 191
2020-03-31 10:51:31.000000  130 661
2020-03-31 10:51:31.250000  131 662
2020-03-31 10:51:31.750000  131 662
2020-03-31 10:51:31.000000  130 661
2020-03-31 10:54:50.000000  871 663
2020-03-31 11:00:34.000000  308 769
2020-03-31 11:09:25.000000  343 491
2020-03-31 11:09:25.500000  344 492
2020-03-31 11:26:10.000000  458 87

Grateful for your help!

pandas datetime python
1 Answers

We can groupby on the index and create a column of increasing timedeltas in seconds.

This solution updates the index in place, but you can use set_index to create a copy of the result of desired.

g = df.groupby(level=0)
deltas = g.cumcount().div(g['price'].transform('size')).to_numpy()

df.index += pd.to_timedelta(deltas, unit='ms')

Or, as an outrageous one liner which returns a copy:

df = (df.groupby(level=0)
        .cumcount()
        .div(g['price'].transform('size'))
        .apply(pd.to_timedelta, unit='s')
        .add(df.index)
        .pipe(df.set_index))

df

                         price  volume
2020-03-31 09:32:46.000     63      59
2020-03-31 09:39:20.000     99      23
2020-03-31 10:38:46.000     20      32
2020-03-31 10:51:31.000     52       1
2020-03-31 10:51:31.250     53       2
2020-03-31 10:51:31.500     53       2
2020-03-31 10:51:31.750     52       1
2020-03-31 10:54:50.000      2      21
2020-03-31 11:00:34.000     87      29
2020-03-31 11:09:25.000     37       1
2020-03-31 11:09:25.500     38       2
2020-03-31 11:26:10.000     74      87
user_49090870
Related