Average_Transaction_Value_Stats

Average Transaction Value Stats

This skill covers computing mean eur_amount from payments.csv, with optional filters on merchant, card scheme, and month range, grouped by a categorical column.

Dataset Overview

payments.csv — core transaction table. Key columns:

• merchant: Crossfit_Hanna, Rafa_AI, Golfclub_Baron_Friso, Belles_cookbook_store, Martinis_Fine_Steakhouse
• card_scheme: NexPay, GlobalCard, SwiftCharge, TransactPlus
• year: always 2023 in this dataset
• day_of_year: 1–365 (no month column — compute month ranges using the table below)
• eur_amount: transaction amount in euros
• shopper_interaction: Ecommerce or POS
• issuing_country: BE, ES, FR, GR, IT, LU, NL, SE
• acquirer_country: FR, GB, IT, NL, US (NOT the same as issuing_country values)
• ip_country: BE, ES, FR, GR, IT, LU, NL, SE
• aci: A, B, C, D, E, F, G
• device_type: Windows, Linux, MacOS, iOS, Android, Other
• is_credit: True or False
• card_bin: 13 possible values (e.g., 4017, 4133, 4236, ...)
• email_address: hashed email (has NaN values — groupby excludes NaN automatically)
• card_number, ip_address: hashed IDs (no NaN)

Merchant → acquirer_country mapping (each merchant has a fixed acquirer):

• Belles_cookbook_store → US
• Crossfit_Hanna → NL (majority) and GB
• Golfclub_Baron_Friso → IT
• Martinis_Fine_Steakhouse → FR
• Rafa_AI → NL

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

Use (df['day_of_year'] >= start) & (df['day_of_year'] <= end) to filter date ranges.

Critical Filtering Rules

Card scheme is a filter column, not part of the merchant name

When a question says "Merchant_X's TransactPlus transactions", it means:

df[(df['merchant'] == 'Merchant_X') & (df['card_scheme'] == 'TransactPlus')]

Never omit the card_scheme filter when a card scheme is mentioned in the question.

Multi-month ranges

"Between January and April" means months January through April inclusive (day_of_year 1–120). Include both endpoint months.

Standard Solution Pattern

import pandas as pd

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

# 1. Apply all filters the question specifies
mask = pd.Series([True] * len(df), index=df.index)

# Optional: filter by merchant
mask &= (df['merchant'] == 'Merchant_Name')

# Optional: filter by card scheme
mask &= (df['card_scheme'] == 'CardSchemeName')

# Optional: filter by date range
mask &= (df['day_of_year'] >= DAY_START) & (df['day_of_year'] <= DAY_END)

filtered = df[mask]

# 2. Group by the specified column and compute mean eur_amount
result = filtered.groupby('grouping_column')['eur_amount'].mean()

# 3. Round to 2 decimal places (unless question specifies otherwise)
result = result.round(2)

# 4. Sort ascending by value
result = result.sort_values(ascending=True)

# 5. Format as list of strings
answer = [f"{idx}: {val}" for idx, val in result.items()]
print(answer)

Output Format

• List of grouped averages: ['GroupA: 71.18', 'GroupB: 86.79', ...]
  • Sorted in ascending order by amount
  • Amounts rounded to 2 decimal places
  • Use the grouping key exactly as it appears in the data (e.g., FR, SE, Ecommerce)
• Single scalar average: return as a number (e.g., 90.696)

Interpreting "Average per Unique X"

When the question asks for "average transaction amount per unique email/card/customer":

• Compute the mean per entity, then take the mean of those per-entity averages:

  result = df.groupby('email_address')['eur_amount'].mean().mean()
  

• This differs from total_amount / count_of_unique_entities (which gives a wrong result).

Common Mistakes to Avoid

1. Wrong acquirer_country values: acquirer_country is FR, GB, IT, NL, US — not the same as issuing_country (BE, ES, FR, GR, IT, LU, NL, SE).
2. Missing card_scheme filter: "NexPay transactions" = card_scheme == 'NexPay', not just merchant filter.
3. Wrong date range: Verify month boundaries from the table. September–October is days 244–304.
4. Sorting direction: Always sort ascending by eur_amount unless the question says descending.
5. Rounding display: Use f"{val:.2f}" to show trailing zeros when formatting strings.
6. NaN in email_address: groupby automatically excludes NaN keys — no need to drop them explicitly.