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 ?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments