How to count the duration of a field in a given value while having the field change history data?

Question

I'm working with field change history data which has timestamps for when the field value was changed. In this example, I need to calculate the overall case duration in 'Termination in Progress' status.

The given case was changed from and to this status three times in total: see screenshot

I need to add up all three durations in this case and in other cases it can be more or less than three.

Does anyone know how to calculate that in Python?

Answer 1

Welcome to Stack Overflow!

Based on the limited data you provided, here is a solution that should work although the code makes some assumptions that could cause errors so you will want to modify it to suit your needs. I avoided using list comprehension or array math to make it more clear since you said you're new to Python.

Assumptions:

• You're pulling this data into a pandas dataframe
• All Old values of "Termination in Progress" have a matching new value for all Case Numbers

import datetime
import pandas as pd
import numpy as np


fp = r'<PATH TO FILE>\\'
f = '<FILENAME>.csv'

data = pd.read_csv(fp+f)
#convert ts to datetime for later use doing time delta calculations
data['Edit Date'] = pd.to_datetime(data['Edit Date'])
# sort by the same case number and date in opposing order to make sure values for old and new align properly
data.sort_values(by = ['CaseNumber','Edit Date'], ascending = [True,False],inplace = True)

#find timestamps where Termination in progress occurs
old_val_ts = data.loc[data['Old Value'] == 'Termination in progress']['Edit Date'].to_list()
new_val_ts = data.loc[data['New Value'] == 'Termination in progress']['Edit Date'].to_list()

#Loop over the timestamps and calc the time delta
ts_deltas = list()
for i in range(len(old_val_ts)):
    item = old_val_ts[i] - new_val_ts[i]
    ts_deltas.append(item)

# this loop could also be accomplished with list comprehension like this:
#ts_deltas = [old_ts - new_ts for (old_ts, new_ts) in zip(old_val_ts, new_val_ts)]

print('Deltas between groups')
print(ts_deltas)
print()

#Sum the time deltas
total_ts_delta = sum(ts_deltas,datetime.timedelta())
print('Total Time Delta')
print(total_ts_delta)

Deltas between groups
[Timedelta('0 days 00:08:00'), Timedelta('0 days 00:06:00'), Timedelta('0 days 02:08:00')]

Total Time Delta
0 days 02:22:00

I've also attached a picture of the solution minus my file path for obvious reasons. Hope this helps. Please remember to mark as correct if this solution works for you. Otherwise let me know what issues you run into.

EDIT:

If you have multiple case numbers you want to look at, you could do it in various ways, but the simplest would be to just get a list of unique case numbers with data['CaseNumber'].unique() then iterate over that array filtering for each case number and appending the total time delta to a new list or a dictionary (not necessarily the most efficient solution, but it will work).

cases_total_td = {}

unique_cases = data['CaseNumber'].unique()
for case in unique_cases:
    temp_data = data[data['CaseNumber'] == case]
    
    #find timestamps where Termination in progress occurs
    old_val_ts = data.loc[data['Old Value'] == 'Termination in progress']['Edit Date'].to_list()
    new_val_ts = data.loc[data['New Value'] == 'Termination in progress']['Edit Date'].to_list()

    #Loop over the timestamps and calc the time delta
    ts_deltas = list()
    for i in range(len(old_val_ts)):
        item = old_val_ts[i] - new_val_ts[i]
        ts_deltas.append(item)

    ts_deltas = [old_ts - new_ts for (old_ts, new_ts) in zip(old_val_ts, new_val_ts)]

    #Sum the time deltas
    total_ts_delta = sum(ts_deltas,datetime.timedelta())

    
    cases_total_td[case] = total_ts_delta

print(cases_total_td)

{1005222: Timedelta('0 days 02:22:00')}