The Ecommerce Orders Dataset: Nine Faults and How to Fix Them

Most teaching datasets are too clean to teach anything. This one is not. It is an order export from a fictional outdoor retailer, 3,047 rows, and it contains nine deliberate faults. None of them raise an error. Every one of them changes at least one number your manager asked for.

If you would rather find them yourself first, the download includes a brief that gives you the task and nothing else. Stop reading here and come back afterwards.

What you are given

Thirteen columns of orders: an id, a date, a customer email, country, channel, product sku and name, quantity, unit price, order total, weight, currency and status. The task is the one you actually get in a first analytics job. Total revenue for the period, revenue by month, and the five best selling products by units.

You can produce all three in about four lines of pandas. All three will be wrong.

What is wrong with it

Every count below is taken after the duplicates in fault one are removed, because that is the order you would work in.

1. The export was run twice

Forty seven rows are byte for byte copies of other rows, order id included. Revenue and units are overstated by roughly 1.5%. The duplication is exact, so no total looks odd. There is simply more of everything.

df.duplicated().sum()   # 47
df = df.drop_duplicates()

2. Three date formats in one column

The column mixes ISO, UK and US formats. The parsing is not the problem. The problem is that 444 rows are genuinely ambiguous, meaning both readings give a valid date, and the two readings disagree.

uk = pd.to_datetime(df["order_date"], format="mixed", dayfirst=True)
us = pd.to_datetime(df["order_date"], format="mixed", dayfirst=False)
(uk != us).sum()   # 444 rows silently disagree

You cannot fix this from the file. You go back to whoever produced it and ask which system wrote which rows. If you have to proceed anyway, carry a flag column and say so in the output. The error concentrates in the first twelve days of every month, which is exactly where a monthly split lives.

3. A unit change partway through

Weight is recorded in grams until 20 April and in kilograms afterwards. 1,243 rows are in kilograms. Nothing errors, and the average looks merely odd rather than wrong.

The obvious fix is to convert on the date, and it is wrong, because the dates are fault two. Splitting on the date disagrees with the truth on 107 rows. Split on magnitude instead. The two groups run 0.085 to 0.640 and 85 to 640, with nothing in between.

weight = pd.to_numeric(df["weight"], errors="coerce")
df["weight_g"] = np.where(weight < 50, weight * 1000, weight)

4. Prices stored as text

228 rows carry the unit price as a string: GBP45.00, 1,299.00, a pound sign. Those rows become NaN and drop out of any sum, understating revenue by about 8%.

The trap is that errors="coerce" is the standard advice and it is precisely what makes the loss silent. Count the NaNs it creates, every single time.

5. One country, six spellings

Ten raw values reduce to six once you trim and lowercase, and those six are really only four countries. The United Kingdom appears as UK, uk , UK, United Kingdom, GB and gb across 1,852 rows. Trimming and lowercasing leaves three of them still standing, so any country breakdown splits the UK three ways and none of the rows is the real figure.

6. Mojibake in the product names

366 rows read Café Latte Mug. That is Café written as UTF-8 and read back as Latin-1. Product grouping splits in two, and the mangled character survives into whatever you publish.

df["product_name"].str.encode("latin-1").str.decode("utf-8")

7. Returns hidden as negative quantities

102 rows carry a negative quantity and a negative total. The status column does not identify them reliably, because refunded orders also appear with positive quantities. If you sum without deciding, returns net off quietly and your units figure is neither gross nor net. It is an accident.

8. The total does not equal quantity times price

208 rows disagree, and in 204 of them the total is lower, which is what an order level discount looks like when it is applied at the order and never written back to the unit price. This tells you which column is authoritative. Rebuild revenue from quantity times price and you overstate it.

9. Two currencies and no rate

The currency column holds GBP and EUR, and the file contains no exchange rate anywhere. Sum the total column as it stands and you have added pounds to euros. There is no fix inside the data. You need a rate table and a decision about which date’s rate to use.

The preprocessing step

Put together, the whole repair is about forty lines. Notice that it reports what it changed rather than changing things quietly, and that it refuses to resolve the two faults that cannot be resolved from the file.

import pandas as pd
import numpy as np
df = pd.read_csv("01-ecommerce-orders.csv", dtype=str,
                 encoding="utf-8", keep_default_na=False)
report = {"rows_in": len(df)}
# 1. the export was run twice
df = df.drop_duplicates()
report["duplicates_dropped"] = report["rows_in"] - len(df)
# 2. parse both readings and record the disagreement instead of hiding it
uk = pd.to_datetime(df["order_date"], format="mixed", dayfirst=True, errors="coerce")
us = pd.to_datetime(df["order_date"], format="mixed", dayfirst=False, errors="coerce")
df["order_date_uk"] = uk
df["date_is_ambiguous"] = uk != us
report["ambiguous_dates"] = int(df["date_is_ambiguous"].sum())
# 4. prices arrive as text on some rows
report["prices_repaired"] = int(pd.to_numeric(df["unit_price"], errors="coerce").isna().sum())
df["unit_price"] = (df["unit_price"].str.replace(r"[^0-9.\-]", "", regex=True)
                    .replace("", np.nan).astype(float))
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
df["order_total"] = pd.to_numeric(df["order_total"], errors="coerce")
# 3. grams and kilograms, split on magnitude because the dates are unreliable
weight = pd.to_numeric(df["weight"], errors="coerce")
in_kg = weight < 50
df["weight_g"] = np.where(in_kg, weight * 1000, weight)
report["weights_converted"] = int(in_kg.sum())
# 5. one country, six spellings
df["country"] = (df["country"].str.strip().str.lower()
                 .replace({"gb": "uk", "united kingdom": "uk"}))
# 6. UTF-8 that was read as Latin-1 upstream
def unmojibake(s):
    try:
        return s.encode("latin-1").decode("utf-8")
    except (UnicodeEncodeError, UnicodeDecodeError):
        return s
report["names_repaired"] = int(df["product_name"].str.contains("Ã", regex=False).sum())
df["product_name"] = df["product_name"].map(unmojibake)
# 7. label returns rather than letting them net off
df["is_return"] = df["quantity"] < 0
# 8. flag the rows where the total is not quantity times price
df["total_disagrees"] = ~np.isclose(df["quantity"] * df["unit_price"],
                                    df["order_total"], rtol=1e-6, atol=0.005)
# 9. there is no rate in the file, so do not pretend there is
report["currencies"] = sorted(df["currency"].unique())
print(report)

Run that and you get 47 duplicates dropped, 444 dates you should not trust, 228 prices repaired, 1,243 weights converted, 366 names fixed, and two currencies you cannot add together. Only now are you in a position to answer the three questions, and the honest answer to the third one is that revenue by month carries a known error you have written down.

What this dataset teaches

Seven of the nine faults are invisible to any tool that checks only for nulls and types. A profiling report would pass this file. The habit that catches them is duller and more reliable: look at the distinct values of every column, plot the median of every numeric column by month, and check that your totals reconcile against a column you did not build them from.

Have fun, Andrei