How do I efficiently replace the time portion of datetime values in a pandas column?

Question

I have a dataframe with a number of datetime values. I'd like to change the time portion of these to a specific time that is the same for all observations in the dataframe, but preserve the date portion of the datetime.

I can do this with a single datetime, using the datetime package and the .replace() function:

import datetime

dt_test=datetime.datetime(2020, 5, 17,21,30,15)
print(dt_test)

dt_test=dt_test.replace(hour=6,minute=0,second=0)

print(dt_test)

Which returns:

2020-05-17 21:30:15
2020-05-17 06:00:00

I feel like I should be able to use the .dt operator to apply this to the whole column, but receive an error when I attempt to do so.

import pandas as pd
dates=[
    datetime.datetime(2018,1,1,4,30,15),
    datetime.datetime(2018,1,2,4,30,15),
    datetime.datetime(2018,1,3,4,30,15),
    datetime.datetime(2018,1,3,6,0,0),
    datetime.datetime(2018,1,3,12,30,15),
    datetime.datetime(2018,1,1,4,30,15),
    datetime.datetime(2018,1,2,4,30,15),
    datetime.datetime(2018,1,4,4,30,15),
    datetime.datetime(2018,1,2,12,30,15),
    datetime.datetime(2018,1,4,12,30,15),
]
ids=list(range(len(dates)))

df=pd.DataFrame(zip(ids,dates),columns=['id','date_time'])
df

Returns:

    id  date_time
0   0   2018-01-01 04:30:15
1   1   2018-01-02 04:30:15
2   2   2018-01-03 04:30:15
3   3   2018-01-03 06:00:00
4   4   2018-01-03 12:30:15
5   5   2018-01-01 04:30:15
6   6   2018-01-02 04:30:15
7   7   2018-01-04 04:30:15
8   8   2018-01-02 12:30:15
9   9   2018-01-04 12:30:15

Where the 'date_time' column is dtype: datetime64[ns], as desired (see this question).

I then attempt to use pandas' .replace() function with the .dt operator:

df['date_time'].dt.replace(hour=6,minute=0,second=0)
df

But I receive the following error (using jupyter, if that matters):

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
 in 
----> 1 df['date_time'].dt.replace(hour=6,minute=0,second=0)
      2 df

AttributeError: 'DatetimeProperties' object has no attribute 'replace'

How can I go about efficiently replacing the time portion of a datetime variable in a pandas column?

Note: I found this question that can set the time to midnight for all dates, but the time does matter for me and is unlikely to be midnight.

user_4238408 · Answer

For hour=0, minute=0, second=0, Pandas has an built-in:

df['date_time'].dt.normalize()

and then you can shift:

df['date_time'].dt.normalize() + pd.Timedelta('01:02:03')

user_10197418 · Answer

Besides using normalize()as @QuangHoang shows in his answer, you can floor the timestamps to the day (i.e. giving you also the date with H:M:S all set to zero), and add a timedelta:

df['date_time'].dt.floor('d') + pd.Timedelta(hours=6)

Another option would be to use replace by applying a lambda:

df['date_time'] = df['date_time'].apply(lambda t: t.replace(hour=6,minute=0,second=0))

Performance-wise, the iterative apply is significantly slower for larger series sizes:

Interestingly, flooring to the day has a slight advantage over the normalize method in this benchmark for sizes > 10k elements. And I think floor is more readable (my personal taste) since there is no general definition what normalize means.

Benchmark: Python 3.8.7 x86-64, pandas 1.2.1. Code (see also simple benchmark):

import pandas as pd
from simple_benchmark import benchmark

timeseries = pd.Series([
    pd.Timestamp(2018,1,1,4,30,15),
    pd.Timestamp(2018,1,2,4,30,15),
    pd.Timestamp(2018,1,3,4,30,15),
    pd.Timestamp(2018,1,3,6,0,0),
    pd.Timestamp(2018,1,3,12,30,15),
    pd.Timestamp(2018,1,1,4,30,15),
    pd.Timestamp(2018,1,2,4,30,15),
    pd.Timestamp(2018,1,4,4,30,15),
    pd.Timestamp(2018,1,2,12,30,15),
    pd.Timestamp(2018,1,4,12,30,15),
])

add = pd.Timedelta(hours=6)

def floor_day(s):
    return s.dt.floor('d') + add

def normalize_day(s):
    return s.dt.normalize() + add

def apply_replace(s):
    return s.apply(lambda t: t.replace(hour=6,minute=0,second=0))

funcs = [floor_day, normalize_day, apply_replace]
arguments = {i*timeseries.size: timeseries.repeat(i) for i in [1, 10, 100, 1000, 10000, 100000]}
argument_name = 'series_size'
b = benchmark(funcs, arguments, argument_name)
b.plot()

How do I efficiently replace the time portion of datetime values in a pandas column?

2 Answers