Eliminate repeated datetime index values by including small increments of timedelta

Here is the provided 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

By using 

df.loc[df.index.duplicated(keep=False)]
, I am able to identify rows with non-unique indexes. To rectify this, I aim to add increments of 1 second divided by the number of rows to each index, ensuring a monotonically increasing index.

The expected output should resemble 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

Your assistance with this matter is greatly appreciated!

pythonpandasdatetime

Answer №1

By utilizing the groupby function on the index, a new column of increasing timedeltas in seconds can be generated.

This method modifies the index directly, although you have the option to utilize set_index for creating a duplicate with the desired outcome.

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

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

Alternatively, an over-the-top one-liner that provides 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

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Unable to detect any conflicts in the Conda UnsatisfiableError notification

Having trouble installing pytorch in conda within a Docker environment, resulting in an UnsatisfiableError. The error message does not reveal any clear conflicts or I might be misinterpreting it. The Docker image being used is nvidia/cuda:10.1-cudnn7-devel ...

pythondockerpytorchconda

Saving Plotted Graph Data to a Data File in Python: A Step-by-Step Guide

I recently used matplotlib.pyplot to graph several wave functions over time, displaying them on multiple vertical axes and saving the resulting graph as a jpg using savefig. Now I'm interested in finding a simple method to export all these wave functi ...

pythoncsvmatplotliboutput

Accessing the value of a specific index in Python by reading a .txt file into an array

I have a large .txt data file presented in the following format (composed of all numbers): 0 1.2 2 3.1 20 21.2 22 23 30 31 32 33.01 The goal is to extract the values from the second column of this matrix and store them into a variable. I wrote the code b ...

pythoncsvnumpy

Is there a way to redirect links within an iframe when a user decides to open them in a new tab?

I am currently developing a web application that allows users to access multiple services, such as Spark and others. When a user selects a service, like Spark for example, the app will open a new tab displaying my page (service.html) with user information ...

javascriptpythonhtmlweb

Using the list as keys is the best way to eliminate duplicate values from a nested

Initially, I had two regular lists named files and g_list. My goal was to eliminate duplicates from files and synchronize it with g_list. I came across this solution: from collections import OrderedDict as odict od = odict.fromkeys(zip(files, g_list) ...

pythoncollections

When passing the output of one function as an argument to another function, an error is occurring known as the 'StaleElementReferenceException'

My Python program consists of two functions - one extracts text from an image using pytesseract, and the other function uses this extracted text to make a Google search with selenium. When I call both functions separately in the same program, they work no ...

pythonseleniumpython-tesseract

Exploring geodesic distance calculations on a 3D triangular mesh with the help of scikit-fmm or gdist

I'm currently working on evaluating a geodesic distance matrix for the TOSCA dataset, specifically looking at a 3D mesh example like the one shown below: https://i.stack.imgur.com/CofTU.png During my analysis, I experimented with two different Pyth ...

pythonscikit-learndistancemesh

Steps for generating a dataframe with grouped categories:

I am working with a pandas dataframe called df, which contains the following fields: id name category 01 Eddie magician 01 Eddie plumber 02 Martha actress 03 Jeremy dancer 03 Jeremy actor I aim to transform this dataframe into a new one, d ...

pythonpandasdataframe

Encountering a RuntimeError when attempting to run a JustPy web app on Jupyter

I've been attempting to run justpy web apps, like the one below, on Jupyter: import justpy as jp def hello_world(): wp = jp.WebPage() d = jp.Div(text='Hello world!') wp.add(d) return wp jp.justpy(hello_world) Unfortunately ...

pythonjupyter-notebookjupyterpython-asynciojustpy

Discover the dictionary key linked to a specific value in Python

My task is to look up the value of two elements in a dictionary rather than their key. The code I currently have does not search for the value of the dictionary: for key in val["timeSlot"]: for value in val[key]: if test_date in value: ...

pythondictionary

Exploring Python's installed modules

Check out this simple code I created to display the installed modules: import sys as s mod=s.modules.keys() for indx,each in enumerate(mod): print indx,each However, my goal is to modify it so that it only prints the parent module name. For example: ...

python

The py2exe package was not found in the system

I'm in the process of converting a Tkinter application into an executable file using py2exe. Everything seems to be working correctly, except when a specific function is called, the .exe file generates the following error: Exception in Tkinter callba ...

pythonpy2exe

Replace the number in the string with a new number if it contains a specific substring

I have a mapping structure as follows: mapping = {'sum12':2, 'sum6':1, 'avg12':2, 'avg6':1, 'diff':3, 'mean':4} Additionally, I possess a dataframe containing variables na ...

python

Error: The module 'keras.api._v2.keras.callbacks' does not have the 'Tensorboard' attribute

In order to utilize other models on TensorFlow Hub, such as the ones listed below, I wrote this code. However, I encountered an issue with the following error message: AttributeError: module 'keras.api._v2.keras.callbacks' has no attribute ' ...

pythontensorflowmachine-learningkerasdeep-learning

Basic paddleball game with an unresponsive ball

I've been learning Python through a course designed for kids, and one of the projects we worked on was creating a simple paddleball game. I managed to get the ball bouncing off the walls earlier, but now it's not working as expected after complet ...

pythontkinter

What is the best way to organize a list based on an integer string found within the list?

Lately, I've been encountering some challenges with code and I wanted to share it with this fantastic community! My issue revolves around a list of strings that are essentially string-based lists separated by a special character ('~'). Here ...

pythonalgorithmlistsorting

Finding the smallest value within the data from the past N days

In my dataset, I have the following information: ID Date X 123_Var 456_Var 789_Var A 16-07-19 3 777 250 810 A 17-07-19 9 637 121 529 A 20-07-19 2 295 272 490 A 21-07-19 3 778 600 ...

pythonpandasnumpy

accumulated total for the current month and year

I'm struggling to figure out how to calculate the cumulative total for month-to-date (MTD) and year-to-date (YTD). Can someone please assist me in obtaining this result? Any help would be greatly appreciated. ...

pythonpandascumsum

Filtering pandas dataframe to only show rows from certain months

I am dealing with a pandas dataframe that includes a date column spanning from 2015 to 2021. print(data) date time wind_speed wind_direction 0 2015-01-01 00:00 00:00 28.0 25.0 1 2015-01-01 01:00 01:00 ...

pythonpandasdataframedatetime

Incorporating Radar Visuals into a Customized Widget

I have been attempting to incorporate the Radar chart code (found at this link) into a GUI that includes a widget. Instead of simply plotting x and y values on the widget, my goal is to display a radar chart, but I'm facing difficulties in achieving t ...

pythonpython-3.xmatplotlibpyqtpyqt5