Finding the index of a sub dataframe and match in the main dataframe

I have a dataframe as below:

To achieve the Time Delta, I am doing the following:

delta_time = a.iloc[-1]['CloseDate'] - a.iloc[0]['StorageDate']

I need to subtract the last CloseDate from the first StorageDate for each CallID (a total of 16821), and the delta_time must go in the last row of each CallID, where there is value (the same as I get the CloseDate from).

I'm doing as follows:

callid = 1
while callid <= 16821:
    df1 = df1[df1['CallID'] == callid]
    delta_time = df1.iloc[-1]['CloseDate'] - df1.iloc[0]['StorageDate']
    callid += 1

But the problem is that I'm not being abble to parse the delta_time value to the correct row.

Before I tried doing with loc and iloc, and I managed to send it to the correct row in df1 with the following structure:

 delta_time = df1.iloc[-1]['CloseDate'] - df1.iloc[0]['StorageDate']
 df1.loc[1, 'Time Delta'] = delta_time

It works, but it's unefficient since I have to change the value inside the loc for every different CallID and iloc[-1] doesn't seem to work. Moreover, I don't know how to parse it to the main dataframe and not only the one I created to do the math.

Can anybody help me here?

Use groupby.transform and where:

df[['StorageDate', 'CloseDate']] = df[['StorageDate', 'CloseDate']].apply(pd.to_datetime)

g = df.groupby('CallID')

df['Time Delta'] = (g['CloseDate'].transform('last')
                    .sub(g['StorageDate'].transform('first'))
                    .where(~df['CallID'].duplicated(keep='last'))
                   )

Output:

   CallID         StorageDate           CloseDate      Time Delta
0       1 2023-02-08 14:35:09 2023-02-08 14:35:56             NaT
1       1 2023-02-08 14:35:56 2023-02-08 14:42:00 0 days 00:06:51
2       2 2023-02-07 10:17:18 2023-02-07 10:22:23             NaT
3       2 2023-02-07 10:22:23 2023-02-07 15:09:14             NaT
4       2 2023-02-07 15:09:14 2023-02-07 16:20:50             NaT
5       2 2023-02-07 16:20:49 2023-02-08 09:23:16             NaT
6       2 2023-02-08 09:23:16 2023-02-08 09:27:21 0 days 23:10:03
7       3 2023-03-10 10:31:25 2023-03-10 10:41:37             NaT
8       3 2023-03-10 10:41:37 2023-03-10 14:23:18 0 days 03:51:53

Reproducible input:

df = pd.DataFrame({'CallID': [1, 1, 2, 2, 2, 2, 2, 3, 3],
                   'StorageDate': ['2023-02-08 14:35:09', '2023-02-08 14:35:56', '2023-02-07 10:17:18', '2023-02-07 10:22:23', '2023-02-07 15:09:14', '2023-02-07 16:20:49', '2023-02-08 09:23:16', '2023-03-10 10:31:25', '2023-03-10 10:41:37'],
                   'CloseDate': ['2023-02-08 14:35:56', '2023-02-08 14:42:00', '2023-02-07 10:22:23', '2023-02-07 15:09:14', '2023-02-07 16:20:50', '2023-02-08 09:23:16', '2023-02-08 09:27:21', '2023-03-10 10:41:37', '2023-03-10 14:23:18']})

df[['StorageDate', 'CloseDate']] = df[['StorageDate', 'CloseDate']].apply(pd.to_datetime)

Use Series.duplicated for filter last rows generated by GroupBy.transform:

m = ~df['CallID'].duplicated(keep='last')
g = df.groupby('CallID')
df.loc[m, 'Time Delta'] = (g['CloseDate'].transform('last')[m]
.sub(g['StorageDate'].transform('first')[m]))
print (df)
CallID         StorageDate           CloseDate       Time Delta
0       1 2023-02-08 14:35:09 2023-02-08 14:35:56              NaN
1       1 2023-02-08 14:35:56 2023-02-08 14:42:00  0 days 00:06:51
2       2 2023-02-07 10:17:18 2023-02-07 10:22:23              NaN
3       2 2023-02-07 10:22:23 2023-02-07 15:09:14              NaN
4       2 2023-02-07 15:09:14 2023-02-07 16:20:50              NaN
5       2 2023-02-07 16:20:49 2023-02-08 09:23:16              NaN
6       2 2023-02-08 09:23:16 2023-02-08 09:27:21  0 days 23:10:03
7       3 2023-03-10 10:31:25 2023-03-10 10:41:37              NaN
8       3 2023-03-10 10:41:37 2023-03-10 14:23:18  0 days 03:51:53

Another solution with aggregate by GroupBy.agg with mapping difference:

def f(x):
ts = x.total_seconds()
hours, remainder = divmod(ts, 3600)
minutes, seconds = divmod(remainder, 60)
return ('{:02d}:{:02d}:{:02d}').format(int(hours), int(minutes), int(seconds))
m = ~df['CallID'].duplicated(keep='last')
df1 = df.groupby('CallID').agg({'CloseDate':'last', 'StorageDate':'first'})
df.loc[m, 'Time Delta'] = (df.loc[m, 'CallID'].map(df1['CloseDate'].sub(df1['StorageDate']))
.apply(f))
print (df)
CallID         StorageDate           CloseDate Time Delta
0       1 2023-02-08 14:35:09 2023-02-08 14:35:56        NaN
1       1 2023-02-08 14:35:56 2023-02-08 14:42:00   00:06:51
2       2 2023-02-07 10:17:18 2023-02-07 10:22:23        NaN
3       2 2023-02-07 10:22:23 2023-02-07 15:09:14        NaN
4       2 2023-02-07 15:09:14 2023-02-07 16:20:50        NaN
5       2 2023-02-07 16:20:49 2023-02-08 09:23:16        NaN
6       2 2023-02-08 09:23:16 2023-02-08 09:27:21   23:10:03
7       3 2023-03-10 10:31:25 2023-03-10 10:41:37        NaN
8       3 2023-03-10 10:41:37 2023-03-10 14:23:18   03:51:53