CODEWITHSUNDEEP
pythonpandastimetimedelta
user9394674
user9394674

Reputation:

How to efficiently order time python

Sorry about the vague title. This problem is easier to show.

I have a dataframe that is ordered by time. I want to convert this to total seconds. The issue is that time goes from 08:00:00 AM to 03:00:00 AM. So the total seconds is fine until time goes from 23:59:59 to 00:00:00.

An example of some timestamps are:

['23:45:00'
'23:45:00'
'23:47:00'
'23:49:00'
'23:55:00'
'00:10:00'
'00:10:00'
'00:10:00'
'01:05:00'
'01:08:00'
'01:10:00'
'01:15:00'
'02:05:00'
'02:07:00'
'03:05:00'
'03:10:00'
'03:15:00'
'03:25:00']

So total seconds is fine until midnight. Then it starts again. At the moment, I'm amending this by adding 24 hrs hour after midnight. 

df['Time'] = pd.DatetimeIndex(df['Time']) + timedelta(hours=24)

All of this isn't very efficient if you have to index the appropriate rows and then add n number of hours.

I'm not sure if this is useful but I've attached some code that produces random timestamps below:

import pandas as pd
import random
from datetime import timedelta

def randomTime():

    rtime = int(random.random()*86400)

    hours   = int(rtime/3600)
    minutes = int((rtime - hours*3600)/60)
    seconds = rtime - hours*3600 - minutes*60

    time_string = '%02d:%02d:%02d' % (hours, minutes, seconds)
    return time_string

time = [randomTime() for _ in range(8)]

k = 5
N = 8

d = ({'Time' : (time)})

df = pd.DataFrame(data=d)

Upvotes: 2

Views: 150

Answers (1)

Ami Tavory
Ami Tavory

Reputation: 76346

I completely agree with @abarnet's comment that you should be adding 24, not 25 - hours each time there's a drop.

An efficient way of finding the increment, using your above example, could be

>>> (pd.to_datetime(df.Time) < pd.to_datetime(df.Time).shift(1)).cumsum() * pd.Timedelta(hours=24)
0    0 days
1    0 days
2    0 days
3    0 days
4    0 days
5    1 days
6    1 days
7    1 days
8    1 days
9    1 days
10   1 days
11   1 days
12   1 days
13   1 days
14   1 days
15   1 days
16   1 days
17   1 days
Name: Time, dtype: timedelta64[ns]

This

  • checks whether the time drops relative to the row before
  • finds the cumulative number of times this happened
  • translates it into a timedelta of 24 hours for the number of times

Upvotes: 2

Related Questions