Average_Fee_Estimation

Average Fee Estimation

Core Formula

fee = fixed_amount + rate * transaction_value / 10000

All values in EUR. The average fee is the arithmetic mean of fees computed across all matching rules in fees.json.

Data Files

• fees.json: 1000 fee rules (card_scheme, account_type, merchant_category_code, aci, is_credit, capture_delay, monthly_fraud_level, monthly_volume, intracountry, fixed_amount, rate)
• merchant_category_codes.csv: columns mcc (int) and description (string) — use this to look up MCC code from a description

Null / Empty = "Applies to All"

For every field in fees.json:

• Scalar fields (is_credit, capture_delay, intracountry, etc.): null → rule applies to all values
• List fields (account_type, merchant_category_code, aci): empty list [] → rule applies to all values

When the question specifies a filter value, include a rule if the rule's field is null/empty OR the field explicitly contains that value.

Question Type Patterns

Type 1: Credit/debit filter only

"For credit transactions, what is the average fee that card scheme X would charge for Y EUR?"

import json
with open('fees.json') as f:
    fees = json.load(f)

matching = [r for r in fees
            if r['card_scheme'] == 'X'
            and (r['is_credit'] is None or r['is_credit'] == True)]  # True for credit

fee_values = [r['fixed_amount'] + r['rate'] * Y / 10000 for r in matching]
answer = round(sum(fee_values) / len(fee_values), 6)

Type 2: Account type filter

"For account type T, what is the average fee that card scheme X would charge for Y EUR?"

matching = [r for r in fees
            if r['card_scheme'] == 'X'
            and (r['account_type'] == [] or 'T' in r['account_type'])]

fee_values = [r['fixed_amount'] + r['rate'] * Y / 10000 for r in matching]
answer = round(sum(fee_values) / len(fee_values), 6)

Type 3: Account type + MCC description filter

"For account type T and the MCC description: [description], what is the average fee that card scheme X would charge for Y EUR?"

First look up the MCC code:

import pandas as pd
mcc_df = pd.read_csv('merchant_category_codes.csv')
# Match by substring or full description
mcc_code = mcc_df[mcc_df['description'].str.contains('keyword', case=False)]['mcc'].values[0]

Then filter:

matching = [r for r in fees
            if r['card_scheme'] == 'X'
            and (r['account_type'] == [] or 'T' in r['account_type'])
            and (r['merchant_category_code'] == [] or mcc_code in r['merchant_category_code'])]

fee_values = [r['fixed_amount'] + r['rate'] * Y / 10000 for r in matching]
answer = round(sum(fee_values) / len(fee_values), 6)

Type 4: Cheapest / most expensive card scheme

"In the average scenario, which card scheme would provide the cheapest/most expensive fee for Y EUR?"

Average ALL rules per card scheme (no filtering):

from collections import defaultdict
scheme_fees = defaultdict(list)
for r in fees:
    fee = r['fixed_amount'] + r['rate'] * Y / 10000
    scheme_fees[r['card_scheme']].append(fee)

scheme_avg = {s: sum(fl)/len(fl) for s, fl in scheme_fees.items()}
cheapest = min(scheme_avg, key=scheme_avg.get)
most_expensive = max(scheme_avg, key=scheme_avg.get)

Implementation Notes

1. Empty list [] vs null: In fees.json, list fields use [] (not null) to mean "all". Check with r['account_type'] == [], not r['account_type'] is None.

2. MCC lookup: Use str.contains() with a distinctive keyword from the description. The mcc column is an integer.

3. No other data files needed: For average fee questions, only fees.json and (for MCC) merchant_category_codes.csv are required. Do not filter by payments.csv data.

4. Output format: Round to 6 decimal places. If the question asks for a card scheme name (cheapest/most expensive), return the scheme name string.

5. "Not Applicable" rule: Only use if the question is genuinely unanswerable (e.g., no rules match, undefined scenario). For "average scenario" questions, average ALL rules across the card scheme without additional filtering.

Verified Examples