Before You Build: Reading What Your Data Is Telling You

Module 5, Unit 1 | Lesson 1 of 3

By the end of this lesson, you will be able to:

• Profile a real-world CSV dataset using Python and pandas to identify structural, completeness and quality issues (K11, S9)
• Distinguish between missing values, inconsistent values and invalid values, and explain why each needs a different treatment (K11, S10)
• Produce a structured data profile document that becomes the first artefact in your pipeline's audit trail (K11, S9, B6)
• Connect your profiling findings to design decisions you will make in L1.2 and throughout the module (S10, B6)

In Module 3 you built a Commit Log Agent — a Python script that reads a git log, sends it to an LLM, and gets back a structured summary. That agent worked well for a single run. In Module 5 you are going to turn it into something much more powerful: a production-grade data pipeline that ingests messy real-world data, cleans it, routes it through agentic logic, and produces a traceable, auditable output.

Before you write a single line of pipeline code, you need to understand exactly what you are working with.

Data profiling is the process of examining a dataset to understand its structure, content, completeness and quality. It is the difference between building a pipeline that handles reality and building one that breaks the moment a date arrives in the wrong format.

🔑 Key term — Data profile: A structured document that describes what a dataset contains, how complete it is, where it deviates from expected formats, and what quality issues will need to be addressed before the data can be safely processed.

How to work through this module. The AI Tool Usage Log you are about to meet is a demonstration case, picked because it is small enough — thirty rows, fourteen columns, and one clean instance of nearly every data problem a real pipeline meets. It is not a project you need to deliver. Follow the build at whatever depth is useful: focus on the considerations and reasoning. Several lessons end with an optional prompt to take one of those decisions back to your own organisation. If you are short of time, those are the ones worth it: they are the raw material for the narrative you write in L4.3.

You never have to type the code to keep up. Every lesson in this module ends with a Download the code button holding the project exactly as that lesson leaves it — every file, the dataset, and a README telling you how to run it. Typing the code out is still the better way to learn it, and the downloads exist so that a typo, a lost afternoon or a machine you cannot install on never costs you the lesson.

The dataset: AI Tool Usage Log

Your organisation has been logging every time a learner uses an AI tool. The data lives in a CSV file — ai_tool_usage_log.csv — collected from a browser extension that records usage events across the cohort.

Download it directly from the module resources:

Download ai_tool_usage_log.csv

Place the file in your module6_pipeline/ working directory.

The file has 14 columns and 30 rows. Here is what each column is supposed to contain:

Coach Cora

Notice that quality_score is described as "optional" — that is a red flag before you even open the file. Optional fields in real-world CSVs almost always have missing values, and your pipeline needs to decide upfront: is a missing quality score a valid state, or a data error? Write your answer down before you start profiling — then check it against the bottom of this lesson, where the profile forces the decision, and against L1.2, where that decision becomes a line of schema you have to live with.

Set up your environment

Create a new folder for your Module 5 work, and — before installing anything — create a virtual environment inside it:

mkdir module6_pipeline
cd module6_pipeline

python -m venv .venv

# macOS / Linux
source .venv/bin/activate
# Windows (PowerShell)
.venv\Scripts\Activate.ps1

Your prompt should now show (.venv). Everything you install from here lands in this project, not in your system Python. Now record the module's dependencies in a requirements.txt — you will add to it as the module progresses, and it is part of your final submission:

pandas
pydantic>=2
openai>=3
requests
python-dotenv
tiktoken

pip install -r requirements.txt

🔑 Key term — Virtual environment: An isolated Python installation scoped to a single project. Without one, every project on your machine shares the same packages, so upgrading a library for one project can silently break another — and your colleague cannot reproduce your results because their versions differ. A .venv plus a requirements.txt is the minimum bar for a project anyone else has to run. If you ever see advice to pip install --break-system-packages, that is a workaround for not having done this; do this instead.

Add a .gitignore in the same folder now, before you write any code — you will be storing an API key from Unit 3 onwards:

.venv/
.env
__pycache__/
logs/
.classifier_cache.json

Copy ai_tool_usage_log.csv into your working directory. Create a new Python script called profile.py — this is where all your profiling code will live.

Load and inspect the dataset

Start with the basics: shape, column names, and inferred data types.

import pandas as pd

df = pd.read_csv("ai_tool_usage_log.csv")

print("Shape:", df.shape)
print("\nColumns:", df.columns.tolist())
print("\nData types:\n", df.dtypes)

Expected output:

Shape: (30, 14)

Columns: ['log_id', 'date', 'learner_id', 'tool_name', 'task_type', 'model_used',
          'prompt_tokens', 'completion_tokens', 'response_time_ms', 'quality_score',
          'time_saved_mins', 'flagged_issue', 'human_reviewed', 'cost_usd']

Data types:
 log_id                object
 date                  object
 learner_id            object
 tool_name             object
 task_type             object
 model_used            object
 prompt_tokens          int64
 completion_tokens      int64
 response_time_ms       int64
 quality_score        float64
 time_saved_mins        int64
 flagged_issue         object
 human_reviewed        object
 cost_usd             float64

The first finding is immediate: date has type object. That means pandas could not infer a consistent date format — it loaded the entire column as plain strings. Something is inconsistent in that column.

Curious Cat

Why does quality_score come back as float64 when scores should be whole numbers between 1 and 5? Because pandas uses float64 for any numeric column that contains NaN values — integer columns cannot hold nulls in pandas. The dtype itself is a signal: float64 on a column that should be integer means there are missing values in there.

Check for missing values

print("\nMissing values per column:")
print(df.isnull().sum())

print(f"\nTotal missing: {df.isnull().sum().sum()}")
print(f"Completeness: {(1 - df.isnull().sum().sum() / df.size) * 100:.1f}%")

Expected output:

Missing values per column:
log_id               0
date                 0
learner_id           0
tool_name            0
task_type            0
model_used           0
prompt_tokens        0
completion_tokens    0
response_time_ms     0
quality_score        6
time_saved_mins      0
flagged_issue        0
human_reviewed       0
cost_usd             0
dtype: int64

Total missing: 6
Completeness: 98.6%

Six missing quality_score values out of 30 rows — that is 20% of that column missing, and your pipeline cannot silently ignore it. So here is the answer to the question Coach Cora asked you at the top: in this module a missing quality score is a valid state, not a data error. A learner who never rated an interaction is a fact about the world, not a corrupted row, and nothing downstream needs that score to route the record correctly.

In L1.2 that decision becomes a single line of schema — quality_score: Optional[float] = None — and the validator built around it accepts an absent value while rejecting a present one outside 1.0–5.0. Absence is allowed; a nonsense score is not.

It is worth pricing the alternative before dismissing it. Treat absence as a validation failure instead, and six of your thirty records fail — 20%, double the 10% error threshold you will build in L1.3. The pipeline would halt on a dataset with nothing actually wrong with it. A decision this small, taken in the wrong place, is the difference between a pipeline that runs and one that stops every night.

🔑 Key term — Null value: A missing or absent value in a dataset. Nulls are different from zero or empty string — they represent the absence of any data. In pandas, nulls appear as NaN (Not a Number) regardless of column type. They must be handled explicitly; most operations silently skip them, which can produce misleading results.

Spot inconsistent values

Completeness is only part of the picture. Values can be present and still be wrong — or present in multiple incompatible forms.

Tool names

print("\nUnique tool names:")
print(df["tool_name"].value_counts())

Expected output:

tool_name
GPT-4            7
Gemini Pro       5
ChatGPT-4        3
Claude3          3
Claude 3         2
Claude 3 Opus    2
gpt4             2
GPT4             2
gpt-4-turbo      2
gemini-pro       1
ChatGPT 4        1
Name: count, dtype: int64

Eleven distinct values — but there are really only three tools: GPT-4, Gemini Pro, and Claude 3. The same tool has been entered as GPT-4, gpt4, GPT4, ChatGPT-4, ChatGPT 4, and gpt-4-turbo. A downstream report grouping by tool_name would silently split one tool into six categories and produce completely wrong numbers.

Date formats

print("\nSample date values:")
print(df["date"].head(10).tolist())

Expected output:

['2024-01-08', '08/01/2024', '2024-01-08', '2024-01-09', '09-01-2024',
 '2024-01-09', '2024-01-10', '2024-01-10', '10/01/2024', '2024-01-10']

Three formats in 10 rows: ISO (2024-01-08), day-first slash (08/01/2024), and day-first hyphen (09-01-2024). If you call pd.to_datetime() without specifying a format, pandas will guess — and it may silently swap day and month, turning the 8th of January into the 1st of August.

Boolean values

print("\nUnique human_reviewed values:")
print(df["human_reviewed"].value_counts())

Expected output:

human_reviewed
Yes    10
No      8
no      6
YES     5
NO      1
Name: count, dtype: int64

Five representations of a two-value field. Any code that checks if row["human_reviewed"] == "Yes": will silently miss YES records — 33% of the positive cases invisible, with no error and no warning.

Numeric anomalies

print("\ntime_saved_mins statistics:")
print(df["time_saved_mins"].describe())

print("\nRows with negative time_saved_mins:")
print(df[df["time_saved_mins"] < 0][["log_id", "time_saved_mins", "flagged_issue"]])

Expected output:

time_saved_mins statistics:
count    30.000000
mean     17.400000
std      11.409615
min     -12.000000
25%      10.250000
50%      15.500000
75%      26.500000
max      38.000000
Name: time_saved_mins, dtype: float64

Rows with negative time_saved_mins:
     log_id  time_saved_mins  flagged_issue
7   LOG-008               -5  hallucination
17  LOG-018              -12  hallucination

Negative time saved is not necessarily a data error — a learner might report that using an AI tool cost them time rather than saved it. But your pipeline needs an explicit rule about how to handle this. It is also worth noting that both negative entries coincide with hallucination flags.

Coach Cora

Patterns like this — negative time saved always appearing alongside hallucination flags — are exactly the kind of discovery that only happens during profiling. By the time you are in production, these correlations are buried in noise. Write them down now, in your profile document. When something goes wrong later, you will be glad you did.

Write your profile summary

Good profiling ends with a document — not just console output you scroll past once. Add this to profile.py:

import json

profile = {
    "row_count": len(df),
    "column_count": len(df.columns),
    "missing_values": df.isnull().sum().to_dict(),
    "tool_name_variants": df["tool_name"].nunique(),
    "tool_name_all_values": df["tool_name"].unique().tolist(),
    "date_formats_detected": [
        "ISO (YYYY-MM-DD)",
        "day-first slash (DD/MM/YYYY)",
        "day-first hyphen (DD-MM-YYYY)"
    ],
    "human_reviewed_variants": df["human_reviewed"].unique().tolist(),
    "negative_time_saved_rows": df[df["time_saved_mins"] < 0]["log_id"].tolist(),
    "flagged_issues_breakdown": df["flagged_issue"].value_counts().to_dict(),
}

with open("data_profile.json", "w") as f:
    json.dump(profile, f, indent=2)

print("\nProfile saved to data_profile.json")

This file is your pipeline's first artefact — the documented starting point that every subsequent decision traces back to. Every cleaning rule you write in L1.2, every routing decision you make in Unit 3, should connect to something you found here.

Build activity

Run a full profile of ai_tool_usage_log.csv and produce a data_profile.json that answers all of the following:

1. How many rows and columns does the dataset have?
2. Which columns have missing values, and how many?
3. How many distinct values appear in tool_name? List them all.
4. What date formats are present in the date column?
5. What are all the unique values in human_reviewed?
6. Which rows have a time_saved_mins value below zero?
7. What is the breakdown of flagged_issue values?

Keep profile.py and data_profile.json. You will use them directly in L1.2.

Challenge Chase

Your profile tells you what is wrong, but not how bad it is. Extend your data_profile.json with a severity rating for each finding: High (will break the pipeline if not fixed), Medium (will silently corrupt results), or Low (cosmetic, tolerable). Which issues are High severity, and what specifically breaks if you leave them unfixed?

The code from this lesson

This is the profiling script and the dataset, as the lesson leaves it. The code files are assembled from the code blocks above, so they match what you were asked to write; the dataset and a short README come with them.

Take it if you would rather read and run the code than type it out. Typing it is still the better way to learn it — this is here so a typo cannot cost you the lesson.

Checklist

• I have installed pandas and can load ai_tool_usage_log.csv without errors
• My profile script identifies all 6 missing quality_score values
• I have listed all 11 tool_name variants and can explain which real tools they represent
• I have identified the three date formats present in the dataset
• I have found the two rows with negative time_saved_mins and noted their flagged_issue values
• I have saved a complete data_profile.json with all findings
• I can explain in plain English what each quality issue will do to a pipeline that doesn't handle it

Quick Check

KSB evidence focus

• K11 — Understands relevant data governance, data privacy and security issues. The pii_detected flag in your dataset is not academic — it represents a real governance boundary. Your profile has identified which records crossed that boundary. In L1.2, you will write the validation rules that enforce it programmatically.

• S9 — Can apply appropriate data analysis techniques and visualise the results. Data profiling is applied data analysis. The techniques here — completeness checks, value distribution, format detection, anomaly identification — are the same techniques used in production data engineering. Your data_profile.json is the output of that analysis.

• S10 — Can carry out data processing tasks, including processing/cleaning, data transformations, and feature engineering. You have not cleaned anything yet — that comes in L1.2. But you have completed the prerequisite: a rigorous, documented understanding of what needs to be cleaned and why. Skipping this step is how pipelines ship with silent data corruption.

• B6 — Shows curiosity and initiative. You noticed the correlation between negative time_saved_mins and hallucination flags. That observation was not prompted. It is exactly the kind of pattern-noticing that separates a developer who profiles carefully from one who just runs the pipeline and hopes for the best.

Up next: Lesson 2 takes everything you found here and turns it into enforcement. You will write a Pydantic schema that validates every incoming record, normalises the messy fields, and produces clean, typed data — ready for your pipeline to process with confidence.