创建指标等于 1 的日期范围

Question

如何为 ids 创建日期范围的数据框，其中 indicator = 1？

#Proxy main high frequency dataframe
main_data = [['site a', '2021-03-05 01:00:00', 1], 
             ['site a', '2021-03-05 01:30:00', 1], 
             ['site a', '2021-03-05 02:00:00', 0],
             ['site a', '2021-03-05 02:30:00', 1],
             ['site a', '2021-03-05 02:30:00', 1],
             ['site b', '2021-04-08 20:00:00', 0],
             ['site b', '2021-04-09 20:00:00', 1],
             ['site b', '2021-04-10 20:00:00', 1],
             ['site b', '2021-04-10 20:30:00', 1]] 
 
# Create the pandas DataFrame
main_df = pd.DataFrame(main_data, columns = ['id', 'timestamp', 'indicator'])
main_df['timestamp'] = pd.to_datetime(main_df['timestamp'], infer_datetime_format=True)

print(main_df)
id  timestamp   indicator
0   site a  2021-03-05 01:00:00 1
1   site a  2021-03-05 01:30:00 1
2   site a  2021-03-05 02:00:00 0
3   site a  2021-03-05 02:30:00 1
4   site a  2021-03-05 02:30:00 1
5   site b  2021-04-08 20:00:00 0
6   site b  2021-04-09 20:00:00 1
7   site b  2021-04-10 20:00:00 1
8   site b  2021-04-10 20:30:00 1

所需的输出数据帧：

print(desired_df)

    id      start               end
0   site a  2021-03-05 01:00:00 2021-03-05 01:30:00
1   site a  2021-03-05 02:30:00 2021-03-05 02:30:00
2   site b  2021-04-09 20:00:00 2021-04-10 20:30:00

Answer 1

IIUC：

1. groupby 指标列序列并记录“start”和“end”的min 和max 值。
2. 删除不需要的列和重复项。

main_df["start"] = main_df.groupby(main_df["indicator"].ne(main_df["indicator"].shift()).cumsum())["timestamp"].transform("min")
main_df["end"] = main_df.groupby(main_df["indicator"].ne(main_df["indicator"].shift()).cumsum())["timestamp"].transform("max")

output = main_df[main_df["indicator"].eq(1)].drop_duplicates(["start", "end"])

>>> output
       id               start                 end
0  site a 2021-03-05 01:00:00 2021-03-05 01:30:00
3  site a 2021-03-05 02:30:00 2021-03-05 02:30:00
6  site b 2021-04-09 20:00:00 2021-04-10 20:30:00

Answer 2

您可以将 grouby 与命名聚合一起使用，如下所示，首先创建指标 1、ind_grp 组，其中 eq 为零，cumsum：

ind_grp = main_df['indicator'].eq(0).cumsum()

main_df.groupby(['id', ind_grp], as_index=False)\
       .agg(start=('timestamp', 'min'),
              end=('timestamp','max'))

输出：

       id               start                 end
0  site a 2021-03-05 01:00:00 2021-03-05 01:30:00
1  site a 2021-03-05 02:00:00 2021-03-05 02:30:00
2  site b 2021-04-08 20:00:00 2021-04-10 20:30:00

Answer 3

这是一个解决方案：

group = main_df[main_df['indicator'] == 1].groupby(main_df['indicator'].ne(main_df['indicator'].shift(1)).cumsum()[main_df['indicator'] == 1])
pd.DataFrame({'id': group['id'].first().tolist(), 'start': group['timestamp'].first().tolist(), 'end': group['timestamp'].last().tolist()})

输出：

>>> desired_df
       id               start                 end
0  site a 2021-03-05 01:00:00 2021-03-05 01:30:00
1  site a 2021-03-05 02:30:00 2021-03-05 02:30:00
2  site b 2021-04-09 20:00:00 2021-04-10 20:30:00