Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Data 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
(541909, 8)

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
InvoiceNo              object
StockCode              object
Description            object
Quantity                int64
InvoiceDate    datetime64[ns]
UnitPrice             float64
CustomerID            float64
Country                object
dtype: object

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)
InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice CustomerID Country
0 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 2010-12-01 08:26:00 2.55 17850.0 United Kingdom
1 536365 71053 WHITE METAL LANTERN 6 2010-12-01 08:26:00 3.39 17850.0 United Kingdom
2 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 2010-12-01 08:26:00 2.75 17850.0 United Kingdom
3 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6 2010-12-01 08:26:00 3.39 17850.0 United Kingdom
4 536365 84029E RED WOOLLY HOTTIE WHITE HEART. 6 2010-12-01 08:26:00 3.39 17850.0 United Kingdom
5 536365 22752 SET 7 BABUSHKA NESTING BOXES 2 2010-12-01 08:26:00 7.65 17850.0 United Kingdom
6 536365 21730 GLASS STAR FROSTED T-LIGHT HOLDER 6 2010-12-01 08:26:00 4.25 17850.0 United Kingdom
7 536366 22633 HAND WARMER UNION JACK 6 2010-12-01 08:28:00 1.85 17850.0 United Kingdom
8 536366 22632 HAND WARMER RED POLKA DOT 6 2010-12-01 08:28:00 1.85 17850.0 United Kingdom
9 536367 84879 ASSORTED COLOUR BIRD ORNAMENT 32 2010-12-01 08:34:00 1.69 13047.0 United Kingdom

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
InvoiceNo       0.0
StockCode       0.0
Description     0.3
Quantity        0.0
InvoiceDate     0.0
UnitPrice       0.0
CustomerID     24.9
Country         0.0
dtype: float64

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()
Country
United Kingdom    133600
EIRE                 711
Hong Kong            288
Unspecified          202
Switzerland          125
Name: count, dtype: int64

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()
InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice CustomerID Country
141 C536379 D Discount -1 2010-12-01 09:41:00 27.50 14527.0 United Kingdom
154 C536383 35004C SET OF 3 COLOURED FLYING DUCKS -1 2010-12-01 09:49:00 4.65 15311.0 United Kingdom
235 C536391 22556 PLASTERS IN TIN CIRCUS PARADE -12 2010-12-01 10:24:00 1.65 17548.0 United Kingdom
236 C536391 21984 PACK OF 12 PINK PAISLEY TISSUES -24 2010-12-01 10:24:00 0.29 17548.0 United Kingdom
237 C536391 21983 PACK OF 12 BLUE PAISLEY TISSUES -24 2010-12-01 10:24:00 0.29 17548.0 United Kingdom

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
np.float64(87.4)

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)
Description
check                     159
?                          47
damages                    45
damaged                    43
found                      25
sold as set on dotcom      20
adjustment                 16
Damaged                    14
thrown away                 9
Unsaleable, destroyed.      9
Name: count, dtype: int64

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)
InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice CustomerID Country
485 536409 22111 SCOTTIE DOG HOT WATER BOTTLE 1 2010-12-01 11:45:00 4.95 17908.0 United Kingdom
489 536409 22866 HAND WARMER SCOTTY DOG DESIGN 1 2010-12-01 11:45:00 2.10 17908.0 United Kingdom
494 536409 21866 UNION JACK FLAG LUGGAGE TAG 1 2010-12-01 11:45:00 1.25 17908.0 United Kingdom
517 536409 21866 UNION JACK FLAG LUGGAGE TAG 1 2010-12-01 11:45:00 1.25 17908.0 United Kingdom
521 536409 22900 SET 2 TEA TOWELS I LOVE LONDON 1 2010-12-01 11:45:00 2.95 17908.0 United Kingdom
527 536409 22866 HAND WARMER SCOTTY DOG DESIGN 1 2010-12-01 11:45:00 2.10 17908.0 United Kingdom

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]
2995

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
np.float64(91.4)

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()
InvoiceDate
2010-12-31    42481
2011-01-31    35147
2011-02-28    27707
2011-03-31    36748
2011-04-30    29916
2011-05-31    37030
2011-06-30    36874
2011-07-31    39518
2011-08-31    35284
2011-09-30    50226
2011-10-31    60742
2011-11-30    84711
2011-12-31    25525
Freq: ME, dtype: int64

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)
np.float64(-896812.49)

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)
27.82
clean_revenue = clean['Revenue'].sum()
round(clean_revenue, 2)
round(naive_revenue - clean_revenue, 2)
np.float64(1010520.29)

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)
Description
PAPER CRAFT , LITTLE BIRDIE           168469.60
REGENCY CAKESTAND 3 TIER              142264.75
WHITE HANGING HEART T-LIGHT HOLDER    100392.10
JUMBO BAG RED RETROSPOT                85040.54
MEDIUM CERAMIC TOP STORAGE JAR         81416.73
PARTY BUNTING                          68785.23
ASSORTED COLOUR BIRD ORNAMENT          56413.03
RABBIT NIGHT LIGHT                     51251.24
CHILLI LIGHTS                          46265.11
PAPER CHAIN KIT 50'S CHRISTMAS         42584.13
Name: Revenue, dtype: float64
clean.groupby('CustomerID')['Revenue'].sum().sort_values(ascending=False).head(10)
CustomerID
14646    279138.02
18102    259657.30
17450    194390.79
16446    168472.50
14911    136161.83
12415    124564.53
14156    116560.08
17511     91062.38
12346     77183.60
16029     72708.09
Name: Revenue, dtype: float64

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()
(Timestamp('2010-12-01 08:26:00'), Timestamp('2011-12-09 12:50:00'))

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
True

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)
InvoiceNo
576339    541
579196    532
580727    528
578270    441
573576    434
dtype: int64

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)
np.float64(65.27)

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()
array(['RETRO LEAVES MAGNETIC NOTEPAD',
       'RETO LEAVES MAGNETIC SHOPPING LIST',
       'LEAVES MAGNETIC  SHOPPING LIST', 'VINTAGE LEAF MAGNETIC NOTEPAD'],
      dtype=object)

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)
np.float64(301.65)

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)
DayOfWeek
Thursday     1939228.91
Tuesday      1672493.12
Wednesday    1559469.25
Friday       1459797.08
Monday       1326500.48
Sunday        779738.80
Name: Revenue, dtype: float64

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)
Description Quantity CustomerID
390689 PAPER CRAFT , LITTLE BIRDIE 80995 16446
36375 MEDIUM CERAMIC TOP STORAGE JAR 74215 12346
303431 WORLD WAR 2 GLIDERS ASSTD DESIGNS 4800 12901
140979 SMALL POPCORN HOLDER 4300 13135
60437 EMPIRE DESIGN ROSETTE 3906 18087

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)
Raw rows: 541909
Cleaned rows: 391150
Rows removed by the audit: 150759
Naive revenue: 9747747.93
Trustworthy cleaned revenue: 8737227.64
Unique real customers: 4334

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
True

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()
np.True_

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 Description column: filter the rows where it is missing and check how many of them also have a UnitPrice of 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 common Description values to see what kind of adjustments they are.
  • On the cleaned data, run value_counts() on the StockCode values 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.

  1. Why is (df['Quantity'] * df['UnitPrice']).sum() on the raw retail data called the "naive revenue trap"?

  2. In the retail data, how does the lesson identify cancelled invoices?

  3. What does this code print?

    import pandas as pd
    df = pd.DataFrame({'a': [1, 1, 2], 'b': ['x', 'x', 'y']})
    print(df.duplicated().sum())
    
  4. Why does the lesson call .astype(str) on InvoiceNo before using .str.startswith('C')?

  5. What does df[~df['StockCode'].astype(str).str[0].str.isdigit()] select?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. 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))
    
  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=...) and drop_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())
    
  3. 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.