How to optimise pandas iterrows with large df

I have different dataframes, customers (unique on "customer_id") with 30k rows and transactions with 2mil rows. For each row in customers I am trying to grab a selection from transactions based on conditions that are subject to the given row in df1. I have tried the below naive code but it is far too slow... I am unsure where to optimise.

for index, row in customers.iterrows()():
    selection = transactions.loc[(transactions["customer_id"] == row["customer_id"]) &
                                 ((transactions["purchased_ts"] - row["signup_ts"]).dt.days > 0) &
                                 ((transactions["purchased_ts"] - row["signup_ts"]).dt.days < 183)
                                 , :]
    total_amount = float(selection["amount"].sum())
    customers.loc[customers["customer_id"] == row["customer_id"], "total_amount"] = total_amount

Dataframe is a data-structure made specifically to favour vectorised table wise operations. When you iterate over it you loose the advantages (in most cases)

Generate the data:

import pandas as pd
import numpy as np

# Create customers dataframe
num_customers = 100
customer_ids = np.arange(1, num_customers + 1)
signup_timestamps = pd.date_range(start='2023-01-01', periods=num_customers, freq='D')

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'signup_timestamp': signup_timestamps
})

# Create transactions dataframe
num_transactions = 500
transaction_customer_ids = np.random.choice(customer_ids, size=num_transactions)
purchased_timestamps = pd.date_range(start='2023-01-01', periods=num_transactions, freq='H')
amounts = np.random.uniform(low=10, high=1000, size=num_transactions)

transactions = pd.DataFrame({
    'customer_id': transaction_customer_ids,
    'purchased_timestamp': purchased_timestamps,
    'amount': amounts
})

# Print the dataframes
print("Customers DataFrame:")
print(customers.head())

print("\nTransactions DataFrame:")
print(transactions.head())

Initial dataframes:

Customers DataFrame:
customer_id signup_timestamp
0            1       2023-01-01
1            2       2023-01-02
2            3       2023-01-03
3            4       2023-01-04
4            5       2023-01-05

Transactions DataFrame:
customer_id purchased_timestamp      amount
0           17 2023-01-01 00:00:00  771.878903
1           14 2023-01-01 01:00:00  599.129092
2           76 2023-01-01 02:00:00  418.872408
3           86 2023-01-01 03:00:00  741.925505
4           41 2023-01-01 04:00:00  229.850105

Note: Please always provide an MRE like this one so that users can focus on solution not recreating your data.

Solution:

# Merge Dataframes
selection = pd.merge(customers, transactions, on=["customer_id"])

# Get date difference & filter
selection['date_diff'] =  selection['purchased_timestamp'] - selection['signup_timestamp']
selection['date_diff'] = selection['date_diff'].dt.days
selection = selection.loc[selection['date_diff'].between(0, 183)]

print("Customers DataFrame:")
print(selection.head())

Combined dataframes:

customer_id signup_timestamp purchased_timestamp      amount  date_diff
0            1       2023-01-01 2023-01-03 08:00:00   77.277407          2
1            1       2023-01-01 2023-01-08 01:00:00   40.460298          7
2            1       2023-01-01 2023-01-11 04:00:00  613.818700         10
3            1       2023-01-01 2023-01-11 11:00:00  310.237391         10
4            1       2023-01-01 2023-01-21 01:00:00  287.355806         20

Updating customers table:

# Compute "total_amount"
total_dict = selection.groupby('customer_id')['amount'].sum().to_dict()
# Map the values to customers table.
customers['total_amount'] = customers['customer_id'].map(total_dict)
print(customers)

Final Output:

customer_id signup_timestamp  total_amount
0             1       2023-01-01   1329.149603
1             2       2023-01-02   1650.039620
2             3       2023-01-03   2422.828485
3             4       2023-01-04   2768.061753
4             5       2023-01-05    358.508196
..          ...              ...           ...
95           96       2023-04-06           NaN
96           97       2023-04-07           NaN
97           98       2023-04-08           NaN
98           99       2023-04-09           NaN
99          100       2023-04-10           NaN

Note: This simply illustrates the mindset you should have when working with dataframes. It can be optimised further in many ways. My goal here is to show an alternative to iterating over the rows not to provide the most efficient method.