How to Preserve Row Identity Through Data Cleaning

Cleaning can make a dataset look more consistent while quietly making individual records harder to trace. If rows are filtered, deduplicated, merged or reordered without stable identifiers, an analyst may be unable to explain where a result came from—or whether a source record disappeared by mistake. Preserving row identity prevents that ambiguity and makes the final dataset easier to review, reproduce and defend.

Why row identity gets lost

A row's displayed position is not a reliable identity. Row 125 in a CSV can become row 87 after filtering. A pandas index can be reset, duplicated by concatenation or changed during a merge. Sorting also moves records without changing their meaning.

Several common cleaning operations complicate lineage:

• Removing duplicate records reduces multiple source rows to one output row.
• Filtering invalid observations removes rows from the prepared dataset.
• Aggregating transactions creates one summary row from many inputs.
• Joining reference data can duplicate a source row when the join key is not unique.
• Splitting a field or exploding a list can create multiple outputs from one row.
• Combining files can produce identical row numbers from different sources.

A stable business key, such as employee_id, may help, but it does not always solve the problem. The key may be missing, malformed or duplicated—the exact conditions that cleaning is intended to investigate. It may also identify an entity rather than a particular source record. An employee can legitimately have several payroll rows.

The safer approach is to preserve both business identity and source identity.

Create a source identity before changing anything

Add lineage fields immediately after loading the data, before filtering, sorting or resetting the index. For a single file, a source row number is often enough for local investigation:

1import pandas as pd23raw = pd.read_csv("orders.csv")4working = raw.reset_index(names="_source_row")5working["_source_file"] = "orders.csv"

The pair _source_file and _source_row points back to one input record. Keep these fields internal if they should not appear in a published table, but retain them in the prepared dataset or an accompanying audit artifact.

For multiple files, include a stable file identifier. A bare row number is ambiguous because every file can contain a row zero. Useful lineage fields include:

• Source system or feed name
• File name or delivery identifier
• Sheet name for Excel workbooks
• Source row number
• Ingestion date or batch identifier

Do not use processing time alone as row identity. Rerunning the same input would assign a different value, weakening reproducibility. Likewise, avoid relying only on a hash of cleaned values. Cleaning can change those values, and two genuinely repeated records can have the same hash.

When privacy or file naming rules prevent retaining a raw file name, create a stable batch identifier and store its mapping in controlled metadata.

Separate source identity from business keys

Source identity answers, “Which input row produced this record?” A business key answers, “Which real-world object or event does this record represent?” Both questions matter.

Consider this input:

| _source_row | order_id | item | quantity | |---:|---|---|---:| | 18 | A104 | Cable | 2 | | 19 | A104 | Adapter | 1 | | 20 | A105 | Cable | -1 |

order_id is not unique because an order can contain several line items. Deduplicating on order_id would discard valid data. The source row distinguishes the two A104 records, while a composite business key such as order_id plus item may represent an order line.

Before cleaning, document the expected grain in one sentence. For example: “Each row represents one item within one customer order.” This simple statement guides duplicate checks and prevents an analyst from treating every repeated identifier as an error.

If no trustworthy business key exists, do not manufacture meaning from the current row order. Preserve source identity, investigate candidate keys and record any assumptions used to form a composite key.

Reconcile rows after every structural operation

Value standardization usually leaves row coverage unchanged. Structural operations—filters, deduplication, joins, aggregation and explosion—need explicit reconciliation.

For a filter, create a reason before removing anything:

1working["_exclusion_reason"] = pd.NA2working.loc[working["quantity"] < 0, "_exclusion_reason"] = "negative_quantity"34accepted = working[working["_exclusion_reason"].isna()].copy()5excluded = working[working["_exclusion_reason"].notna()].copy()

This produces an accepted dataset and an exclusion table. The exclusion table preserves the original record, source identity and stated reason. It is more informative than a log entry saying that one or more rows were removed.

For deduplication, keep a mapping from every duplicate source row to the retained representative. Record the duplicate rule as well. Exact equality across all columns is different from matching on a customer ID and transaction date.

For joins, check the intended relationship before merging. A supposed many-to-one lookup with duplicate keys can multiply rows. In pandas, the merge indicator can also show whether records matched on the left, right or both sides:

1joined = accepted.merge(2    product_lookup,3    on="product_code",4    how="left",5    indicator=True,6    validate="many_to_one",7)

The validation condition turns an assumed relationship into an explicit check. The indicator supports investigation of unmatched codes without confusing them with rows removed earlier.

Build a row reconciliation table

A useful reconciliation table summarizes each structural stage while retaining detailed row-level evidence elsewhere. It might contain:

| Stage | Input rows | Output rows | Explanation | |---|---:|---:|---| | Load | 10,240 | 10,240 | Source file loaded | | Validity filter | 10,240 | 10,218 | Excluded records stored with reasons | | Exact duplicate review | 10,218 | 10,211 | Duplicate mapping retained | | Product lookup | 10,211 | 10,211 | Many-to-one join preserved coverage |

These figures are examples of structure, not targets. A lower row count is not inherently evidence of better quality, and an unchanged count does not prove that a join was correct. The explanation and row-level mappings are what make the summary useful.

Add assertions for invariants that should always hold. If a lookup must not alter coverage, assert that the source identity remains unique and that the row count is unchanged. If an aggregation intentionally reduces rows, verify that every accepted source identifier is represented in the group mapping.

Avoid common lineage mistakes

Do not overwrite raw files. Keep immutable inputs and write prepared outputs separately. Otherwise, a source row identifier may point to content that no longer exists.

Do not discard excluded rows immediately. Quarantine them with reasons so reviewers can distinguish intentional exclusions from accidental loss.

Do not treat drop_duplicates() as a complete duplicate policy. State the comparison columns, ordering rule and reason for retaining one record over another.

Finally, do not hide technical lineage fields before validation is complete. They can be removed from a consumer-facing export while remaining in the audit package.

Actionable conclusion

For your next cleaning workflow, add source file and source row fields immediately after ingestion. Write down the dataset grain, keep business keys separate from lineage keys, and create reason-coded tables for exclusions and duplicate resolution. Reconcile coverage after every filter, join or aggregation. The goal is not to prevent all row-count changes; it is to make every intentional change traceable and every unexpected change visible.