Average_Transaction_Value_Stats

Average Transaction Value Stats

Questions in this category ask: "What is the average transaction value grouped by X for merchant Y's card_scheme Z transactions between months A and B?"

The answer is always a list of strings sorted ascending by average amount, formatted with exactly 2 decimal places.

Data Source

Load payments.csv. Key columns:

• merchant — exact merchant name (e.g., Crossfit_Hanna, Rafa_AI, Golfclub_Baron_Friso, Belles_cookbook_store, Martinis_Fine_Steakhouse)
• card_scheme — exact scheme name (NexPay, GlobalCard, SwiftCharge, TransactPlus)
• day_of_year — integer 1–365; use this for month filtering (no separate month column)
• eur_amount — transaction amount in euros (the value to average)
• Grouping columns: issuing_country, acquirer_country, aci, shopper_interaction, email_address, ip_country, device_type, etc.

Month → day_of_year Mapping (2023, non-leap year)

For a range like "September and October", use start=244 and end=304.

Solution Pattern

import pandas as pd

df = pd.read_csv('/path/to/payments.csv')

# Apply filters
mask = (
    (df['merchant'] == 'MerchantName') &
    (df['card_scheme'] == 'SchemeName') &
    (df['day_of_year'] >= START_DAY) &
    (df['day_of_year'] <= END_DAY)
)
filtered = df[mask]

# Group and compute mean
avg = filtered.groupby('grouping_col')['eur_amount'].mean().round(2)
avg_sorted = avg.sort_values(ascending=True)

# Format — always use :.2f to ensure trailing zeros (e.g., 75.70 not 75.7)
result = [f"{k}: {v:.2f}" for k, v in avg_sorted.items()]

Output Format

The answer is a Python list of strings: ['KEY1: XX.XX', 'KEY2: YY.YY', ...]

• Sorted ascending by amount
• Each element: "CATEGORY: VALUE" — string format, not tuples like ('KEY', value)
• All amounts use exactly 2 decimal places via :.2f

Correct: ['C: 75.70', 'A: 87.91', 'B: 131.71']
Wrong: [('C', 75.7), ('A', 87.91), ('B', 131.71)]

Special Case: Grouping by Email Address

When asked for "average transaction value per unique email", compute the mean of per-email means (not total_amount / unique_email_count):

# Correct: average of per-email averages
per_email_avg = df.groupby('email_address')['eur_amount'].mean()
result = per_email_avg.mean().round(3)

# Wrong: total / count
# result = df['eur_amount'].sum() / df['email_address'].nunique()

The two methods give different results. Use the mean-of-means approach unless the question explicitly says "total divided by unique count".

Common Mistakes to Avoid

1. Tuple output instead of strings — the most frequent error. Always format as f"{k}: {v:.2f}".
2. Wrong date boundary — for multi-month ranges, use the start of the first month and the end of the last month. "September and October" = days 244–304, not 244–273.
3. Missing :.2f formatting — pandas .round(2) alone may drop trailing zeros (e.g., 75.7). Always apply :.2f in the format string.
4. Wrong averaging for email grouping — see Special Case above.

Verification

After computing, sanity-check:

• Count of filtered rows is reasonable (hundreds to thousands for a 2-month merchant+scheme filter)
• All grouping keys present in results match the unique values in the filtered data
• Values are sorted ascending