Python and SQL for data profiling

Why profiling moves from Excel to code

Excel profiling with COUNTIF and pivots breaks beyond a few hundred thousand rows, is hard to repeat for the next mock load, and nobody can see how a number was produced. A 30-line Python script runs identically on mock 1, mock 2 and cutover, and an AI assistant can read its output and propose the next rules.

Load the extract without damaging it

If pandas guesses types, supplier 0000100045 becomes 100045 and PIN codes lose leading zeros. Read everything as text with dtype=str, and say explicitly which placeholders (#, N/A, a single space) mean missing, so fill rates are honest. Then print the shape: 119 columns instead of 120 means a delimiter inside a name shifted everything.

import pandas as pd

df = pd.read_csv(
    "bp_extract.csv",
    dtype=str,                      # keep leading zeros and codes as text
    keep_default_na=False,          # do not guess what is missing...
    na_values=["", " ", "#", "N/A", "NA", ".", "-"],  # ...say it explicitly
    encoding="utf-8",
)
df = df.apply(lambda col: col.str.strip())   # trim whitespace

print(df.shape)          # (rows, columns)
print(df.head(3).T)      # transposed: easier to read wide SAP extracts

The column profile in one table

Four numbers per column: fill rate, distinct count, top value and its share. A REGION 99% filled but 60% one value was probably defaulted. A TAX_NUMBER with fewer distinct values than filled rows means duplicates or dummy IDs. Split by country or source system too: that is where you find the one system causing most problems.

profile = pd.DataFrame({
    "filled_%": df.notna().mean().mul(100).round(1),
    "distinct": df.nunique(dropna=True),
    "top_value": df.mode(dropna=True).iloc[0],
})
print(profile.sort_values("filled_%"))

# Fill rate of key fields per country
key_cols = ["POST_CODE1", "REGION", "TAX_NUMBER", "SMTP_ADDR"]
print(df.groupby("COUNTRY")[key_cols].agg(lambda s: s.notna().mean()).round(2))

Try it: complete the profiling script

df = pd.read_csv("bp.csv", ______fullmatchdtype=intcountnotnanuniquedtype=strcontainsisna, keep_default_na=False)
fill_rate = df.______fullmatchdtype=intcountnotnanuniquedtype=strcontainsisna().mean()
distinct = df.______fullmatchdtype=intcountnotnanuniquedtype=strcontainsisna()
pin_ok = df["POST_CODE1"].str.______fullmatchdtype=intcountnotnanuniquedtype=strcontainsisna(r"[1-9]\d{5}", na=False)

Pattern checks: filled is not valid

Fill rate says a value exists, not that it is right. Write one tested regex per critical field: an Indian PIN is six digits not starting with 0; a GSTIN is 15 characters (state code, PAN block, entity digit, Z, check character). Then add cross-field rules, such as GSTIN state code against address region. Example values here are synthetic.

Try it: which check catches it?

Walk through a profiling run

From numbers to cleansing rules

Each finding gets a stable rule ID, an owner and a fix route, so you can report trends across mocks. When data is already in a staging database, the volume metrics are a few lines of SQL: COUNT(post_code1) / COUNT(*) is a fill rate, because COUNT(column) skips NULLs. Use the same rule IDs in SQL and Python.

Try it: the suspicious fill rate

Check yourself