How can I move datetime to the closest five minutes?

November 16, 2023

I am working with a dataframe in pandas. In this dataframe there is a column which type is datetime. It represents the moment in which a product is sold, other columns indicate the price, the type, amount sold, amount remaining…

To do an analysis, I want more datetime columns to check how many products are sold in intervals from 5 minutes to one hour. I need to convert the datetime data to the upper five minutes.

As an example for the 5 minutes case:

2023-11-16 13:17:32 would become 2023-11-16 13:20:00, 2023-11-16 13:21:09 would become 2023-11-16 13:25:00 and so on.

For the 1 hour case, both of them would become 2023-11-16 14:00:00.

Can this be done?

I know the functions ceil and floor, but I don’t know how to use them in datetime format

>Solution :

A possible solution, which uses pandas.Series.dt.ceil:

df = pd.DataFrame({
    'col': pd.to_datetime([
        '2023-11-16 13:17:32',
        '2023-11-16 13:21:09',
    ])
})

df['rounded_5m'] = df['col'].dt.ceil('5T')
df['rounded_1h'] = df['col'].dt.ceil('1H')

Output:

                  col          rounded_5m          rounded_1h
0 2023-11-16 13:17:32 2023-11-16 13:20:00 2023-11-16 14:00:00
1 2023-11-16 13:21:09 2023-11-16 13:25:00 2023-11-16 14:00:00