Replace time in datetime column in Pandas

user_4984061 0
Viewed 346

How do I replace specific times in pandas column that is datetime format? I need to replace all times of 23:52:00 with 23:59:59 in df.end

data = [['site a',  '2021-03-05 23:52:00'], 
        ['site a', '2021-03-06 12:00:00'], 
        ['site b', '2021-04-08 23:00:00'],
        ['site c', '2021-04-09 23:52:00']] 
 
df = pd.DataFrame(data, columns = ['id', 'end'])
df['end'] = pd.to_datetime(df['end'], infer_datetime_format=True)

Using this where statement, none of the times change. this due to this statement: df['end'].dt.time == '23:52:00'

np.where(df['end'].dt.time == '23:52:00', (df['end'].dt.normalize() + pd.Timedelta('23:59:59')), df['end'])

Out[74]: array(['2021-03-05T23:52:00.000000000', '2021-03-06T12:00:00.000000000',
       '2021-04-08T23:00:00.000000000', '2021-04-09T23:52:00.000000000'],
      dtype='datetime64[ns]')
pandas datetime python
3 Answers

You're comparing wrong types, convert the time to sting:

mask = df['end'].dt.time.astype(str) == '23:52:00'

To replace the values found:

import datetime

df.loc[mask, 'end'] = df[mask]['end'] + datetime.timedelta(minutes=7)

replace "minutes=7" with the difference you want.

user_148262510

If you prefer to compare using datetime objects, you can do (no need to use np.where):

mask = df['end'].dt.time == pd.to_datetime('23:52:00').time()
df.loc[mask, 'end'] = df['end'].dt.normalize() + pd.Timedelta('23:59:59')
user_8665270

you have to use date time not string. pandas.to_datetime('23:52:00')

user_144220930
Related