在python中查找日期范围重叠 [英] Find date range overlap in python
问题描述
我正在尝试找到一种基于特定列(id)在数据框中查找重叠数据范围(每行提供的开始/结束日期)的更有效方法.
I am trying to find an more efficient way of finding overlapping data ranges (start/end dates provided per row) in a dataframe based on a specific column (id).
数据框在发件人"列上排序
Dataframe is sorted on 'from' column
我认为有一种方法可以像我一样避免双重"应用功能...
I think there is a way to avoid "double" apply function like I did...
import pandas as pd
from datetime import datetime
df = pd.DataFrame(columns=['id','from','to'], index=range(5), \
data=[[878,'2006-01-01','2007-10-01'],
[878,'2007-10-02','2008-12-01'],
[878,'2008-12-02','2010-04-03'],
[879,'2010-04-04','2199-05-11'],
[879,'2016-05-12','2199-12-31']])
df['from'] = pd.to_datetime(df['from'])
df['to'] = pd.to_datetime(df['to'])
id from to
0 878 2006-01-01 2007-10-01
1 878 2007-10-02 2008-12-01
2 878 2008-12-02 2010-04-03
3 879 2010-04-04 2199-05-11
4 879 2016-05-12 2199-12-31
我使用应用"功能在所有组上循环,并且在每个组中,我每行使用应用":
I used the "apply" function to loop on all groups and within each group, I use "apply" per row:
def check_date_by_id(df):
df['prevFrom'] = df['from'].shift()
df['prevTo'] = df['to'].shift()
def check_date_by_row(x):
if pd.isnull(x.prevFrom) or pd.isnull(x.prevTo):
x['overlap'] = False
return x
latest_start = max(x['from'], x.prevFrom)
earliest_end = min(x['to'], x.prevTo)
x['overlap'] = int((earliest_end - latest_start).days) + 1 > 0
return x
return df.apply(check_date_by_row, axis=1).drop(['prevFrom','prevTo'], axis=1)
df.groupby('id').apply(check_date_by_id)
id from to overlap
0 878 2006-01-01 2007-10-01 False
1 878 2007-10-02 2008-12-01 False
2 878 2008-12-02 2010-04-03 False
3 879 2010-04-04 2199-05-11 False
4 879 2016-05-12 2199-12-31 True
我的代码的灵感来自以下链接:
My code was inspired from the following links :
推荐答案
您可以移动to
列并直接减去日期时间.
You could just shift the to
column and perform a direct subtraction of the datetimes.
df['overlap'] = (df['to'].shift()-df['from']) > timedelta(0)
在按id
分组时应用它可能看起来像
Applying this while grouping by id
may look like
df['overlap'] = (df.groupby('id')
.apply(lambda x: (x['to'].shift() - x['from']) > timedelta(0))
.reset_index(level=0, drop=True))
演示
>>> df
id from to
0 878 2006-01-01 2007-10-01
1 878 2007-10-02 2008-12-01
2 878 2008-12-02 2010-04-03
3 879 2010-04-04 2199-05-11
4 879 2016-05-12 2199-12-31
>>> df['overlap'] = (df.groupby('id')
.apply(lambda x: (x['to'].shift() - x['from']) > timedelta(0))
.reset_index(level=0, drop=True))
>>> df
id from to overlap
0 878 2006-01-01 2007-10-01 False
1 878 2007-10-02 2008-12-01 False
2 878 2008-12-02 2010-04-03 False
3 879 2010-04-04 2199-05-11 False
4 879 2016-05-12 2199-12-31 True
这篇关于在python中查找日期范围重叠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!