Data that doesn't match across sites

In short: when several sites collect "the same" data, the columns share names and almost nothing else. Before any model, write one data dictionary, map every site to it in code, and check the result per site. It is slow, dull work, and it decides whether the model learns biology or bookkeeping.

For a couple of years I worked with a research consortium of more than ten institutions across several countries. The question was how genes, prenatal adversity and the early-life environment relate to developmental outcomes. Each partner ran its own cohort, with its own questionnaires, its own lab, its own way of writing things down. Part of my job was building federated machine learning so the data could stay where it was. Before any of that could work, the data had to mean the same thing everywhere.

Federated learning makes this harder, not easier. When you can't pool the data, you can't eyeball it in one place either. Every mismatch has to be found by asking, by summary statistics, or by a model behaving strangely.

What "the same variable" hides

On paper, every site had birth weight, maternal age, a depression score and a developmental outcome measure. In practice each of those carried its own local history. The table below is illustrative, with made-up sites and values, but every kind of difference in it is the ordinary kind you meet in multi-site cohort data.

None of these is hard on its own. The danger is that each one is silent. Grams and kilograms both load as numbers. A model will take a column where one site is a thousand times bigger than another and find it extremely predictive of which site the participant came from.

Labels are the hardest part

Units are mechanical. Labels are not. Two sites can both report a developmental outcome as "high" or "low", but if one used a clinical interview and the other an informant questionnaire with a cut-off, those are different things that happen to share a word. Even with the same instrument, a translated version with dropped items does not give the same total.

For questionnaires the options are, roughly:

• Keep only the items every site shares, and rebuild the score from those. Honest, but you lose information.
• Standardise within site, so each score becomes "how far from this site's average". This removes real differences between populations along with the artefacts, so be clear about what you are giving up.
• Use a published crosswalk between instruments, if one exists and the clinical people on the team trust it.

Which one is right is not an engineering call. I'd bring the options to the researchers who know the instruments and let them pick, then write the choice down where the code can see it.

One dictionary, one mapping per site

The approach I trust is boring on purpose. One shared data dictionary says what each harmonised variable is, its unit, its allowed range and its missing code. Each site gets a small mapping that turns its raw export into that shape. The mapping is code, versioned, and reviewed, not a set of manual edits in a spreadsheet.

import numpy as np
import pandas as pd

DICTIONARY = {
    "birth_weight_g":  {"min": 300, "max": 6500},
    "child_age_months": {"min": 0,   "max": 120},
    "maternal_dep":     {"min": 0,   "max": 30},
}

SITE_MAPS = {
    "site_a": {"birth_weight_g": ("bw", 1.0),     "missing": []},
    "site_b": {"birth_weight_g": ("bw_kg", 1000), "missing": [-999]},
    "site_c": {"birth_weight_g": ("bw_lb", 453.592), "missing": [99]},
}

def harmonise(df, site):
    m = SITE_MAPS[site]
    out = pd.DataFrame(index=df.index)
    raw = df.replace(m["missing"], np.nan) if m["missing"] else df
    for var, (col, factor) in ((k, v) for k, v in m.items() if k != "missing"):
        out[var] = raw[col] * factor
        lo, hi = DICTIONARY[var]["min"], DICTIONARY[var]["max"]
        bad = out[var].notna() & ~out[var].between(lo, hi)
        if bad.any():
            raise ValueError(f"{site}.{var}: {bad.sum()} values outside {lo}-{hi}")
    out["site"] = site
    return out

Note the range check raises instead of clipping. A value outside the allowed range almost always means the mapping is wrong, not that a birth weight really was nine kilograms. You want the pipeline to stop and make someone look. (The site C row is simplified: a real pounds-and-ounces split needs two columns combined first.)

Check per site, every time

After harmonising, I compare simple summaries per site before training anything: means, ranges, share missing, and the label rate. Differences between sites are expected, these are different populations. A difference that is far too large, or a distribution with a strange second bump, is usually a mapping bug.

The other check is to train a small model to predict the site from the harmonised features. If it can do that easily, something site-specific survived. Look at what it uses. Often it's one column you thought was fixed.

Start with the dictionary

If you are about to combine data from more than one site, write the data dictionary before you write any model code. Send it to every site and ask them to fill in, for each variable, their column name, unit, missing code and instrument version. The answers will tell you how much work is ahead, and they'll save you from finding it out from a model that is very good at guessing where a sample came from.