日期区间合并技术求助:按连续日期规则合并时间记录
日期记录合并问题
我有一组包含开始日期(started)和结束日期(stop)的记录:
| ID | started | stop |
|---|---|---|
| 1 | 2019-01-14 | 2018-02-05 |
| 2 | 2019-01-14 | 2019-03-06 |
| 3 | 2019-03-07 | 2019-03-20-> |
| 4 | Some-Date | NULL |
| 5 | 2020-09-08 | 2020-09-14 |
| 6 | 2020-09-15 | 2020-10-14 |
| 7 | ->2019-03-21 | 2019-03-30 |
合并规则
需要合并满足以下时间顺序的记录:前一条记录的结束日期 = 后一条记录的开始日期 + 1天
预期合并结果
| ID | started | stop |
|---|---|---|
| 1 | 2019-01-14 | 2018-02-05 |
| 2 | 2019-01-14 | 2019-03-30 |
| 3 | Some-Date | NULL |
| 4 | 2020-09-08 | 2020-10-14 |
我尝试通过检查日期差为1天来分组记录,但得到了异常结果。以下是我编写的Python代码:
import datetime class Records: def __init__(self, start_dt, stop_dt): self.groupNum = None self.dayDiff = None self.start_dt = start_dt self.stop_dt = stop_dt def setGroupNum(self, groupNum): self.groupNum = groupNum def setdayDiff(self, dayDiff): self.dayDiff = dayDiff def main(): recordsLst = [] resultLst = [] recordsLst.append(Records(datetime.date(2017, 8, 14), datetime.date(2018, 3, 5))) recordsLst.append(Records(datetime.date(2019, 1, 14), datetime.date(2019, 3, 6))) recordsLst.append(Records(datetime.date(2019, 3, 7), datetime.date(2019, 3, 20))) recordsLst.append(Records(datetime.date(2023, 12, 30), datetime.date(9999, 12, 31))) recordsLst.append(Records(datetime.date(2020, 9, 8), datetime.date(2020, 9, 14))) recordsLst.append(Records(datetime.date(2020, 9, 15), datetime.date(2020, 10, 14))) recordsLst.append(Records(datetime.date(2019, 3, 21), datetime.date(2019, 3, 30))) recordsLst.sort(key=lambda x: x.start_dt, reverse=False) for index, a in enumerate(recordsLst): for b in recordsLst[index:]: # 如果是同一条记录 if (a.start_dt.day == b.start_dt.day and a.start_dt.month == b.start_dt.month and a.start_dt.year == b.start_dt.year) and \ (a.stop_dt.day == b.stop_dt.day and a.stop_dt.month == b.stop_dt.month and a.stop_dt.year == b.stop_dt.year): a.setGroupNum('same') # 如果符合时间顺序 if a.stop_dt.month == b.start_dt.month \ and a.stop_dt.year == b.start_dt.year \ and (a.stop_dt.day - b.start_dt.day) == -1: a.setdayDiff(-1) a.setGroupNum(index) # 注:Datum未定义,此处为原代码笔误 # resultLst.append(Datum(a.stop_dt, b.start_dt)) else: a.setdayDiff(None) print(index, a, b) if __name__ == "__main__": main()
另外还有一个新的Pandas数据集:
import pandas as pd import datetime df = pd.DataFrame([[datetime.date(2016, 1, 2), datetime.date(2016, 5, 5)], # 案例 A->B, B->C, B->D => A->D [datetime.date(2010, 2, 14), datetime.date(2010, 3, 22)], [datetime.date(2010, 3, 23), datetime.date(2010, 4, 12)], [datetime.date(2010, 3, 23), datetime.date(2010, 5, 14)], [datetime.date(2010, 5, 15), datetime.date(2010, 6, 7)], # -> 2010-02-14 | 2010-10-20 # 案例 A->B, A->C, B->D => A->D [datetime.date(2011, 1, 1), datetime.date(2011, 2, 2)], [datetime.date(2011, 1, 1), datetime.date(2011, 3, 4)], [datetime.date(2011, 2, 3), datetime.date(2011, 4, 4)], # -> 2011-01-01 | 2011-04-04 # 案例 A->C, B->C, C->D => A->D [datetime.date(2012, 5, 5), datetime.date(2012, 6, 6)], [datetime.date(2012, 5, 7), datetime.date(2012, 6, 6)], [datetime.date(2012, 6, 7), datetime.date(2012, 12, 12)], # -> 2012-05-05 | 2012-12-12 [datetime.date(2010, 6, 8), datetime.date(2010, 10, 20)], [datetime.date(2016, 5, 6), datetime.date(2016, 10, 10)], [datetime.date(2011, 1, 1), datetime.date(9999, 12, 31)]], columns=['start', 'end'])
恳请各位提供帮助,谢谢!
内容的提问来源于stack exchange,提问作者Rody
相关产品推荐
相关产品推荐

