Map customer PIN codes to district and state without duplicate rows

The trap in the directory

The Department of Posts publishes the "All India Pincode Directory" as an open dataset, updated monthly, on the government portal data.gov.in and mirrored on the IndiaAI AIKosh catalogue. It is the natural reference for this job. Its grain is the post office, not the PIN: a busy urban PIN can have several sub-offices listed against it, each with its own row. Column names have changed between releases, so read the file you download and map its headers onto four names of your own. The ones used below (pincode, officename, district, statename) are illustrative.

A VLOOKUP on this file quietly returns the first matching office, which is usually fine for the state and sometimes wrong for the district. A pandas merge or SQL join does something worse: it returns every match, so a customer in a PIN with three offices becomes three customers, and every total built on the result is inflated.

Why customer PINs arrive broken

Before any lookup, look at what is actually in the PIN column of a CRM or billing export. Typical defects:

Treat the PIN as text from the moment you read the file. In Excel, set the column to Text before pasting, or use =TEXT(A2,"000000") only if you are certain the value was a number that lost nothing else. In pandas, read with dtype=str.

The format rule is simple: six digits, and the first digit is between 1 and 9. A value that passes this test may still not exist, so the directory lookup is the real check.

Collapse first, then join

Everything below is synthetic. The 999999 rows are invented to stand in for a PIN whose offices sit in two districts; real directories have such cases along district borders.

import pandas as pd

# Synthetic extract shaped like a post-office-level PIN directory:
# one row per post office, so a PIN usually appears many times.
directory = pd.DataFrame({
    "pincode": ["201301", "201301", "201301", "201304", "110092", "110092",
                "122001", "122001", "999999", "999999"],
    "officename": ["Sector 19 SO", "Sector 20 SO", "Sector 27 SO", "Surajpur SO",
                   "Shakarpur SO", "Laxmi Nagar SO", "Gurgaon HO", "Sector 5 SO",
                   "Border Town SO", "River Bank BO"],
    "district": ["Gautam Buddha Nagar", "Gautam Buddha Nagar", "GAUTAM BUDDHA NAGAR",
                 "Gautam Buddha Nagar", "East Delhi", "East Delhi",
                 "Gurugram", "Gurgaon", "District A", "District B"],
    "statename": ["UTTAR PRADESH", "Uttar Pradesh", "Uttar Pradesh", "Uttar Pradesh",
                  "DELHI", "Delhi", "HARYANA", "Haryana", "State X", "State X"],
})
# The two 999999 rows imitate a PIN whose offices fall in two districts.

# Old and new names for the same district, agreed with the business once.
DISTRICT_ALIASES = {"Gurgaon": "Gurugram"}

customers = pd.DataFrame({
    "cust_id": [1, 2, 3, 4, 5, 6, 7],
    "pin_raw": ["201301", " 201 304", 110092.0, "122001", "02013", "999999", None],
})

# 1. Clean the customer PIN: text, digits only, exactly six, first digit not 0.
pin = (customers["pin_raw"].astype("string")
       .str.replace(r"\.0$", "", regex=True)          # 110092.0 from an Excel float
       .str.replace(r"\D", "", regex=True))
customers["pin"] = pin.where(pin.str.fullmatch(r"[1-9]\d{5}"))

# 2. Collapse the directory to ONE row per PIN before joining.
d = directory.assign(district=directory["district"].str.strip().str.title()
                                .replace(DISTRICT_ALIASES),
                     statename=directory["statename"].str.strip().str.title())
per_pin = d.groupby("pincode").agg(
    districts=("district", lambda s: sorted(set(s))),
    state=("statename", lambda s: s.iloc[0] if s.nunique() == 1 else None),
    offices=("officename", "size"))
per_pin["district"] = per_pin["districts"].map(lambda x: x[0] if len(x) == 1 else None)
per_pin["lookup_status"] = per_pin["districts"].map(
    lambda x: "ok" if len(x) == 1 else "ambiguous: " + " / ".join(x))

out = customers.merge(per_pin[["district", "state", "lookup_status"]],
                      left_on="pin", right_index=True, how="left", validate="many_to_one")
out.loc[out["pin"].isna(), "lookup_status"] = "invalid PIN"
out["lookup_status"] = out["lookup_status"].fillna("PIN not in directory")
print(out[["cust_id", "pin_raw", "pin", "district", "state", "lookup_status"]].to_string(index=False))
assert len(out) == len(customers)          # the join did not multiply customers

Seven customers go in and seven come out. 201301 and the space-damaged 201304 both resolve to Gautam Buddha Nagar in Uttar Pradesh, the float 110092.0 resolves to East Delhi, and 122001 resolves to Gurugram even though one directory row still says Gurgaon. Customer 6 is marked "ambiguous: District A / District B", and customers 5 and 7 are marked invalid.

Three choices worth defending in review

Title case before comparing. The directory mixes "GAUTAM BUDDHA NAGAR" and "Gautam Buddha Nagar". Without normalising case, one PIN would look like it spans two districts that are really one.

An explicit alias table. Districts get renamed and new districts are carved out. Keep old-to-new names in a small, dated mapping sheet that someone in the business has signed off, rather than editing directory rows by hand. When a district is split, an alias is not enough: PINs in the old district may now belong to either new one, and those should go on the ambiguous list until checked.

`validate="many_to_one"` plus a row-count assert. Many customers can share a PIN; each PIN must appear once on the right. If a future directory release sneaks in a duplicate after your collapse step, pandas raises an error instead of silently doubling rows, and the assert is a second line of defence.

A one-row-per-PIN table in Power Query

If the lookup must live in a workbook, build the one-row-per-PIN table on a separate sheet first. Power Query handles it well: load the directory, trim and capitalise the district column, merge an alias table, then Group By PIN with "Count Distinct Rows" on district and "Min" on district. Rows where the distinct count is above one are your ambiguous list. The customer sheet then uses =XLOOKUP(B2, PinTable[pincode], PinTable[district], "PIN not in directory"), which is safe because the lookup table is now unique.

Keep the directory dated

Save the downloaded file with its download date in the name and note it on the output. A PIN that was missing in one release can appear in the next, and when someone asks why a customer moved district between two monthly reports, the dated file is the answer.

Location fields cleaned this way feed branch territories, delivery planning and regional dashboards. Joining reference data safely, with checks that catch a bad join before it reaches a report, runs through much of the practice work in our Data Analyst programme. If your customer names are as messy as the PINs, match duplicate customer names that differ only in honorifics and spelling.