How to add start date for pandas datetime conversion and extend formula to all rows like in excel?

user_11661267 0
Viewed 438

I have create a simple single column dataframe with string time values in %H:%M:%S format. 

import pandas as pd 

data = ['22:00:00', '22:30:00', '23:00:00', '23:30:00', '00:00:00', '00:30:00', '01:00:00']
df = pd.DataFrame(data, columns = ['DateTime'])
df

The result is:

    DateTime
0   22:00:00
1   22:30:00
2   23:00:00
3   23:30:00
4   00:00:00
5   00:30:00
6   01:00:00

How do I attach a date, i.e. today: 2019-10-28 to the time in the first row and have it extend to all the remaining times, wrapping over a new day at midnight (like in column 4).

Desired result is:

    DateTime
0   2019-10-28 22:00:00
1   2019-10-28 22:30:00
2   2019-10-28 23:00:00
3   2019-10-28 23:30:00
4   2019-10-29 00:00:00
5   2019-10-29 00:30:00
6   2019-10-29 01:00:00

I have attempted 

df['DateTime'] = pd.to_datetime(df.DateTime, format='%H:%M:%S')

but it starts the date at 1900-01-01 and does not wrap over at midnight.

pandas datetime python
2 Answers

Fix by create the hidden group , with cumsum since the time different is less than 0 , we should find that is new day, since 23:99 always greater than 00:00. 

s=pd.to_timedelta(df.DateTime)
pd.to_timedelta(s.diff().dt.total_seconds().lt(0).cumsum(),unit='D')+s+pd.to_datetime('2019-10-28')
Out[37]: 
0   2019-10-28 22:00:00
1   2019-10-28 22:30:00
2   2019-10-28 23:00:00
3   2019-10-28 23:30:00
4   2019-10-29 00:00:00
5   2019-10-29 00:30:00
6   2019-10-29 01:00:00
Name: DateTime, dtype: datetime64[ns]
user_79645270

Use the pandas date range capabilities.

Given that you have a starting date and a starting time, and you have a set period you can extend this "excel" style for a given number of rows (or periods)

pd.date_range("2019-01-01T22:00:00", freq="30min", periods=5)
# >> DatetimeIndex(['2019-01-01 22:00:00', '2019-01-01 22:30:00', '2019-01-01 23:00:00', '2019-01-01 23:30:00', '2019-01-02 00:00:00'], dtype='datetime64[ns]', freq='30T')
user_23143910
Related