Join Documentary Research Tables Without Multiplying the People in Them

Before you join two research tables, finish this sentence: one row in the result will mean one ______. A person, a visit, an interview, a household, and a possible match are different answers. The number on your documentary’s research slide depends on which answer you choose.

A join connects records through specified keys. It does not establish that those keys identify real people correctly, make repeated observations independent, or turn a missing record into evidence that nothing happened. You need a defensible relationship between the tables and a count that respects the unit you intend to describe.

The practical choice is not always “which join?” Sometimes you need a detailed table with several rows per person. Sometimes you need to summarize events before attaching them to people. Sometimes the honest result is two related tables, with unresolved records still visible, rather than one reassuringly tidy spreadsheet.

The fictional example below uses a small registry and a visit log. Its code was executed with pandas 2.2.3; the input files, outputs, assertions, and runnable script accompany this article.[^fixture] No real participants or client records are involved.

Decide what the pitch needs to say

Imagine you are preparing a documentary about people returning to a community archive. Your research folder contains a registry and a visit log. You want to know how many registered people have documented visits in the supplied extract.

That question is narrower than “How many people use the archive?” It does not establish the registry’s completeness, the visit log’s coverage, or whether every identifier corresponds to a distinct person. Those would require additional evidence. Here, the identifiers are stipulated for the exercise.

Our registry contains four source records:

The visit log contains five:

Before touching the software, predict the relationships. P01 has two visit records. P02 has one. P03 has no matching visit identifier in this extract. P04 appears in the visit log but not the registry. R04 and V05 both lack person identifiers; that does not make them records of the same person.

Already, “four people and five visits” would be too confident. We have four registry records and five visit records. We have three nonmissing registry keys. We have not resolved the fourth registry record’s identity.

Write those distinctions into your working notes before the result acquires a headline. A slide can compress the explanation later. It should not compress different things into the same number.

Predict the repeated rows before they appear

For this example, the registry’s known person keys should be unique. The visit log can legitimately repeat them. Joining registry to visits therefore has an intended one-to-many relationship: one registry key may connect to several visit records.

P01 will appear twice in a detailed result. That repetition is useful when the task is to inspect its two visits. It becomes a mistake only when somebody counts those result rows as two people.

Now imagine an interview table with two interviews for P01. Join P01’s two visits directly to those two interviews using only the person key, and you produce four pairs:

Those are four combinations, not four visits, four interviews, or four people. The executed fixture produces exactly these four pairings. The pandas guide describes this multiplication when matching keys repeat on both sides: the result contains the Cartesian product of the associated rows.[^guide]

The result might be appropriate for a task that really requires every possible pairing. It cannot establish that an interview took place during a particular visit. That relationship would need its own evidence and key. A shared person identifier tells you who the records concern, not how their events relate.

This is why dropping duplicates after a join is not a substitute for designing it. Which duplicate would you delete? Removing one P01 row from the visit result would discard an actual visit record. Keeping just one interview–visit pair would silently invent a preferred relationship.

Keep unknown identifiers out of the matching pool

There is a specific pandas trap here. Its merge operation matches null keys to other null keys. The API documentation warns that this differs from usual SQL join behavior.[^api]

A naive outer merge of our complete inputs produces six rows, including a row joining R04 to V05. Both identifiers are missing. The software has followed its rule; the research has gained an unsupported connection.

Our repair is to separate records with missing keys before joining. They remain part of the source inventory, in explicit unresolved tables. We have not deleted them or assigned them replacement identities.

Here is the complete setup for the core example:

import pandas as pd
from pandas.errors import MergeError

people = pd.DataFrame({
    "registry_row": ["R01", "R02", "R03", "R04"],
    "person_id": pd.Series(["P01", "P02", "P03", None], dtype="string"),
})
visits = pd.DataFrame({
    "visit_id": ["V01", "V02", "V03", "V04", "V05"],
    "person_id": pd.Series(["P01", "P01", "P02", "P04", None], dtype="string"),
})

people_known = people[people["person_id"].notna()].copy()
visits_known = visits[visits["person_id"].notna()].copy()
people_unresolved = people[people["person_id"].isna()].copy()
visits_unresolved = visits[visits["person_id"].isna()].copy()

assert people["registry_row"].is_unique
assert visits["visit_id"].is_unique
assert people_known["person_id"].is_unique

For real files, deciding what counts as missing belongs upstream of this split. A blank cell, the literal text “unknown,” and an identifier that belongs to another system are not automatically the same condition. Document your interpretation rather than guessing a replacement from a participant’s name.

Preserve the original columns and source files. A normalized working key is useful only when you can explain how it was made and return to the original value. The same caution applies to leading zeros, mixed identifier types, and reused numbers across editions: do not repair the join by erasing the distinction that made two records different.

Make a wrong relationship fail visibly

Before running the intended join, deliberately test the wrong assumption:

try:
    people_known.merge(
        visits_known,
        on="person_id",
        how="outer",
        validate="one_to_one",
    )
except MergeError as exc:
    print(f"{type(exc).__name__}: {exc}")

The executed example reports:

MergeError: Merge keys are not unique in right dataset; not a one-to-one merge

This failure is useful. It locates a disagreement between the declared relationship and the data. In our case, the two P01 visits are expected, so the correct declaration is one-to-many. In another project, the repeated key might reveal duplicated imports, a missing edition field, or two people sharing an identifier. Investigate before changing the validation merely to get a result.

The pandas guide documents validation as a key-uniqueness check; it is not verification of the people behind those keys.[^guide] A passing join could still connect two wrong records perfectly.

Now run the intended relationship, explicitly naming both the key and the join type:

joined = people_known.merge(
    visits_known,
    on="person_id",
    how="outer",
    validate="one_to_many",
    indicator=True,
)

Using an outer join here is an audit choice: we want to see keyed records that appear on either side, not only successful matches. The indicator labels each result as both, left_only, or right_only.[^api]

Our actual result is:

There are five rows. Three are linked visit records. Those three records link to two distinct registry identifiers. P03 and P04 each need a different explanation, and the two unresolved input records remain outside this table.

An inner join would answer a narrower question by retaining only matched keys. That may be useful for a later calculation, but using it as your only research output would make P03 and P04 disappear from view.[^api] Keep the reconciliation even when the presentation needs only the matched subset.

Notice that the outer result is an audit view, not a uniform list of visits. Its P03 row has no visit at all; its P04 row has no registry record. The opening unit question therefore has two stages: identify what each audit row contains, then select the appropriate records for the analytical view. Counting every audit row would mix successful links with evidence of missing relationships.

For the registered-identifiers question, select both and count distinct person keys. For a visit-record inventory, follow the unique visit identifiers, including the unmatched and unresolved records. For a registry inventory, follow registry row identifiers instead. The same audit can support those three views, but it does not make their totals interchangeable.

Also check where a key is unique. Suppose a later registry edition starts numbering from P01 again. Appending both editions and joining only on person_id could cross their records. Adding an edition field is justified only when it belongs to the source identity rule, not because a bigger key happens to make the warning disappear. If the person is intended to retain one stable identity across editions, separating editions may instead conceal the longitudinal relationship you need to verify. Define the relationship first; then choose the key that expresses it.

Choose detail or a person-level summary

Suppose the next task is finding which visits might support a scene. Keep the detailed visit table. Its repeated person identifier lets you follow the chronology without pretending the visits belong to separate people. If interviews are also relevant, keep them as a separate related table unless an evidenced event relationship allows a more specific join.

Suppose instead the pitch needs a small summary of documented visit records per registered identifier. Count the visit records first, then join that summary to the registry:

visit_counts = visits_known.groupby("person_id", as_index=False).agg(
    documented_visit_records=("visit_id", "size")
)

summary = people_known.merge(
    visit_counts,
    on="person_id",
    how="left",
    validate="one_to_one",
)
summary["documented_visit_records"] = (
    summary["documented_visit_records"].astype("Int64")
)

In the executed example, the intermediate count table preserves P04’s one visit record. The registry summary then contains P01 with two records, P02 with one, and P03 with a missing count. P04’s exclusion from that final view is intentional because this particular view describes registered identifiers. Its visit has not vanished from the audit.

This choice matters. Count visits from their source table, where each visit_id is unique, rather than counting rows after attaching unrelated interviews. The latter would give P01 four combinations and invite an inflated count. The aggregation rule should say exactly what it counts: here, one documented visit record per unique visit identifier, not time spent at the archive or distinct days attended.

What about P03’s blank? If your measure is explicitly “number of linked visit records in this extract,” a zero can be a legitimate display choice. It must not become “P03 never visited.” The example retains a missing count alongside the status “no linked visit record” to make that distinction difficult to overlook.

Likewise, do not replace every repeated value with an average just to recover one row per person. A mean date, a first interview, and a latest status answer different questions. If you select the latest event, specify which timestamp governs and what happens when two records share it. If no defensible summary serves the pitch, retain the detail and write a narrower claim.

Reconcile records, not just the final row total

A row-count check alone cannot tell you whether a join is right. Our nine input records do not become nine joined rows, because one result can contain both a registry record and a visit record. The registry record R01 also appears in two results. Neither behavior is inherently an error.

Instead, account for the source identifiers. Every registry row should appear in the joined result or the unresolved registry file. Every visit identifier should appear in the joined result or the unresolved visit file. In this one-to-many design, each keyed visit should appear only once.

The accompanying script checks those conditions with assertions. It accounts for all four registry records and all five visit records, including the two unresolved records. It separately checks that the matched subset has three visits and two distinct registry keys. Those are different tests because they defend different claims.[^fixture]

Give unmatched records dispositions, not convenient explanations. “P04 is absent from this registry extract” is supported. “P04 forgot to register” is not. “R04 lacks an identifier” is supported. “R04 must be P04” is not. The gap can remain open while the supported portion of the research moves forward.

You also need to name the time and scope of the source extract. A correct join cannot reconcile an undated registry with a visit log covering an unknown period. The computation may run, but a claim about participation across a particular year would still lack its necessary boundary.

The same problem can hide inside an authoritative dataset

The unit question is not limited to messy project spreadsheets. The tidycensus documentation distinguishes person-level and housing-unit-level records in Census microdata. In its PUMS example, SERIALNO identifies the housing unit, while the combination of SERIALNO and SPORDER identifies a person within it.[^pums]

That is a useful schema lesson, not permission to treat every dataset’s serial number as a person identifier. Read the source’s definition at the edition and scope you are using. A household attribute repeated alongside several household members does not describe several additional households. Nor should those repeated entries silently enlarge a household-level denominator.

No Census microdata were analyzed for this article. The point is the relationship described in the documentation: a table can carry information about more than one unit, even when every column arrives together in a single download.

Put the supported number in the deck

Return to the fictional documentary. A defensible sentence is:

In the supplied extract, three documented visit records link to two registry identifiers. One keyed visit is unmatched to the registry, and one registry record and one visit record lack person identifiers.

That is research-note wording, not compulsory slide copy. A slide could lead with “Two registered identifiers have linked visit records,” while its nearby note identifies the extract and qualifications. Do not substitute “two participants confirmed” unless the research actually verifies that claim.

Keep the person-level view, event-level evidence, unresolved records, and transformation code together in the research handoff. Limit the pitch’s exposed detail to what its reader needs: making a join does not itself justify circulating a newly combined set of participant information.

When the count seems surprisingly persuasive, inspect the unit again. The useful result is not a larger table. It is a sentence whose nouns still mean what the source records can support.

Sources

[^api]: pandas, “pandas.DataFrame.merge”, null-key warning and parameters how, indicator, and validate. Inspected September 19, 2026. The live documentation identified itself as version 3.0.6; the accompanying experiment ran on 2.2.3. The documented features used here were exercised locally; no claim of testing all versions is made.

[^guide]: pandas, “Merge, join, concatenate and compare”, sections “Merge types,” “Merge key uniqueness,” and “Merge result indicator.” Inspected September 19, 2026.

[^pums]: tidycensus, “Working with Census microdata”, section “Person vs. housing unit.” Inspected September 19, 2026. Cited for its documented units and keys, not as an analysis performed for this article.

[^fixture]: Original fictional example, executed September 19, 2026. Runnable Python script, recorded results, and checked outer-join output. The script creates every input and checks the deliberately failing validation, null-key match, correct join, aggregation, unmatched records, and independent child-table multiplication.

Frequently asked questions

What should I decide before joining two research tables?

Complete the sentence: one row in the result will mean one ______. A person, a visit, an interview, a household, and a possible match are different answers. A join connects records through specified keys; it does not establish that those keys identify real people correctly, make repeated observations independent, or turn a missing record into evidence that nothing happened.

Why can joining visits and interviews inflate the result?

When matching keys repeat on both sides, the result contains the Cartesian product of the associated rows. If one person's two visits are joined directly to that person's two interviews using only the person key, the result has four pairs. Those are four combinations, not four visits, four interviews, or four people. Dropping duplicates afterward is not a substitute for designing the join, because deciding which duplicate to delete would discard a real record or invent a preferred relationship.

How does pandas treat missing join keys, and what is a safer approach?

Its merge operation matches null keys to other null keys, unlike usual SQL join behavior. A naive outer merge can therefore join two records whose keys are both missing. A safer approach is to separate records with missing keys before joining and keep them in explicit unresolved tables. Do not delete them or assign replacement identities, and document how you decide what counts as missing.

What does validate='one_to_many' actually check?

It checks key uniqueness for the declared relationship and raises a MergeError when the data disagree with that declaration. It does not verify the people behind the keys. A passing join could still connect two wrong records perfectly. If a repeated key appears, investigate duplicate imports, missing edition fields, or shared identifiers before changing the validation just to get a result.

How should I reconcile an outer-join audit?

Account for source identifiers, not just the final row total. Every registry row should appear in the joined result or the unresolved registry file, and every visit identifier should appear in the joined result or the unresolved visit file. In the one-to-many example, the audit has five rows, three linked visit records connected to two distinct registry identifiers, one left-only registry row, and one right-only visit row; unresolved input records remain outside that table. Different inventories, such as registry identifiers, visit records, and matched links, require different selections and counts.