pd.date_range for 9am each day between two pd.Timestamp objects

user_216229 0
Viewed 75

What I have:

pd.Timestamp('2021-07-05 08:10:11')
pd.Timestamp('2021-07-07 12:13:14')

What I'm looking for:

[pd.Timestamp('2021-07-05 09:00:00'), 
 pd.Timestamp('2021-07-06 09:00:00'), 
 pd.Timestamp('2021-07-07 09:00:00')]

It feels like I should be able to do this with some combination of of pd.date_range and pd.offsets, but I can't find the right combination.

Given that 9am is the standard meteorological time point, I'm hoping someone has a nice solution for this already!

pandas python
2 Answers

Method 1

We can use datetime.replace with pd.date_range and a frequency of D:

start = pd.Timestamp('2021-07-05 08:10:11').replace(hour=9, minute=0, second=0)
end = pd.Timestamp('2021-07-07 12:13:14').replace(hour=9, minute=0, second=0)

pd.date_range(start, end, freq='D')

DatetimeIndex(['2021-07-05 09:00:00', '2021-07-06 09:00:00',
               '2021-07-07 09:00:00'],
              dtype='datetime64[ns]', freq='D')

Method 2: Using pd.offsets.BusinessHour

We can pass an offset to pd.date_range and in this case we can use BusinessHour since it will by default use 09:00.

start = pd.Timestamp('2021-07-05 08:10:11')
end = pd.Timestamp('2021-07-07 12:13:14')

dates = pd.date_range(start, end, freq=pd.offsets.BusinessHour()).to_series()
dates.groupby(dates.dt.date).min()

2021-07-05   2021-07-05 09:00:00
2021-07-06   2021-07-06 09:00:00
2021-07-07   2021-07-07 09:00:00
dtype: datetime64[ns]

To set another start time, we can set the start argument in pd.offsets.BusinessHour

user_90812670

It turns out this is way more fiddly and annoying than expected...

So much so that I've created a gist with a small suite of unit test and a selection of possible solutions.

Currently, the only working solution is this one, which isn't as pretty or succinct as I feel pandas should be capable of:

start = start.round('D') + pd.Timedelta(hours=9)
[x for x in pd.date_range(start, end, freq='1D')
 if start <= x <= end]

If you find cases where the above doesn't work, please let me know so I can add a test for it, and propose a solution if you're able!

user_2162290
Related