Profile an Excel Dataset with pandas

The first hour with a new spreadsheet export decides how the rest of the work goes. Which columns are really numbers? Which are mostly empty? Is CustomerID unique, or does the same customer appear twice? Does the Postcode column hold postcodes, or also phone numbers typed in the wrong cell? df.describe() answers a fraction of that for numeric columns only. A profile answers it for every column in one table, and writing it to Excel turns it into something to discuss with whoever owns the data. This page, part of Validating Excel Data with Python, builds a profile function step by step and runs it on a realistic export.

Prerequisites

pip install pandas openpyxl xlsxwriter

customers_export.xlsx has 500 customers with an ID, name, segment, postcode, join date, annual spend, employee count and a legacy code. The export has problems that are typical and not obvious by scrolling: a duplicated ID, a segment spelled two ways, postcodes mixed with a phone number and a placeholder, two spend values stored as text, a mostly empty employee column and a legacy column that is almost entirely blank.

import pandas as pd

df = pd.read_excel("customers_export.xlsx")
print(df.shape)
print(df.dtypes.to_dict())
print(df.describe().round(1))

describe() covers the numeric and date columns — here Employees and Joined — and says nothing about the text columns, which is where most of the problems are. AnnualSpend is missing from it altogether: a text value in the column made pandas read the whole column as generic objects instead of numbers.

Step 1 — The basic per-column facts

Start with what is true of any column: its type, how many values are missing, how many are distinct, and which values are most common:

def basic_profile(df):
    rows = []
    for col in df.columns:
        s = df[col]
        top = s.value_counts(dropna=True).head(3)
        rows.append({
            "column": col,
            "dtype": str(s.dtype),
            "missing %": round(s.isna().mean() * 100, 1),
            "distinct": s.nunique(dropna=True),
            "top values": ", ".join(f"{v} ({c})" for v, c in top.items()),
        })
    return pd.DataFrame(rows)

profile = basic_profile(df)
print(profile.to_string(index=False))

The table already tells a story. Legacy code is over 99% empty and has two distinct values — a candidate for deletion. Employees is 58% empty, which matters if anything downstream divides by it. Segment has four distinct values where three were expected: business in lower case next to Business. Postcodes are 8% missing.

Step 2 — Detect columns that mix types

A column with numbers and a few stray strings is read as text, and every numeric operation on it fails or — worse — sorts "1,250.00" among the text. Count the Python types present in each column:

def type_mix(s):
    kinds = s.dropna().map(lambda v: type(v).__name__).value_counts()
    return ", ".join(f"{k}: {c}" for k, c in kinds.items())

profile["types"] = [type_mix(df[c]) for c in df.columns]
mixed = profile[profile["types"].str.contains(",")]
print(mixed[["column", "types"]].to_string(index=False))
print(df.loc[df["AnnualSpend"].map(lambda v: isinstance(v, str)), ["CustomerID", "AnnualSpend"]])

AnnualSpend holds mostly floats, three whole numbers and one string, 1,250.00 — a value formatted as text by whoever typed it. The profile names the cell, which turns "why won't this column sum?" into a specific fix. The export also had an n/a in that column, and it does not show up: read_excel treats n/a, NA, null and a few similar strings as missing by default, so it arrived as a blank. That is convenient for loading but bad for profiling, because the placeholder is itself a finding. To see such values, read with keep_default_na=False for the profile run. Postcode holds only strings, so it does not show up here — its problem is the shape of the strings, which the next step looks at.

Step 3 — Summarise the shape of the values

Replace every letter with A and every digit with 9 and count the resulting patterns. Well-behaved columns have one or two patterns; anything else stands out:

import re

def shape(v):
    return re.sub(r"[0-9]", "9", re.sub(r"[A-Za-z]", "A", str(v)))

def patterns(s, n=4):
    counts = s.dropna().map(shape).value_counts()
    return ", ".join(f"{p} ({c})" for p, c in counts.head(n).items()) + (f" … +{len(counts) - n}" if len(counts) > n else "")

profile["patterns"] = [patterns(df[c]) if df[c].dtype == object or str(df[c].dtype).startswith("str") else "" for c in df.columns]
print(profile.loc[profile["patterns"] != "", ["column", "patterns"]].to_string(index=False))
print(df["Postcode"].dropna().map(shape).value_counts().tail(3))

CustomerID has a single pattern, AA-99999, as an ID should. Postcode has the expected UK shapes plus a few that do not belong: 99999 999999 (a phone number), AAAAAAA (the word "unknown") and A99AA (a postcode typed without its space). Patterns find this kind of problem without anyone having to write a validation rule first; once found, the rules can be written — see Validate Email and Phone Formats in Excel Data.

Step 4 — Numbers, dates and text lengths

For numeric and date columns add the range; for text columns the length range, which catches truncated values and stray long entries:

def ranges(s):
    num = pd.to_numeric(s, errors="coerce") if s.dtype == object else s
    if pd.api.types.is_numeric_dtype(num) and num.notna().any():
        return f"{num.min():,.2f} to {num.max():,.2f}"
    if pd.api.types.is_datetime64_any_dtype(s):
        return f"{s.min():%Y-%m-%d} to {s.max():%Y-%m-%d}"
    lengths = s.dropna().astype(str).str.len()
    return f"length {lengths.min()} to {lengths.max()}" if len(lengths) else ""

profile["range"] = [ranges(df[c]) for c in df.columns]
print(profile[["column", "range"]].to_string(index=False))

For a mixed column like AnnualSpend, the range is computed on the values that do convert, so it still gives a sense of the scale. The join dates span January 2019 to February 2026, which is plausible; a date in 1900 or in the future would be worth a question.

Step 5 — Candidate keys and duplicates

A column that should identify each row must be complete and unique. Test every column, and list the duplicates of the one that should be the key:

profile["unique"] = [df[c].notna().all() and df[c].is_unique for c in df.columns]
print(profile.loc[profile["unique"], "column"].tolist())
dupes = df[df["CustomerID"].duplicated(keep=False)]
print(dupes[["CustomerID", "Name", "Segment"]].to_string(index=False))

CustomerID is not unique: CU-10037 appears twice, attached to two different names. Name is unique here, but a name is never a good key. Before this export is loaded anywhere with CustomerID as the primary key, someone has to decide which of the two records is right — Find Duplicate Rows in Excel with Python covers the options.

Step 6 — Write the profile to Excel

Put the profile on the first sheet, with the worst columns easy to spot, and each column's full value counts on a second sheet:

with pd.ExcelWriter("customers_profile.xlsx", engine="xlsxwriter") as xw:
    profile.to_excel(xw, sheet_name="Profile", index=False)
    wb, ws = xw.book, xw.sheets["Profile"]
    ws.set_column("A:B", 14)
    ws.set_column("C:D", 10)
    ws.set_column("E:H", 42)
    ws.conditional_format(1, 2, len(profile), 2, {"type": "data_bar", "bar_color": "#F59E0B", "bar_solid": True})
    ws.freeze_panes(1, 1)
    counts = pd.concat({c: df[c].astype(str).value_counts().head(20) for c in df.columns}, names=["column", "value"])
    counts.rename("count").to_frame().to_excel(xw, sheet_name="Top values")
print(profile[["column", "missing %", "distinct", "unique"]].to_string(index=False))

A data bar on the missing-percentage column makes the emptiest columns jump out. The profile is small, readable and specific — the right thing to send to the owner of the export with the question "which of these are expected?"

Common pitfalls

• Profiling after cleaning. Profile the raw export first; cleaning hides exactly the problems the profile should show.
• Letting pandas guess types silently. A text column that should be numeric is a finding; report it rather than coercing.
• value_counts without dropna. Decide whether blanks count as a value; report them separately as the missing share.
• Treating unique columns as keys. Uniqueness in one export is not a guarantee; check what the column means.
• Profiling huge sheets row by row. map over millions of values is slow; profile a sample for patterns and the full data for counts.

Conclusion

Before cleaning anything, build a one-row-per-column profile: type, missing share, distinct count and top values; the Python types mixed in each column; the shapes of text values; ranges and lengths; and whether each column is complete and unique. Write it to Excel with the worst columns highlighted, and use it to agree with the data's owner which problems are expected and which need fixing at the source.

Frequently asked questions

How do I profile a dataset in pandas? Loop over the columns and collect the dtype, the share of missing values, the number of distinct values and the most common values for each, then add checks for mixed types, value patterns, ranges and uniqueness. Collect the results into a DataFrame with one row per column.

Why doesn't df.describe() show my text columns? By default describe() summarises numeric columns only. describe(include="all") adds count, unique, top and freq for text columns, but still says nothing about blanks, mixed types or patterns.

How do I find a column that mixes numbers and text? Map each non-missing value to its Python type name and count the types per column. A column that reports both float and str has numbers and text mixed, usually because of placeholders or formatted values.

Are there libraries that do this automatically? Yes, packages such as ydata-profiling produce large HTML reports. A small profile you build yourself is quicker to run, easy to write to Excel and focused on the checks your data needs.

Related

• Up one level: Validating Excel Data with Python — checks that data is fit for use.
• Check Excel Data Types with pandas — enforcing the types the profile reveals.
• Find and Report Missing Values in an Excel File — blanks in more depth.
• Standardise Category Values with a Mapping Table — fixing values like "business" and "Business".