[Python] Seeking Advice on Efficient Pandas Operations for Conditional Summing

I am a newbie to Python and pandas and would appreciate any help I can get. I have the below code and would like to know whether there is a more efficient way to write it to improve performance. I tried using cumsum but it does not give me the same output.

Context: I need to calculate the total vesting_value_CAD for each trancheID by summing all trancheIDs having the same employeeID, groupName, and vesting_year with agreementDate <= the current trancheID's agreementDate, excluding the current row.

Code:

import pandas as pd
from datetime import datetime

# Create the dataframe
data = {
'employeeID': [2, 2, 2, 2, 2, 2, 2],
'groupName': ['A', 'A', 'A', 'A', 'A', 'B', 'A'],
'agreementID': [7, 7, 1, 1, 8, 9, 6],
'agreementDate': ['3/1/2025', '3/1/2025', '4/1/2025', '3/1/2025', '2/1/2025', '3/1/2025', '3/1/2025'],
'trancheID': [28, 29, 26, 27, 30, 31, 32],
'vesting_year': [2025, 2026, 2026, 2027, 2026, 2026, 2026],
'vesting_value_CAD': [200, 300, 400, 500, 50, 30, 40]
}

df = pd.DataFrame(data)

# Convert agreementDate to datetime
df['agreementDate'] = pd.to_datetime(df['agreementDate'], format='%m/%d/%Y')

# Function to calculate total vesting_value_CAD for each trancheID
def calculate_total_vesting_value(row):
# Filter the dataframe based on the conditions
filtered_df = df[(df['employeeID'] == row['employeeID']) &
(df['groupName'] == row['groupName']) &
(df['vesting_year'] == row['vesting_year']) &
(df['agreementDate'] <= row['agreementDate']) &
(df['trancheID'] != row['trancheID'])]

# Calculate the sum of vesting_value_CAD
total_vesting_value = filtered_df['vesting_value_CAD'].sum()

return total_vesting_value

# Apply the function
df['total_vesting_value_CAD'] = df.apply(calculate_total_vesting_value, axis=1)


print(df)

Continue reading...