Skip to navigation
Data Organization

Preparing Your Data

View as Markdown

Converting From a Flat Transaction Table

Most raw financial datasets start as a single large CSV with one row per transaction. Here is a systematic procedure to convert it into the required format.

Original flat table example:

transaction_id, account_id, merchant_id, amount, timestamp, channel, is_fraud
TXN001, ACC042, MRC019, 149.99, 2024-01-15 14:32:00, online, 0
TXN002, ACC007, MRC019, 4823.50, 2024-01-15 14:41:00, in_store, 1

Step 1 — Assign zero-based integer IDs:

import pandas as pd
txn = pd.read_csv("transactions.csv")
# Re-index accounts and merchants to 0-based integers
account_ids  = {v: i for i, v in enumerate(txn.account_id.unique())}
merchant_ids = {v: i for i, v in enumerate(txn.merchant_id.unique())}
txn["src"] = txn.account_id.map(account_ids)
txn["dst"] = txn.merchant_id.map(merchant_ids)

Step 2 — Write node files (one row per unique entity):

import os
os.makedirs("my_data/nodes", exist_ok=True)
os.makedirs("my_data/edges", exist_ok=True)
# Account node features: aggregate per-account statistics
acct_features = (
    txn.groupby("account_id")
    .agg(avg_amount=("amount", "mean"),
         txn_count=("transaction_id", "count"),
         fraud_rate=("is_fraud", "mean"))
    .reset_index()
    .sort_values("account_id")    # sort so row index = account_id after mapping
)
# Drop the string ID column — only keep numeric features
acct_features = acct_features[["avg_amount", "txn_count", "fraud_rate"]]
acct_features.to_csv("my_data/nodes/account.csv", index=False)
# Merchant node features
merch_features = (
    txn.groupby("merchant_id")
    .agg(avg_amount=("amount", "mean"),
         chargeback_rate=("is_fraud", "mean"))
    .reset_index()
    .sort_values("merchant_id")
)
merch_features[["avg_amount", "chargeback_rate"]].to_csv(
    "my_data/nodes/merchant.csv", index=False)

Step 3 — Write edge files:

# Connectivity
txn[["src", "dst"]].to_csv("my_data/edges/account_transacts_merchant.csv", index=False)
# Labels
txn[["is_fraud"]].rename(columns={"is_fraud": "fraud"}).to_csv(
    "my_data/edges/account_transacts_merchant_label.csv", index=False)
# Edge attributes: per-transaction features
# Encode the 'channel' categorical with one-hot
channel_ohe = pd.get_dummies(txn.channel, prefix="Channel")
attr = pd.concat([txn[["amount"]], channel_ohe], axis=1)
attr.to_csv("my_data/edges/account_transacts_merchant_attr.csv", index=False)

Step 4 — Create the temporal test split:

# Split by date: last 20% of days go to test
txn["date"] = pd.to_datetime(txn.timestamp).dt.date
cutoff = sorted(txn.date.unique())[int(len(txn.date.unique()) * 0.8)]
train = txn[txn.date <= cutoff]
test  = txn[txn.date >  cutoff]
# Repeat Steps 1–3 for test, writing to my_data/test_gnn/

Feature Engineering Guidelines

The GNN works with whatever numeric features you provide. The following practices will directly improve model quality.

Normalise continuous features. GNNs are sensitive to feature scale. Standardise (subtract mean, divide by standard deviation) or min-max normalise all continuous features before writing the CSV.

from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
numeric_cols = ["Balance", "AvgMonthlyTxn", "OverdraftCount"]
node_df[numeric_cols] = scaler.fit_transform(node_df[numeric_cols])
# Save the scaler — you will need it to normalise test data consistently
import joblib
joblib.dump(scaler, "account_scaler.pkl")

Encode categoricals numerically. The pipeline reads only numeric values. Convert all categorical columns. Binary encoding is more compact than one-hot for higher-cardinality fields (e.g. 4 types → 2 bits instead of 4 columns):

import numpy as np
# Binary encoding — more compact than one-hot for fields with many categories
def binary_encode(series, prefix):
    n_bits = int(np.ceil(np.log2(series.max() + 1))) or 1
    return pd.DataFrame(
        {f"{prefix}_bit{i}": ((series.astype(int) >> i) & 1).astype(float)
         for i in range(n_bits)}
    )
node_df = pd.concat([
    node_df.drop(columns=["AccountType", "Region"]),
    binary_encode(node_df["AccountType"], "AccountType"),
    binary_encode(node_df["Region"], "Region"),
], axis=1)
node_df = node_df.astype(float)

Cap outliers before normalisation. A single extreme transaction amount can dominate the embedding. Clip to a reasonable percentile (e.g., 99th) before normalising.

txn["amount"] = txn.amount.clip(upper=txn.amount.quantile(0.99))

Aggregate time features. For time-of-day, use cyclic encoding (sine/cosine) or equally-spaced bins. Do not pass raw Unix timestamps.

import numpy as np
txn["hour_sin"] = np.sin(2 * np.pi * txn.hour / 24)
txn["hour_cos"] = np.cos(2 * np.pi * txn.hour / 24)