当我在熊猫数据中使用groupby和聚合时，如何连接另一列的日期？

Question

我最初有以下数据，然后执行groupby和聚合来连接重叠的时间范围。我想在最后的dataframe中添加另一列，这个列将由重叠行上的数据连接而形成。

df['newid']=(df['START']-df['END'].shift()).dt.total_seconds().gt(0).cumsum()
print (df.to_string(index=False))

                ELEMENT                                    TEXT               START                 END  newid
 OLT2227-LT3-PON0-ONT03           USECASE1 - ALARM1 -NO OVERLAP 2021-01-19 18:00:00 2021-01-19 19:00:00      0
 OLT2227-LT3-PON0-ONT03          USECASE1 - ALARM2 - NO OVERLAP 2021-01-19 19:10:00 2021-01-19 20:00:12      1
 OLT2227-LT3-PON0-ONT05     USECASE2 - ALARM1 - Fully Contained 2021-01-19 18:00:00 2021-01-19 23:00:00      1
 OLT2227-LT3-PON0-ONT05     USECASE2 - ALARM2 - Fully Contained 2021-01-19 19:00:00 2021-01-19 20:00:12      1
 OLT2227-LT3-PON0-ONT10  USECASE3 - ALARM1 - START-END-RELATION 2021-01-19 22:00:00 2021-01-19 22:30:00      2
 OLT2227-LT3-PON0-ONT10  USECASE3 - ALARM2 - START-END-RELATION 2021-01-19 22:30:00 2021-01-19 23:00:12      2
 OLT2227-LT3-PON0-ONT21                         USECASE3-ALARM1 2021-01-19 22:00:00 2021-01-19 22:10:00      2
 OLT2227-LT3-PON0-ONT21                  USECASE3-ALARM2-NO-END 2021-01-19 22:15:00 2042-01-19 20:00:12      3
  OLT2227-LT3-PON0-ONT4                               USECASE-4 2021-01-19 17:30:00 2042-01-19 20:00:12      3
  OLT2227-LT3-PON0-ONT4                               USECASE-4 2021-01-19 20:00:00 2021-01-19 23:00:00      3
 OLT2227-LT3-PON0-ONT99                               USECASE-5 2021-01-19 17:30:00 2021-01-19 22:00:00      3
 OLT2227-LT3-PON0-ONT99                               USECASE-5 2021-01-19 20:00:00 2042-01-19 20:00:12      3

newdf=df.groupby(['newid','ELEMENT']).agg({'START':'min','END':'max'}).reset_index(level=1)
print (newdf.to_string(index=False))

                ELEMENT               START                 END
 OLT2227-LT3-PON0-ONT03 2021-01-19 18:00:00 2021-01-19 19:00:00
 OLT2227-LT3-PON0-ONT03 2021-01-19 19:10:00 2021-01-19 20:00:12
 OLT2227-LT3-PON0-ONT05 2021-01-19 18:00:00 2021-01-19 23:00:00
 OLT2227-LT3-PON0-ONT10 2021-01-19 22:00:00 2021-01-19 23:00:12
 OLT2227-LT3-PON0-ONT21 2021-01-19 22:00:00 2021-01-19 22:10:00
 OLT2227-LT3-PON0-ONT21 2021-01-19 22:15:00 2042-01-19 20:00:12
  OLT2227-LT3-PON0-ONT4 2021-01-19 17:30:00 2042-01-19 20:00:12
 OLT2227-LT3-PON0-ONT99 2021-01-19 17:30:00 2042-01-19 20:00:12

正如您所看到的，在最后一个dataframe中，我只得到了columns元素，START和END。但是，我想要的是一个数据文件，它将在聚合过程中连接文本列。

                ELEMENT               START                 END                    TEXT
 OLT2227-LT3-PON0-ONT03 2021-01-19 18:00:00 2021-01-19 19:00:00     USECASE1 - ALARM1 -NO OVERLAP
 OLT2227-LT3-PON0-ONT03 2021-01-19 19:10:00 2021-01-19 20:00:12     USECASE1 - ALARM2 - NO OVERLAP
 OLT2227-LT3-PON0-ONT05 2021-01-19 18:00:00 2021-01-19 23:00:00     USECASE2 - ALARM1 - Fully Contained; USECASE2 - ALARM2 - Fully Contained
 OLT2227-LT3-PON0-ONT10 2021-01-19 22:00:00 2021-01-19 23:00:12     USECASE3 - ALARM1 - START-END-RELATION; USECASE3 - ALARM2 - START-END-RELATION
 OLT2227-LT3-PON0-ONT21 2021-01-19 22:00:00 2021-01-19 22:10:00     USECASE3-ALARM1
 OLT2227-LT3-PON0-ONT21 2021-01-19 22:15:00 2042-01-19 20:00:12     USECASE3-ALARM2-NO-END 
  OLT2227-LT3-PON0-ONT4 2021-01-19 17:30:00 2042-01-19 20:00:12     USECASE-4 ; USECASE-4
 OLT2227-LT3-PON0-ONT99 2021-01-19 17:30:00 2042-01-19 20:00:12     USECASE-5 ; USECASE-5

有谁能帮忙吗？

Answer 1

您可以聚合方法str.join。

(df.groupby(['newid','ELEMENT'])
    .agg({'START': 'min', 'END':'max', 'TEXT': ' ; '.join})
    .reset_index(1))

输出(仅文本列)：

USECASE1 - ALARM1 -NO OVERLAP
USECASE1 - ALARM2 - NO OVERLAP
USECASE2 - ALARM1 - Fully Contained ; USECASE2 - ALARM2 - Fully Contained
USECASE3 - ALARM1 - START-END-RELATION ; USECASE3 - ALARM2 - START-END-RELATION
USECASE3-ALARM1
USECASE3-ALARM2-NO-END
USECASE-4 ; USECASE-4
USECASE-5 ; USECASE-5