识别时间重叠并按datetime拆分生成新行的技术实现求助

原始数据片段 #

ID    Type    Start               End             
1   sleep   01/01/2020 07:00    01/01/2020 12:00
1   sleep   01/01/2020 12:00    01/01/2020 16:30
1   sleep   01/01/2020 16:30    01/01/2020 20:30
2   sleep   01/04/2019 08:00    01/04/2019 10:30
2   sleep   01/04/2019 08:00    01/04/2019 12:00
2   sleep   01/04/2019 12:00    01/04/2019 14:00
2   sleep   01/04/2019 14:00    01/04/2019 18:00
3   sleep   02/24/2015 14:45    02/25/2015 07:05
3   sleep   02/25/2015 07:05    02/25/2015 14:05

处理规则 #

ID  Type    Start               End                 Rule            
1   sleep   01/01/2020 07:00    01/01/2020 12:00    ok, no change   
1   sleep   01/01/2020 12:00    01/01/2020 16:30    ok, no change   
1   sleep   01/01/2020 16:30    01/01/2020 20:30    ok, no change   
2   sleep   01/04/2019 08:00    01/04/2019 10:30    Ignore  
2   sleep   01/04/2019 08:00    01/04/2019 12:00    select          
2   sleep   01/04/2019 12:00    01/04/2019 14:00    ok, no change   
2   sleep   01/04/2019 14:00    01/04/2019 18:00    ok, no change   
3   sleep   02/24/2015 14:45    02/25/2015 07:05    split           
3   sleep   02/25/2015 07:05    02/25/2015 14:05    ok, no change   

规则核心：

• ok, no change：保留原行，不做修改
• Ignore：直接丢弃该行
• select：保留该行，同时丢弃与之重叠的短区间行
• split：保留原行的同时，新增两行将跨天区间拆分为「当天14:45到23:59」「23:59到次日07:05」

期望最终输出 #

ID  Type    Start               End             
1   sleep   01/01/2020 07:00    01/01/2020 12:00
1   sleep   01/01/2020 12:00    01/01/2020 16:30
1   sleep   01/01/2020 16:30    01/01/2020 20:30
2   sleep   01/04/2019 08:00    01/04/2019 12:00
2   sleep   01/04/2019 12:00    01/04/2019 14:00
2   sleep   01/04/2019 14:00    01/04/2019 18:00
3   sleep   02/24/2015 14:45    02/25/2015 07:05
3   sleep   02/24/2015 14:45    02/24/2015 23:59
3   sleep   02/24/2015 23:59    02/25/2015 07:05
3   sleep   02/25/2015 07:05    02/25/2015 14:05

目前在实现上述数据处理逻辑时遇到瓶颈，请求技术协助完成该流程。

内容的提问来源于stack exchange，提问作者Prajwal Mani Pradhan