Run tally of events within a given period using datetime

Question

I have a txt file with a number of dates, each signifying an event:

15MAR18 103000      
15MAR18 120518      
17MAR18 121203      
17MAR18 134443      
17MAR18 151733      
19MAR18 165013      
19MAR18 182253      
19MAR18 195533    

I am trying to get a running tally of how many 'events' occur within a 24 hour time period.

I can read the file and parse into datetime objects ok:

for line in range(0, len(event_list):
     eventTime = event_list[line][:14]
     eventTime = datetime.strptime(eventTime, '%d%b%y %H%M%S')

     eventTime_next = event_list[line+1][:14]
     eventTime_next = datetime.strptime(next, '%d%b%y %H%M%S')

I don't know how to next go about it.

I tried to compare the line ahead with the previous and but I don't think that's the way to go about it.

I need it so the following happens

15MAR18 103000      1
15MAR18 120518      2
17MAR18 121203      1
17MAR18 134443      2
17MAR18 151733      3
19MAR18 165013      1
19MAR18 182253      2
19MAR18 195533      3

I.e So the count will go back to 1 when 24 hours has elapsed since the first comparison... and then start again with the new start reference.

I hope this makes sense?

Or is this something for pandas library or something?

Answer 1

If you have access to pandas, pd.Series.cumcount is one way. This method uses Date only to extract the running total.

I'm not sure whether you also need to consider Time.

import pandas as pd
from io import StringIO

mystr = StringIO("""15MAR18 103000      
15MAR18 120518      
17MAR18 121203      
17MAR18 134443      
17MAR18 151733      
19MAR18 165013      
19MAR18 182253      
19MAR18 195533""")

# replace mystr with 'file.csv' below
df = pd.read_csv(mystr, header=None, names=['Date', 'Time'], delim_whitespace=True)

df['RunningTotal'] = df.groupby('Date').cumcount() + 1

#       Date    Time  RunningTotal
# 0  15MAR18  103000             1
# 1  15MAR18  120518             2
# 2  17MAR18  121203             1
# 3  17MAR18  134443             2
# 4  17MAR18  151733             3
# 5  19MAR18  165013             1
# 6  19MAR18  182253             2
# 7  19MAR18  195533             3