Building a Graph From Tabular Relationship Data

#ai #machinelearning #tutorial #llm

Almost every graph starts life as relational tables. The conversion is mechanical once three decisions are made, and one of the three — id remapping — is a silent correctness bug rather than a matter of taste.

Deciding what is a node

Start with three tables: customers (customer_id, region, signup_date, tenure_days), products (product_id, category, price), and orders (order_id, customer_id, product_id, amount, ordered_at). The rule that resolves nearly every case:

A table with a primary key that other tables point at is a node type. A table whose whole job is to link two keys is an edge type. So customers and products are nodes, and orders are edges — even though orders has its own primary key. The order id is not an entity you want to reason about; it is an identifier for a relationship.

The harder case is a repeated categorical column such as region. It can stay a customer feature, or it can become a node type with a customer–region edge. The test is behavioural, not aesthetic: do you want information to flow between rows that share this value? As a feature, region is a tag on each customer and nothing more. As a node, it creates a two-hop path between every pair of customers in the same region, so their representations start blending. If a region contains 400,000 customers, that node is a hub through which everything mixes, which is usually a way of turning four hundred thousand distinct customers into one regional average. Keep high-cardinality-of-membership categoricals as features; promote a category to a node when its membership is small and meaningful.

If you end up with more than one node type, the model has to change too — see heterogeneous graph neural networks.

The id remapping nobody warns you about

Graph libraries do not store your ids. They store a node feature matrix and an edge index of integer positions into it, because a message-passing layer is a gather over rows of a dense array. So node ids must be contiguous integers from 0 to n−1, per node type.

Real keys never are. A UUID is not an integer. An auto-increment integer key with deletions has gaps — and if you pass ids up to 9,000,000 for 40,000 customers, you have either allocated a feature matrix with 8,960,000 zero rows or produced an index-out-of-range error, depending on the library. Both are silent in the sense that neither tells you what the actual mistake was.

Build the mapping explicitly, keep it, and save it. Without the reverse mapping you cannot interpret a single prediction: the model returns node 3,317, and only the mapping says who that is.

Build it

Load the tables and fix the join keys. Drop order rows whose customer or product does not exist — a dangling foreign key becomes either a phantom node or a crash, and pruning it first is the only way to know how many there were.

import pandas as pd
import numpy as np

customers = pd.read_csv("customers.csv")
products  = pd.read_csv("products.csv")
orders    = pd.read_csv("orders.csv", parse_dates=["ordered_at"])

before = len(orders)
orders = orders[
    orders.customer_id.isin(customers.customer_id)
    & orders.product_id.isin(products.product_id)
]
print("dropped", before - len(orders), "orders with dangling keys")

Build a contiguous index per node type. Sort first so the mapping is deterministic across runs — otherwise a re-run produces a different node 3,317 and every saved artefact silently disagrees with every other.

customers = customers.sort_values("customer_id").reset_index(drop=True)
products  = products.sort_values("product_id").reset_index(drop=True)

cust_index = pd.Series(customers.index.values, index=customers.customer_id)
prod_index = pd.Series(products.index.values,  index=products.product_id)

src = cust_index.loc[orders.customer_id].to_numpy()
dst = prod_index.loc[orders.product_id].to_numpy()
edge_index = np.vstack([src, dst])          # shape (2, num_edges)
print(edge_index.shape, "edges over",
      len(customers), "customers and", len(products), "products")

Build the feature matrices, one per node type, in that same row order. The row order is the contract: row i of the feature matrix must be the node the mapping calls i.

cust_x = pd.concat([
    customers[["tenure_days"]].astype("float32"),
    pd.get_dummies(customers.region, prefix="reg").astype("float32"),
], axis=1).to_numpy()

prod_x = pd.concat([
    products[["price"]].astype("float32"),
    pd.get_dummies(products.category, prefix="cat").astype("float32"),
], axis=1).to_numpy()

assert cust_x.shape[0] == len(customers)
assert prod_x.shape[0] == len(products)

Add reverse edges and edge attributes. Messages flow along stored edges only; without the reverse direction products never hear from their buyers.

rev_index = edge_index[::-1].copy()          # product -> customer

edge_attr = orders[["amount"]].astype("float32").to_numpy()
edge_time = orders.ordered_at.astype("int64").to_numpy() // 10**9   # unix secs

Save the graph and the mapping together. They are one artefact. A saved edge index whose mapping was regenerated later is worse than no graph, because it still loads.

np.savez_compressed(
    "graph.npz",
    edge_index=edge_index, rev_index=rev_index,
    edge_attr=edge_attr, edge_time=edge_time,
    cust_x=cust_x, prod_x=prod_x,
)
cust_index.to_frame("row").to_parquet("customer_id_to_row.parquet")
prod_index.to_frame("row").to_parquet("product_id_to_row.parquet")

That is a loadable heterogeneous graph. Feeding it to a specific library is then a matter of assigning these arrays to that library’s container type; the arrays themselves are the portable part, and keeping the pipeline framework-agnostic up to this point is worth the small amount of extra code.

Direction, weights and time

Direction. Store edges directed and materialise the reverse explicitly, as above, rather than storing undirected edges and hoping the library symmetrises. Two directions with separate weights can learn two different functions; one symmetric edge cannot.

Weights. A customer who ordered a product forty times is forty rows. Collapsing them into one edge with a count preserves the information and shrinks the graph, and it changes what mean aggregation computes — forty parallel edges give that product forty times the weight in a mean, whereas one weighted edge does not unless the aggregation is weighted. Decide which you mean.

Time. Keep edge_time even if the first model ignores it. It is what lets you build a leak-free temporal split later, and reconstructing it after the fact usually means rebuilding the whole graph. If timing is the signal rather than a filter, the architecture changes too — see temporal graph networks.

Checking the graph before you train on it

Degree distribution. Print the min, median, 99th percentile and max degree per node type. A max degree in the millions is a hub that will dominate every batch; a median of zero means most of your nodes are isolated and the graph is not doing anything.
Isolated node count. Nodes with no edges get a representation from their own features and nothing else. That is fine, as long as you know how many there are and did not intend otherwise.
Connected components. One giant component plus dust is normal. A hundred equal-sized components usually means a join key was wrong or a filter was applied too early.
Self-loops and duplicate edges. Both are legal and both change aggregation. Count them deliberately rather than discovering them through a training curve.
Cycles, if the graph is meant to be a DAG. A hierarchy built from a parent-id column can contain a cycle from a single bad row, and every consumer that needs a topological order will fail on it — cycle detection is the check.