Data Analytics Trending

Data Cleaning: A Practical Step-by-Step Guide

A step-by-step data cleaning guide on 541,909 real transaction lines: profiling before touching anything, removing duplicates, standardising messy text, fixing inconsistent categories, investigating extreme values, keeping a cleaning log, and validation checks that caught a problem the cleaning itself created.

GloryTecks Team

Training & Placement Faculty

1 June 2026 8 min read

Last updated: 25 September 2026

Share:

Data cleaning is where analysis is won or lost. A wrong join or a forgotten cancellation does not produce an error; it produces a confident, wrong number. It also takes much of an analyst's time: the paper that introduced tidy data principles opens by noting how much effort goes into getting data ready for analysis (Wickham: Tidy Data). This guide cleans a real, messy dataset step by step: every transaction of a UK online gift retailer over a year, 541,909 lines (UCI: Online Retail). The principles apply whatever tool you use.

Step 1: Profile before changing anything

Keep the raw data untouched and look at it first: size, missing values, duplicates, ranges.

python
import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str, "Description": str})

print(raw.shape)
print("missing values:", raw.isna().sum()[lambda s: s > 0].to_dict())
print("exact duplicate rows:", raw.duplicated().sum())
print("date range:", raw["InvoiceDate"].min(), "to", raw["InvoiceDate"].max())
text
(541909, 8)
missing values: {'Description': 1454, 'CustomerID': 135080}
exact duplicate rows: 5268
date range: 2010-12-01 08:26:00 to 2011-12-09 12:50:00

Reading the file with explicit text types matters: stock codes like 85123A and invoice numbers like C536379 would otherwise be mangled, and the description column mixes text with blanks. The profile already raises four questions: 1,454 lines without a description, a quarter without a customer ID, 5,268 exact duplicates, and a date range that ends part-way through December. Missing values need a decision, not an automatic fill (pandas: working with missing data); here, lines without a customer can still count towards sales but not towards customer analysis.

Step 2: Remove exact duplicates, then standardise text

Rows identical in every column, including the timestamp to the minute, are almost certainly recording errors, so they are removed (pandas: drop_duplicates). Then the descriptions:

python
df = raw.drop_duplicates().copy()
text = df["Description"].dropna()
print("descriptions with stray spaces:", (text != text.str.strip()).sum(), "| with double spaces:", text.str.contains("  ").sum())
df["Description"] = df["Description"].str.strip().str.replace(r"\s+", " ", regex=True)
names_per_code = df.dropna(subset=["Description"]).groupby("StockCode")["Description"].nunique()
print("stock codes with more than one description:", (names_per_code > 1).sum(), "of", len(names_per_code))
print(df.loc[df["StockCode"] == "85123A", "Description"].value_counts().to_dict())
canonical = df.dropna(subset=["Description"]).groupby("StockCode")["Description"].agg(lambda s: s.value_counts().index[0])
df["Description"] = df["StockCode"].map(canonical)
text
descriptions with stray spaces: 112371 | with double spaces: 22093
stock codes with more than one description: 643 of 3958
{'WHITE HANGING HEART T-LIGHT HOLDER': 2290, 'CREAM HANGING HEART T-LIGHT HOLDER': 9, '?': 1, 'wrongly marked carton 22804': 1}

Over 112,000 descriptions have leading or trailing spaces, invisible in a spreadsheet but enough to make two "identical" products group separately. Even after trimming, 643 stock codes carry more than one description, including warehouse notes such as "?" and "wrongly marked carton 22804". The fix is to choose one canonical description per code, here the most frequent one, and apply it everywhere, so every report groups products the same way.

Step 3: Make categories consistent

python
print("country values needing attention:", [c for c in sorted(df["Country"].unique()) if c in {"EIRE", "RSA", "Unspecified", "European Community", "Channel Islands"}])
df["Country"] = df["Country"].replace({"EIRE": "Ireland", "RSA": "South Africa"})
print("lines with country 'Unspecified':", (df["Country"] == "Unspecified").sum())
text
country values needing attention: ['Channel Islands', 'EIRE', 'European Community', 'RSA', 'Unspecified']
lines with country 'Unspecified': 442

"EIRE" and "RSA" are codes for Ireland and South Africa; renaming them lets the data join cleanly with other country tables. "Unspecified" and "European Community" cannot be mapped to a single country, so they stay as they are and are reported separately rather than guessed. The Channel Islands are a real place that many country lists handle differently; note it rather than silently merging it into the United Kingdom.

Step 4: Investigate extreme values before removing them

python
print(df.nlargest(2, "Quantity")[["InvoiceNo", "StockCode", "Quantity", "InvoiceDate"]].to_string(index=False))
print(df.nsmallest(2, "Quantity")[["InvoiceNo", "StockCode", "Quantity", "InvoiceDate"]].to_string(index=False))
print(df.nlargest(3, "UnitPrice")[["StockCode", "Description", "UnitPrice"]].to_string(index=False))
text
InvoiceNo StockCode  Quantity         InvoiceDate
   581483     23843     80995 2011-12-09 09:15:00
   541431     23166     74215 2011-01-18 10:01:00
InvoiceNo StockCode  Quantity         InvoiceDate
  C581484     23843    -80995 2011-12-09 09:27:00
  C541433     23166    -74215 2011-01-18 10:17:00
StockCode Description  UnitPrice
        M      Manual   38970.00
AMAZONFEE  AMAZON FEE   17836.46
AMAZONFEE  AMAZON FEE   16888.02

The two largest orders, over 80,000 and 74,000 units, were both cancelled within 16 minutes by matching cancellation invoices. The highest "prices" are not products at all but manual adjustments and marketplace fees. Deleting the biggest values blindly as outliers would have been wrong in both directions: the quantities needed pairing with their cancellations, and the prices needed excluding as non-product lines. Always look at extreme records before deciding what they are.

Step 5: Build the clean table and keep a cleaning log

Each rule is applied in order, with the row count after each, so anyone can see what was removed and reproduce it:

python
services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
log = [("raw lines", len(raw)), ("after removing exact duplicates", len(df))]
is_cancellation = df["InvoiceNo"].str.startswith("C")
sales = df[~is_cancellation]
log.append(("without cancellation lines", len(sales)))
sales = sales[(sales["Quantity"] > 0) & (sales["UnitPrice"] > 0)]
log.append(("with positive quantity and price", len(sales)))
sales = sales[~sales["StockCode"].isin(services) & ~sales["StockCode"].str.startswith("gift_")]
log.append(("products only (no postage, fees, vouchers)", len(sales)))
reversals = df[is_cancellation].assign(Quantity=lambda d: -d["Quantity"])[["CustomerID", "StockCode", "Quantity", "InvoiceDate"]]
pairs = sales.reset_index().merge(reversals, on=["CustomerID", "StockCode", "Quantity"], suffixes=("", "_cancelled"))
reversed_lines = pairs.loc[pairs["InvoiceDate_cancelled"] >= pairs["InvoiceDate"], "index"].unique()
sales = sales.assign(possibly_reversed=sales.index.isin(reversed_lines))
for step, rows in log:
    print(f"{step:<45} {rows:>7}")
flagged = sales["possibly_reversed"]
print(f"lines matching a later cancellation (same customer, product and quantity): {flagged.sum()} "
      f"({(sales.loc[flagged, 'Quantity'] * sales.loc[flagged, 'UnitPrice']).sum():,.0f} in value)")
text
raw lines                                      541909
after removing exact duplicates                536641
without cancellation lines                     527390
with positive quantity and price               524878
products only (no postage, fees, vouchers)     522540
lines matching a later cancellation (same customer, product and quantity): 4835 (489,252 in value)

Cancelled lines are removed from the sales table, but the original lines they reversed are still in it. Matching a cancellation to a purchase with the same customer, product and quantity, dated at or after the purchase, flags 4,835 lines worth about 489,000. The match is approximate: a customer may buy the same quantity twice and cancel once. So the lines are flagged, not deleted, and each analysis decides whether to use gross sales or sales net of these likely reversals.

Step 6: Validate the result, and let the checks catch you

Finish with explicit checks that must hold for the clean table:

python
checks = {
    "no duplicate rows": not sales.duplicated().any(),
    "quantities positive": sales["Quantity"].gt(0).all(),
    "prices positive": sales["UnitPrice"].gt(0).all(),
    "every line has a description": sales["Description"].notna().all(),
    "one description per stock code": sales.groupby("StockCode")["Description"].nunique().max() == 1,
    "dates within the documented range": sales["InvoiceDate"].between("2010-12-01", "2011-12-10").all(),
}
for name, passed in checks.items():
    print(f"{'PASS' if passed else 'FAIL'}  {name}")
text
FAIL  no duplicate rows
PASS  quantities positive
PASS  prices positive
PASS  every line has a description
PASS  one description per stock code
PASS  dates within the documented range

One check fails, and it is instructive. Duplicates were removed in step 2, but standardising descriptions in the same step made some rows that differed only in their description text identical. Cleaning can create the problems it removes elsewhere, which is exactly why the checks run at the end:

python
extra = sales.drop(columns="possibly_reversed").duplicated()
print("rows that became duplicates after standardising descriptions:", extra.sum())
sales = sales[~extra]
print("rows after removing them:", len(sales), "| no duplicate rows:", not sales.drop(columns="possibly_reversed").duplicated().any())
text
rows that became duplicates after standardising descriptions: 2
rows after removing them: 522538 | no duplicate rows: True

Only two rows, but without the check nobody would have known. Rerunning the checks after every change to the cleaning script keeps it honest.

Principles that transfer to any dataset

  • Never edit raw data. Clean in a script or a documented query, so every step can be rerun.
  • Profile first, then decide rules, then apply them.
  • Count every exclusion and keep the log with the output.
  • Investigate extremes before removing them; many are legitimate or belong to a different category.
  • Standardise text and categories to one canonical form, and keep a mapping table.
  • Flag uncertain cases instead of silently deleting them.
  • Validate at the end with explicit checks, and expect them to catch something.

For specific problems, see how to handle missing data, how to audit a messy dataset and outlier detection for analysts.

Should I delete rows with missing values?

Only when they cannot serve the analysis. Here, orders without a customer ID are still valid sales; deleting them would understate revenue by a large margin. Decide per analysis and document it.

Can I clean data in Excel instead of Python?

Yes, for moderate sizes. Power Query records each step so it can be repeated, which gives much of the same reproducibility as a script. Manual edits in cells cannot be audited or repeated.

How do I know when cleaning is finished?

When the validation checks pass, totals reconcile with a trusted source, and every exclusion is documented. Cleaning is rarely perfect; it needs to be fit for the question.

Should cleaning rules be shared with the rest of the team?

Yes. Put the definitions, such as what counts as a sale, which codes are fees and how cancellations are handled, in one documented place, ideally in the shared data model, so every report applies the same rules and numbers agree across teams.

Are exact duplicate rows always errors?

Usually, when every field including the timestamp matches, but check the source system. Some systems legitimately record repeated identical lines, in which case they should be kept.

Handling missing values, removing duplicates and data transformation are covered in the data cleaning and preparation module of the Data Analytics course.

Sources

Tags:#data analytics#data#cleaning#practical

GloryTecks Team

Training & Placement Faculty

The GloryTecks faculty team mentors learners across Data Science, AI and Data Engineering, with a focus on real-time projects and placement readiness in Hyderabad.

Want to master Data Analytics?

Join GloryTecks’ expert-led Data Analytics training in Hyderabad with real-time projects and 100% placement support.

Explore course
Previous

Dashboard Design Principles for Data Analysts

Next

Excel vs Power BI: Which Should You Learn First?

Related articles

Data Analytics
6 May 2026 5 min

How to Tell a Story with Data: A Practical Framework

A framework for telling a story with data: five steps, context, change, cause, consequence and choice, organised with the main message first so the audience never gets lost, applied to a real online retailer's year from a flat summer to a pre-Christmas surge, with the chart for each step and the traps to avoid.

GloryTecks Team
Read
Data Analytics
13 Jun 2026 5 min

Data Analyst Roadmap 2026: Skills, Tools and Timeline

A data analyst roadmap for 2026: the six stages to learn in order, from spreadsheets and SQL to BI tools, statistics and communication, what each stage should let you do, a realistic part-time timeline, a project that proves each stage, and how to tell when you are ready to apply for analyst roles.

GloryTecks Team
Read
Data AnalyticsTrending
26 May 2026 7 min

Dashboard Design Principles for Data Analysts

Dashboard design principles for data analysts: designing for one audience and a few questions, fitting on one screen, giving every number context, choosing charts that encode values accurately, arranging content in reading order, and using colour sparingly and accessibly, with a script that checks palette contrast.

GloryTecks Team
Read