Applicable_Fee_IDs

Applicable Fee IDs Skill

Dataset Files

• fees.json: 1000 fee rules with matching criteria
• merchant_data.json: merchant account_type, capture_delay, MCC, acquirers (list of dicts)
• payments.csv: transactions with columns: merchant, card_scheme, year, day_of_year, is_credit, eur_amount, issuing_country, acquirer_country, aci, has_fraudulent_dispute
• acquirer_countries.csv: maps acquirer name to country_code (rarely needed—payments.csv already has acquirer_country)

Fee Rule Structure (fees.json)

Each fee has: ID, card_scheme, account_type (list), capture_delay, monthly_fraud_level, monthly_volume, merchant_category_code (list), is_credit, aci (list), fixed_amount, rate, intracountry

Critical matching rule: An empty list [] for account_type, aci, or merchant_category_code means "applies to all values"—same as null. There are NO null values for these list fields in fees.json; only empty lists and populated lists exist.

A fee rule applies when ALL of these match:

Question Types & Approaches

Type 1: Simple Attribute Filter

Pattern: "What fee IDs apply to account_type = X and aci = Y?"

Filter fees.json directly—no payments data needed:

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

account_type = 'F'
aci = 'A'

result = [
    fee['ID'] for fee in fees
    if (not fee['account_type'] or account_type in fee['account_type'])
    and (not fee['aci'] or aci in fee['aci'])
]
print(', '.join(str(x) for x in sorted(result)))

Apply the same pattern for other simple field combinations (card_scheme, is_credit, etc.).

Type 2: Merchant + Date (Day/Month/Year) → Applicable Fee IDs

Pattern: "For the Nth day of 2023, what Fee IDs apply to [Merchant]?" or "What Fee IDs applied in [Month] 2023?"

Step 1: Get merchant characteristics

with open('merchant_data.json') as f:
    merchants = json.load(f)  # list of dicts
merchant = next(m for m in merchants if m['merchant'] == 'Belles_cookbook_store')
account_type = merchant['account_type']      # e.g. 'R'
mcc = merchant['merchant_category_code']     # e.g. 5942
capture_delay_raw = merchant['capture_delay']  # e.g. '1', '7', 'immediate', 'manual'

Step 2: Map capture_delay to fee rule format

def map_capture_delay(raw):
    try:
        days = int(raw)
        if days < 3: return '<3'
        elif days <= 5: return '3-5'
        else: return '>5'
    except ValueError:
        return raw  # already 'immediate', 'manual', '<3', '3-5', '>5'
capture_delay = map_capture_delay(capture_delay_raw)

Step 3: Determine the time window and filter payments

import pandas as pd, datetime
payments = pd.read_csv('payments.csv')

# For a specific day (day_of_year=200, year=2023):
txns = payments[(payments['merchant']=='Belles_cookbook_store') &
                (payments['year']==2023) & (payments['day_of_year']==200)]

# For October 2023:
oct_start = datetime.date(2023, 10, 1).timetuple().tm_yday   # 274
oct_end   = datetime.date(2023, 10, 31).timetuple().tm_yday  # 304
txns = payments[(payments['merchant']=='Belles_cookbook_store') &
                (payments['year']==2023) &
                (payments['day_of_year'] >= oct_start) &
                (payments['day_of_year'] <= oct_end)]

# For all of 2023:
txns = payments[(payments['merchant']=='Belles_cookbook_store') & (payments['year']==2023)]

Step 4: Compute monthly volume & fraud categories for the relevant month(s)

Monthly stats must be computed for the calendar month(s) containing the transactions. For a specific day query, compute for the month that day falls in. For month-wide query, compute for that month. For year-wide query, compute per-month and union results.

def volume_category(eur):
    if eur < 100_000:   return '<100k'
    elif eur < 1_000_000: return '100k-1m'
    elif eur < 5_000_000: return '1m-5m'
    else:               return '>5m'

def fraud_category(rate_pct):
    if rate_pct < 7.2:    return '<7.2%'
    elif rate_pct < 7.7:  return '7.2%-7.7%'
    elif rate_pct <= 8.3: return '7.7%-8.3%'
    else:                 return '>8.3%'

# For a single month:
total_vol = txns['eur_amount'].sum()
fraud_vol = txns[txns['has_fraudulent_dispute'] == True]['eur_amount'].sum()
fraud_rate = (fraud_vol / total_vol * 100) if total_vol > 0 else 0
vol_cat = volume_category(total_vol)
fraud_cat = fraud_category(fraud_rate)

Step 5: Identify applicable fee IDs

def fee_applies(fee, cs, is_credit, aci, intracountry, vol_cat, fraud_cat,
                account_type, mcc, capture_delay):
    if fee['card_scheme'] is not None and fee['card_scheme'] != cs:
        return False
    if fee['account_type'] and account_type not in fee['account_type']:
        return False
    if fee['capture_delay'] is not None and fee['capture_delay'] != capture_delay:
        return False
    if fee['monthly_fraud_level'] is not None and fee['monthly_fraud_level'] != fraud_cat:
        return False
    if fee['monthly_volume'] is not None and fee['monthly_volume'] != vol_cat:
        return False
    if fee['merchant_category_code'] and mcc not in fee['merchant_category_code']:
        return False
    if fee['is_credit'] is not None and fee['is_credit'] != is_credit:
        return False
    if fee['aci'] and aci not in fee['aci']:
        return False
    if fee['intracountry'] is not None and bool(fee['intracountry']) != intracountry:
        return False
    return True

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

applicable = set()
for _, row in txns.iterrows():
    intracountry = (row['issuing_country'] == row['acquirer_country'])
    for fee in fees:
        if fee_applies(fee, row['card_scheme'], row['is_credit'], row['aci'],
                       intracountry, vol_cat, fraud_cat, account_type, mcc, capture_delay):
            applicable.add(fee['ID'])

print(', '.join(str(x) for x in sorted(applicable)))

For year-wide queries, loop over each month (1–12), compute monthly stats for that month's transactions, then union all applicable fee IDs:

applicable = set()
for month in range(1, 13):
    m_start = datetime.date(2023, month, 1).timetuple().tm_yday
    m_end = (datetime.date(2023, month+1, 1) - datetime.timedelta(days=1)).timetuple().tm_yday \
            if month < 12 else 365
    month_txns = txns[(txns['day_of_year'] >= m_start) & (txns['day_of_year'] <= m_end)]
    if month_txns.empty:
        continue
    total_vol = month_txns['eur_amount'].sum()
    fraud_vol = month_txns[month_txns['has_fraudulent_dispute']==True]['eur_amount'].sum()
    fraud_rate = (fraud_vol / total_vol * 100) if total_vol > 0 else 0
    v_cat = volume_category(total_vol)
    f_cat = fraud_category(fraud_rate)
    for _, row in month_txns.iterrows():
        intracountry = (row['issuing_country'] == row['acquirer_country'])
        for fee in fees:
            if fee_applies(fee, row['card_scheme'], row['is_credit'], row['aci'],
                           intracountry, v_cat, f_cat, account_type, mcc, capture_delay):
                applicable.add(fee['ID'])

Type 3: Reverse Lookup — Which Merchants Does Fee X Affect?

Pattern: "Which merchants were affected by Fee ID 709 in 2023?"

fee = next(f for f in fees if f['ID'] == 709)
# Filter transactions matching the fee's criteria
subset = payments[payments['year'] == 2023].copy()
if fee['card_scheme']:
    subset = subset[subset['card_scheme'] == fee['card_scheme']]
if fee['is_credit'] is not None:
    subset = subset[subset['is_credit'] == fee['is_credit']]
if fee['aci']:
    subset = subset[subset['aci'].isin(fee['aci'])]
if fee['intracountry'] is not None:
    subset = subset[(subset['issuing_country'] == subset['acquirer_country']) == bool(fee['intracountry'])]
# merchant-level filters (account_type, mcc, capture_delay) require joining merchant_data
merchants_affected = sorted(subset['merchant'].unique())
print(', '.join(merchants_affected))

If the fee has account_type, mcc, or capture_delay constraints, also filter merchants from merchant_data before joining.

Output Format

• Applicable fee IDs: comma-separated integers in ascending order: 3, 4, 5, 7, ...
• Empty result: empty string ""
• If question is irrelevant: Not Applicable

Common Pitfalls

• Empty list ≠ null for lists: In fees.json, empty [] for account_type/aci/merchant_category_code means "all"—treat it the same as null
• capture_delay conversion: Merchant's numeric delay (e.g., "1", "7") must be mapped to fee rule strings ("<3", "3-5", ">5")
• Monthly stats are required: Fees with monthly_fraud_level or monthly_volume constraints only apply when the merchant's actual monthly stats match—never skip this calculation
• intracountry is float in fees.json: 0.0 = international, 1.0 = domestic; compare with bool(fee['intracountry'])
• merchant_data.json is a list: Access with next(m for m in merchants if m['merchant'] == name), not dict-style lookup
• payments.csv has acquirer_country directly: No need to join acquirer_countries.csv for intracountry calculation