Join CSV Files Without Duplicating Rows: Check Key Cardinality First

To join two CSV files without unexpected duplicate rows, first decide what one row in the output should represent. Then test whether the join key is unique in the table where uniqueness is required.

Do not start by joining and applying DISTINCT or drop_duplicates(). A join can repeat a record because its key legitimately matches several records in the other table. Removing rows afterward may conceal a bad join—or delete valid observations.

See how a one-to-many join multiplies rows

Suppose stations.csv contains one row per monitoring station:

station_id	station_name
S1	Central
S2	Harbor

observations.csv contains one row per station and date:

station_id	observed_date	pm25
S1	2026-09-20	8.2
S1	2026-09-21	9.1
S2	2026-09-21	6.4

Joining on station_id produces:

station_id	station_name	observed_date	pm25
S1	Central	2026-09-20	8.2
S1	Central	2026-09-21	9.1
S2	Harbor	2026-09-21	6.4

“Central” appears twice because station S1 has two observations. These are not duplicate output rows: each represents a different station-date observation.

For any matching key, the join emits:

rows on left × rows on right

If S1 appeared twice in stations.csv and twice in observations.csv, the join would emit four S1 rows. This is a many-to-many join. Pandas describes the result as the Cartesian product of the records associated with that repeated key (pandas user guide).

Declare the intended output grain

Choose the grain—the meaning of one row—before choosing the join:

• One row per observation: join observations to a station lookup. station_id may repeat in observations but must be unique in the station table. This is many-to-one.
• One row per station: reduce observations to one summary row per station_id, then join that summary to stations. This should be one-to-one.
• One row per station and date: use the composite key station_id, observed_date. Each combination, rather than either column alone, must have the required uniqueness.

A bare CSV does not carry a uniqueness constraint. That rule must come from source documentation, a separate schema or an explicit test. W3C’s CSV on the Web primer shows how JSON metadata can declare either a single-column primary key or a key made from several columns (W3C). For a reusable publication workflow, document the key as described in Primary Keys for Published CSV Datasets.

Audit both sides before joining

This pandas script loads identifiers as strings, parses the date and measurement columns, rejects blank keys and prints repeated key groups:

import pandas as pd

stations = pd.read_csv(
    "stations.csv",
    dtype={"station_id": "string"}
)
observations = pd.read_csv(
    "observations.csv",
    dtype={"station_id": "string"}
)

observations["observed_date"] = pd.to_datetime(
    observations["observed_date"],
    format="%Y-%m-%d",
    errors="raise"
)
observations["pm25"] = pd.to_numeric(
    observations["pm25"],
    errors="raise"
)

keys = ["station_id"]

for name, df in {
    "stations": stations,
    "observations": observations
}.items():
    blank_key = df[keys].isna().any(axis=1)
    blank_key |= df[keys].apply(
        lambda column: column.str.strip().eq("")
    ).any(axis=1)

    if blank_key.any():
        raise ValueError(f"{name}: {blank_key.sum()} rows have blank keys")

    counts = (
        df.groupby(keys, dropna=False)
          .size()
          .reset_index(name="row_count")
    )
print(f"\nRepeated keys in {name}:")
    print(counts[counts["row_count"] > 1])

Do not automatically delete every repeated key. Inspect what the records mean:

• Two identical records may be accidental duplication.
• Two records with the same station and different dates are separate observations.
• Two records with the same supposed station key but conflicting names may indicate a source error or a key that is too broad.
• Values such as S1 and S1 require a documented normalization rule; trimming them may reveal a collision that was previously hidden.

Make pandas enforce the expected cardinality

For an output with one row per observation, put observations on the left and validate that the station lookup is unique:

joined = observations.merge(
    stations,
    on="station_id",
    how="left",
    validate="many_to_one",
    indicator=True
)

if len(joined) != len(observations):
    raise AssertionError("The join changed the observation row count")

unmatched = joined.loc[
    joined["_merge"] == "left_only",
    "station_id"
]
if not unmatched.empty:
    raise ValueError(
        f"Unmatched station IDs: {unmatched.unique().tolist()}"
    )

joined = joined.drop(columns="_merge")
joined.to_csv("station_observations_joined.csv", index=False)

The validate argument checks the declared relationship: one_to_one requires unique keys on both sides, one_to_many requires them on the left, and many_to_one requires them on the right. The optional indicator adds _merge, identifying rows found only on the left, only on the right or on both sides (pandas API reference).

Be deliberate with missing keys. Pandas warns that null merge keys on both sides match each other, unlike usual SQL join behavior. Rejecting blank keys before the merge prevents unrelated missing identifiers from being paired.

Aggregate first when the public table needs one row per entity

If the published result should contain one station per row, summarize the observations before joining:

station_summary = (
    observations.groupby("station_id", as_index=False)
    .agg(
        observation_count=("observed_date", "size"),
        mean_pm25=("pm25", "mean"),
        latest_observed_date=("observed_date", "max")
    )
)

published = stations.merge(
    station_summary,
    on="station_id",
    how="left",
    validate="one_to_one",
    indicator=True
)

published.to_csv("stations_public.csv", index=False)

Name and document every aggregation. A mean, latest value and record count answer different questions; none is a neutral way to “remove duplicates.” Also state how missing measurements are handled.

Before publication, verify:

1. The output grain is stated in plain language.
2. Key columns contain no blanks.
3. Repeated keys have been explained or corrected.
4. Merge validation matches the intended relationship.
5. The output row count matches the prediction for that relationship.
6. Unmatched keys have been reviewed.
7. Measures, dates and provenance remain documented.

If the source files are versioned, repeat the key audit for every release; a formerly unique key can become non-unique. How to Compare Two Versions of a Public CSV Dataset covers schema drift and duplicate-key checks.

Once stations_public.csv passes these tests and contains no sensitive information, it can be uploaded to TablePage as a public dataset page with a filterable table. Publish the join key, output grain, aggregation method and source dates alongside it so readers can interpret repeated entities and totals correctly.