Pandas, convert datetime column to nearest specified 12th hour-CodePudding

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)

What's the easiest way to do this with a dataframe that contains a timestamp column?

CodePudding user response：

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:

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

CodePudding user response：

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)