import pandas as pd, json fees = json.load(open('fees.json')) df = pd.read_csv('payments.csv') merch = {m['merchant']: m for m in json.load(open('merchant_data.json'))} m = merch['Belles_cookbook_store'] print("merchant:", m) # month containing day 10 of 2023 = January 2023 (days 1..31) jan = df[(df['merchant']=='Belles_cookbook_store') & (df['year']==2023) & (df['day_of_year']<=31)] total_vol = jan['eur_amount'].sum() fraud_vol = jan.loc[jan['has_fraudulent_dispute']==True,'eur_amount'].sum() fraud_ratio = fraud_vol/total_vol print(f"Jan2023: total_vol={total_vol:.2f} fraud_vol={fraud_vol:.2f} ratio={fraud_ratio:.4%}") # volume bucket / fraud bucket helpers def vol_bucket(v): if v < 100_000: return "<100k" if v < 1_000_000: return "100k-1m" if v < 5_000_000: return "1m-5m" return ">5m" def fraud_matches(rule_f, ratio_pct): if rule_f is None: return True pct = ratio_pct*100 if rule_f.startswith('<'): return pct < float(rule_f[1:-1]) if rule_f.startswith('>'): return pct > float(rule_f[1:-1]) lo, hi = rule_f[:-1].split('%-') return float(lo) <= pct <= float(hi) mv = vol_bucket(total_vol) print("volume bucket:", mv) def fee_applies(f, tx, merch): if f['card_scheme'] != tx['card_scheme']: return False at = f['account_type'] if at and merch['account_type'] not in at: return False # capture_delay: merchant '1' -> <3 ; '7' -> >5 ; else exact cd = f['capture_delay'] if cd is not None: md = merch['capture_delay'] if md in ('1','2'): ok = (cd == '<3') elif md in ('7',): ok = (cd == '>5') else: ok = (cd == md) if not ok: return False if not fraud_matches(f['monthly_fraud_level'], fraud_ratio): return False mvf = f['monthly_volume'] if mvf is not None and mvf != mv: return False mcc = f['merchant_category_code'] if mcc and merch['merchant_category_code'] not in mcc: return False ic = f['is_credit'] if ic is not None and bool(ic) != bool(tx['is_credit']): return False aci = f['aci'] if aci and tx['aci'] not in aci: return False intr = f['intracountry'] if intr is not None: domestic = (tx['issuing_country'] == tx['acquirer_country']) if bool(intr) != domestic: return False return True sub = df[(df['merchant']=='Belles_cookbook_store') & (df['year']==2023) & (df['day_of_year']==10)] applicable = set() for _, tx in sub.iterrows(): for f in fees: if fee_applies(f, tx, m): applicable.add(f['ID']) print("\nApplicable fee IDs (day 10, 2023):", sorted(applicable)) # sanity: also whole-year-2023 for the merchant (in case of intent ambiguity) suby = df[(df['merchant']=='Belles_cookbook_store') & (df['year']==2023)] appy = set() for _, tx in suby.iterrows(): for f in fees: if fee_applies(f, tx, m): appy.add(f['ID']) print("Applicable fee IDs (whole 2023):", sorted(appy)) # show the matching rules detail print("\nMatching fee rules:") for f in sorted(fees, key=lambda x: x['ID']): if f['ID'] in applicable: print(f['ID'], f['card_scheme'], 'acct=',f['account_type'], 'cd=',f['capture_delay'], 'fraud=',f['monthly_fraud_level'], 'vol=',f['monthly_volume'], 'credit=',f['is_credit'], 'aci=',f['aci'], 'intr=',f['intracountry'])

tasks/dabstep/dabstep-fee-ids-belles-10-jan-2023