Standardizing Phone Numbers in a CSV File With Python

Phone numbers are the field where people write the most formats for the same thing. (415) 555-0132, 415-555-0132, +1 415 555 0132, 4155550132 and 415.555.0132 are one number, and a join, a dedupe or a dialer treats them as five. The fix is to convert to one canonical form, E.164: a plus sign, the country code and the national number with no punctuation (+14155550132). There are two ways to get there, a regular expression you can read in a minute and a library that knows the numbering plans of every country, and the choice depends on one question: do you only need the shape right, or do you need the number to exist? This article runs both on the same 166,475 phone values. It is part of the cleaning series, and phone columns are one of the places where leading zeros and types matter, since a phone is text.

The regex version

For US and Canadian numbers (country code 1) the rule is short: drop an extension, keep the digits, drop a leading 1 if there are 11 digits, and accept exactly 10:

def norm_phone(text):
    """US numbers to +1XXXXXXXXXX; anything else is returned as digits with a flag."""
    if text is pd.NA or text is None:
        return None
    digits = re.sub(r"\D", "", re.sub(r"(ext\.?|x)\s*\d+$", "", str(text), flags=re.I))
    if len(digits) == 11 and digits.startswith("1"):
        digits = digits[1:]
    return "+1" + digits if len(digits) == 10 else None

import sys, time, importlib.metadata
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
import phonenumbers
from phonenumbers import PhoneNumberFormat as F
from clean import *

print("phonenumbers", importlib.metadata.version("phonenumbers"))
samples = ["(415) 555-0132", "415-555-0188", "+1 415 555 0101", "4155550177", "415.555.0132", "555-0100 ext. 12", "+380 44 123 4567", "+44 20 7946 0958", "12345", "", "call me"]
print(f"{'input':20s} {'regex':14s} {'phonenumbers (US default)':28s} valid  possible")
for s in samples:
    try:
        n = phonenumbers.parse(s, "US")
        lib = phonenumbers.format_number(n, F.E164) + (f" x{n.extension}" if n.extension else "")
        valid, possible = phonenumbers.is_valid_number(n), phonenumbers.is_possible_number(n)
    except phonenumbers.NumberParseException as e:
        lib, valid, possible = f"error: {e._msg[:18]}", "", ""
    print(f"{s!r:20s} {str(norm_phone(s)):14s} {lib:28s} {str(valid):6s} {possible}")

df = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))[0].apply(lambda c: c.str.strip())
ph = df["phone"]
has = ph != ""
t = time.perf_counter(); regex = ph[has].map(norm_phone); t_re = time.perf_counter() - t
t = time.perf_counter()
def check(s):
    try:
        n = phonenumbers.parse(s, "US")
    except phonenumbers.NumberParseException:
        return "unparseable"
    if phonenumbers.is_valid_number(n): return "valid"
    return "possible" if phonenumbers.is_possible_number(n) else "impossible"
lib = ph[has].map(check); t_lib = time.perf_counter() - t
print(f"\n{int(has.sum())} phone values ({int((~has).sum())} empty)")
print(f"regex: {int(regex.notna().sum())} normalized, {int(regex.isna().sum())} not, {t_re:.2f}s")
print(f"phonenumbers: {lib.value_counts().to_dict()}, {t_lib:.2f}s")
agree = (regex.notna() == (lib == 'valid'))
print(f"regex accepted but the library says not valid: {int((regex.notna() & (lib != 'valid')).sum())}")
ex = ph[has][regex.notna() & (lib != 'valid')].head(4).tolist()
print("examples:", ex)

python /batches/csv-0001-0200/lab/clean/t0116.py

phonenumbers 9.0.41
input                regex          phonenumbers (US default)    valid  possible
'(415) 555-0132'     +14155550132   +14155550132                 True   True
'415-555-0188'       +14155550188   +14155550188                 True   True
'+1 415 555 0101'    +14155550101   +14155550101                 True   True
'4155550177'         +14155550177   +14155550177                 True   True
'415.555.0132'       +14155550132   +14155550132                 True   True
'555-0100 ext. 12'   None           +15550100 x12                False  True
'+380 44 123 4567'   None           +380441234567                True   True
'+44 20 7946 0958'   None           +442079460958                True   True
'12345'              None           +112345                      False  False
''                   None           error: The string supplie           
'call me'            None           error: The string supplie           

166475 phone values (33303 empty)
regex: 166475 normalized, 0 not, 0.28s
phonenumbers: {'valid': 93552, 'possible': 72923}, 4.09s
regex accepted but the library says not valid: 72923
examples: ['+1 896 744 7005', '436-354-1359', '+1 677 691 7927', '957.432.3275']

The first table has the regex column. The five US spellings all came out as the same string: +14155550132 for (415) 555-0132 and 415.555.0132, +14155550188, +14155550101 and +14155550177 for the rest. That is the job, and the regex does it in 0.28 s for 166,475 values. Where it stops is visible in the rest of the table. +380 44 123 4567 (Ukraine) and +44 20 7946 0958 (the UK) returned None, since they are not ten digits after the country code, and 555-0100 ext. 12 returned None because it has seven digits and no area code. 12345, the empty string and call me also returned None, correctly, and the empty string should not be an error.

You can extend the regex with more countries, and the cost is that you now own a numbering-plan database. Country codes vary from one to three digits, national numbers vary in length, some countries have a trunk prefix 0 that is dropped in international format, and a handful of regions share a code. That is the argument for a library.

The library version

phonenumbers takes a string and a default region, and returns a parsed number you can validate and format:

n = phonenumbers.parse("(415) 555-0132", "US")
phonenumbers.format_number(n, phonenumbers.PhoneNumberFormat.E164)   # '+14155550132'
phonenumbers.is_valid_number(n), phonenumbers.is_possible_number(n)

The second column of the table shows its results. The five US spellings agreed with the regex, and the library went further. +380 44 123 4567 became +380441234567 and is valid; +44 20 7946 0958 became +442079460958 and is valid. The extension in 555-0100 ext. 12 was parsed out: the number is +15550100 with extension 12, flagged not valid (seven digits with no area code is not a complete US number) and possible. 12345 became +112345, not valid and not possible. Empty strings and call me raised NumberParseException, which you catch and treat as "no number".

Two notions of correctness are in play. is_possible_number checks the length against the country's rules, a cheap test that rules out the clearly wrong. is_valid_number checks the number against the country's actual number ranges: area codes that exist, exchanges that are allocated. Valid is stricter. Neither tells you the number is assigned to anybody, or that it answers.

What the regex let through

On the 166,475 non-empty phone values in the generated file, the regex normalized every one: regex: 166475 normalized, 0 not. The library said:

phonenumbers: {'valid': 93552, 'possible': 72923}
regex accepted but the library says not valid: 72923

93,552 valid and 72,923 possible but not valid. That is 43.8% of the numbers the regex approved. The examples it printed (+1 896 744 7005, 436-354-1359, +1 677 691 7927, 957.432.3275) have ten digits and plausible punctuation, and their area codes (896, 436, 677, 957) are not assigned. The data is random, so the proportion is an artifact, and it makes the point: a 10-digit number is not necessarily a phone number. Real data will have fewer such values and they will be harder to spot (typos, a transposed digit, a made-up 555 number). A regex checks shape. For anything that dials, texts or verifies, validity is a separate step, and the right library does it.

The library took 4.09 s for the 166,475 values, about 25 microseconds each, and the regex 0.28 s. Both are fine for a file; at hundreds of millions of rows, think about doing the library step only on the rows the regex could not decide, or on a deduplicated set.

Practical rules

Keep the original. Write the E.164 number to a new column and keep the raw one. A normalization that was wrong (a default region that did not apply, an extension swallowed) cannot be undone from the cleaned column.

Choose the default region deliberately. phonenumbers.parse("4155550177", "US") assumes the number is American. A number without a + is only meaningful in the context of a country, so use the row's own country column when you have one ("US", "GB") and fall back to a single region only for a file that is all one country.

Split the extension. Store extension in its own column. A dialer that receives +15550100 x12 as text has a problem, and E.164 has no place for it.

Do not call formatting validation. A number can be perfectly formatted and unreachable. If you need to know that a number works, you need a verification step, such as sending a code, and no amount of cleaning replaces it.

Treat phone numbers as personal data. They identify people. Do not paste examples into logs, tickets or chat from a real file; the examples in this article are synthetic for that reason, and a cleaning job's report should show counts, not values.

Blanks stay blank. 33,303 of the 199,778 rows had no phone. An empty phone is missing data, not a malformed number, and the cleaned column should be empty for them, as the missing values article argues.

How to tell it worked

Count the rows: normalized plus unparseable plus empty equals total. Check that every normalized value matches ^\+[1-9]\d{1,14}$, which is the E.164 shape (no leading zero in the country code, at most 15 digits). Take ten rows, call the library on them by hand and compare. Count distinct numbers before and after: the drop is the number of duplicates you just exposed. Look at a sample of the possible but not valid rows and see whether they are typos you can fix or junk to drop.

What E.164 formatting does not prove

It does not prove the number exists, that it belongs to the person in the row, that it is mobile or landline, or that anyone will answer. It makes comparison, dedupe and storage reliable, which is the cleaning goal. Validity is a policy decision: flag the not-valid rows, keep them in a review file, and decide with the owner of the data whether to drop, fix or keep them. The next article splits columns that contain more than one value, such as a name or an address.