datetime列增量达到午夜时的日期时间戳

Question

我有一个只带有递增顺序的时间戳的列。 我使用to_datetime（）来处理该列，但它会自动在整个列中添加同一天，而在遇到午夜时不会增加。 那个么，我怎样才能合乎逻辑地告诉它，当它跨过午夜时，它会增加一天

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

打印这些值输出：

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

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

而iloc[121]应该是2020-11-20

示例数据如下所示：

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

有人能帮帮我吗

Answer 1

您可以检查后续时间戳的时间增量小于0（=日期更改）的位置。使用其总和，并将其作为时间增量（天）添加到datetime列：

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