Match

Nomenclature reconciliation: fuzzy matching provider codes against MARIS lookup tables. 
import numpy as np
from jellyfish import jaro_winkler_similarity

source

uniq_across_dfs

def uniq_across_dfs(
    dfs:dict[str, pandas.DataFrame], # Group name to DataFrame, e.g. `{'SEAWATER': df}`
    col:str, # Column to collect values from
)->list: # Distinct values, in no particular order

Distinct values of col across all group DataFrames, skipping groups without it

Groups without the column are skipped, since not every sample type reports every variable. Here the sediment group has no NUCLIDE column:

dfs = dict(SEAWATER=pd.DataFrame({'NUCLIDE': ['cs137', 'cs134_137_tot', 'cs134_137_tot']}),
    BIOTA=pd.DataFrame({'NUCLIDE': ['cs137', 'cs134', 'cs134_137_tot']}),
    SEDIMENT=pd.DataFrame({'SED_TYPE': ['sand', 'mud']}))
vals = uniq_across_dfs(dfs, 'NUCLIDE')
test_eq(set(vals), {'cs134', 'cs137', 'cs134_137_tot'})
sorted(vals)
['cs134', 'cs134_137_tot', 'cs137']

When a provider doesn’t supply its own nomenclature, we derive one from the data. lut_from returns the distinct values as a table with a value column, which is the default left_on column of fuzzy_merge.

source

lut_from

def lut_from(
    dfs:dict[str, pandas.DataFrame], # Group name to DataFrame, e.g. `{'SEAWATER': df}`
    col:str, # Column to collect values from
    incl_nchars:bool=False, # Add an `n_chars` column with each value's length?
)->pandas.DataFrame: # Provider table with a sorted `value` column

Provider table of the distinct, non-missing values of col across all groups

The values come back sorted. With incl_nchars=True, the table also gives each value’s length:

df_lut = lut_from(dfs, 'NUCLIDE', incl_nchars=True)
test_eq(list(df_lut['value']), ['cs134', 'cs134_137_tot', 'cs137'])
test_eq(list(df_lut['n_chars']), [5, 13, 5])
df_lut
value n_chars
0 cs134 5
1 cs134_137_tot 13
2 cs137 5

Missing values are dropped, because there is nothing to match them against:

with_na = {'BIOTA': pd.DataFrame({'SPECIES': ['fucus', None, 'gadus morhua', np.nan]})}
test_eq(list(lut_from(with_na, 'SPECIES')['value']), ['fucus', 'gadus morhua'])

Mapping provider codes to MARIS

A semi-automated workflow for reconciling provider nomenclature against MARIS reference lookups.

This workflow is designed for the reality that mapping provider codes to MARIS is inherently imperfect. The computer can handle the bulk work with brute-force matching, but reliably getting the last mile right requires a domain expert in the loop.

The idea is:

  1. Get familiar with the provider’s codes: inspect the raw data and list the unique terms that need mapping.
  2. Try an automatic mapping: let the computer do fuzzy matching between provider codes and MARIS references.
  3. Fix what it got wrong: apply expert overrides for cases the fuzzy match could not resolve correctly.
  4. Check the result: verify the final mapping before using it in the pipeline.

Handler authors should follow this pattern whenever they need to align provider nomenclature (species names, nuclide codes, units, etc.) to MARIS identifiers. The functions below give you the building blocks; steps 1 and 4 are manual review steps.

source

fuzzy_merge

def fuzzy_merge(
    left:pandas.DataFrame, # Provider table
    right:pandas.DataFrame, # MARIS reference table
    left_on:str='value', # Column in `left` to match on
    right_on:str='name', # Column in `right` to match on
    dist_fn:Callable=levenshtein_distance, # Distance function, where lower means closer
    lowercase:bool=True, # Compare in lowercase?
)->pandas.DataFrame: # `left` rows with the columns of their closest `right` row and a `score`

Pair each row of left with the closest row of right by dist_fn

fuzzy_merge pairs each provider value with its closest MARIS name. The score is the distance: 0 for an exact match, and higher for weaker matches, which need review. Here cs134_137 is as far from cs137 as from cs134_137_tot, and the first one wins, which is wrong:

maris = pd.DataFrame({'name': ['cs137', 'k40', 'cs134_137_tot'], 'maris_id': [1, 2, 33]})
prov = pd.DataFrame({'value': ['cs137', 'k40', 'cs-137', 'cs134_137']})
merged = fuzzy_merge(prov, maris)
test_eq(list(merged['score']), [0, 0, 1, 4])
test_eq(list(merged['maris_id']), [1, 2, 1, 1])
merged
value name maris_id score
0 cs137 cs137 1 0
1 k40 k40 2 0
2 cs-137 cs137 1 1
3 cs134_137 cs137 1 4

Case is ignored by default. With lowercase=False, a case difference counts as one edit:

upper = pd.DataFrame({'value': ['Cs137']})
test_eq(fuzzy_merge(upper, maris)['score'].iloc[0], 0)
test_eq(fuzzy_merge(upper, maris, lowercase=False)['score'].iloc[0], 1)

dist_fn accepts any function where lower means closer. A similarity such as Jaro–Winkler works once subtracted from 1. It weights shared prefixes, so it matches cs134_137 to cs134_137_tot:

jw = fuzzy_merge(prov, maris, dist_fn=lambda a, b: 1 - jaro_winkler_similarity(a, b))
test_eq(list(jw['maris_id']), [1, 2, 1, 33])
jw
value name maris_id score
0 cs137 cs137 1 0.000000
1 k40 k40 2 0.000000
2 cs-137 cs137 1 0.044444
3 cs134_137 cs134_137_tot 33 0.061538

fuzzy_merge raises a ValueError when the provider and MARIS tables share a column name, because the MARIS value would replace the provider value. It also raises one when a match column has a missing value:

with expect_fail(ValueError, "share columns ['maris_id']"): fuzzy_merge(prov.assign(maris_id=0), maris)
with expect_fail(ValueError, 'Missing values in `value` at rows [1]'): fuzzy_merge(pd.DataFrame({'value': ['cs137', None]}), maris)

source

fix_lut

def fix_lut(
    merged:pandas.DataFrame, # Output of `fuzzy_merge`
    overrides:dict, # Expert overrides: {provider value: MARIS name}
    maris:pandas.DataFrame, # MARIS reference table
    left_on:str, # Provider value column
    right_on:str, # MARIS name column
    id_col:str, # MARIS id column
)->pandas.DataFrame: # Copy of `merged` with the overridden rows corrected and scored 0

Replace matched entries with expert overrides by name

fix_lut applies an expert’s corrections. Each override sets the MARIS name and id, and resets the score to 0. It works on a copy, so merged stays as fuzzy_merge left it:

fixed = fix_lut(merged, {'cs134_137': 'cs134_137_tot'}, maris, left_on='value', right_on='name', id_col='maris_id')
test_eq(list(fixed['maris_id']), [1, 2, 1, 33])
test_eq(list(fixed['score']), [0, 0, 1, 0])
test_eq(merged['maris_id'].iloc[3], 1)
fixed
value name maris_id score
0 cs137 cs137 1 0
1 k40 k40 2 0
2 cs-137 cs137 1 1
3 cs134_137 cs134_137_tot 33 0

An override whose MARIS name doesn’t exist is usually a typo. fix_lut issues a warning and leaves that row as matched:

def fix_typo():
    typo = fix_lut(merged, {'cs134_137': 'cs134137_tot'}, maris, left_on='value', right_on='name', id_col='maris_id')
    test_eq(typo['maris_id'].iloc[3], 1)
test_warns(fix_typo, show=True)
<class 'UserWarning'>: 'cs134137_tot' not found in MARIS name table, skipping override for 'cs134_137'

Assembling the mappings

A handler defines its mappings when its module loads, but the data (dfs) only exists once the handler runs. The functions below therefore return a function of dfs. When called, it runs fuzzy_merge and fix_lut against the MARIS table from get_lut, and returns a plain {provider value: MARIS id} dict.

make_lut_from is the general builder, for providers that supply their own nomenclature table. make_lut covers providers that don’t: it derives the table from the data with lut_from.

source

make_lut_from

def make_lut_from(
    mk_prov, # Function from `dfs` to the provider table, or the table itself
    key_col:str, # Provider column the data uses, which becomes the lookup key
    match_col:str, # Provider column matched against the MARIS names
    lut_key:str, # `NC_DTYPES` key of the MARIS table, e.g. `'NUCLIDE'` or `'SPECIES'`
    fixes:dict | None=None, # Expert overrides: {`match_col` value: MARIS name}
)->Callable: # Function from `dfs` to a {provider value: MARIS id} dict

Lookup builder that matches a provider table against a MARIS table when called

HELCOM, for example, supplies a species file that maps its RUBIN codes to scientific names. The codes are the lookup keys, and the names are matched. A table that doesn’t depend on the data can be passed directly:

provider_species = pd.DataFrame({'code': ['GADU MOR', 'MYTI EDU'], 'sci_name': ['Gadus morhua', 'Mytilus edulis']})
species_lut = make_lut_from(provider_species, key_col='code', match_col='sci_name', lut_key='SPECIES')
lut = species_lut(dfs)
test_eq(lut, {'GADU MOR': 99, 'MYTI EDU': 129})
lut
{'GADU MOR': 99, 'MYTI EDU': 129}

source

make_lut

def make_lut(
    lut_key:str, # `NC_DTYPES` key of the MARIS table, and the `dfs` column holding the provider values
    fixes:dict | None=None, # Expert overrides: {provider value: MARIS name}
)->Callable: # Function from `dfs` to a {provider value: MARIS id} dict

Lookup builder for providers without a nomenclature table: matches the values found in dfs

make_lut reads the provider values from the lut_key column of each group. Against the real nuclide table, cs134_137 matches cs134 (id 31), so the handler adds an override for it:

nuc_dfs = dict(SEAWATER=pd.DataFrame({'NUCLIDE': ['cs137', 'cs134', 'k40']}),
    BIOTA=pd.DataFrame({'NUCLIDE': ['cs137', 'k40', 'sr90', 'cs134_137']}))
test_eq(make_lut('NUCLIDE')(nuc_dfs)['cs134_137'], 31)
nuclide_lut = make_lut('NUCLIDE', fixes={'cs134_137': 'cs134_137_tot'})
lut = nuclide_lut(nuc_dfs)
test_eq(lut['cs134_137'], 76)
lut
{'cs134': 31, 'cs134_137': 76, 'cs137': 33, 'k40': 4, 'sr90': 12}

Reference id parsing

Parsing and validating the reference ids that the CLI passes to handlers, such as maris_legacy’s ref_ids.

source

parse_ref_ids

def parse_ref_ids(
    ref_ids:str | list[int] | None, # Comma-separated ids, a list of ids, or None for all valid ids
    valid:list[int] | None=None, # Known ids to check against
)->list[int]: # Parsed ids

Parse ref_ids into a list of ints, optionally checked against valid

The CLI passes ids as a comma-separated string. Spaces and empty entries are ignored, and a list passes through unchanged:

test_eq(parse_ref_ids('16, 30,, 45'), [16, 30, 45])
test_eq(parse_ref_ids([16, 30]), [16, 30])

With valid, an unknown id raises an error, and None selects every valid id:

test_eq(parse_ref_ids(None, valid=[16, 30, 45]), [16, 30, 45])
with expect_fail(ValueError, 'Unknown ref_ids: [999]'): parse_ref_ids('16,999', valid=[16, 30])

A value that isn’t an integer is rejected with the expected format:

with expect_fail(ValueError, 'Expected comma-separated integers'): parse_ref_ids('16,abc')