将时间序列转换为开始

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

您可以按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

Answer 2

另一种方法是创建一个组列，指示每行属于哪个组。这可以通过使用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