pandas - 并发冲突

Question

我正在尝试在 Python 中查找连续的日期时间。我到了这样的地步，我能够通过循环找到每一行是否冲突，但我仍然坚持如何找出一个事件是否是并发的。关于如何完成此任务的任何建议？也对类似的方法持开放态度!

并发是指 Name 和 Event Date 相同且连续冲突计数 >= 3。

示例数据 1:

<表类="s-表"><头>事件ID姓名<日>日期 事件开始事件结束<正文>123查尔斯·霍珀8/4/208/4/20 上午 8:308/4/20 上午 10:30456查尔斯·霍珀8/4/208/4/20 上午 8:508/4/20 上午 9:20789查尔斯·霍珀8/4/208/4/20 上午 8:308/4/20 上午 10:001011丹尼尔佩雷斯8/10/208/10/20 上午 9 点8/10/20 上午 11:001213沙阿金8/5/208/5/20 中午 12:0020 年 8 月 5 日下午 1 点1415沙阿金8/5/208/5/20 中午 12:3020 年 8 月 5 日下午 1 点

当前代码:

import pandas as pd
import numpy as np

df = pd.read_excel(r'[Path]\TestConcurrent.xlsx')

df['Start'] = df['Event Start']
df['End'] = df['Event End']



df['conflict'] = len(df) 

 #Edit from answer
    df['concurrent']  = df.groupby(['Name','Event Date','conflict'])['Event ID'].transform('count').ge(3)
    
    print(df)

当前输出:

[![在此处输入图片描述][1]][1]

预期输出:

[![在此处输入图片描述][2]][2]

Answer 1

好的，最后的方法!如果我理解，那么您并不关心“连续”列，您只想了解连续 3 个重叠的窗口。这是一种尝试立即回答该问题的方法。它通过了两个测试数据集(感谢编辑!)

import pandas as pd
import numpy as np
import itertools
import io

#Creating the test df you provided
df = pd.read_csv(io.StringIO("""
Event ID;Name;Date;Event Start;Event End
123;Hoper, Charles;8/4/20;8/4/20 8:30 AM;8/4/20 10:30 AM
456;Hoper, Charles;8/4/20;8/4/20 8:50 AM;8/4/20 9:20 AM
789;Hoper, Charles;8/4/20;8/4/20 8:30 AM;8/4/20 10 AM
1011;Perez, Daniel;8/10/20;8/10/20 9 AM;8/10/20 11 AM
1213;Shah, Kim;8/5/20;8/5/20 12 PM;8/5/20 1 PM
1415;Shah, Kim;8/5/20;8/5/20 12:30 PM;8/5/20 1 PM
"""),sep=';')

#Overridding with The second test df
df = pd.read_csv(io.StringIO("""
Event ID;Name;Date;Event Start;Event End
88;Cooper, Herbert;10/20/20;10/20/20 8:10 AM;10/20/20 9:48 AM
99;Cooper, Herbert;10/20/20;10/20/20 9:19 AM;10/20/20 11:30 AM
10;Cooper, Herbert;10/20/20;10/20/20 11:52 AM;10/20/20 1:26 PM
11;Cooper, Herbert;10/20/20;10/20/20 1:22 AM;10/20/20 2:15 PM
12;Cooper, Herbert;10/20/20;10/20/20 3:23 PM;10/20/20 4:10 PM
"""),sep=';')

df['Event Start'] = pd.to_datetime(df['Event Start'])
df['Event End'] = pd.to_datetime(df['Event End'])
df['overlap'] = False

#Iterating line by line keeping track of whether 3 conflicts are found
last_name = None
last_date = None
last_end = pd.Timestamp.max
num_consecutive = 0

for i,r in df.iterrows():
    
    streak_continues = all([
        last_name == r['Name'],
        last_date == r['Date'],
        r['Event Start'] < last_end,
    ])
        
    if not streak_continues:
        if num_consecutive >= 3:
            df.loc[
                df['Name'].eq(last_name) & df['Date'].eq(last_date),
                'overlap'
            ] = True
            
        num_consecutive = 0
        
    last_name = r['Name']
    last_date = r['Date']
    last_end = r['Event End']
    num_consecutive += 1
        
df