date comparison pands

user_14607278 0
Viewed 160

I'm having trouble counting the total Record IDs where Start and End for each row are consecutive. Consecutive means when a row starts before the previous row ends and Name == Name. Record IDs 1-3 are consecutive because they overlap and have consecutive start/end datetimes.

I only want to display TRUE where total consecutive conflicts > = 3, else FALSE.

import pandas as pd
import io

#SAMPLE DATA 1 DF
df = pd.read_csv(io.StringIO("""
Record ID;Record Name;Record Start;Record End
1;SMITH, JOHN;10/20/20 8:00 AM;10/20/20 9:30 AM
2;SMITH, JOHN;10/20/20 9:20 AM;10/20/20 10:30 AM
3;SMITH, JOHN;10/20/20 10:20 AM;10/20/20 11:00 AM
4;SMITH, JOHN ;10/20/20 1:00 AM;10/20/20 2:15 PM
5;SMITH, JOHN;10/20/20 2:00 PM;10/20/20 4:00 PM
"""),sep=';')

# SAMPLE DATA 2 DF
df = pd.read_csv(io.StringIO("""
Record ID;Record Name;Record Start;Record End
1;SMITH, JOHN;10/4/20 8:00 AM;10/20/20 9:30 AM
2;SMITH, JOHN;10/4/20 9:20 AM;10/20/20 10:30 AM
3;SMITH, JOHN;10/4/20 11:20 AM;10/20/20 12:00 PM
4;SMITH, JOHN ;10/4/20 1:00 PM;10/20/20 2:15 PM
5;SMITH, JOHN;10/4/20 3:15 PM;10/20/20 4:00 PM
"""),sep=';')

df['Start'] = df['Record Start']
df['End'] = df['Record End']


df['overlap?'] =  False


print(df)

Expected Output for Sample Data 1:
   Record ID     Record Name  ... overlap? total records consec >=3?
0          1     SMITH, JOHN  ...     True                    True
1          2     SMITH, JOHN  ...     True                    True
2          3     SMITH, JOHN  ...     True                    True
3          4    SMITH, JOHN   ...     True                    False
4          5     SMITH, JOHN  ...     True                    False



Expected Output for Sample Data 2:

   Record ID   Record Name  ... overlap? total records consec >=3?
0          1   SMITH, JOHN  ...     True                    False
1          2   SMITH, JOHN  ...     True                    False
2          3   SMITH, JOHN  ...     True                    False
3          4  SMITH, JOHN   ...    False                    False
4          5   SMITH, JOHN  ...     True                    False

This gives a false positive. Its just grouping by Name and Date and counting the number of overlaps. But doesn't look at if those overlaps are consecutive.

python-datetime
1 Answers

use dataframe.loc to get the current row and previous row then add one to the previous row count column if the date is equal else if not equal then set count to 1. filter all rows in the dataframe with count greater than 3. You can build a running total based on the name and date also.

I used timedelta frequently in my solution. I use total_seconds between the start datetime and the end datetime then divide by 60 to get minutes which it is added to the start time to create datetime offsets from start with one minute intervals.

apply creates the minute intervals between the start and end date times.

df = pd.read_csv(io.StringIO("""
Record ID;Record Name;Record Start;Record End
1;SMITH, JOHN;10/20/20 8:00 AM;10/20/20 9:30 AM
2;SMITH, JOHN;10/20/20 9:20 AM;10/20/20 10:30 AM
3;SMITH, JOHN;10/20/20 10:20 AM;10/20/20 11:00 AM
4;COOPER, ALLEN;10/20/20 1:00 PM;10/20/20 2:15 PM
5;PEREZ, HILL;10/20/20 3:15 PM;10/20/20 4:00 PM
6;SMITH, JOHN;10/4/21 8:00 AM;10/20/21 9:30 AM
7;SMITH, JOHN;10/4/21 9:20 AM;10/20/21 10:30 AM
8;SMITH, JOHN;10/4/21 11:20 AM;10/20/21 12:00 PM
9;SMITH, JOHN ;10/4/21 1:00 PM;10/20/21 2:15 PM
10;SMITH, JOHN;10/4/21 3:15 PM;10/20/21 4:00 PM
"""),sep=';')

df['Record Start']=pd.to_datetime(df['Record Start'])
df['Record End']=pd.to_datetime(df['Record End'])
def create_datetime(date,hour,minute,second):
    month=date.month
    day=date.day
    year=date.year
    return datetime(year=year,month=month,day=day,hour=hour,minute=minute,second=second,microsecond=0)
def get_minutes(row):
    start=row['Record Start']
    end = row['Record End']

    results=[start + timedelta(minutes=x) for x in range(0, round((end-start).total_seconds()//60)+1)]
    
    #for item in results:
    #    print(item)
    #sys.exit()
    return results

df['minutes'] = df.apply(get_minutes, axis=1)

def intersection(lst1, lst2):
    return list(set(lst1) & set(lst2))

prev_row=None
for index,row in df.iterrows():
    if index==0:
        df.loc[index,'Count']=1
    else:
        prev_row=df.iloc[index-1]
        
    if not prev_row is None:
        if prev_row['Record Name']==row['Record Name']:
            count=prev_row['Count']
            lst1=row['minutes']
            lst2=prev_row['minutes']
            if len(intersection(lst1,lst2))>0:
                df.loc[index,'Count']=count+1
            else:
                df.loc[index,'Count']=1
        else:
            df.loc[index,'Count']=1
        
    #print(df[df['Count']>=3])   
    print(df)

output:

 Record ID    Record Name       Record Start         Record End  Count
 0          1    SMITH, JOHN   10/20/20 8:00 AM   10/20/20 9:30 AM         1.0
 1          2    SMITH, JOHN   10/20/20 9:20 AM  10/20/20 10:30 AM    2.0
 2          3    SMITH, JOHN  10/20/20 10:20 AM  10/20/20 11:00 AM    3.0
 3          4  COOPER, ALLEN   10/20/20 1:00 PM   10/20/20 2:15 PM    1.0
 4          5    PEREZ, HILL   10/20/20 3:15 PM   10/20/20 4:00 PM    1.0
 5          6    SMITH, JOHN    10/4/21 8:00 AM   10/20/21 9:30 AM    1.0
 6          7    SMITH, JOHN    10/4/21 9:20 AM  10/20/21 10:30 AM    2.0
 7          8    SMITH, JOHN   10/4/21 11:20 AM  10/20/21 12:00 PM    3.0
 8          9   SMITH, JOHN     10/4/21 1:00 PM   10/20/21 2:15 PM    1.0
 9         10    SMITH, JOHN    10/4/21 3:15 PM   10/20/21 4:00 PM    1.0
user_40011770
Related