CODEWITHSUNDEEP
pythonpandasdataframedatetime
Brainiac
Brainiac

Reputation: 159

Pandas datetime column increment day when reach midnight timestamp

I have pandas column with only timestamps in incremental order. I use to_datetime() to work with that column but it automatically adds same day throughout column without incrementing when encounters midnight. So how can I logically tell it to increment day when it crosses midnight.

rail[8].iloc[121]
rail[8].iloc[100]

printing these values outputs:

TIME   2020-11-19 00:18:00
Name: DSG, dtype: datetime64[ns]

TIME   2020-11-19 21:12:27
Name: KG, dtype: datetime64[ns]

whereas iloc[121] should be 2020-11-20

Sample data is like:

Data

df1.columns = df1.iloc[0]
ids = df1.loc['TRAIN NO'].unique()
df1.drop('TRAIN NO',axis=0,inplace=True)
rail = {}
for i in range(len(ids)):
    rail[i] = df1.filter(like=ids[i])
    rail[i] = rail[i].reset_index()
    rail[i].rename(columns={0:'TRAIN NO'},inplace=True)
    rail[i] = pd.melt(rail[i],id_vars='TRAIN NO',value_name='TIME',var_name='trainId')
    rail[i].drop(columns='trainId',inplace=True)
    rail[i].rename(columns={'TRAIN NO': 'CheckPoints'},inplace=True)
    rail[i].set_index('CheckPoints',inplace=True)
    rail[i].dropna(inplace=True)
    rail[i]['TIME'] = pd.to_datetime(rail[i]['TIME'],infer_datetime_format=True)

                 
CheckPoints     TIME

DEPOT   2020-11-19 05:10:00
KG  2020-11-19 05:25:00
RI  2020-11-19 05:51:11
RI  2020-11-19 06:00:00
KG  2020-11-19 06:25:44
... ...
DSG 2020-11-19 23:41:50
ATHA    2020-11-19 23:53:56
NBAA    2020-11-19 23:58:00
NBAA    2020-11-19 00:01:00
DSG 2020-11-19 00:18:00

Could someone help me out..!

Upvotes: 1

Views: 982

Answers (1)

FObersteiner
FObersteiner

Reputation: 25564

You can check where the timedelta of subsequent timestamps is less than 0 (= date changes). Use the cumsum of that and add it as a timedelta (days) to your datetime column:

import pandas as pd

df = pd.DataFrame({'time': ["23:00", "00:00", "12:00", "23:00", "01:00"]})

# cast time string to datetime, will automatically add today's date by default
df['datetime'] = pd.to_datetime(df['time'])

# get timedelta between subsequent timestamps in the column; df['datetime'].diff()
# compare to get a boolean mask where the change in time is negative (= new date)
m = df['datetime'].diff() < pd.Timedelta(0)
# m
# 0    False
# 1     True
# 2    False
# 3    False
# 4     True
# Name: datetime, dtype: bool

# the cumulated sum of that mask accumulates the booleans as 0/1:
# m.cumsum() 
# 0    0
# 1    1
# 2    1
# 3    1
# 4    2
# Name: datetime, dtype: int32

# ...so we can use that as the date offset, which we add as timedelta to the datetime column:
df['datetime'] += pd.to_timedelta(m.cumsum(), unit='d')

df
    time            datetime
0  23:00 2020-11-19 23:00:00
1  00:00 2020-11-20 00:00:00
2  12:00 2020-11-20 12:00:00
3  23:00 2020-11-20 23:00:00
4  01:00 2020-11-21 01:00:00

Upvotes: 2

Related Questions