Blog

The Data Journey: Messy to Meaningful — Phase 1: Cleaning Real Retail Data with pandas

Clean a real retail dataset with pandas, step by step

Understanding the domain

Retail data captures everyday transactions: what customers buy, when they buy and how much they spend. It is rich with patterns that help businesses understand customer behaviour, demand trends and revenue drivers.

In real-world systems, data does not arrive neatly packaged for machine learning. In this series we simulate a retail analytics pipeline:

File 1 (orders data): transaction-level data (what was bought, when and how much)
File 2 (country mapping, used from Phase 2): enrichment data

The dataset

We use the Online Retail dataset from the UCI Machine Learning Repository: Chen, D. (2015), Online Retail [Dataset], UCI Machine Learning Repository, https://doi.org/10.24432/C5BW33. It is licensed under CC BY 4.0, so it can be reused with credit.

It holds every transaction made between 1 December 2010 and 9 December 2011 by a UK-based online retailer that sells gifts for all occasions. Many of its customers are wholesalers.

To mirror a real pipeline, we split the original workbook into two files. Download Online Retail.xlsx from the UCI page above, then run this once (reading Excel files needs pip install openpyxl):

Python

import pandas as pd

raw = pd.read_excel("Online Retail.xlsx", dtype={"InvoiceNo": str, "StockCode": str})

orders = raw[["CustomerID", "InvoiceNo", "StockCode", "Description",
              "Quantity", "InvoiceDate", "UnitPrice"]]
orders.to_csv("Online Retail.csv", index=False)

country = raw[["CustomerID", "Country"]].drop_duplicates()
country.to_csv("Customer_country.csv", index=False)

This creates two files:

Online Retail.csv: the order lines (CustomerID, InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice)
Customer_country.csv: each customer's country, which we join back in Phase 2 (8 customers appear with two countries; we deal with this in Phase 2)

Objective of the series

We will turn raw transactional data into:

Business insights (revenue, trends)
A machine learning use case: predicting which customers will be high value

The dataset contains:

Customer behaviour → CustomerID
Transactions → InvoiceNo, InvoiceDate
Products → StockCode, Description
Revenue drivers → Quantity, UnitPrice

Phase 1: data understanding and cleaning

Load and explore the dataset
Understand its structure and missing values
Handle missing data (drop or fill, depending on the goal)
Fix data types (dates, numbers, IDs)
Deal with cancellations, non-product lines and duplicates

Goal: turn raw, messy data into a clean, usable dataset for analysis and machine learning.

Import libraries and load the dataset

Python

import pandas as pd

df = pd.read_csv("Online Retail.csv",
                 dtype={"InvoiceNo": str, "StockCode": str})
df.shape

Output

(541909, 7)

We read InvoiceNo and StockCode as text: some invoice numbers start with "C" and some stock codes contain letters.

Initial exploration

Python

df.info()

(abridged)

Text

 #   Column       Non-Null Count   Dtype
---  ------       --------------   -----
 0   CustomerID   406829 non-null  float64
 1   InvoiceNo    541909 non-null  str
 2   StockCode    541909 non-null  str
 3   Description  540455 non-null  str
 4   Quantity     541909 non-null  int64
 5   InvoiceDate  541909 non-null  str
 6   UnitPrice    541909 non-null  float64

(On pandas 2, the text columns show as object rather than str.)

Python

df.describe()

(abridged)

Text

          CustomerID       Quantity      UnitPrice
count  406829.000000  541909.000000  541909.000000
mean    15287.690570       9.552250       4.611114
min     12346.000000  -80995.000000  -11062.060000
50%     15152.000000       3.000000       2.080000
max     18287.000000   80995.000000   38970.000000

The profile already shows trouble: negative quantities and prices, and a single line of 80,995 units.

Check missing values

Python

df.isnull().sum()

Output

CustomerID     135080
Description      1454
(all other columns 0)

The most important column, CustomerID, has the most missing values (135,080). This is not just a data problem; it is a business reality. These rows could be guest checkouts, untracked customers or gaps in data collection.

Think before you clean. Before fixing anything, pause and ask what you are trying to build. The answer decides how you clean, and it affects both the quality of the analysis and whether machine learning is feasible.

Handle missing values

CustomerID: we drop rows without one, because this series builds customer-level analysis and machine learning. You will not always drop it: for a sales analysis you might keep those rows.

Python

df = df.dropna(subset=["CustomerID"])
df.shape                          # (406829, 7)
df["Description"].isna().sum()   # 0

Interestingly, all 1,454 missing descriptions disappear with the missing customer IDs, so no description needs filling. Check for side effects like this rather than assuming each step is needed.

Fix data types

Python

df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"])
df["CustomerID"] = df["CustomerID"].astype(int)

Handle cancellations

In this dataset, an invoice number starting with "C" marks a cancellation, recorded with a negative quantity.

Python

cancel = df["InvoiceNo"].str.startswith("C")
cancel.sum(), cancel.mean()      # 8,905 lines, 2.2% of the remaining lines
cancellations = df[cancel]
purchases = df[~cancel]

Simply dropping negative quantities is not enough, because the original purchase stays in the data. The largest examples are a single order for 80,995 units of "PAPER CRAFT , LITTLE BIRDIE" and one for 74,215 units of "MEDIUM CERAMIC TOP STORAGE JAR". Both were cancelled, yet they would dominate any "top products" analysis.

So, for each cancellation, we remove one matching purchase: the same customer, product and quantity, dated on or before the latest matching cancellation, taking the most recent first.

Python

keys = ["CustomerID", "StockCode", "Quantity"]
canc = (cancellations.assign(Quantity=-cancellations["Quantity"])
        .groupby(keys).agg(n_cancel=("InvoiceNo", "size"),
                           last_cancel=("InvoiceDate", "max"))
        .reset_index())

p = purchases.merge(canc, on=keys, how="left")
p["n_cancel"] = p["n_cancel"].fillna(0)
eligible = p["InvoiceDate"] <= p["last_cancel"]
p["rank_recent"] = (p[eligible].sort_values("InvoiceDate", ascending=False)
                    .groupby(keys).cumcount())
fully_cancelled = eligible & (p["rank_recent"] < p["n_cancel"])
purchases = p.loc[~fully_cancelled, purchases.columns]
fully_cancelled.sum()            # 2,904 purchase lines removed
p[fully_cancelled].nlargest(3, "Quantity")[["CustomerID", "InvoiceNo", "Description", "Quantity", "InvoiceDate"]]

Output

        CustomerID InvoiceNo                         Description  Quantity         InvoiceDate
397451       16446    581483         PAPER CRAFT , LITTLE BIRDIE     80995 2011-12-09 09:15:00
37126        12346    541431      MEDIUM CERAMIC TOP STORAGE JAR     74215 2011-01-18 10:01:00
32732        15749    540815  FAIRY CAKE FLANNEL ASSORTED COLOUR      3114 2011-01-11 12:55:00

This is a practical approximation. Only about a third of cancellation lines (2,904 of 8,905) match a purchase; the rest are partial returns, non-product adjustments or refer to orders placed before the data starts, and they are left alone. If you want to study returns, keep the cancellations table rather than discarding it.

Remove non-product lines

Some stock codes are not products at all: postage and carriage, manual adjustments, bank charges and a few other codes. Real product codes start with five digits.

Python

non_product = ~purchases["StockCode"].str.match(r"^\d{5}")
purchases.loc[non_product, "StockCode"].value_counts()
purchases = purchases[~non_product]

Output

POST            1076
M                249
C2               132
DOT               16
BANK CHARGES      12
PADS               4

Remove invalid prices and duplicates

Python

purchases = purchases[(purchases["Quantity"] > 0) & (purchases["UnitPrice"] > 0)]
before = len(purchases)
purchases = purchases.drop_duplicates()
before - len(purchases)                          # 5,141 exact duplicate lines removed

Exact duplicates (every column identical, including the timestamp) are most likely double-recorded lines. On a real project, confirm this with the data owner before removing them.

Quick sanity check

Python

purchases[["Quantity", "UnitPrice"]].describe()
purchases["CustomerID"].nunique(), purchases["InvoiceNo"].nunique()   # (4324, 18275)

(abridged)

Text

            Quantity      UnitPrice
count  388357.000000  388357.000000
mean       12.659360       2.853701
min         1.000000       0.040000
50%         6.000000       1.950000
max      4800.000000     649.500000

The extremes are now plausible for a gift wholesaler. The largest line is a 4,800-unit order, and the highest unit price is £649.50. The clean dataset has 388,357 lines from 4,324 customers and 18,275 invoices.

Save the output for Phase 2

Python

purchases.to_csv("Online Retail Phase 1 Output.csv", index=False)

Conclusion

We started with messy, real-world data and turned it into something structured, reliable and ready to work with. Along the way we saw that quick fixes can mislead: dropping negative quantities alone would have left cancelled 80,995-unit and 74,215-unit orders in the data.

Clean data isn't exciting, but everything that follows (analysis, insights, machine learning) depends on this foundation.

Coming next: Phase 2

Now that the data is clean, what stories is it hiding? In Phase 2 we will:

Explore patterns in customer behaviour
Understand sales trends over time
Ask the right questions of the data

Clean data is just the beginning; insight is where things get interesting.

Next: Phase 2: Merging, Features and Sales Trends

All code in this article was tested with Python 3 and pandas 3.0.5 on the UCI Online Retail workbook.

Written in conjunction with Sankhya Analytics.