Duplicate rows: define the observation key before deleting
A duplicate row is a repeated observation, not simply a repeated company name or number. Define the observation key before removing records. Two identical values can describe different segments, while two different values can be competing versions of the same fact. An automatic cleanup that ignores those distinctions can erase real disclosure or preserve double counting.
Start with the table’s grain
Write a sentence describing what one row represents. For a financial fact, that might be issuer, reporting period, consolidated or segment scope, measure, unit and version. A source-page locator helps you inspect the record but is not necessarily part of its economic identity. One fact copied onto three pages is still one reported fact.
The W3C tabular data model describes rows, columns and keys for tabular information. Those concepts support explicit identity rules; the standard does not decide which financial version or segment is appropriate for your analysis. That decision requires reading the disclosure and retaining the relevant dimensions.
A hypothetical cleanup decision
Assume an invented issuer reports revenue of 80 for each of two regions. An extraction tool also collects the consolidated revenue of 160 from both the financial statements and a management slide. Finally, an amended statement changes that consolidated amount to 158. Sorting on company and period alone makes these five records look like duplicates, even though they need three different treatments.
| Records | Identity question | Appropriate treatment |
|---|---|---|
| North 80 and South 80 | Different regional scope? | Keep both regional observations |
| Consolidated 160 from two locations | Same measure, scope and version? | Keep one fact with two provenance links |
| Original 160 and amended 158 | Competing reporting versions? | Preserve both, select version for the stated task |
The two regions happen to have equal values; equality does not establish duplication. The repeated 160 has different locations; separate locations do not establish independence. The amended 158 differs numerically, but it may replace the earlier value for a current financial comparison. For a historical information study, both publication versions may matter.
Separate exact matches from semantic matches
An exact full-row match is a useful starting point, particularly for accidental file imports. Keep an import log so you can trace why it appeared twice. Semantic duplication requires a richer comparison: normalize identifiers and units, then inspect reporting scope, period type and version. Quarterly and year-to-date revenue should never collapse merely because the numeric value happens to coincide.
Formatting differences create another trap. A value of 1.6 million and 1,600 thousand may represent the same measure. Preserve the raw strings and units, create a separately documented normalized value, and compare within a tolerance justified by disclosed rounding. A broad tolerance that treats every nearby number as identical can erase legitimate revisions.
Use a review queue instead of silent deletion
Create candidate groups by the proposed economic key. Assign reasons such as exact import repetition, repeated publication, revised disclosure or unresolved dimension. Inspect groups where values conflict or units differ. Save retained and excluded row identifiers together with the decision reason. Reversibility matters when a later footnote reveals that two similar records actually cover different populations.
Reconcile totals and unique observation counts after cleanup. In the example, the regional table should still total 160, while the chosen amended consolidated table contains 158. That discrepancy requires explanation; do not force them to agree by removing a region or inventing an allocation. The source may not have revised the regional presentation, or the scope may differ.
Bound the conclusion
No key is universally correct. A current valuation model, a disclosure-history archive and a model-evaluation dataset need different version rules. State the analytical purpose beside the key, preserve the original extraction and quarantine unresolved cases. AI can propose candidate duplicates, but resemblance is not a sufficient deletion instruction.
FAQ
Can I deduplicate by issuer and date?
Only if those fields uniquely identify the observation for your task. Financial tables usually need additional measure, scope, unit and version dimensions.
Should a correction overwrite the original row?
Preserve the original and link the correction. Then expose the chosen version in the derived dataset so that current and historical analyses remain distinguishable.