total-fees-calculation

# Total Fees Calculation These questions ask: *"What are the total fees (in euros) that [Merchant] paid/should pay in [time period]?"* The answer is computed by: filtering transactions → matching each to exactly one fee rule → summing `fee = fixed_amount + rate * eur_amount / 10000`. **Do NOT read manual.md or payments-readme.md** — all required knowledge is in this skill. Skip directly to the algorithm. ## Dataset Files | File | Purpose | |------|---------| | `payments.csv` | One row per transaction; key columns: `merchant`, `year`, `day_of_year`, `card_scheme`, `is_credit`, `eur_amount`, `issuing_country`, `acquirer_country`, `aci`, `has_fraudulent_dispute`, `is_refused_by_adyen` | | `merchant_data.json` | Merchant objects: `merchant`, `account_type`, `capture_delay`, `merchant_category_code` | | `fees.json` | ~1000 fee rule objects (see matching logic below) | ## Complete Algorithm Run this in one code block — adapt the merchant name, year, and time filter for the question: ```python import json, pandas as pd from datetime import date, timedelta # ── Load data ────────────────────────────────────────────────────────── with open('fees.json') as f: fees = json.load(f) with open('merchant_data.json') as f: merchant_data = json.load(f) merchant_map = {m['merchant']: m for m in merchant_data} payments = pd.read_csv('payments.csv') def doy_to_month(doy, year): return (date(year, 1, 1) + timedelta(days=doy - 1)).month payments['month'] = payments.apply(lambda r: doy_to_month(r['day_of_year'], r['year']), axis=1) # ── Merchant attributes ──────────────────────────────────────────────── merchant_name = 'MERCHANT_NAME' # ← fill in year = 2023 # ← fill in m = merchant_map[merchant_name] merchant_at = m['account_type'] merchant_mcc = m['merchant_category_code'] def map_capture_delay(cd): if cd in ('immediate', 'manual'): return cd n = int(cd) return '<3' if n < 3 else ('3-5' if n <= 5 else '>5') merchant_cd = map_capture_delay(m['capture_delay']) # ── Helper: parse range strings ──────────────────────────────────────── def parse_range(s, pct=False): def val(v): v = v.strip().replace('%', '') if v.endswith('m'): return float(v[:-1]) * 1e6 if v.endswith('k'): return float(v[:-1]) * 1e3 return float(v) / 100.0 if pct else float(v) if s.startswith('<'): return (0.0, val(s[1:])) if s.startswith('>'): return (val(s[1:]), float('inf')) a, b = s.split('-') return (val(a), val(b)) # ── Helper: find the best matching fee rule for one transaction ───────── def find_rule(tx, monthly_volume, monthly_fraud_rate): ic = (tx['issuing_country'] == tx['acquirer_country']) candidates = [] for rule in fees: if rule['card_scheme'] != tx['card_scheme']: continue # Empty list [] means "applies to all" if rule['account_type'] and merchant_at not in rule['account_type']: continue if rule['merchant_category_code'] and merchant_mcc not in rule['merchant_category_code']: continue if rule['aci'] and tx['aci'] not in rule['aci']: continue # None means "applies to all" if rule['capture_delay'] is not None and rule['capture_delay'] != merchant_cd: continue if rule['is_credit'] is not None and rule['is_credit'] != tx['is_credit']: continue if rule['intracountry'] is not None: if rule['intracountry'] != (1.0 if ic else 0.0): continue if rule['monthly_fraud_level'] is not None: lo, hi = parse_range(rule['monthly_fraud_level'], pct=True) if not (lo <= monthly_fraud_rate < hi): continue if rule['monthly_volume'] is not None: lo, hi = parse_range(rule['monthly_volume']) if not (lo <= monthly_volume < hi): continue candidates.append(rule) if not candidates: return None # Pick the most specific rule (most non-null / non-empty fields) return max(candidates, key=lambda r: sum([ bool(r['account_type']), r['capture_delay'] is not None, bool(r['merchant_category_code']), r['is_credit'] is not None, bool(r['aci']), r['intracountry'] is not None, r['monthly_fraud_level'] is not None, r['monthly_volume'] is not None ])) # ── Filter target transactions (exclude refused) ─────────────────────── # CHOOSE ONE of the three patterns below: # Pattern A — specific day (e.g. "For the 10th of 2023"): target_day = 10 # ← fill in txs = payments[(payments['merchant'] == merchant_name) & (payments['year'] == year) & (payments['day_of_year'] == target_day) & (payments['is_refused_by_adyen'] == False)] # Compute monthly stats for the calendar month that contains this day target_month = doy_to_month(target_day, year) # Pattern B — specific month (e.g. "In April 2023"): # target_month = 4 # ← fill in # txs = payments[(payments['merchant'] == merchant_name) & # (payments['year'] == year) & # (payments['month'] == target_month) & # (payments['is_refused_by_adyen'] == False)] # Pattern C — full year (e.g. "In 2023"): # txs = payments[(payments['merchant'] == merchant_name) & # (payments['year'] == year) & # (payments['is_refused_by_adyen'] == False)] # ── Monthly stats (use ALL merchant transactions, including refused) ─── # For Pattern A or B: compute stats for the single month all_merchant = payments[(payments['merchant'] == merchant_name) & (payments['year'] == year)] monthly_txs = all_merchant[all_merchant['month'] == target_month] monthly_volume = monthly_txs['eur_amount'].sum() fraud_volume = monthly_txs[monthly_txs['has_fraudulent_dispute'] == True]['eur_amount'].sum() monthly_fraud_rate = fraud_volume / monthly_volume if monthly_volume > 0 else 0 # ── Calculate and sum fees ───────────────────────────────────────────── # For Pattern A or B (single month stats): total_fee = 0.0 for _, row in txs.iterrows(): rule = find_rule(row.to_dict(), monthly_volume, monthly_fraud_rate) if rule: total_fee += rule['fixed_amount'] + rule['rate'] * row['eur_amount'] / 10000 print(round(total_fee, 2)) # For Pattern C (full year) — replace the block above with: # total_fee = 0.0 # for month in range(1, 13): # m_txs = all_merchant[all_merchant['month'] == month] # mv = m_txs['eur_amount'].sum() # fv = m_txs[m_txs['has_fraudulent_dispute'] == True]['eur_amount'].sum() # mfr = fv / mv if mv > 0 else 0 # month_txs = txs[txs['month'] == month] # for _, row in month_txs.iterrows(): # rule = find_rule(row.to_dict(), mv, mfr) # if rule: # total_fee += rule['fixed_amount'] + rule['rate'] * row['eur_amount'] / 10000 # print(round(total_fee, 2)) ``` ## Key Rules **Rule selection** — when multiple rules match, pick the **most specific** one (the rule with the most non-null / non-empty fields). Never use the first/lowest-ID rule. **Empty list `[]`** in `account_type`, `merchant_category_code`, `aci` means the rule applies to ALL values (same as `null`). **No matching rule → fee = 0** for that transaction. This is expected; many transaction-type combinations are not covered by the fee schedule. **Monthly stats scope** — always compute `monthly_volume` and `monthly_fraud_rate` over the **full natural calendar month** using **all** merchant transactions (including refused ones), even when the question asks about a single day within that month. **intracountry** — compute directly as `issuing_country == acquirer_country` from `payments.csv`; do not look up the acquirer's country from `merchant_data.json`. **Refused transactions** — exclude `is_refused_by_adyen == True` from the fee-paying transactions, but include them when computing monthly stats. ## Expected Output A single number rounded to 2 decimal places. If no applicable answer exists, output `Not Applicable`.