average-transaction-value-stats

# Average Transaction Value Stats — dabstep Dataset ## Dataset Overview **payments.csv** is the primary dataset. Key columns: - `merchant`: Merchant name (e.g., `Crossfit_Hanna`, `Belles_cookbook_store`, `Golfclub_Baron_Friso`, `Martinis_Fine_Steakhouse`, `Rafa_AI`) - `card_scheme`: Payment network (e.g., `NexPay`, `GlobalCard`, `SwiftCharge`, `TransactPlus`) - `year`: Transaction year (all data is 2023) - `day_of_year`: Day 1–365 (no date column — months must be derived from this) - `eur_amount`: Transaction amount in euros - `shopper_interaction`: `Ecommerce` or `POS` - `issuing_country`: Card-issuing country code (SE, NL, LU, IT, BE, FR, GR, ES) - `acquirer_country`: Acquiring bank country code - `aci`: Authorization Characteristics Indicator (A–G) - `email_address`: Hashed shopper email (can be NaN) ## Critical: Apply ALL Filters Specified in the Question When a question says **"merchant X's card_scheme Y transactions"**, filter by **BOTH** `merchant` AND `card_scheme`. Forgetting one filter is the most common error. ```python df_filtered = df[ (df['merchant'] == 'Merchant_Name') & (df['card_scheme'] == 'SchemeName') & (df['day_of_year'] >= start_day) & (df['day_of_year'] <= end_day) ] ``` ## Month → day_of_year Conversion (2023, non-leap year) | Month | day_of_year range | |-----------|-------------------| | January | 1 – 31 | | February | 32 – 59 | | March | 60 – 90 | | April | 91 – 120 | | May | 121 – 151 | | June | 152 – 181 | | July | 182 – 212 | | August | 213 – 243 | | September | 244 – 273 | | October | 274 – 304 | | November | 305 – 334 | | December | 335 – 365 | Use the **start of the first month** and **end of the last month** as inclusive bounds. ## Standard Analysis Pattern ```python import pandas as pd df = pd.read_csv('/path/to/payments.csv') # Step 1: Apply all filters df_filtered = df[ (df['merchant'] == 'Merchant_Name') & # if specified (df['card_scheme'] == 'SchemeName') & # if specified (df['day_of_year'] >= START_DAY) & # if date range given (df['day_of_year'] <= END_DAY) ] # Step 2: Group and compute mean result = ( df_filtered .groupby('grouping_column')['eur_amount'] .mean() .round(2) .sort_values(ascending=True) # always ascending unless stated otherwise ) # Step 3: Format output answer = [f"{k}: {v:.2f}" for k, v in result.items()] ``` ## Output Format The required format `[grouping_i: amount_i, ]` means a **Python list of strings**: ```python ['BE: 86.39', 'SE: 91.89', 'ES: 92.83', ...] ``` - Each element: `"GROUPING_VALUE: AMOUNT"` (colon-space separator) - When grouping by country: use the country code - Amounts rounded to **2 decimal places** (e.g., `101.00` not `101.0`) - Sorted in **ascending order** by amount **Do NOT** return tuples like `[('BE', 86.39), ...]`. ## Special Case: "Average per Unique X" When the question says **"average transaction amount per unique email"** (or per unique card, etc.): ```python # Correct: mean of per-group means per_group_mean = df.groupby('email_address')['eur_amount'].mean() answer = round(per_group_mean.mean(), 3) ``` This is NOT `total_amount / count_unique_emails`. The correct interpretation is: 1. For each unique email, compute their average transaction amount 2. Take the average of those per-email averages ## Validation Checklist Before submitting your answer: 1. Did you filter by **all** criteria mentioned (merchant, card_scheme, date range)? 2. Did you use the correct `day_of_year` bounds for the requested months? 3. Is the output sorted **ascending** by amount? 4. Are amounts rounded to **2 decimal places** and formatted as strings with `:`? 5. For "per unique X" questions: did you use mean-of-means, not total/count?