0

I'd like to convert any minutes within 10 minute interval to its 5th minute. For example, minutes between 10 and 19 are converted to 15, minutes between 20 and 29 are converted to 25 and so on.

The following is an excerpt from my datetime column in the dataframe.

2020-06-25 13:23:48
2020-06-25 13:36:18
2020-06-25 13:41:32
2020-06-25 13:59:27

My desirable output is:

2020-06-25 13:25:00
2020-06-25 13:35:00
2020-06-25 13:45:00
2020-06-25 13:55:00
Ankit Bhatt
  • 5
  • 3
  • 1
    Does this answer your question? [Pandas: Timestamp index rounding to the nearest 5th minute](https://stackoverflow.com/questions/24479577/pandas-timestamp-index-rounding-to-the-nearest-5th-minute) – ignoring_gravity Jul 15 '20 at 12:13

2 Answers2

1

Here's how i done it, though Im sure there are other clever ways to do this:

df = pd.DataFrame({'time' : [pd.datetime(*x) for x in [[2020,6,25,13,23,48],[2020,6,25,13,36,18],[2020,6,25,13,41,32],[2020,6,25,13,59,27]]]})
df.time = df.time.apply(lambda x: pd.datetime(x.year, x.month, x.day, x.hour, (x.minute//10)*10+5, 0))

and the result goes like this:

print(df):
                 time
0 2020-06-25 13:25:00
1 2020-06-25 13:35:00
2 2020-06-25 13:45:00
3 2020-06-25 13:55:00
Rm4n
  • 623
  • 7
  • 14
0

Building your dataframe:

import pandas as pd
from datetime import datetime

df = pd.DataFrame(
    data={
        "dt": [
            "2020-06-25 13:23:48", "2020-06-25 13:36:18",
            "2020-06-25 13:41:32", "2020-06-25 13:59:27"
        ]
    }
)

df looks like this:

              datetime
0  2020-06-25 13:23:48
1  2020-06-25 13:36:18
2  2020-06-25 13:41:32
3  2020-06-25 13:59:27

We can define a function to apply to the column dt of df:

def change_dt(x):
    f = "%Y-%m-%d %H:%M:%S"
    dt = datetime.strptime(x, f)
    
    if dt.minute < 10:
        m = 5
    elif dt.minute >= 10 and dt.minute < 20:
        m = 15
    elif dt.minute >= 20 and dt.minute < 30:
        m = 25
    elif dt.minute >= 30 and dt.minute < 40:
        m = 35
    elif dt.minute >= 40 and dt.minute < 50:
        m = 45
    else:
        m = 55
    
    return dt.replace(minute=m, second=0).strftime(f)

We replace the column dt of df by the result of applying the function change_dt to this column:

df.loc[:, "dt"] = df["dt"].apply(change_dt)

df looks like this:

              datetime
0  2020-06-25 13:25:00
1  2020-06-25 13:35:00
2  2020-06-25 13:45:00
3  2020-06-25 13:55:00