将数据帧日期按组拆分为单个最小最大日期范围

Question

我有一个数据框架，看起来像这样:

S.No  date          origin  dest    journeytype
1     2021-10-21    FKG      HYM    OP
2     2021-10-21    FKG      HYM    PK
3     2021-10-21    HYM      LDS    OP
4     2021-10-22    FKG      HYM    OP
5     2021-10-22    FKG      HYM    PK
6     2021-10-22    HYM      LDS    OP
7     2021-10-23    FKG      HYM    OP
8     2021-10-24    AVM      BLA    OP
9     2021-10-24    AVM      DBL    OP
10    2021-10-27    AVM      BLA    OP

我需要拆分单个的origin, destination &旅程型为个人出发&;end_date列。

上述输入的输出数据框如下所示:

start_date  end_date   origin   dest    journeytype
2021-10-21  2021-10-23  FKG     HYM     OP
2021-10-21  2021-10-22  FKG     HYM     PK
2021-10-21  2021-10-22  HYM     LDS     OP
2021-10-24  2021-10-24  AVM     BLA     OP
2021-10-24  2021-10-24  AVM     DBL     OP
2021-10-27  2021-10-27  AVM     BLA     OP

如果任何组的日期是非连续的，则需要在结果

中显示为单独的记录

Answer 1

如果可能的话，通过比较差异来指定连续的值，如果比1大，则每组使用:

df['date'] = pd.to_datetime(df['date'])
g = df.groupby(['origin','dest','journeytype'])['date'].diff().dt.days.gt(1).cumsum()
df = (df.groupby(['origin','dest','journeytype', g], sort=False)['date']
.agg(start_date='min', end_date='max')
.reset_index())
df = df[['start_date', 'end_date','origin', 'dest', 'journeytype']]
print (df)
start_date   end_date origin dest journeytype
0 2021-10-21 2021-10-23    FKG  HYM          OP
1 2021-10-21 2021-10-22    FKG  HYM          PK
2 2021-10-21 2021-10-22    HYM  LDS          OP
3 2021-10-24 2021-10-24    AVM  BLA          OP
4 2021-10-24 2021-10-24    AVM  DBL          OP
5 2021-10-27 2021-10-27    AVM  BLA          OP