Hourly time series in minutes between two timestamps using Pandas

Question

I have a range of timestamps with start time and end time. I would like to generate the number of minutes per hour between the two timestamps:

import pandas as pd

start_time = pd.to_datetime('2013-03-26 21:49:08',infer_datetime_format=True)
end_time = pd.to_datetime('2013-03-27 05:21:00, infer_datetime_format=True)

pd.date_range(start_time, end_time, freq='h')

which gives:

DatetimeIndex(['2013-03-26 21:49:08', '2013-03-26 22:49:08',
               '2013-03-26 23:49:08', '2013-03-27 00:49:08',
               '2013-03-27 01:49:08', '2013-03-27 02:49:08',
               '2013-03-27 03:49:08', '2013-03-27 04:49:08'],
              dtype='datetime64[ns]', freq='H')

Sample result: I would like to compute the number of minutes bounded by the hour between the start and end times, like below:

 2013-03-26 21:00:00'  - 10m 52secs
 2013-03-26 22:00:00'  - 60 m 
 2013-03-26 23:00:00'  - 60 m

 2013-03-27 05:00:00'  - 21 m

I have looked at pandas resample, but not exactly sure how to achieve this. Any direction is appreciated.

score 3 · Answer 1 · answered Feb 07 '18 at 00:52

Construct two Series corresponding to the start and end time of each hour. Use clip_lower and clip_upper to restrict them to be within your desired timespan, then subtract:

# hourly range, floored to the nearest hour
rng = pd.date_range(start_time.floor('h'), end_time.floor('h'), freq='h')

# get the left and right endpoints for each hour
# clipped to be inclusive of [start_time, end_time]
left = pd.Series(rng, index=rng).clip_lower(start_time)
right = pd.Series(rng + 1, index=rng).clip_upper(end_time)

# construct a series of the lengths
s = right - left

The resulting output:

2013-03-26 21:00:00   00:10:52
2013-03-26 22:00:00   01:00:00
2013-03-26 23:00:00   01:00:00
2013-03-27 00:00:00   01:00:00
2013-03-27 01:00:00   01:00:00
2013-03-27 02:00:00   01:00:00
2013-03-27 03:00:00   01:00:00
2013-03-27 04:00:00   01:00:00
2013-03-27 05:00:00   00:21:00
Freq: H, dtype: timedelta64[ns]

score 0 · Answer 2 · answered Feb 06 '18 at 20:41

0

Utilizing datetime.timedelta() in some sort of for loop seems like it's what you're looking for.

https://docs.python.org/2/library/datetime.html#datetime.timedelta

answered Feb 06 '18 at 20:41

Lucas Churchman

1
1
2

I tried timedelta like t2 - t1. However, I want the difference in minutes per hour for all the hours between the two timestamps. – Priva Feb 06 '18 at 21:16

score 0 · Answer 3 · answered Feb 06 '18 at 22:51

It seems like this might be a viable solution:

import pandas as pd
import datetime as dt

def bounded_min(t, range_time):
    """ For a given timestamp t and considered time interval range_time,
    return the desired bounded value in minutes and seconds"""

    # min() takes care of the end of the time interval, 
    # max() takes care of the beginning of the interval
    s = (min(t + dt.timedelta(hours=1), range_time.max()) - 
         max(t, range_time.min())).total_seconds() 
    if s%60:
        return "%dm %dsecs" % (s/60, s%60)
    else:
        return "%dm" % (s/60)

start_time = pd.to_datetime('2013-03-26 21:49:08',infer_datetime_format=True)
end_time = pd.to_datetime('2013-03-27 05:21:00', infer_datetime_format=True)

range_time = pd.date_range(start_time, end_time, freq='h')
# Include the end of the time range using the union() trick, as described at:
# https://stackoverflow.com/questions/37890391/how-to-include-end-date-in-pandas-date-range-method
range_time = range_time.union([end_time])

# This is essentially timestamps for beginnings of hours 
index_time = pd.Series(range_time).apply(lambda x: dt.datetime(year=x.year, 
                              month=x.month, 
                              day=x.day,
                              hour=x.hour, 
                              minute=0, 
                              second=0))

bounded_mins = index_time.apply(lambda x: bounded_min(x, range_time))

# Put timestamps and values together
bounded_df = pd.DataFrame(bounded_mins, columns=["Bounded Mins"]).set_index(index_time)

print bounded_df

Gotta love the powerful lambdas:). Maybe there is a simpler way to do it though.

Output:

                      Bounded Mins
2013-03-26 21:00:00   10m 52secs
2013-03-26 22:00:00          60m
2013-03-26 23:00:00          60m
2013-03-27 00:00:00          60m
2013-03-27 01:00:00          60m
2013-03-27 02:00:00          60m
2013-03-27 03:00:00          60m
2013-03-27 04:00:00          60m
2013-03-27 05:00:00          21m

At the end, instead of creating a dataframe, you can also just set the index on the bounded_mins series using: `bounded_mins.index = index_time` — Dmitry Duplyakin, Feb 06 '18 at 22:59

Hourly time series in minutes between two timestamps using Pandas

3 Answers3