python - How can I concatenate date from another column when I use groupby and aggregation in a pandas dataframe

Question

I am having the following dataframe initially, then I perform a groupby and an aggregate to concatenate overlapping time ranges. I want to add another column in the final dataframe and this column will be formed by a concatenation of data on the overlapping rows.

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

As you can see, In the last dataframe, I get only the columns ELEMENT, START and END. However, what I would like to get is a dataframe that will concatenate the TEXT columns during the process of Aggregation.

                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
 

Can any one please help ?

question from:https://stackoverflow.com/questions/65909373/how-can-i-concatenate-date-from-another-column-when-i-use-groupby-and-aggregatio

Answer 1

You can aggregate the method str.join:

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

Output (TEXT column only):

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