Convert Percentage Strings Like "12.5%" to Numbers with pandas

A column of margins, discount rates or growth figures should be the easiest thing in the world to average. In practice it arrives mixed: some cells were typed as text with a percent sign, some were real percentage cells that pandas reads as fractions, one uses a decimal comma, one is in basis points, and one is a bare 12.5 where somebody forgot the %. pd.to_numeric makes a mess of all of it, and an average taken naively is wrong by a factor of a hundred in some rows. This page, part of Cleaning Excel Data With Pandas, converts every form to one consistent fraction and flags the values a rule cannot safely decide.

Prerequisites

pip install pandas openpyxl

margins.xlsx has eight products. Product B is a real percentage cell (0.125 formatted as 0.0%); the others are typed text, a typed number, or blank.

Step 1 — See what pandas reads

import re
import pandas as pd

margins = pd.read_excel("margins.xlsx")
print(margins)
print(pd.to_numeric(margins["margin"], errors="coerce").tolist())

The formatted cell arrives as 0.125 — pandas reads the stored value, not the display — while product A's text "12.5%" arrives as a string. to_numeric keeps 0.125, 12.5 and 0.4, and turns the four text values with symbols into NaN. Averaging that result would mix a fraction (0.125), a percentage number (12.5) and a fraction again (0.4), and ignore half the rows.

Step 2 — Decide on the target: fractions

Before converting anything, choose one representation. Use fractions — 12.5% is 0.125. That is what Excel stores in a percentage cell, what formulas expect when they multiply by a rate, and what a 0.0% number format displays correctly. Everything the parser produces will be a fraction, and the display will be handled by formatting at the end.

Step 3 — Parse every form into a fraction

Handle each marker explicitly: a % sign means divide by 100, bp means divide by 10,000, a decimal comma becomes a point, and a number that is already numeric came from a cell and is left as it is. Anything else is reported as a question rather than guessed:

def parse_rate(value):
    if pd.isna(value):
        return None, "blank"
    if isinstance(value, (int, float)):
        return float(value), "numeric cell"
    s = str(value).strip().lower().replace(" ", "")
    m = re.fullmatch(r"([+-]?\d+(?:[.,]\d+)?)(%|bps?)?", s)
    if not m:
        return None, "unparseable"
    number = float(m.group(1).replace(",", "."))
    unit = m.group(2)
    if unit == "%":
        return number / 100, "percent text"
    if unit in ("bp", "bps"):
        return number / 10_000, "basis points"
    return number, "bare text number"

parsed = margins["margin"].map(parse_rate)
margins["rate"] = parsed.str[0].astype("Float64")
margins["how"] = parsed.str[1]
print(margins[["product", "margin", "rate", "how"]])

Most rows now agree: A, B and C are all 0.125, D is -0.03 and E is 0.0025. Recording how each value was read in its own column is cheap and makes the next step possible — deciding what to do about the rows a rule cannot settle.

Step 4 — Flag the ambiguous values instead of guessing

Two rows carry no unit: the typed number 12.5 and the text "0.4". 0.4 is plausibly a fraction — a 40% margin — and 12.5 is plausibly a percentage with the sign left off. But "plausibly" is doing a lot of work: a 1250% markup exists, and so does a 0.4% fee. List these rows for a person to confirm, with a suggested value, rather than dividing silently:

unitless = margins["how"].isin(["numeric cell", "bare text number"]) & (margins["source"] != "formatted")
suspect = unitless & (margins["rate"].abs() > 1)
margins.loc[suspect, "suggested"] = margins.loc[suspect, "rate"] / 100
print(margins.loc[unitless, ["product", "margin", "rate", "suggested"]])

The rule here is deliberately narrow: a unitless value above 1 in a margin column is suggested as a percentage, and a unitless value at or below 1 is left as a fraction. Both are shown, so the reviewer sees the decision. When a source is known — "the Paris office always types percentages without the sign" — write that rule for that source, and stop flagging its rows. Validate Excel Data with pandera Schemas shows how to encode such a range rule so a whole column fails loudly when it is broken.

Step 5 — Apply the decision and check the range

Once the ambiguous rows are settled — here, accepting the suggestion for product F — take the final value and check that every rate lies in a sensible range for the column:

margins["rate_final"] = margins["suggested"].fillna(margins["rate"])
out_of_range = margins["rate_final"].notna() & ~margins["rate_final"].between(-1, 1)
print(margins[["product", "rate_final"]])
print("out of range:", margins.loc[out_of_range, "product"].tolist())
print("average margin:", round(margins["rate_final"].mean(), 4))

A margin between -100% and +100% is the range check for this column; a growth rate would allow more. The average now uses seven comparable values and ignores the blank, instead of mixing fractions with percentage numbers.

Step 6 — Write fractions with a percent format

Write the fractions as numbers and let the cell format show them as percentages, so the workbook can calculate with them and readers see familiar figures:

from openpyxl import load_workbook

out = margins[["product", "rate_final", "margin", "how"]].rename(columns={"rate_final": "margin_pct", "margin": "original"})
out.to_excel("margins_clean.xlsx", index=False)
wb = load_workbook("margins_clean.xlsx")
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.number_format = "0.0%"
wb.save("margins_clean.xlsx")
print(pd.read_excel("margins_clean.xlsx")["margin_pct"].round(4).tolist())

Reading the file back returns the same fractions, and in Excel the column shows 12.5%, -3.0% and 0.3% (for 25 basis points, rounded to one decimal place; use 0.00% if basis-point precision matters). Format Numbers as Percentages in Excel with Python covers the format codes.

Report changes in rates carefully

Once rates are clean fractions, the next report usually compares them — this quarter's margin against last quarter's — and there are two different "changes" that are easy to confuse. A margin moving from 10% to 12% has risen by 2 percentage points, and by 20% relative to where it was. Label the columns so readers know which one they are looking at:

q = pd.DataFrame({"product": ["A", "B"], "last_q": [0.10, 0.125], "this_q": [0.12, 0.10]})
q["change_pts"] = (q["this_q"] - q["last_q"]) * 100          # percentage points
q["change_rel"] = q["this_q"] / q["last_q"] - 1              # relative change, a fraction
print(q.round(3))

Product A's margin is up 2 points, or 20%; product B's is down 2.5 points, or 20% the other way. Write change_pts with a number format like +0.0" pts";-0.0" pts" and change_rel with +0.0%;-0.0%, so the two never look alike on the page. Mixing them — calling a two-point rise "up 2%" — is one of the most common errors in management reports, and one of the hardest to spot once it has been typed into a slide.

Common pitfalls

• Dividing everything by 100. Percent-formatted cells are already fractions; dividing them again gives 0.00125.
• Coercing text with to_numeric. Every value with a % becomes NaN.
• Guessing unitless values. Flag them; a silent guess is right until the day it is not.
• Averaging rates without weights. The mean of seven margins treats a tiny product like a large one; for a company-wide margin, weight by revenue.
• Formatting instead of converting. A 0.0% format on the text "12.5%" does nothing — it is still text.

Performance notes

The parser runs once per value through Python, which is fine for the size of typical rate tables. For large columns dominated by one form — say, text with a % sign — convert that majority with vectorised operations (s.str.rstrip("%").astype(float) / 100) and send only the remaining rows through parse_rate.

Conclusion

Store rates as fractions. Parse each value by its marker — divide % text by 100 and basis points by 10,000, replace decimal commas, keep numeric cells as they are because Excel already stores percentages as fractions — and record how each was read. Flag unitless values with a suggested reading instead of guessing, apply a range check, and write the fractions back with a 0.0% number format.

Frequently asked questions

Why does read_excel give 0.125 for a cell that shows 12.5%? Excel stores a percentage as a fraction and only displays it multiplied by 100. The cell holds 0.125 with a percent number format, and pandas reads the stored value. That is the right number to calculate with.

Should percentages be stored as 12.5 or 0.125? As fractions, 0.125. That is what Excel stores, what formulas expect (a price times 0.125), and what a percent number format displays correctly as 12.5%.

What about a bare number like 12.5 in a percentage column? It is ambiguous: someone may have meant 12.5% or 1250%. A value above 1 in a margin column almost always means the percent sign was left off, but the rule belongs to the data, so flag such values and decide per source rather than silently dividing.

What is a basis point? One hundredth of a percentage point, so 25 bp is 0.25% or 0.0025 as a fraction. Interest rates and fees are often quoted that way.

Related

• Up one level: Cleaning Excel Data With Pandas — the full cleaning workflow.
• Convert Currency Strings Like "$1,234.56" and "(500)" to Numbers with pandas — the same approach for amounts.
• Read Cell Values as Displayed in Excel with Python — when you need "12.5%" as text instead.
• Check Excel Data Types with pandas — spotting mixed-type columns early.