Round time to previous 15 min unless within 10 mins

Question

Before everyone down votes, this is a tricky question to phrase in a single title. For a given timestamp, I want to round it to the previous 15 min when it's more than 10 mins away (i.e 11-15 mins). If it's less than 10 mins away I want to round that to the previous, previous 15 min.

This may be easier to display:

1st timestamp = 08:12:00. More than 10 mins so round to nearest 15 min = 08:00:00
2nd timestamp = 08:07:00. Less than 10 mins so round to the previous, previous 15 min = 7:45:00

I can round values greater than 10 mins easily enough. The ones less than 10 mins is where I'm struggling. I attempted to convert the timestamps to total seconds to determine if it's less than 600 seconds (10 mins). If less than 600 seconds I would take another 15 mins off. If more than 600 seconds I would leave as is. Below is my attempt.

import pandas as pd
from datetime import datetime, timedelta

d = ({
    'Time' : ['8:10:00'],                                                                                          
     })

df = pd.DataFrame(data=d)

df['Time'] = pd.to_datetime(df['Time'])

def hour_rounder(t):
    return t.replace(second=0, microsecond=0, minute=(t.minute // 15 * 15), hour=t.hour)

FirstTime = df['Time'].iloc[0]
StartTime = hour_rounder(FirstTime)

#Strip date
FirstTime = datetime.time(FirstTime)
StartTime = datetime.time(StartTime)

#Convert timestamps to total seconds
def get_sec(time_str):
    h, m, s = time_str.split(':')
    return int(h) * 3600 + int(m) * 60 + int(s)

FirstTime = str(FirstTime)
FirstTime_secs = get_sec(FirstTime)

StartTime = str(StartTime)
StartTime_secs = get_sec(StartTime)

#Determine difference
diff = FirstTime_secs - StartTime_secs

Your two situations can be combined into one: substract 10 minutes and round to the previous quarter hour. — Thierry Lathuille, Mar 06 '19 at 06:49

jezrael · Accepted Answer · 2019-03-06T10:31:26.877

If possible working with timedeltas first use to_timedelta, then Series.dt.floor and if modulo 15 is less or equal 10 remove 15 minutes:

d = {'Time': ['08:00:00', '08:01:00', '08:02:00', '08:03:00', '08:04:00', 
              '08:05:00', '08:06:00', '08:07:00', '08:08:00', '08:09:00', 
              '08:10:00', '08:11:00', '08:12:00', '08:13:00', '08:14:00', 
              '08:15:00', '08:16:00', '08:17:00', '08:18:00', '08:19:00',
              '08:20:00', '08:21:00', '08:22:00', '08:23:00', '08:24:00', 
              '08:25:00', '08:26:00', '08:27:00', '08:28:00', '08:29:00',
              '08:30:00', '08:31:00', '08:32:00', '08:33:00', '08:34:00', 
              '08:35:00', '08:36:00', '08:37:00', '08:38:00', '08:39:00']}

df = pd.DataFrame(d)

df['Time'] = pd.to_timedelta(df['Time'])

s = df['Time'].dt.floor(freq='15T')
#https://stackoverflow.com/a/14190143 for convert timedeltas to minutes
df['new'] = np.where(((df['Time'].dt.total_seconds() % 3600) // 60) % 15 <= 10, 
                      s - pd.Timedelta(15 * 60, 's'), s)
print (df)

       Time      new
0  08:00:00 07:45:00
1  08:01:00 07:45:00
...
9  08:09:00 07:45:00
10 08:10:00 07:45:00
11 08:11:00 08:00:00
12 08:12:00 08:00:00
...
24 08:24:00 08:00:00
25 08:25:00 08:00:00
26 08:26:00 08:15:00
27 08:27:00 08:15:00
...
38 08:38:00 08:15:00
39 08:39:00 08:15:00

If need working with datetimes solution is similar with Series.dt.minute:

df = pd.DataFrame({'Time':pd.date_range('2015-01-01 08:00:00', freq='T', periods=40)})

s = df['Time'].dt.floor(freq='15T')
df['new'] = np.where(df['Time'].dt.minute % 15 <= 10, s - pd.Timedelta(15*60, 's'), s)

print (df)
                  Time                 new
0  2015-01-01 08:00:00 2015-01-01 07:45:00
1  2015-01-01 08:01:00 2015-01-01 07:45:00
...
9  2015-01-01 08:09:00 2015-01-01 07:45:00
10 2015-01-01 08:10:00 2015-01-01 07:45:00
11 2015-01-01 08:11:00 2015-01-01 08:00:00
12 2015-01-01 08:12:00 2015-01-01 08:00:00
13 2015-01-01 08:13:00 2015-01-01 08:00:00
...
24 2015-01-01 08:24:00 2015-01-01 08:00:00
25 2015-01-01 08:25:00 2015-01-01 08:00:00
26 2015-01-01 08:26:00 2015-01-01 08:15:00
27 2015-01-01 08:27:00 2015-01-01 08:15:00
...
38 2015-01-01 08:38:00 2015-01-01 08:15:00
39 2015-01-01 08:39:00 2015-01-01 08:15:00

Alternative solution from comment:

df['new1'] = df['Time'].sub(pd.Timedelta(11*60, 's')).dt.floor(freq='15T')

Thanks @jezrael. I'm getting an error though on `ValueError: invalid abbreviation: T`. The `s` function works but not `df[new]` — jonboy, Mar 06 '19 at 09:32
@jonboy - if change `T` to `min` it working ? or `freq='15T'` ? — jezrael, Mar 06 '19 at 09:56
@jonboy - Or need change `pd.Timedelta('11T')` to `pd.Timedelta(11*60, 's')` ? — jezrael, Mar 06 '19 at 10:01

Round time to previous 15 min unless within 10 mins

1 Answers1