fw.relate

Use fw.relate when a question spans two tables, or a table and itself: are the keys in one export found in the other, how many rows each key has on each side, and whether a shared detail agrees. Every answer is a state (all, some, none, or a cardinality such as n:1) with its counts alongside.

relate never joins the tables, so it cannot fan out a large table. It does not discover join keys, compare values after a transformation (derive the column first), or place attributes across a join; use grain on a joined frame for that.

Jump to the worked example or the argument reference.

Function reference

fw.relate(
    left,
    right=None,
    *,
    on,
    compare=None,
    match='typed',
    dropna=True,
    scopes=None,
    missing=None,
    table_ids=None,
    limits=None,
    progress=None,
    cancel=None,
    timeout=None,
    safe_errors=None,
)

This page describes Fieldwork 0.3.2. Import the package with import fieldwork as fw. View the documented source.

Arguments

Argument	Type / default	Effect
left	pandas.DataFrame; required	The left table. Analysis preserves its values and row order; duplicate column labels are rejected.
right	pandas.DataFrame or None; None	The right table. None relates left to itself: the left columns of on are references and the right columns are the key each row is referenced by.
on	mapping; required	Nonempty key pairs, left column to right column, such as {"member_id": "member_id"}. Several pairs form a composite key. Rows missing any key column are excluded and counted per side as incomplete_key.
compare	mapping or None; None	Attribute pairs, left column to right column, tested for agreement on matched keys. None compares nothing.
match	“typed” or “text”; 'typed'	How values match across tables. "typed" keeps booleans, numbers, strings and temporal kinds apart and matches numbers numerically, so 101 matches 101.0. "text" also matches integer-valued numbers to their decimal text, so 101 matches "101"; "0101" still does not.
dropna	bool; True	True ignores missing compared values. False treats missing as one shared value, so missing on both sides agrees. Keys are never matched on missing values.
scopes	pair or None; None	(left, right) saved row selections, each a Scope of its own table or None for every row.
missing	pair or None; None	(left, right) sentinel mappings, such as ({"member_id": [-1]}, None). Native nulls are always missing.
table_ids	pair or None; None	(left, right) nonempty table identities used in statements and features. None uses ('left', 'right'), or ('table', 'table') for a self-reference. They do not bypass source-fingerprint checks.
limits	mapping or None; None	Output budget (fieldwork.typing.RelateLimits). example_limit (5): source rows saved per finding and side; select and inspect(..., all_matches=True) still recover every matching row.
progress	bool or callable; None	Silent by default; True displays progress, or pass a synchronous ProgressEvent callback. See runtime controls.
cancel	CancellationToken or None; None	Cooperative cancellation token; cancellation raises AnalysisCancelled without returning a partial result.
timeout	float or None; None	Finite nonnegative seconds from call start; None disables the cooperative deadline. A running pandas/NumPy work item must finish before cancellation.
safe_errors	bool or None; None	True raises any other failure as AnalysisError, naming the operation, phase and column without echoing the original message, which can quote cell values. See runtime controls.

Returns and output choices

Returns a fieldwork.Result with kind="relation" (schema 2.0). Its source, scope and missing_convention describe the left table; sides["left"] and sides["right"] each record their own source, scope, sentinels, key and compared columns, row accounting (evaluated, incomplete_key, matched, unmatched) and key accounting (distinct, matched, unmatched, repeated).

• key_coverage["left_to_right"] and ["right_to_left"]: of one side’s distinct complete keys, how many are found on the other side. The state is all, some, none, or empty when that side has no complete key. found_rows and not_found_rows count the rows behind them.
• relation: the cardinality over matched keys, 1:1, 1:n (one left row, several right rows), n:1 or n:m, with joined_rows, the size an inner join would have. The type is None when no key matches.
• agreement: one record per compared pair. A matched key is unavailable when a side has no value, ambiguous when a side has several, and otherwise agrees or disagrees. The state covers agreeing plus disagreeing keys (empty when there are none); ambiguity is some when any key is ambiguous. Variation within one table is therefore never reported as disagreement between the tables.
• reciprocity: for a self-reference, whether resolved references a → b are matched by a row with key b that references a. Self-loops are counted in self_referencing_keys and excluded. None for two tables.
• warnings: a VALUE_KIND_MISMATCH record when the value kinds of a key or compared pair do not overlap, so a type mismatch is never reported only as “no key is found”.

Each coverage, relation, agreement and reciprocity record becomes a finding whose structure keeps its state for topology exports. Use plaintext, SVG or HTML for a report, and to_dict() for an archive. recompute(left, right=right) reruns the analysis on both verified sources.

Interpretation and boundaries

Coverage, relation and agreement count distinct keys, not rows; reciprocity counts distinct resolved references. A finding’s examples and exceptions belong to one side, named by selector["side"]:

Finding	Examples	Exceptions
Coverage	rows whose key is found	rows with a complete key that is not found
Relation	rows of matched keys repeated on the side	rows of matched keys that occur once there
Agreement	rows of agreeing keys	rows of disagreeing keys
Reciprocity	rows whose reference is reciprocated	rows whose resolved reference is not

result.inspect(left, finding, side="right", right=right) returns the right side’s rows, and select builds a Scope of the chosen side’s table. Complete selections and right-side rows need the right frame, since matching needs both tables; both frames are verified against their fingerprints. A self-reference takes no right.

A missing column raises KeyError. An empty on, a column repeated within on or compare, or a per-side argument without two entries raises ValueError; a non-mapping on or compare raises TypeError.

Walkthrough: a tool library’s loans and members

A neighborhood tool library keeps two exports: one row per loan, and one row per member with their home branch and a volunteer buddy. Before the coordinator merges them into a newsletter list, they want to know whether every loan belongs to a known member, whether loans are recorded at the member’s home branch, and whether the buddy pairs are mutual.

Download the runnable example. It follows the same steps and saves a figure and the result in your working directory.

Load the two exports

One loan has no member recorded. That missing value makes pandas store the loan table’s member numbers as floats, while the member table keeps integers.

import json
from pathlib import Path

import pandas as pd
import fieldwork as fw

members = pd.DataFrame(
    [
        (101, "North", 102),
        (102, "North", 101),
        (103, "South", 104),
        (104, "South", None),
        (105, "South", 101),
    ],
    columns=["member_id", "home_branch", "buddy_id"],
)
loans = pd.DataFrame(
    [
        ("L1", 101, "drill", "North"),
        ("L2", 101, "ladder", "North"),
        ("L3", 102, "saw", "South"),
        ("L4", 103, "drill", "North"),
        ("L5", 103, "sander", "South"),
        ("L6", 107, "ladder", "South"),
        ("L7", None, "saw", "South"),
    ],
    columns=["loan", "member_id", "tool", "branch"],
)
print(loans.to_string(index=False))
print("Key types:", loans["member_id"].dtype, "and", members["member_id"].dtype)

loan  member_id   tool branch
  L1      101.0  drill  North
  L2      101.0 ladder  North
  L3      102.0    saw  South
  L4      103.0  drill  North
  L5      103.0 sander  South
  L6      107.0 ladder  South
  L7        NaN    saw  South
Key types: float64 and int64

Relate loans to members

Name the tables so the statements read naturally. The default typed matching compares numbers numerically, so 101.0 matches 101.

link = fw.relate(
    loans,
    members,
    on={"member_id": "member_id"},
    compare={"branch": "home_branch"},
    table_ids=("loans", "members"),
)
for finding in link["findings"]:
    print(finding["id"], finding["statement"])

f0 Some loans.member_id keys are found in members.member_id
f1 Some members.member_id keys are found in loans.member_id
f2 loans.member_id to members.member_id is n:1 on matched keys
f3 loans.branch and members.home_branch agree on some comparable matched keys

Find the loans and members that do not connect

Coverage counts distinct member numbers. The loan without a member number is not a key at all; it is counted separately as an incomplete key.

print(link["key_coverage"]["left_to_right"])
print(link["sides"]["left"]["rows"])
unknown = link.inspect(loans, "f0", exceptions=True)
print(unknown.to_string(index=False))

{'state': 'some', 'keys': 4, 'found': 3, 'not_found': 1, 'found_rows': 5, 'not_found_rows': 1}
{'evaluated': 7, 'incomplete_key': 1, 'matched': 5, 'unmatched': 1}
loan  member_id   tool branch
  L6      107.0 ladder  South

Member 107 borrowed a ladder but is not in the member export. In the other direction, the exceptions are members on the right side, so pass side="right" and the member frame:

no_loans = link.inspect(loans, "f1", exceptions=True, side="right", right=members)
print(no_loans.to_string(index=False))

 member_id home_branch  buddy_id
       104       South       NaN
       105       South     101.0

Members without loans are expected in a lending library; the missing member 107 is the record to follow up.

Read cardinality and agreement separately

Each member number occurs once in the member export and up to twice in the loans, so matched keys are n:1: joining would give one row per matched loan. Agreement then compares each member’s loan branches with their home branch.

print(link["relation"]["type"], "with", link["relation"]["joined_rows"], "joined rows")
agreement = link["agreement"][0]
for field in ["state", "ambiguity", "agreeing_keys", "disagreeing_keys", "ambiguous_keys"]:
    print(f"{field}: {agreement[field]}")
print(link.inspect(loans, "f3", exceptions=True).to_string(index=False))

n:1 with 5 joined rows
state: some
ambiguity: some
agreeing_keys: 1
disagreeing_keys: 1
ambiguous_keys: 1
loan  member_id tool branch
  L3      102.0  saw  South

Member 101 borrowed only at North, their home branch, and agrees. Member 102 borrowed at South although their home branch is North, and disagrees. Member 103 borrowed at both branches, so the loan table itself has two values for that member: it is ambiguous rather than a disagreement, and it is not counted either way.

Match member numbers that arrive as text

A spreadsheet export often writes member numbers as text. Typed matching never matches numbers to strings, so nothing is found, and the result warns that the key columns hold different kinds of values instead of reporting only missing members.

members_csv = members.astype({"member_id": "string"})
typed = fw.relate(
    loans, members_csv, on={"member_id": "member_id"}, table_ids=("loans", "members")
)
print(typed["key_coverage"]["left_to_right"]["state"])
print(typed["warnings"][0]["code"], typed["warnings"][0]["left_kinds"], typed["warnings"][0]["right_kinds"])
as_text = fw.relate(
    loans,
    members_csv,
    on={"member_id": "member_id"},
    match="text",
    table_ids=("loans", "members"),
)
print(as_text["key_coverage"]["left_to_right"]["state"], as_text["warnings"])

none
VALUE_KIND_MISMATCH ['number'] ['string']
some []

match="text" writes integer-valued numbers as decimal text, so 101.0 matches "101" and coverage is the same as before. Text is still compared exactly: a zero-padded "0101" would not match.

Check that buddy pairs are mutual

With no right table, relate resolves each member’s buddy_id against the member numbers of the same table. Member 104 has no buddy, so that row has an incomplete reference.

buddies = fw.relate(members, on={"buddy_id": "member_id"})
for finding in buddies["findings"]:
    print(finding["id"], finding["statement"])
print(buddies["reciprocity"]["reciprocated"], "of", buddies["reciprocity"]["resolved_references"], "reciprocated")
print(buddies.inspect(members, "f3", exceptions=True).to_string(index=False))

f0 Every table.buddy_id reference resolves to a table.member_id row
f1 Some table.member_id keys are referenced by table.buddy_id
f2 table.buddy_id to table.member_id is n:1 on matched keys
f3 Some resolved table.buddy_id references are reciprocated
2 of 4 reciprocated
 member_id home_branch  buddy_id
       103       South     104.0
       105       South     101.0

Members 101 and 102 name each other. Member 103 names 104, who has no buddy, and 105 names 101, who is already paired with 102. Those are the two pairs to confirm.

Save the evidence

The figure lists each finding with its counts. The JSON export keeps both sides’ sources, so the analysis can be recomputed once both frames are supplied again.

Path("relate.svg").write_text(fw.render_svg(link), encoding="utf-8")
Path("tool-library-relation.json").write_text(json.dumps(link.to_dict()), encoding="utf-8")
saved = fw.Result.from_dict(json.loads(Path("tool-library-relation.json").read_text()))
again = saved.recompute(loans, right=members)
print("Same coverage after recompute:", again["key_coverage"] == link["key_coverage"])

Same coverage after recompute: True

Continue exploring

fw.grain, fw.compare, fw.render_svg. See results and configuration for inspection, selection and serialization contracts.