Lesson 1 · Real-World Data Analytics
Data Cleaning in Python Pandas: Audit a Real Retail Dataset | Data Analytics #1
Before you analyse any dataset, audit it. In this pandas tutorial we take 500,000+ real online retail transactions and check every problem that would…
- CourseReal-World Data Analytics
- Lesson1 of 100
- Video18 min
- FormatJupyter notebook · 30 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- online_retail.csv preview45.5 MB
📓 Full notebook
Download .ipynbData Analytics 100, Video 1: Acquiring and Auditing a Real Retail Transactions Dataset#
- Video one of a hundred-video real-world data analytics series.
- The real dataset: over half a million actual transaction line items from a real UK-based online gift retailer.
- Before any analysis, a real analyst always audits the data first. Let's get into it.
This lesson is for anyone starting out in practical data analysis who wants to see what a real audit looks like before a single number is reported. The business question is simple: how much did this retailer really sell, and which figures can you trust? The dataset is a transaction log from a UK-based online gift retailer, with one row per invoice line. The columns that matter most are InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID and Country.
In this lesson you will:
- Load a raw CSV with pandas and check its size, column types and first rows.
- Find missing customer IDs, cancelled invoices, negative quantities, zero prices, duplicates and non-product codes.
- See how far a naive revenue total drifts from the cleaned figure.
- Wrap every cleaning rule in one reusable function and save a trustworthy file for the rest of the series.
Prerequisites: you should know how to run a Jupyter notebook and be comfortable with basic Python. No earlier pandas experience is assumed, but it helps.
Part 1: Where This Real Data Actually Comes From#
Every analysis starts by getting the data into memory and checking that it arrived in one piece. The code imports pandas and reads online_retail.csv with pd.read_csv. The argument parse_dates=['InvoiceDate'] tells pandas to turn that column into real dates straight away, so you can later sort, filter and group by time. df.shape returns a pair of numbers: rows and columns. The output is (541909, 8), so you have over half a million invoice lines across eight columns. Write this number down, because you will compare every cleaned version against it.
import pandas as pd
df = pd.read_csv('online_retail.csv', parse_dates=['InvoiceDate'])
df.shape
Part 2: The Real Columns and Their Types#
Before you calculate anything, you need to know what type of data each column holds, because the type decides which operations make sense. The code calls df.dtypes; only this last line is displayed, so you see the types rather than the plain column list. Quantity is an integer and UnitPrice a float, which is what you want. InvoiceDate is datetime64, thanks to parse_dates. Notice that CustomerID is stored as float64: this is a classic sign of missing values, because pandas cannot keep blanks in a plain integer column. InvoiceNo is object (text), which hints that some invoice numbers contain letters.
df.columns.tolist()
df.dtypes
Part 3: A Real First Look at the Rows#
Looking at real rows grounds you in what one record actually means. df.head(10) shows the first ten lines. You can see that one invoice, 536365, spans several rows: each row is a single product on that invoice, not a whole order. All ten rows come from customers 17850 and 13047 in the United Kingdom on 1 December 2010. Stock codes such as 85123A mix digits and letters, which is why they were read as text. Tip: always check what a single row represents before you sum or count anything.
df.head(10)
Part 4: Auditing Real Missing Values#
Missing values quietly break averages, joins and customer counts, so you audit them early. df.isna() marks each blank cell as True; .sum() would count them per column, but only the last line is shown. That line uses .mean(), which on a True/False table gives the share of Trues, then .round(3) * 100 turns it into a percentage. The output shows CustomerID is missing on 24.9% of rows, about one in four, while Description is missing on only 0.3%. Every other column is complete. Anything you do per customer will therefore lose roughly a quarter of the lines.
df.isna().sum()
df.isna().mean().round(3) * 100
Part 5: Investigating the Real Missing Customer IDs#
A quarter of rows without a customer is too big to drop without understanding it. The code filters those rows with df[df['CustomerID'].isna()] into missing_cust, then counts their countries with value_counts().head(). The output shows the gap is overwhelmingly British: 133,600 of the missing rows are from the United Kingdom, far ahead of EIRE with 711 and Hong Kong with 288. There is also an Unspecified country with 202 rows. This pattern suggests a recording issue on the UK side, such as guest checkouts, rather than random blanks.
missing_cust = df[df['CustomerID'].isna()]
missing_cust.shape[0]
missing_cust['Country'].value_counts().head()
Part 6: Real Cancelled Invoices#
Cancelled orders sit in the same table as real sales, so leaving them in distorts every revenue and quantity figure. In this dataset a cancellation is marked by an invoice number starting with the letter C. The code converts InvoiceNo to text with astype(str), and .str.startswith('C') returns True or False per row. That mask is stored as is_cancelled. The count line is not displayed; the output shows the first five cancelled rows instead. Notice the C prefix, the negative quantities, and a Discount line with stock code D. Keep is_cancelled, because it is reused when you measure lost revenue.
is_cancelled = df['InvoiceNo'].astype(str).str.startswith('C')
is_cancelled.sum()
df[is_cancelled].head()
Part 7: Real Negative Quantities#
Negative quantities usually mean returns or corrections, and you want to know whether they are the same thing as cancellations. The code first builds the mask df['Quantity'] < 0. The displayed line then takes only the invoice numbers of negative-quantity rows, checks which start with C, and uses .mean() to get the share of Trues as a percentage. The output is 87.4%: most negative quantities are on cancelled invoices, but not all of them. The remaining 12.6% are adjustments that a C filter alone would miss, which is why the cleaning step later filters on quantity as well.
(df['Quantity'] < 0).sum()
(df.loc[df['Quantity'] < 0, 'InvoiceNo'].astype(str).str.startswith('C')).mean().round(3) * 100
Part 8: Real Zero and Negative Prices#
A price of zero or below cannot be a genuine sale, so these rows need explaining before you remove them. (df['UnitPrice'] <= 0).sum() counts them, but the displayed result is the next line: the ten most common descriptions among those rows. The output reads like a stock-room notebook: check (159), ? (47), damages (45), damaged (43), found (25) and similar entries. These are internal inventory notes, not customer orders. Notice that damaged and Damaged appear separately, because value_counts is case-sensitive.
(df['UnitPrice'] <= 0).sum()
df.loc[df['UnitPrice'] <= 0, 'Description'].value_counts().head(10)
Part 9: Real Duplicate Rows#
Exact duplicate rows inflate counts and revenue, and they often come from double logging. df.duplicated() flags a row as True if an identical row appeared earlier, and .sum() counts them (not displayed). The second line uses keep=False, which flags every copy, including the first, so you can see the pairs side by side. Sorting by InvoiceNo groups them. The output shows invoice 536409 with the UNION JACK FLAG LUGGAGE TAG and HAND WARMER SCOTTY DOG DESIGN lines each appearing twice with the same time, price and customer. Tip: without keep=False you only see the second copy, which hides the pattern.
df.duplicated().sum()
df[df.duplicated(keep=False)].sort_values('InvoiceNo').head(6)
Part 10: Real Non-Product Stock Codes#
Not every stock code is a product. Postage, discounts, bank charges and manual adjustments are recorded with codes that start with a letter. The code takes the first character of each code with .astype(str).str[0], checks it with .str.isdigit(), and the ~ flips the result to keep the non-numeric codes. Only the last line is shown: non_numeric.shape[0], which gives 2,995 rows. That is a small share of the data, but these lines can carry large values, so including them would blur product-level analysis. Run the value_counts line on its own to see which codes they are.
non_numeric = df[~df['StockCode'].astype(str).str[0].str.isdigit()]
non_numeric['StockCode'].value_counts().head(10)
non_numeric.shape[0]
Part 11: Real Country Distribution#
Knowing where customers are tells you whether a 'global' result is really a UK result. The code counts distinct countries with nunique() and lists the top ten, but only the last line is displayed. That line compares each row's country with 'United Kingdom' and takes .mean(), giving the share of rows as a percentage. The output is 91.4%: more than nine in ten lines come from the UK. Any overall figure in this dataset will therefore mainly describe British buying, and other countries are best compared with care because their samples are much smaller.
df['Country'].nunique()
df['Country'].value_counts().head(10)
(df['Country'] == 'United Kingdom').mean().round(3) * 100
Part 12: The Real Date Range#
You need to know the time span before talking about trends or yearly totals. The first line finds the earliest and latest invoice dates. The displayed line uses set_index('InvoiceDate') and resample('ME'), which groups rows into calendar months ending on the last day, then .size() counts rows per month. Volume climbs from 27,707 rows in February 2011 to a peak of 84,711 in November 2011, the pre-Christmas rush. December 2011 shows only 25,525 rows, because the data stops part-way through that month. Tip: never compare a partial month with full ones.
df['InvoiceDate'].min(), df['InvoiceDate'].max()
df.set_index('InvoiceDate').resample('ME').size()
Part 13: The Naive Revenue Trap#
This step shows why auditing matters in money terms. naive_revenue multiplies Quantity by UnitPrice for every row and sums it, cancellations and all. The displayed result is cancelled_revenue: the same calculation restricted with df.loc[is_cancelled, ...]. The output is -896,812.49, meaning cancelled lines subtract almost 900 thousand from the raw total. A naive total mixes real sales, refunds, adjustments and stock notes into one figure that does not answer any clear business question. The naive figure is printed later in the written summary, so you can compare it with the cleaned one.
naive_revenue = (df['Quantity'] * df['UnitPrice']).sum()
round(naive_revenue, 2)
cancelled_revenue = (df.loc[is_cancelled, 'Quantity'] * df.loc[is_cancelled, 'UnitPrice']).sum()
round(cancelled_revenue, 2)
Part 14: Building a Real Cleaning Function#
Once you know what is wrong, you write the rules down once in a function, so the same cleaning can be repeated and checked. clean_retail works on raw.copy() so the original stays untouched. It removes C invoices, keeps only positive quantities and prices, keeps stock codes that start with a digit, drops rows with no CustomerID using dropna(subset=...), and removes exact duplicates with drop_duplicates(). It then converts CustomerID to int (safe now that blanks are gone), adds a Revenue column and resets the index. Defining the function produces no output.
def clean_retail(raw):
out = raw.copy()
out = out[~out['InvoiceNo'].astype(str).str.startswith('C')]
out = out[out['Quantity'] > 0]
out = out[out['UnitPrice'] > 0]
out = out[out['StockCode'].astype(str).str[0].str.isdigit()]
out = out.dropna(subset=['CustomerID'])
out = out.drop_duplicates()
out['CustomerID'] = out['CustomerID'].astype(int)
out['Revenue'] = out['Quantity'] * out['UnitPrice']
return out.reset_index(drop=True)
Part 15: Applying It to the Real Data#
Applying the function tells you how much of the raw data survives the audit. clean = clean_retail(df) runs every rule in one go. The displayed line works out the share of rows removed: one minus the ratio of cleaned rows to raw rows, times 100. The output is 27.82%, so more than a quarter of lines were dropped, mostly the missing customer IDs. The next code then compares revenue: naive_revenue - clean_revenue is 1,010,520.29, about a million in revenue that the naive sum got wrong. Removing that much data is normal for raw transaction logs.
clean = clean_retail(df)
clean.shape
round(100 * (1 - clean.shape[0] / df.shape[0]), 2)
clean_revenue = clean['Revenue'].sum()
round(clean_revenue, 2)
round(naive_revenue - clean_revenue, 2)
Part 16: Real Top Products, Now That the Data Is Trustworthy#
With trustworthy data you can finally answer real questions. The code groups the cleaned rows by Description, sums Revenue, sorts from highest to lowest and shows the top ten. PAPER CRAFT , LITTLE BIRDIE leads with 168,469.60, followed by REGENCY CAKESTAND 3 TIER at 142,264.75 and WHITE HANGING HEART T-LIGHT HOLDER at 100,392.10. The second grouping does the same by CustomerID: customer 14646 tops the list with 279,138.02. Be careful: some leaders, like the paper craft item, owe their position to one huge order, which you will see in the extremes step.
clean.groupby('Description')['Revenue'].sum().sort_values(ascending=False).head(10)
clean.groupby('CustomerID')['Revenue'].sum().sort_values(ascending=False).head(10)
Part 17: The Real Scope Now on the Table#
Before moving on, you record the scope of what you are analysing: how many customers, how many orders and what period. The code counts unique customers and invoices with nunique(), but only the final line is displayed, the earliest and latest invoice dates in the cleaned data. The output runs from 2010-12-01 08:26 to 2011-12-09 12:50, about a year and a week. The customer count appears later in the written summary as 4,334. Tip: always state the date range alongside any total so readers know what period it covers.
clean['CustomerID'].nunique()
clean['InvoiceNo'].nunique()
clean['InvoiceDate'].min(), clean['InvoiceDate'].max()
Part 18: Saving the Real Cleaned Dataset#
Saving the cleaned table means every later lesson starts from the same trustworthy data without rerunning the audit. to_csv writes the file, with index=False so the row numbers are not saved as an extra column. The code then reads it straight back with parse_dates=['InvoiceDate'] and checks that reloaded.shape == clean.shape. The output is True, so no rows or columns were lost in the round trip. A quick reload check like this catches problems such as wrong separators or lost columns before they spread.
clean.to_csv('online_retail_clean.csv', index=False)
reloaded = pd.read_csv('online_retail_clean.csv', parse_dates=['InvoiceDate'])
reloaded.shape == clean.shape
Part 19: One Real Chart Before We Wrap Up#
A chart is often the quickest way to share an audit result with someone who will never open the notebook. The code counts cleaned rows per country with value_counts().head(10), then draws a bar chart with matplotlib. plt.xticks(rotation=45, ha='right') tilts the country names so they don't overlap, and tight_layout() stops labels being cut off. Instead of showing the chart inline, savefig writes it to retail_top_countries.png and plt.close() frees the figure, so there is no output here. Open the saved image and look for one bar, the United Kingdom, towering over every other country.
import matplotlib.pyplot as plt
top_countries = clean['Country'].value_counts().head(10)
plt.figure(figsize=(9, 5))
plt.bar(top_countries.index, top_countries.values, color='steelblue')
plt.xticks(rotation=45, ha='right')
plt.title('Real Cleaned Transaction Rows by Country')
plt.tight_layout()
plt.savefig('retail_top_countries.png', dpi=120)
plt.close()
Part 20: Real Basket Size per Invoice#
Basket size, the number of product lines per invoice, tells you how customers shop: a few items at a time or large wholesale-style orders. clean.groupby('InvoiceNo').size() counts rows per invoice. describe() gives summary statistics, but only the last line is displayed: the five biggest baskets. Invoice 576339 has 541 different lines, and the next four all exceed 430. Orders this large look like trade buyers rather than individual gift shoppers, which matters when you later segment customers. Run describe() on its own to see the typical basket size.
basket_size = clean.groupby('InvoiceNo').size()
basket_size.describe()
basket_size.sort_values(ascending=False).head(5)
Part 21: Real Repeat vs One-Time Customers#
Repeat customers are usually worth far more than one-off buyers, so this share is a key health check. groupby('CustomerID')['InvoiceNo'].nunique() counts distinct orders per customer. The code then counts one-time and repeat customers, but only the last line is shown: (orders_per_customer > 1).mean() gives the share of customers with more than one order. The output is 65.27%, so about two in three customers came back at least once. That is a strong retention base, and it sets up next lesson's customer segmentation.
orders_per_customer = clean.groupby('CustomerID')['InvoiceNo'].nunique()
(orders_per_customer == 1).sum()
(orders_per_customer > 1).sum()
round(100 * (orders_per_customer > 1).mean(), 2)
Part 22: Real StockCode-to-Description Consistency#
A stock code should map to one product, but real systems drift as names are edited or mistyped. groupby('StockCode')['Description'].nunique() counts how many different descriptions each code has. The displayed line uses idxmax() to find the code with the most descriptions and lists them with .unique(). The output shows one code labelled four ways, including RETRO LEAVES MAGNETIC NOTEPAD, the misspelt RETO LEAVES MAGNETIC SHOPPING LIST and VINTAGE LEAF MAGNETIC NOTEPAD. Tip: group by StockCode rather than Description when you need stable product totals.
desc_per_code = clean.groupby('StockCode')['Description'].nunique()
(desc_per_code > 1).sum()
desc_per_code.sort_values(ascending=False).head(5)
clean.loc[clean['StockCode'] == desc_per_code.idxmax(), 'Description'].unique()
Part 23: Real Average Order Value#
Average order value (AOV) is the typical amount spent per order, a core retail metric. groupby('InvoiceNo')['Revenue'].sum() adds up each order. The code calculates both mean and median, but only the median is displayed: 301.65. The median is the middle order when all are sorted, so it is not pulled up by a handful of very large trade orders. Run the .mean() line on its own and compare: when the mean is much higher than the median, a few big orders are skewing the average.
order_value = clean.groupby('InvoiceNo')['Revenue'].sum()
order_value.mean().round(2)
order_value.median().round(2)
Part 24: Real Revenue by Day of the Week#
Knowing which days bring in the most revenue helps with staffing, marketing emails and promotions. .dt.day_name() turns each date into a weekday name, stored in a new DayOfWeek column. The code then sums Revenue by day and sorts it. Thursday leads with 1,939,228.91, followed by Tuesday and Wednesday, while Sunday is lowest at 779,738.80. Look closely: Saturday does not appear at all, which suggests the business did not process orders on Saturdays. Tip: check for missing categories as well as ranking the ones you see.
clean['DayOfWeek'] = clean['InvoiceDate'].dt.day_name()
clean.groupby('DayOfWeek')['Revenue'].sum().sort_values(ascending=False)
Part 25: Real Quantity and Price Extremes#
Extreme values can dominate totals, so you check them before trusting rankings. describe() gives summary statistics for Quantity and UnitPrice, but the displayed line sorts by Quantity and shows the five largest lines. Two stand out: 80,995 units of PAPER CRAFT , LITTLE BIRDIE by customer 16446 and 74,215 units of MEDIUM CERAMIC TOP STORAGE JAR by customer 12346. These single orders explain why both products, and both customers, appear high in the top ten lists. Whether to keep them depends on the question, but you should always know they are there.
clean[['Quantity', 'UnitPrice']].describe()
clean.sort_values('Quantity', ascending=False)[['Description', 'Quantity', 'CustomerID']].head(5)
Part 26: A Real Written Summary, Saved to Disk#
A short written summary turns the audit into something you can share with a manager. The code builds a list of lines with f-strings, writes them to online_retail_audit_summary.txt (adding a newline with chr(10)), then prints them. The output confirms the key facts: 541,909 raw rows, 391,150 cleaned rows, 150,759 rows removed, a naive revenue of 9,747,747.93 against a trustworthy 8,737,227.64, and 4,334 unique customers. Using with open(...) makes sure the file is closed properly even if something goes wrong.
summary_lines = [
f'Raw rows: {df.shape[0]}',
f'Cleaned rows: {clean.shape[0]}',
f'Rows removed by the audit: {df.shape[0] - clean.shape[0]}',
f'Naive revenue: {round(naive_revenue, 2)}',
f'Trustworthy cleaned revenue: {round(clean_revenue, 2)}',
f'Unique real customers: {clean["CustomerID"].nunique()}',
]
with open('online_retail_audit_summary.txt', 'w') as f:
for line in summary_lines:
f.write(line + chr(10))
for line in summary_lines:
print(line)
Part 27: Confirming the Real Summary File#
Saving a file is not the same as knowing it saved correctly. The code uses Python's os module: os.path.exists checks that the file is there, and os.path.getsize(...) > 0 checks that it is not empty. Only the last check is displayed, and it is True. This tiny habit catches silent failures, such as writing to the wrong folder or an exception halfway through writing, before you send the file to anyone.
import os
os.path.exists('online_retail_audit_summary.txt')
os.path.getsize('online_retail_audit_summary.txt') > 0
Part 28: One Last Real Sanity Check#
A final check confirms that the cleaned data really meets the rules you set. clean.isna().sum().sum() adds missing values across all columns into one number (not displayed, run it on its own to confirm it is zero). The displayed line checks that every quantity and every price is above zero, using .all(), which is True only if every value passes. The output is True. Tip: rerun checks like these whenever you change the cleaning function, so a later edit cannot quietly undo earlier work.
clean.isna().sum().sum()
(clean['Quantity'] > 0).all() and (clean['UnitPrice'] > 0).all()
Wrap-Up: What You Learned#
- Real production data always needs an audit before any number gets reported: nulls, cancellations, negative quantities, zero prices, non-product codes, and duplicates.
- This real UK online retailer's cancelled invoices genuinely start with the letter C, and negative quantities mostly, but not entirely, overlap with them.
- A naive revenue sum on unaudited real data can be meaningfully wrong; the real cleaned figure is the one worth trusting.
- The real cleaned dataset is now saved once, so the rest of this retail series builds on trustworthy data from here on.
- Next video: real RFM segmentation, turning this same cleaned customer-and-revenue data into actual customer segments.
Practice on your own
- Repeat the missing-value audit for the
Descriptioncolumn: filter the rows where it is missing and check how many of them also have aUnitPriceof zero. - Using the raw
df, find the negative-quantity rows that do not start with C (the 12.6% from the audit) and list their most commonDescriptionvalues to see what kind of adjustments they are. - On the cleaned data, run
value_counts()on theStockCodevalues you removed as non-product codes, then calculate how much revenue those lines carried in the raw data.
The next video in the series covers RFM segmentation (Recency, Frequency, Monetary value), turning this same cleaned customer and revenue data into actual customer segments.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
Why is
(df['Quantity'] * df['UnitPrice']).sum()on the raw retail data called the "naive revenue trap"?Raw data contains cancelled invoices, returns and junk rows. Summing everything without auditing gives a figure that is not true sales revenue.
In the retail data, how does the lesson identify cancelled invoices?
Cancelled invoices have an InvoiceNo that starts with 'C', found with
.astype(str).str.startswith('C'). Negative quantities overlap with these but not perfectly.What does this code print?
import pandas as pd df = pd.DataFrame({'a': [1, 1, 2], 'b': ['x', 'x', 'y']}) print(df.duplicated().sum())
duplicated()marks the second occurrence of an identical row as True. Row 1 repeats row 0, so the sum is 1.Why does the lesson call
.astype(str)onInvoiceNobefore using.str.startswith('C')?InvoiceNo mixes plain numbers with codes starting with C. Converting to string makes the
.strmethods safe for every row.What does
df[~df['StockCode'].astype(str).str[0].str.isdigit()]select?It takes the first character, checks if it is a digit, and
~flips the result. Codes like POST or M are fees or manual entries, not real products.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Build a small DataFrame of 10 invoice lines with columns InvoiceNo, Quantity, UnitPrice (include two invoices starting with 'C' that have negative quantities). Compute the naive revenue, the number of cancelled lines, and the revenue from cancelled lines. Print all three.
💡 Hint
Use
astype(str).str.startswith('C')to build a cancelled mask.✅ Show solution
import pandas as pd df = pd.DataFrame({ 'InvoiceNo': ['1001','1001','1002','C1003','1004','1005','C1006','1007','1008','1008'], 'Quantity': [2, 3, 1, -2, 5, 4, -1, 6, 2, 1], 'UnitPrice': [2.5, 1.0, 10.0, 2.5, 3.0, 1.5, 10.0, 2.0, 4.0, 5.0], }) is_cancelled = df['InvoiceNo'].astype(str).str.startswith('C') naive_revenue = (df['Quantity'] * df['UnitPrice']).sum() cancelled_revenue = (df.loc[is_cancelled, 'Quantity'] * df.loc[is_cancelled, 'UnitPrice']).sum() print('Naive revenue:', round(naive_revenue, 2)) print('Cancelled lines:', is_cancelled.sum()) print('Cancelled revenue:', round(cancelled_revenue, 2))
Create a 12-row DataFrame with InvoiceNo, StockCode, Quantity, UnitPrice, CustomerID (some CustomerIDs missing, some zero prices, one 'POST' stock code, one duplicate row, one cancelled invoice). Write a function
clean_retail(raw)that drops cancelled invoices, non-positive quantities and prices, non-numeric stock codes, missing CustomerID and duplicates, then adds a Revenue column. Print the row count before and after and the clean revenue.💡 Hint
Filter step by step on a copy, then use
dropna(subset=...)anddrop_duplicates().✅ Show solution
import pandas as pd import numpy as np raw = pd.DataFrame({ 'InvoiceNo': ['1001','1001','1002','1003','C1004','1005','1006','1006','1007','1008','1009','1010'], 'StockCode': ['85123','84029','85123','POST','85123','22423','22423','22423','84029','85123','22423','85123'], 'Quantity': [2, 1, 3, 1, -2, 4, 1, 1, 5, 2, 0, 3], 'UnitPrice': [2.5, 3.0, 2.5, 18.0, 2.5, 12.0, 12.0, 12.0, 3.0, 0.0, 12.0, 2.5], 'CustomerID': [1.0, 1.0, 2.0, 2.0, 3.0, np.nan, 4.0, 4.0, 5.0, 5.0, 6.0, 6.0], }) def clean_retail(raw): out = raw.copy() out = out[~out['InvoiceNo'].astype(str).str.startswith('C')] # cancellations out = out[out['Quantity'] > 0] out = out[out['UnitPrice'] > 0] out = out[out['StockCode'].astype(str).str[0].str.isdigit()] # real products only out = out.dropna(subset=['CustomerID']) out = out.drop_duplicates() out['CustomerID'] = out['CustomerID'].astype(int) out['Revenue'] = out['Quantity'] * out['UnitPrice'] return out.reset_index(drop=True) clean = clean_retail(raw) print('Rows before:', len(raw), 'after:', len(clean)) print('Clean revenue:', clean['Revenue'].sum())
Using a clean 15-row transactions DataFrame (InvoiceNo, CustomerID, Country, Quantity, UnitPrice), build an audit summary: percent of rows removed, total revenue, number of unique customers and invoices, order value mean vs median per invoice, and the share of rows from the United Kingdom. Print a short text report.
💡 Hint
Group Revenue by InvoiceNo to get order values, and use
value_counts(normalize=True)or a boolean mean for the UK share.✅ Show solution
import pandas as pd raw_rows = 18 # pretend the raw file had 18 rows before cleaning clean = pd.DataFrame({ 'InvoiceNo': [1,1,2,2,3,4,4,4,5,6,7,7,8,9,10], 'CustomerID': [1,1,2,2,3,1,1,1,4,5,2,2,6,7,3], 'Country': ['United Kingdom']*9 + ['France','France','United Kingdom','Germany','Germany','United Kingdom'], 'Quantity': [2,1,4,2,10,1,3,2,6,1,2,2,5,3,1], 'UnitPrice': [2.5,4.0,1.5,3.0,0.5,10.0,2.0,1.0,3.5,25.0,2.0,2.0,1.2,4.0,8.0], }) clean['Revenue'] = clean['Quantity'] * clean['UnitPrice'] order_value = clean.groupby('InvoiceNo')['Revenue'].sum() removed_pct = round(100 * (1 - len(clean) / raw_rows), 2) uk_share = round((clean['Country'] == 'United Kingdom').mean() * 100, 1) print(f'Rows removed by audit: {removed_pct}%') print(f'Total revenue: {clean["Revenue"].sum():.2f}') print(f'Unique customers: {clean["CustomerID"].nunique()}, invoices: {clean["InvoiceNo"].nunique()}') print(f'Average order value: {order_value.mean():.2f}, median: {order_value.median():.2f}') print(f'UK share of rows: {uk_share}%')
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



