Profile before changing

Lesson 10
55 min
Module 4: Clean and transform data

Lesson 10 of 18 · SQL and Data Preparation for AIProfile before changing

Course overview

1. Data sources and structure

2. SQL essentials

3. Useful analytical queries

4. Clean and transform data

5. Prepare for modelling safely

6. Data contracts and handoff

7. Case studies

What you will be able to do

Measure the problems in the data before you change any value. If you clean first, you cannot say later what you changed, how many rows it touched, or why.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions. Its setup cells install pandas, build the database and make the extract from Module 3, so it needs no local setup. Read the explanations here, and run the code there.

Open in Colab Open in Kaggle View on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

COUNT(*), COUNT(column), GROUP BY and IS NULL, from Aggregate correctly.
Joins, and the 14 repeated ticket IDs, from Join tables.
What the extract selects, and why it has 1,416 rows, from Reproducible extraction.
How to make a virtual environment and install a package with pip, from Isolate dependencies: Worked example. This lesson makes one for the project.

What you need on your computer (only for the local route; the notebook and the boxes on this page need none of it)

The folder ~/projects/ticket-data with larkfield.db and q.py. You made it in Set up a practice database: Build the database. If the database is missing or changed, reset it with python3 get_data.py --reset.
The extract data/extract/tickets.csv (1,416 rows), from python3 scripts/extract.py (on Windows, python scripts/extract.py). You made it in Reproducible extraction: Guided practice. If the script is missing or broken, get the course’s finished scripts. Copy scripts/extract.py and the two files in queries/ into your project, then run the script again.
A terminal open in that folder, and an internet connection, one time, to install pandas. There is no account and no cost.
The saved queries of this module go in queries/, numbered from 41-.

Tested on macOS with Python 3.14.6 and pandas 3.0.6. The browser boxes use SQLite 3.49.1. Windows steps are not tested. The notebook was tested in Jupyter.

Module 4: from export to clean table

Larkfield is a fictional online shop for home and garden products. Its help desk wants a model that predicts, when a ticket arrives, if it will need a senior agent. In Modules 1 to 3 you queried the help-desk export and saved a reproducible extract. In this module, Mei, the data engineer, cleans that extract. The data is made up for this course; there are no real people in it.

The module has three steps, one per lesson:

Profile the data: find and count every problem. Change nothing. (This lesson.)
Load the data in Python with the correct types, and join and convert it in named steps. (Lesson 2.)
Decide what to do with each problem, keep the original values, and account for every row. (Lesson 3.)

What a profile is

A profile is a short description of a dataset, column by column. It says how many values are missing, which values occur, and the smallest and largest value. You make it before any change, for two reasons. First, it shows you the problems. Second, it is the “before” picture. After cleaning, you compare the result with it.

Look at five made-up tickets first. Every row has a problem that does not cause an error:

ticket_id	channel	order_value	word_count	created_at	Problem
T-1	email	42.50	80	2026-03-02	none
T-2	Email	NULL	45	2026-03-02	a second spelling of `email`; a missing value
T-3	chat	-1	120	2026-03-03	`-1` is not an amount: it is a code for “unknown”
T-3	chat	-1	120	2026-03-03	the same row twice
T-4	web_form	4250	31000	2036-03-04	cents, not dollars; a very long message; a year in the future

A query on this table runs without an error. AVG(order_value) returns a number. But the number is wrong, and nothing tells you. A profile asks five questions, and each question finds one kind of problem:

Question	Kind of problem	In the five tickets
Which values are missing, and what does each missing value mean?	missingness	T-2 has no `order_value`
Does a row, or a key, occur more than once?	duplicates	T-3 twice
Are all values in the list of allowed values?	invalid categories	`Email` next to `email`
Do all values use the same unit?	inconsistent units	4250 cents next to 42.50 dollars
Are the values in a sensible range?	suspicious ranges	31000 words, the year 2036, `-1`

The profile only finds and counts. The decisions come in lesson 3. Some decisions need a person who knows the business. At Larkfield, that person is Grace, the support lead.

Worked example: Mei profiles the export

Mei profiles the full export in larkfield.db (1,417 ticket rows), not only the extract. The extract left out one row. The profile must show that row too.

1. Missingness

COUNT(*) counts rows. COUNT(column) counts the values that are not NULL. (NULL is SQL’s mark for “no value here”.) So COUNT(*) - COUNT(column) is the number of missing values. Predict first: which column has the most NULLs?

Run SQL in your browser

Try it: count the missing values

Run the query. You should see 1 row: 1,417 rows, then 12, 38, 188, 0 and 12 missing values.

SQL query (you can change it)

SELECT
    COUNT(*)                      AS ticket_rows,
    COUNT(*) - COUNT(customer_id) AS no_customer_id,
    COUNT(*) - COUNT(channel)     AS no_channel,
    COUNT(*) - COUNT(order_value) AS no_order_value,
    COUNT(*) - COUNT(word_count)  AS no_word_count,
    COUNT(*) - COUNT(closed_at)   AS no_closed_at
FROM tickets;

Four columns have missing values. A count alone is not enough: the profile must also say what each NULL means. Mei asks Grace about each one:

Column	NULL rows	What NULL means (Grace)
`customer_id`	12	A guest checkout. The customer has no account.
`channel`	38	The help desk did not record the channel. It is unknown.
`order_value`	188	? Mei checks this one in the next box.
`closed_at`	12	The ticket is still open.

Run SQL in your browser

Your turn: which tickets have no order value?

Write a query that groups the tickets with a NULL order_value by team. Remember: IS NULL, not = NULL. The check compares your result with ours.

SQL query (you can change it)

-- Which teams have tickets with no order_value?
-- Show each team and its number of such tickets.
-- Write your query here

Show the answer

SELECT team, COUNT(*) AS n
FROM tickets
WHERE order_value IS NULL
GROUP BY team;

One row: account, 188. The account team has 188 tickets in total, so every account ticket has a NULL order_value, and no other ticket has one. Grace explains: “Account questions, such as a password reset, have no order.” Here the NULL is not an unknown value. It means “there is no order”. This difference matters in lesson 3.

2. Missing values that are not NULL

A system can also mark a missing value with a normal-looking value, such as -1, 0, 9999 or the text N/A. This is a sentinel value: a value that stands for “no real value”. A NULL count does not find it. The most common values often do:

Run SQL in your browser

Try it: the most common order values

Run the query. The first row is NULL (188 rows). The second row is -1, with 11 rows. Real amounts occur at most 3 times each.

SQL query (you can change it)

SELECT order_value, COUNT(*) AS n
FROM tickets
GROUP BY order_value
ORDER BY n DESC
LIMIT 5;

An order cannot cost minus one dollar. Grace confirms it: the order system writes -1 when it cannot find the order. So the profile has two kinds of missing order_value: 188 NULLs that mean “no order”, and 11 sentinels that mean “unknown order”.

3. Duplicates: exact rows and repeated keys

There are two kinds of duplicate. An exact duplicate is a row that is equal to another row in every column. A repeated key is a key, here ticket_id, that occurs in more than one row, even if other columns differ. You met the 14 repeated ticket IDs in Module 1. The profile must also say which kind they are.

Run SQL in your browser

Try it: two kinds of duplicate

Run the query. You should see 1,417 rows, 1,403 distinct IDs, and 1,403 distinct rows.

SQL query (you can change it)

SELECT
    COUNT(*)                  AS ticket_rows,
    COUNT(DISTINCT ticket_id) AS distinct_ids,
    (SELECT COUNT(*) FROM (SELECT DISTINCT * FROM tickets)) AS distinct_rows
FROM tickets;

SELECT DISTINCT * removes exact duplicates only. It gives 1,403 rows, the same number as the distinct IDs. So all 14 extra rows are exact copies. (An export page was sent twice.) This is the easy case: removing a copy loses no information. If the two counts were different, some tickets would have two different rows, and you would need to decide which row is correct.

4. Invalid categories

A category column must use a fixed list of values. For channel, the list is email, chat, phone and web_form.

Run SQL in your browser

Try it: every channel value

Run the query. You should see 9 rows. Find the values that are not in the list of four.

SQL query (you can change it)

SELECT channel, COUNT(*) AS n
FROM tickets
GROUP BY channel
ORDER BY n DESC;

The list of four plus NULL accounts for 1,377 rows (541 + 413 + 200 + 185 + 38). The other 40 rows use four other spellings: Email (20), chat with a space at the start (8), EMAIL (7) and webform (5). In the result table, the space in chat is hard to see. GROUP BY shows it as a separate row, so you know that the value is different.

Run SQL in your browser

Your turn: segments in the customers table

The allowed segments are home, trade and business. Write a query that lists each spelling and its count. Which spelling is not allowed?

SQL query (you can change it)

-- List each spelling of segment in the customers table,
-- with the number of customers that use it.
-- Write your query here

Show the answer

SELECT segment, COUNT(*) AS n
FROM customers
GROUP BY segment
ORDER BY n DESC;

Four rows: home 166, trade 47, business 24, Trade 3. Trade is the same segment as trade, with a capital letter.

5. Inconsistent units

A column can have one name and two units. To find this, compare groups: if one group has values about 100 times larger than the others, suspect a different unit. Mei groups the web_form tickets by month:

Run SQL in your browser

Try it: web form order values by month

Run the query. Look at January. Then change 'web_form' to 'email' and run it again. Do you see the same jump?

SQL query (you can change it)

SELECT
    strftime('%Y-%m', created_at)  AS month,
    COUNT(*)                       AS n,
    ROUND(AVG(order_value), 2)     AS avg_value,
    MAX(order_value)               AS max_value
FROM tickets
WHERE channel = 'web_form'
  AND order_value > 0
GROUP BY month
ORDER BY month;

In January, the average web form order is 7,957.91 dollars. From February to June, it is between 88.76 and 158.07 dollars. (The last row, 2036-01, is one ticket with a wrong year. You meet it in the next check.) Email orders stay between about 100 and 145 dollars in every month. Grace knows the reason: until 1 February 2026, the old web form sent the amount in cents. 4,503 cents is 45.03 dollars. A model that sees these values learns that web form customers buy expensive things, which is false.

6. Suspicious ranges

The smallest and largest value of each column show values that are impossible or very unlikely.

Run SQL in your browser

Try it: the smallest and largest values

Run the query. You should see 4 rows. For each row, ask: “Is this value possible?”

SQL query (you can change it)

SELECT 'created_at' AS column_name, MIN(created_at) AS smallest, MAX(created_at) AS largest FROM tickets
UNION ALL
SELECT 'priority', MIN(priority), MAX(priority) FROM tickets
UNION ALL
SELECT 'order_value', MIN(order_value), MAX(order_value) FROM tickets
UNION ALL
SELECT 'word_count', MIN(word_count), MAX(word_count) FROM tickets;

Column	Smallest	Largest	What Mei finds
`created_at`	2026-01-05 07:30:24	2036-01-24 21:27:43	One ticket is 10 years in the future. It was closed on 2026-01-25, so someone typed 2036 for 2026. The extract leaves this row out.
`priority`	1	3	As expected.
`order_value`	-1	34518	The sentinel and the cents again. 25 values are above 2,000. 24 of them are January web form tickets; the 25th is the 2036 ticket, also from the web form.
`word_count`	0	48210	Grace: 0 words is a ticket with only an attachment (7 rows, valid). 48,210 words is one ticket, `T-10361`, where the customer pasted a log file.

Some problems are visible only when you compare two tables. A ticket cannot exist before the customer’s account:

Run SQL in your browser

Your turn: tickets older than the customer

Add a WHERE clause that keeps a ticket only if its day is before joined_on. Predict the number of rows first.

SQL query (you can change it)

-- Find the tickets that were created before the customer joined.
-- Show ticket_id, customer_id, created_at and joined_on.
-- Tip: date(created_at) gives the day part of the timestamp.
SELECT t.ticket_id, t.customer_id, t.created_at, c.joined_on
FROM tickets AS t
JOIN customers AS c
    ON c.customer_id = t.customer_id
-- add a WHERE clause here
;

Show the answer

SELECT t.ticket_id, t.customer_id, t.created_at, c.joined_on
FROM tickets AS t
JOIN customers AS c
    ON c.customer_id = t.customer_id
WHERE date(t.created_at) < c.joined_on;

3 rows: T-11185 (created 2026-03-25, joined 2026-04-05), T-11224 (2026-05-17, joined 2026-05-24) and T-11375 (2026-03-23, joined 2026-04-10). Either the ticket time or the join date is wrong, and the data does not say which one. Mei cannot correct these rows with confidence.

Run the profile on your computer

The boxes above use the browser. Mei saves the profile queries in her project, so she can run them again on a new export. Open a terminal in ~/projects/ticket-data. Save this file as queries/41-profile-missing.sql:

-- Profile: missing values in tickets (NULL count per column).
SELECT
    COUNT(*)                         AS ticket_rows,
    COUNT(*) - COUNT(customer_id)    AS no_customer_id,
    COUNT(*) - COUNT(channel)        AS no_channel,
    COUNT(*) - COUNT(order_value)    AS no_order_value,
    COUNT(*) - COUNT(word_count)     AS no_word_count,
    COUNT(*) - COUNT(closed_at)      AS no_closed_at
FROM tickets;

python3 q.py queries/41-profile-missing.sql

python q.py queries/41-profile-missing.sql

python3 q.py queries/41-profile-missing.sql

ticket_rows  no_customer_id  no_channel  no_order_value  no_word_count  no_closed_at
-----------  --------------  ----------  --------------  -------------  ------------
1417         12              38          188             0              12

1 row

Save the other checks in the same way, one query per file: 42-profile-ranges.sql for the smallest and largest values, and so on. (Use the next free numbers in your queries/ folder if your numbers differ.)

The same profile in pandas

The rest of this module uses pandas, a Python library for tables. A pandas table is a DataFrame: rows and named columns, like a SQL table, but in the memory of your Python program. pandas is not part of Python, so you install it now, in a virtual environment for this project.

cd ~/projects/ticket-data
python3 -m venv .venv
source .venv/bin/activate
python -m pip install pandas==3.0.6

cd ~/projects/ticket-data
python -m venv .venv
.venv\Scripts\Activate.ps1
python -m pip install pandas==3.0.6

cd ~/projects/ticket-data
python3 -m venv .venv
source .venv/bin/activate
python -m pip install pandas==3.0.6

Successfully installed numpy-2.5.3 pandas-3.0.6 python-dateutil-2.9.0.post0 six-1.17.0

The prompt now starts with (.venv). From here on, the course writes python on every system, because activation made python point to the environment. q.py still works with it: it needs only the standard library.

Write the version that you chose in requirements.txt, by hand, so a new environment gets the same pandas:

pandas==3.0.6

Mei’s pandas profile reads the extract, data/extract/tickets.csv. That is the file that the cleaning step uses. Save it as scripts/profile.py:

"""Profile the extracted tickets. Print counts; change nothing."""

import pandas as pd

df = pd.read_csv("data/extract/tickets.csv")
print(f"Rows: {len(df)}")

missing = df.isna().sum()
print("\nMissing values (columns with at least one):")
print(missing[missing > 0])

print(f"\nExact duplicate rows: {df.duplicated().sum()}")
print(f"Rows with a ticket_id seen before: {df.duplicated(subset='ticket_id').sum()}")

print("\nChannel values:")
print(df["channel"].value_counts(dropna=False))

print("\nRanges:")
print(df[["order_value", "word_count"]].describe().round(2))
print(f"\norder_value = -1: {(df['order_value'] == -1).sum()}")
print(f"order_value > 2000: {(df['order_value'] > 2000).sum()}")
print(f"word_count > 10000: {(df['word_count'] > 10000).sum()}")

Code	What it does	Same as in SQL
`pd.read_csv(...)`	Reads the CSV file into a DataFrame.	the table
`df.isna().sum()`	Counts the missing values in each column.	`COUNT(*) - COUNT(col)`
`df.duplicated()`	Marks each row that is an exact copy of an earlier row. `.sum()` counts them.	`COUNT()` minus `SELECT DISTINCT `
`df.duplicated(subset='ticket_id')`	The same, but compares only `ticket_id`.	`COUNT(*) - COUNT(DISTINCT ticket_id)`
`value_counts(dropna=False)`	Counts each value, and includes the missing values.	`GROUP BY channel`
`describe()`	Count, mean, standard deviation, minimum, quartiles and maximum.	`MIN`, `MAX`, `AVG`

python scripts/profile.py

Rows: 1416

Missing values (columns with at least one):
customer_id     12
channel         38
order_value    188
closed_at       12
segment         21
region         139
joined_on       21
dtype: int64

Exact duplicate rows: 14
Rows with a ticket_id seen before: 14

Channel values:
channel
email       541
chat        413
web_form    199
phone       185
NaN          38
Email        20
 chat         8
EMAIL         7
webform       5
Name: count, dtype: int64

Ranges:
       order_value  word_count
count      1228.00     1416.00
mean        272.09      142.13
std        1262.66     1280.38
min          -1.00        0.00
25%          50.28       59.00
50%          88.79       91.00
75%         164.89      134.25
max       22394.00    48210.00

order_value = -1: 11
order_value > 2000: 24
word_count > 10000: 1

Compare it with the SQL profile. Count everything includes the differences:

1,416 rows, not 1,417: the extract left out the 2036 ticket. That ticket was a web_form ticket with 34,518 cents, so web_form has 199 rows here, not 200, and order_value > 2000 gives 24, not 25.
pandas shows a missing value as NaN (“not a number”).
segment, region and joined_on come from the customer join in the extract. They are missing for 21 rows: the 12 guest tickets and the 9 tickets whose customer is not in the customer table. region has 118 more missing values, because 9 customers have no region.
The mean order_value is 272.09 dollars, but the median (50%) is 88.79. The cents and the -1 values pull the mean. Do not report this mean.

Guided practice: write the profile

The profile is a document, not only output in a terminal. Priya, the data scientist, reads it before she trains a model. Grace checks the meanings.

Make the folder reports in your project, if it does not exist.
Make the file reports/profile.md.
Write the source at the top: the file, its row count and the snapshot date 2026-07-01 06:00.
Add one section for each of the five questions. In each section, write the count and the query or code that gives it.
For each missing value and each strange value, write what it means. If you do not know, write the question for Grace.
Add a last section, “Not decided yet”. List each problem without a fix.
Run python scripts/profile.py again, and compare every number in your profile with the output.

Show an example profile

# Profile: Larkfield tickets

Source: data/extract/tickets.csv, 1,416 rows (larkfield.db has 1,417;
the extract leaves out 1 ticket created in 2036). Snapshot 2026-07-01 06:00.
Made with queries/41-profile-missing.sql, 42-profile-ranges.sql
and scripts/profile.py. Nothing changed.

## Missing values
| Column      | Rows | Meaning (checked with Grace)                     |
|-------------|------|--------------------------------------------------|
| customer_id | 12   | guest checkout, no account                       |
| channel     | 38   | not recorded: unknown                            |
| order_value | 188  | all account tickets: there is no order           |
| order_value | 11   | value -1: the order system could not find it     |
| closed_at   | 12   | ticket still open                                |
| segment, region, joined_on | 21 | no customer row (12 guests, 9 deleted) |
| region      | 118 more | the customer gave no region                  |

## Duplicates
14 rows are exact copies (export page overlap). Same count by ticket_id,
so no ticket has two different rows. 1,402 rows stay after removing them.

## Categories
channel: 40 rows use other spellings (Email 20, " chat" 8, EMAIL 7, webform 5).
segment (customers): Trade 3 next to trade 47.

## Units
order_value: web form tickets before 2026-02-01 are in cents (24 rows:
23 "web_form", 1 "webform"; January average 7,957.91). From February the
web form sends dollars.

## Ranges
word_count: 0 in 7 rows (attachment only: valid); 48,210 in T-10361 (log file).
created_at: 3 tickets before the customer's joined_on date
(T-11185, T-11224, T-11375). Which date is wrong is not known.

## Not decided yet
- What to do with the 3 tickets created before the customer joined.
- What to do with T-10361.
- How to treat the 11 unknown order values in the model.

Your turn: profile the outcome events

Profile the outcomes table with the same five questions. Use the browser box above (change the query) or q.py. Predict first: does outcomes have exact duplicate rows?

Show the expected profile

Rows: 1,874.
Missingness: resolution_code is NULL in 341 rows. It is set only for resolved events (1,533 of 1,533 have one; escalated and reopened have none), so NULL means “not resolved yet in this event”. csat is NULL in 959 rows; only resolved events have it (915 values). A customer does not always answer the survey.
Duplicates: no exact duplicates (1,874 distinct rows, 1,874 distinct outcome_id). But SELECT DISTINCT ticket_id, recorded_at, status gives 1,844 rows: 30 events were logged twice with a new outcome_id. The key is not the ID here. A duplicate check must use the columns that say what the event is.
Orphans: 4 events belong to tickets that are not in tickets.
Categories: status has 3 values, resolution_code has 4 plus NULL. All are allowed.
Ranges: csat is 1 to 5. recorded_at runs to 2026-07-01 05:41:45, before the snapshot. One event (O-00193) is earlier than its ticket’s created_at: it belongs to the 2036 ticket, T-10147, so the wrong date is the ticket’s.

Common mistake

The profile says 188 missing order values. There are 199.

Symptom: the profile counts missing values with df.isna().sum() or COUNT(*) - COUNT(order_value), and reports 188. The mean order value looks a little low, but nothing fails.

Cause: the 11 -1 values are sentinels. For SQL and pandas they are normal numbers. A NULL count does not see them, and AVG and mean() use them as minus one dollar.

Fix:

Look at the smallest values and the most common values of every number column, not only the NULL count.
Look for typical sentinels: -1, 0, 999, 9999, and texts such as N/A, none or unknown.
Ask the owner of the data what each one means, and write the answer in the profile.

In AI work: a model trained on -1 learns that some orders cost minus one dollar. Worse, if “unknown order” is related to escalation, the model links escalation to a very small order, which is not true.

Check your understanding

The tickets table has 1,417 rows and 1,403 distinct ticket IDs. SELECT DISTINCT * also gives 1,403 rows. What does this tell you about the duplicates? What would it mean if SELECT DISTINCT * gave 1,410 rows?

Show the answer

All 14 extra rows are exact copies, so you can remove them and lose nothing. With 1,410 distinct rows, 7 of the repeated IDs would have rows that differ in at least one column. Then some tickets have two versions, and you must find out which version is correct before you remove one.

Check your understanding

order_value is NULL in 188 rows and -1 in 11 rows. Do these 199 rows mean the same thing?

Show the answer

No. The 188 NULLs are exactly the account tickets: there is no order, so there is no value to find. The 11 -1 values are orders that exist, but the order system could not find the amount: the value is unknown. A profile must keep these apart, because the correct treatment is different.

Check your understanding

Why does the profile change nothing, even when a fix is obvious, such as respelling Email to email?

Show the answer

The profile is the “before” picture. If you fix values while you profile, you cannot count later what the cleaning step changed. Also, the cleaning is then not in one reproducible script. And some obvious fixes are wrong: the meaning of a value can need the business owner.

Recap

A profile asks five questions: missingness, duplicates, invalid categories, inconsistent units and suspicious ranges. It changes nothing.
A count is not enough. Write what each missing value means: here, NULL order_value means “no order”, and -1 means “unknown”.
Compare COUNT(DISTINCT key) with SELECT DISTINCT * to tell exact duplicates from repeated keys.
Find mixed units by comparing groups, for example the average per month and channel.
The extract has 1,416 rows; the profile in reports/profile.md explains each problem with a count.

Next, you load the extract in pandas with the correct type for every column, so that pandas does not guess.

Next: Transform with Python