An exact join on an ID column should be the easy part of a matching job. Then it returns half the rows you expected, or links two accounts that have nothing to do with each other, and the cause is often not your matching logic. It happened earlier, when a tool read the file and decided your IDs were numbers.

The pandas behavior below was reproduced with pandas 3.0.2. Where we describe Excel we cite Microsoft’s documentation instead of demonstrating it, since we did not test Excel ourselves.

Leading zeros

A US ZIP code such as 02134, a customer number such as 00123 and a 14-digit GTIN such as 00012345678905 are all identifiers, not quantities. The moment a tool reads them as numbers, the zeros are gone:

import io
import pandas as pd

raw = """customer_id,zip,gtin
00123,02134,00012345678905
00456,10001,04006381333931
00789,94105,
"""

default = pd.read_csv(io.StringIO(raw))
print(default.dtypes.to_dict())
print(default.to_string(index=False))

as_text = pd.read_csv(io.StringIO(raw), dtype=str, keep_default_na=False)
print(as_text.to_string(index=False))
{'customer_id': dtype('int64'), 'zip': dtype('int64'), 'gtin': dtype('float64')}
 customer_id   zip         gtin
         123  2134 1.234568e+10
         456 10001 4.006381e+12
         789 94105          NaN
customer_id   zip           gtin
      00123 02134 00012345678905
      00456 10001 04006381333931
      00789 94105

Three things happened to the default read. customer_id and zip lost their zeros. The gtin column became floating point because one cell is empty, so its codes now print as 1.234568e+10.

The second read, with dtype=str, keeps every character. The pandas documentation for read_csv describes str (or object) as the way to keep values from being interpreted. Setting keep_default_na=False keeps empty cells as empty strings instead of turning them into missing values.

Excel behaves the same way by default, according to Microsoft’s support article on keeping leading zeros and large numbers: it removes leading zeros automatically. We did not test what Excel does when you open and re-save a CSV, but the same rules would apply to any ID column it reads as numbers.

One blank cell changes the whole column

The pandas gotchas page explains the second effect: because the default missing-value marker is a float, a column of integers that contains a missing value is converted to floating point. That is harmless for quantities and destructive for long identifiers:

import io
import pandas as pd

raw = """account,name
12345678901234567,Acme
12345678901234568,Beta
,Gamma
"""
df = pd.read_csv(io.StringIO(raw))
print(df["account"].dtype, df["account"].tolist())
print("first two equal:", df["account"].iloc[0] == df["account"].iloc[1])

without_gap = pd.read_csv(io.StringIO("account,name\n12345678901234567,Acme\n12345678901234568,Beta\n"))
print(without_gap["account"].dtype, without_gap["account"].tolist())
float64 [1.2345678901234568e+16, 1.2345678901234568e+16, nan]
first two equal: True
int64 [12345678901234567, 12345678901234568]

Two different 17-digit accounts, …567 and …568, became the same number the moment one cell below them was empty. Without the empty cell the column stays int64 and both values survive. Floating-point numbers can represent every integer up to 2 to the power of 53, which is 9,007,199,254,740,992, but not every integer above it; the 17-digit values here are above that limit.

That is the more dangerous direction. A missed match shows up as fewer matches than you expected, or as lower recall if you have labels. A collapsed ID shows up as an exact match between two accounts that are different, and nothing in the match flags it.

What Excel does to long numbers

Microsoft’s documentation gives the rules. In its specifications and limits, number precision is listed as 15 digits. The article on leading zeros and large numbers says that Excel converts large numbers to scientific notation (its example is 1.23E+15) and that for numbers of 16 or more digits, such as credit card numbers, digits after the 15th become zero.

The same article lists several ways to prevent it, and each has a catch:

  • Format the cells as Text before entering or pasting the data. This does not change numbers that were already entered.
  • Type an apostrophe before the number. Fine for hand entry; it does not help with a file someone else exported.
  • A custom format such as 00000 for postal codes. This works for codes under 16 digits, and does not restore zeros that were removed earlier.
  • Import through the Data tab (From Text/CSV, the Get & Transform tools) and format the column as text.

The practical consequence is about order. If a file was re-saved in Excel, any digits that were rounded or truncated are already gone from it. Padding can restore lost leading zeros on fixed-width codes (see below), but nothing in a matching tool restores digits that were rounded to zero.

How the damage reaches your match

A join between mismatched types fails loudly, or quietly. pandas refuses to merge an integer column with a text column:

import pandas as pd

left = pd.DataFrame({"zip": [2134, 10001]})        # read as numbers
right = pd.DataFrame({"zip": ["02134", "10001"]})  # kept as text

try:
    left.merge(right, on="zip")
except ValueError as e:
    print("ValueError:", str(e).split(". If you")[0])

print(left.astype(str).merge(right, on="zip").to_string(index=False))
print(left.astype(str).apply(lambda c: c.str.zfill(5)).merge(right, on="zip").to_string(index=False))
ValueError: You are trying to merge on int64 and str columns for key 'zip'
  zip
10001
  zip
02134
10001

The error is the good outcome. The common “fix” of casting everything to text produces the second result: 2134 and 02134 are different strings, so the merge silently returns only 10001. Padding to the known width (zfill(5)) is what brings the second row back.

Character-similarity scores can rank a lost zero above or below a different code. With Python’s difflib similarity ratio, 2134 against 02134 scores 0.889, higher than 02134 against 02135 (0.800), so a lost zero looks closer than a different ZIP code here. The GTIN case goes the other way: the same GTIN with its leading zeros lost (00012345678905 against 12345678905) scores 0.880, while a different GTIN that differs only in its last digit (00012345678905 against 00012345678906) scores 0.929. A threshold tuned on one kind of column behaves differently on the other.

Detecting it

You cannot see a lost zero in a value, but you can see its trace across the column. Read the file as text and look at the lengths and shapes of the values:

import pandas as pd

def audit_id_column(values, name):
    s = pd.Series(values, dtype=str)
    s = s[s != ""]
    findings = []
    lengths = s.str.len().value_counts().sort_index().to_dict()
    if len(lengths) > 1:
        findings.append(f"mixed lengths {lengths}")
    if s.str.match(r"^\d+\.0$").any():
        findings.append("values ending in .0 (a float was written out)")
    if s.str.match(r"^\d(\.\d+)?[eE][+-]?\d+$").any():
        findings.append("scientific notation (digits already lost)")
    print(f"{name}: " + ("; ".join(findings) if findings else "no problems found"))

audit_id_column(["2134", "10001", "94105"], "zip, damaged")
audit_id_column(["02134", "10001", "94105"], "zip, intact")
audit_id_column(["1234567890123456", "1.23457E+15"], "account")
audit_id_column(["12345678905.0", "4006381333931.0"], "gtin, from float")
zip, damaged: mixed lengths {4: 1, 5: 2}
zip, intact: no problems found
account: mixed lengths {11: 1, 16: 1}; scientific notation (digits already lost)
gtin, from float: mixed lengths {13: 1, 15: 1}; values ending in .0 (a float was written out)

This is a screen, not a proof. An ID column that is supposed to have mixed lengths will trigger the first check, and a damaged column where every value happened to lose the same number of zeros will not. Fixed-length codes are easier: when you know a GTIN is 14 digits, a length check catches a lost zero. A check digit alone does not, because leading zeros add nothing to the weighted sum (see how GTIN check digits work).

Repairing it, and what you cannot repair

  • Missing leading zeros on fixed-width codes can be restored by padding to the known width: zfill(5) for five-digit ZIP codes, zfill(14) for GTIN-14. Only do it when you are sure of the width. Phone numbers and many account numbers do not have a fixed one.
  • Truncated or rounded digits cannot be restored. If the value is 1.23457E+15 or ends in zeros that were not there, the original is gone. Re-export from the source system.
  • Collapsed IDs may look fine. After a float round trip, two different IDs can be identical. Count duplicates in the ID column before and after loading; any increase means collisions.

The simplest defense is a habit: read every ID-like column as text, convert only the columns that are really quantities, and check lengths before you trust an exact join. For the cleaning steps that come after, see data cleaning before matching.

Try it free →

Keep reading