Pandas, convert datetime column to nearest specified 12th hour

Question

I want to be able to round an entire date column to whatever two 12hr times the values are closest to.

For example, if I want the column to be rounded to either 8am or 8pm.

dates = pd.to_datetime(['2022-01-28 15:25:22.456', '2022-01-27'])

Should become this

dates2 = pd.to_datetime(['2022-01-28 20:00:00', '2022-01-27 08:00:00'])
DatetimeIndex(['2022-01-28 20:00:00', '2022-01-27 08:00:00'], dtype='datetime64[ns]', freq=None)

This was the solution I came up with thanks to other answers.

Here's a workable example.

from datetime import timedelta

step = timedelta(minutes=30)
start = datetime(2022,1,1,0,0,0)
end = datetime(2022,1,2,0,0,0)
seconds = (end - start).total_seconds()
array = []
for i in range(0, int(seconds), int(step.total_seconds())):
    array.append(start + timedelta(seconds=i))

dates = pd.Series(pd.to_datetime(array))

Everything before 8am gets rounded up to 8am, everything after is rounded up to 8pm. Anything after 8pm is rounded to 8am the next day.

dates2 = (dates-pd.Timedelta(hours=8)).dt.ceil('12H')+pd.Timedelta(hours=8)
pd.concat([dates,dates2],axis=1)

Does this answer your question? [Pandas: Rounding to nearest Hour](https://stackoverflow.com/questions/49561989/pandas-rounding-to-nearest-hour) — Panagiotis Kanavos, Feb 03 '22 at 16:58
Below you will find an almost one line answer. https://stackoverflow.com/a/70975428/11687201 — ai2ys, Feb 03 '22 at 17:10

JayPeerachai · Answer 1 · 2022-02-03T17:01:50.120

1

Use .replace to replace the nearest specific hour.

Change first and second to your preferred hour. In the code below, use 8 and 20.

dates = pd.to_datetime(['2022-01-28 15:25:22.456', '2022-01-27'])

# round time to either 8am or 8pm
def round_time(time, first, second):
    if time.hour < 12:
        return time.replace(hour=first, minute=0, second=0, microsecond=0)
    else:
        return time.replace(hour=second, minute=0, second=0, microsecond=0)

new_date = list()

for i in dates:
    # round time to either 8am or 8pm
    new_date.append(round_time(i, 8, 20))

new_dates = pd.to_datetime(new_date)
new_dates

Output:

DatetimeIndex(['2022-01-28 20:00:00', '2022-01-27 08:00:00'], dtype='datetime64[ns]', freq=None)

edited Feb 03 '22 at 17:01

answered Feb 03 '22 at 16:58

JayPeerachai

3,499
3
14
29

Thanks for your answer. Do you know of a potential one line solution using .apply()? – golden_curry2020 Feb 03 '22 at 17:01
should `if time.hour < 12` be changed to `if time.hour < 14`? otherwise `13:00 --> 20:00` but it should be `13:00 --> 8:00` – Matt Feb 03 '22 at 17:03

ai2ys · Accepted Answer · 2022-02-03T21:29:19.423

1

You can use round and pd.TimeDelta:

dates = pd.to_datetime([
    '2022-01-28 15:25:22.456', 
    '2022-01-27'
])
dates.round('12H')+pd.Timedelta(hours=8)

Output like the requested output:

DatetimeIndex(['2022-01-28 20:00:00', '2022-01-27 08:00:00'], dtype='datetime64[ns]', freq=None)

Additional info:
Apart from round there are also ceil and floor methods available.

Edit:
In case the dates should get rounded to the closest time, different like the output above, as mentioned in the comments below this answer. This should work with the following:

(dates-pd.Timedelta(hours=8)).round('12H')+pd.Timedelta(hours=8)

Output would look like this:

DatetimeIndex(['2022-01-28 20:00:00', '2022-01-26 20:00:00'], dtype='datetime64[ns]', freq=None)

edited Feb 03 '22 at 21:29

answered Feb 03 '22 at 17:06

ai2ys

1,151
10
21

nice answer but I think your order of operations is wrong, currently `0:00` maps to `8:00` – Matt Feb 03 '22 at 17:12
@Matt As far as I can see from the question, mapping 0:00 to 8:00 is what was asked for. – ai2ys Feb 03 '22 at 17:15
Yep, just wanted the hours at 8:00 or 20:00 . I assume '2022-01-27' is treated as '2022-01-27 00:00:01'? – golden_curry2020 Feb 03 '22 at 17:23
1

@golden_curry2020 fair enough if this does what you need! But the closest timestamp to 00:00:01 is actually 20:00:00 the day before – Matt Feb 03 '22 at 17:24
@Matt Whoops, I now understand what you meant. It started going into the next day – golden_curry2020 Feb 03 '22 at 20:42
@Matt I added an option that addresses the issue you mentioned. – ai2ys Feb 03 '22 at 21:33

Pandas, convert datetime column to nearest specified 12th hour

2 Answers2