Data Transformation & Format Cleanup

Here are five rows from a real product export. Every one of them means "one thousand two hundred and ninety-nine pounds":

price"£1,299.00"" 1299 ""1299.00 GBP""1.299,00""1299"

Ask pandas for df["price"].mean() and you get a TypeError, because the column is text. Ask for df["price"].max() and you get something worse: an answer. String comparison says "9.99" is greater than "1299.00", because 9 sorts after 1. The code runs, the number is wrong, and nothing warns you.

Transformation is the work of turning what the file happens to contain into what the analysis actually needs. It is unglamorous and it is where a large fraction of real data-work time goes, so it is worth doing systematically rather than by accumulating one-off fixes in a notebook.

Types Are Not Cosmetic

A column's dtype determines which operations are legal and which are silently wrong. Three things go wrong when a numeric column is stored as text: arithmetic fails outright, comparisons produce nonsense, and memory use grows — text takes far more room than an eight-byte number, and in pandas 2 every string in an object column is a separate Python object with its own header. (pandas 3 stores text in a more compact str dtype by default, but a number stored as text is still text.)

1import pandas as pd2import numpy as np34print(df.dtypes)5print(df.memory_usage(deep=True) / 1e6)   # deep=True counts the actual strings

Converting safely

The naive conversion throws on the first bad value and tells you nothing about the rest:

1# Fails on the first row it cannot parse2df["price"].astype(float)34# Turns unparseable values into NaN so you can inspect them5df["price_num"] = pd.to_numeric(df["price"], errors="coerce")67bad = df.loc[df["price_num"].isna() & df["price"].notna(), "price"]8print(f"{len(bad)} values failed to parse")9print(bad.value_counts().head(20))

That inspection step is the point. errors="coerce" is not a way to make the problem disappear — it is a way to make the problem enumerable. Printing the failures tells you whether you are dealing with currency symbols, thousands separators, the string "unknown", or three genuinely corrupt rows.

Once you know, clean then convert:

1df["price"] = (2    df["price"].astype("string")3      .str.replace(r"[£$€,]", "", regex=True)   # currency symbols and separators4      .str.replace(r"\s*(GBP|USD|EUR)\s*", "", regex=True)5      .str.strip()6      .pipe(pd.to_numeric, errors="coerce")7)

Check the result against the raw column, because one of the five rows above still comes out wrong. "1.299,00" uses a comma as the decimal mark, so removing commas leaves "1.29900", which parses cleanly as 1.299. Values in European format need their own rule — for example, detect a comma followed by exactly two digits at the end, swap the separators, then convert.

Memory: choosing the smallest type that fits

pandas defaults to int64 and float64 — eight bytes per value — regardless of whether the column holds ages from 18 to 90.

1def shrink(df, cat_threshold=0.5, keep=()):2    out = df.copy()3    for col in out.select_dtypes(include=["int"]).columns:4        out[col] = pd.to_numeric(out[col], downcast="integer")5    for col in out.select_dtypes(include=["float"]).columns.difference(keep):6        out[col] = pd.to_numeric(out[col], downcast="float")7    for col in out.select_dtypes(include=["object", "string"]).columns:8        if out[col].nunique() / max(len(out), 1) < cat_threshold:9            out[col] = out[col].astype("category")10    return out1112before = df.memory_usage(deep=True).sum() / 1e613after = shrink(df).memory_usage(deep=True).sum() / 1e614print(f"{before:.1f} MB -> {after:.1f} MB")

The category conversion is usually the largest win. A column of one million rows containing eight distinct country names stops storing a million strings and stores a million small integers plus a lookup table of eight. Reductions of 90% on such columns are routine.

One caution: do not downcast a monetary column to float32. Seven significant digits is not enough to represent £12,345,678.91 exactly, and rounding errors in money are the kind of bug that gets noticed by accountants. That is what the keep argument is for: shrink(df, keep=["price"]) leaves the listed columns at full precision.

Text: From Human Writing to Comparable Values

Free-text fields carry the same value written many ways. "New York", "new york", "NEW YORK " and "New York" are four distinct strings and one city. Any grouping, joining or counting on that column is wrong until they are reconciled.

1s = df["city"].astype("string")23s = (s.str.strip()                              # leading/trailing whitespace4       .str.replace(r"\s+", " ", regex=True)    # collapse internal runs5       .str.title())                            # consistent casing

Order matters. Collapsing whitespace before stripping leaves a single leading space; casing before stripping still leaves the whitespace. Chain these in the order above and the result is stable.

Accents and invisible characters

1import unicodedata23def strip_accents(text):4    if pd.isna(text):5        return text6    return "".join(7        ch for ch in unicodedata.normalize("NFKD", str(text))8        if not unicodedata.combining(ch)9    )1011df["city_key"] = df["city"].map(strip_accents).str.lower()

Normalising to NFKD splits "é" into "e" plus a combining accent mark, and the filter drops the mark. It also turns non-breaking spaces pasted from documents into ordinary spaces; these are invisible on screen and break exact-match joins in a way that is genuinely hard to debug. Curly quotes are not changed by NFKD, so replace those explicitly (.str.replace("\u2019", "'")) if your keys contain apostrophes.

Keep the cleaned value in a separate key column and leave the original intact. When a join fails you need to see both what you matched on and what the source actually said.

Pulling structure out with regular expressions

Many text fields are structured data that was flattened into a sentence. Regular expressions unflatten them.

1refs = pd.Series([2    "ORD-2024-00412 shipped 3 units",3    "ORD-2023-99001 shipped 12 units",4])56parsed = refs.str.extract(7    r"ORD-(?P<year>\d{4})-(?P<seq>\d+)\s+shipped\s+(?P<units>\d+)"8)9parsed["units"] = parsed["units"].astype("int16")

Named groups (?P<year>) become column names directly, which keeps the code readable. A few patterns worth keeping to hand:

Always check the miss rate rather than assuming a pattern caught everything: parsed["year"].isna().mean(). A regex that silently matches 60% of rows is worse than one that fails loudly, because the 40% become NaN and get quietly dropped later.

Dates: The Format That Lies

Dates cause more silent corruption than any other type, because a wrong parse still produces a valid date.

1# Ambiguous: is 03/04/2024 the 3rd of April or the 4th of March?2pd.to_datetime("03/04/2024")                       # guesses month-first3pd.to_datetime("03/04/2024", dayfirst=True)        # UK convention4pd.to_datetime("03/04/2024", format="%d/%m/%Y")    # unambiguous

Where the source format is known, always pass format. Without it, pandas 2 and later guess one format from the first value in the column and apply that guess to every row. The guess can be wrong: if the first value is 03/04/2024, pandas assumes month-first, and a UK file then either raises an error at the first 13/04/2024 or, with errors="coerce", quietly turns every date after the 12th of a month into NaT. Older pandas versions were worse: they parsed row by row, so days 1–12 came out month-first and days 13–31 day-first, with no error at all, and the seasonality in your chart was manufactured. Passing format states the rule once and fails loudly on any row that breaks it.

1df["order_date"] = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")23unparsed = df.loc[df["order_date"].isna() & df["order_date_raw"].notna(), "order_date_raw"]4print(f"{len(unparsed)} dates failed; examples: {unparsed.head().tolist()}")

Sanity checks that catch bad parses

print(df["order_date"].min(), df["order_date"].max())print((df["order_date"] > pd.Timestamp.today()).sum(), "future dates")print(df["order_date"].dt.day.value_counts().sort_index().head(13))

That third line is the day/month detector. If days 13 to 31 appear far less often than days 1 to 12, some of your dates have had their fields swapped or been dropped as unparseable.

Derived date features

Once parsed properly, a date is a rich source of features via the .dt accessor:

1d = df["order_date"].dt2df["year"] = d.year3df["month"] = d.month4df["day_of_week"] = d.dayofweek          # Monday = 05df["is_weekend"] = d.dayofweek.ge(5).astype("int8")6df["quarter"] = d.quarter7df["days_since_signup"] = (df["order_date"] - df["signup_date"]).dt.days

Cyclical features need care. Month 12 and month 1 are adjacent in reality but 11 apart numerically, which misleads any distance-based method. Encode the cycle as a pair of coordinates on a circle:

m = df["month"]df["month_sin"] = np.sin(2 * np.pi * m / 12)df["month_cos"] = np.cos(2 * np.pi * m / 12)

With both components, December and January are genuinely close, and so are Sunday and Monday if you do the same with dayofweek / 7.

Timezones

df["ts"] = (pd.to_datetime(df["ts_utc"], utc=True)              .dt.tz_convert("Europe/London"))

Store timestamps in UTC and convert only for display or for local-time features such as "hour of day". Mixing naive and timezone-aware timestamps raises errors when you subtract them, and mixing local times from different regions produces an ordering that is simply wrong.

Binning Continuous Values

Sometimes a number is more useful as a band. Ages become age groups; scores become tiers. Two ways to cut, and they answer different questions.

1# Fixed, meaningful boundaries2df["age_band"] = pd.cut(3    df["age"],4    bins=[0, 18, 30, 45, 65, 120],5    labels=["under 18", "18-29", "30-44", "45-64", "65+"],6    right=False,7)89# Equal-sized groups, boundaries decided by the data10df["spend_quartile"] = pd.qcut(df["spend"], q=4,11                               labels=["Q1", "Q2", "Q3", "Q4"])

The instability of qcut is the trap. If you compute quartile boundaries on your training data and then re-run qcut on new data, the boundaries move and the same customer can change quartile without changing behaviour. Save the boundaries and reuse them: _, edges = pd.qcut(train, 4, retbins=True), then pd.cut(new, bins=edges).

Note also that binning always destroys information. A 29-year-old and an 18-year-old become identical. Bin when the bands carry genuine meaning or when a threshold effect is real — not by default.

Reshaping Skewed Distributions

Many real quantities — income, house prices, session length, city population — pile up near zero with a long right tail. Methods that assume symmetry behave badly on them, and a handful of huge values dominate every average.

1from sklearn.preprocessing import PowerTransformer23print("skew before:", df["income"].skew().round(2))45df["income_log"] = np.log1p(df["income"])        # log(1+x): safe at zero6print("skew after log:", df["income_log"].skew().round(2))78pt = PowerTransformer(method="yeo-johnson")      # handles zero and negatives9df["income_yj"] = pt.fit_transform(df[["income"]])

A skewness magnitude below about 0.5 is near-symmetric; above 1.0 is strongly skewed and usually worth transforming. Remember that predictions made on a transformed target must be transformed back — np.expm1 undoes np.log1p — and that the back-transformed mean is not the mean of the back-transform.

A transformation is a change of units, not a repair. Everything downstream must be read in the new units, including the coefficients you report.

What This Means When You Build Something

Collect these steps into one function that takes raw input and returns analysis-ready output, then verify the result rather than trusting it:

1def transform(raw):2    df = raw.copy()3    df.columns = df.columns.str.strip().str.lower().str.replace(r"\W+", "_", regex=True)45    df["price"] = (df["price"].astype("string")6                     .str.replace(r"[^\d.\-]", "", regex=True)7                     .pipe(pd.to_numeric, errors="coerce"))89    df["order_date"] = pd.to_datetime(df["order_date"], format="%d/%m/%Y",10                                      errors="coerce")1112    for col in df.select_dtypes(include=["object", "string"]).columns:13        df[col] = (df[col].str.strip()14                          .str.replace(r"\s+", " ", regex=True))1516    df["day_of_week"] = df["order_date"].dt.dayofweek17    df["price_log"] = np.log1p(df["price"].clip(lower=0))18    return shrink(df, keep=["price"])     # money stays float64192021def check(df):22    assert df["price"].notna().mean() > 0.98, "too many prices failed to parse"23    assert df["order_date"].max() <= pd.Timestamp.today(), "future dates present"24    assert df.select_dtypes(include=["object", "string"]).empty, "unconverted text columns"25    print(f"OK: {len(df):,} rows, {df.memory_usage(deep=True).sum()/1e6:.1f} MB")2627clean = transform(raw)28check(clean)

The assertions matter more than the transformations. Every step above can fail partially — a regex that misses a format, a date pattern that does not match a subset — and partial failure produces NaN, which flows downstream looking like ordinary missing data rather than like the bug it is. Asserting on parse rates turns a silent 12% loss into an error message on the line that caused it.