使用 Pandas 将时间序列转换为开始和结束日期

Question

我只是在寻找一种更直观、更快的方法来获取不间断时间序列的开始和结束时间。这里有一个可复制的例子，以及我目前的做法：

import pandas as pd
import numpy as np
import datetime
data = ['1999-01-01 00:00:00', '1999-01-01 01:00:00', '1999-01-01 02:00:00',
'1999-01-10 10:00:00', '1999-01-10 11:00:00', '1999-01-10 12:00:00', '1999-01-10 13:00:00',
'1999-01-20 17:00:00', '1999-01-20 18:00:00', '1999-01-20 19:00:00']
df = pd.DataFrame(data, columns = ['time'])
df['time'] = pd.to_datetime(df['time'])
# Conversion:
new_df = pd.DataFrame(columns=['Start Date', 'End Date'])
new_df2 = pd.DataFrame(columns=['End Date'])
df['diff'] = df['time'].diff(1)
df['diff2'] = df['diff'].shift(-1)
new_df['Start Date'] = df['time'].loc[df['diff'] != pd.to_timedelta(1, unit ='h')].reset_index(drop = True)
new_df2['End Date'] = df['time'].loc[df['diff2'] != pd.to_timedelta(1, unit ='h')].reset_index(drop = True)
new_df['End Date'] = new_df2['End Date']
new_df['Duration [Hours]'] = (new_df['End Date'] - new_df['Start Date']) / np.timedelta64(1, 'h')
print(new_df)

结果数据帧：

Start Date            End Date  Duration [Hours]
0 1999-01-01 00:00:00 1999-01-01 02:00:00               2.0
1 1999-01-10 10:00:00 1999-01-10 13:00:00               3.0
2 1999-01-20 17:00:00 1999-01-20 19:00:00               2.0

任何形式的帮助都是有价值的。

Answer 1

另一种方法是创建一个group列，指示每行属于哪个组。这可以通过使用shift和cumsum来完成。之后，我们可以简单地使用groupby来创建所需的列。

df['group'] = (df['time'].shift(1) != df['time'] - pd.Timedelta(hours=1)).cumsum()
df = df.groupby('group')['time'].agg(['first', 'last', 'count'])
df['count'] = df['count'] - 1
df.columns = ['Start Date', 'End Date', 'Duration [Hours]']
df = df.reset_index(drop=True)

我们需要从计数值中删除1，因为这是我们想要的时间，而不仅仅是每组中的行数。最后两行只是用于清理以匹配所需的输出数据帧。

结果：

Start Date            End Date Duration [Hours]
0   1999-01-01 00:00:00 1999-01-01 02:00:00                2
1   1999-01-10 10:00:00 1999-01-10 13:00:00                3
2   1999-01-20 17:00:00 1999-01-20 19:00:00                2

Answer 2

您可以按time列的日期进行分组，然后用它们的first和last值来减少它们。命名聚合允许立即放入新名称：

new_df = (df.groupby(df.time.dt.date)
.agg(**{"Start Date": ("time", "first"), "End Date": ("time", "last")}))

它给出

>>> new_df
Start Date            End Date
time
1999-01-01 1999-01-01 00:00:00 1999-01-01 02:00:00
1999-01-10 1999-01-10 10:00:00 1999-01-10 13:00:00
1999-01-20 1999-01-20 17:00:00 1999-01-20 19:00:00

然后你可以计算结束和结束之间的时间差；按秒开始日期并将其转换为小时：

diff_in_secs = (new_df["End Date"] - new_df["Start Date"]).dt.total_seconds()
new_df["Duration [Hours]"] = diff_in_secs / 3600

获取

>>> new_df
Start Date            End Date  Duration [Hours]
time
1999-01-01 1999-01-01 00:00:00 1999-01-01 02:00:00               2.0
1999-01-10 1999-01-10 10:00:00 1999-01-10 13:00:00               3.0
1999-01-20 1999-01-20 17:00:00 1999-01-20 19:00:00               2.0

为了与您的输出完全匹配，您可以重置索引：

>>> new_df = new_df.reset_index(drop=True)
>>> new_df
Start Date            End Date  Duration [Hours]
0 1999-01-01 00:00:00 1999-01-01 02:00:00               2.0
1 1999-01-10 10:00:00 1999-01-10 13:00:00               3.0
2 1999-01-20 17:00:00 1999-01-20 19:00:00               2.0