Lesson 73 taught you pandas and numpy. This lab is where you clean a real file. You get a five-row health check file with one blank latency, you fill the blank with the median, you flag the slow services, and you write the unhealthy rows to a new file. By the end you have unhealthy.csv with exactly two rows, and you know why the median is the honest fill, not the mean.
Difficulty: Beginner · Know this first: Lesson 73 — Python; Python 3.12 with pandas and numpy installed
Every hop charges rent in milliseconds. The bar below is a worked example for this lesson's scenario — not a measurement from a live system — but the shape is honest: on a cache hit the trip is almost free, on a miss the kitchen eats the budget. Step through the lesson or switch the scenario and watch the bar change.
Extra diagram · from scratch to master
Start with five rows and one blank. Add one box at a time until unhealthy.csv holds exactly two rows.
The budget bar above shows three fixed stories. This simulator lets you write your own. Move a slider and watch the cleaning, the grouping, and the total change in the budget bar, in the hop timings, and in the total. The numbers are a teaching example, not live measurements. The shape is the lesson: more rows means more grouping, and a wrong fill costs a rerun.
How many rows the file holds. Every row is read, cleaned, and grouped, so more rows means a longer grouping hop.
How many blank latencies need filling. Each blank is filled with the median, and each one has to be checked.
How many services are slower than 500 ms. More slow services means more rows in unhealthy.csv, and more to explain.
Try this: (1) Set rows to 5, blanks to 1, slow to 1. The clean is about 1 min, the group about 1 min, the write about 1 min, and the total is about 8 min. (2) Set all three to the worst end: 500 rows, 50 blanks, 50 slow. The clean is about 13 min, the group about 6 min, the write about 6 min, and the total is about 30 min. Same script. Only the inputs changed.
Three questions. No score is saved anywhere — this is just you against the material.
Here is the cleaning script from this lab, ready to edit. Change it, then run the check. The check looks for the four things a working clean needs: read_csv, a median fill, a slow flag at 500 ms, and a write to unhealthy.csv.
Try breaking it on purpose: change median to mean, or change 500 to 5000. Run the check and watch it fail. Then press Reset and try a change that keeps it passing, like adding a latency_s column.
Expected output when it passes: The script reads the file, fills the blank with the median, flags slow over 500 ms, and writes unhealthy.csv with exactly two rows.
I take the same file four times here. First I read it. Then I fill it. Then I group it. Last I write the answer out.
Flags in the next block, letter by letter. I read every flag out loud before I run a command I copied. A flag I cannot explain is a flag I do not run yet.
wc -l checks.csv counts the lines. The -l means lines. The answer should be 6, one header row and five data rows. I run it before I trust any answer from the file, because a count built on a short file is a wrong count.python3 clean_checks.py runs the cleaning script. No flags. The file name is the argument, not a flag.python3 -c "import pandas as pd; df = pd.read_csv('checks.csv'); print(df.to_string(index=False))" runs one line of Python from the command line. The -c means command string. I use it to peek at a file without writing a script for it.python3 -m py_compile clean_checks.py checks the syntax without running the code. The -m means run a library module as a script. I use it to catch a typo before I run the real thing.python3 --version prints the version. I run it first on a new box, because the version decides what the code can use.pip install pandas numpy installs the two libraries this lab needs. No flags here. I run it once per box, before the lab, and I check the install with python3 -c "import pandas, numpy" after it.pip install -r requirements.txt is the form I use when the list of libraries lives in a file. The -r means read the names from the file. Without it, pip would try to install a package literally called requirements.txt.pip install --upgrade pandas is the form I use when a library is already there and I want the newest version. The --upgrade means replace what is installed. Without it, pip sees the package is already there and does nothing.Check it: run python3 -c "import pandas, numpy; print(pandas.__version__, numpy.__version__)". Both versions should print. If the import fails, run the pip install line first.
Definition: pandas read_csv turns a CSV file into a table called a DataFrame. The header row becomes the column names. A missing value shows as NaN, which is not zero and not an error. It is a value that is not there.
Types:
Uses: I use this for any new file: read it, print it, count it. Try it now: print checks.csv and point at the NaN.
Dimensions: At low volume everything is instant. What breaks first is the file path: a script run from the wrong folder cannot find its file.
Flags in the next block, letter by letter. I read every flag out loud before I run a command I copied. A flag I cannot explain is a flag I do not run yet.
cat unhealthy.csv prints the file the script wrote. No flags. I read it back after writing it, because a file I never reread is a file I never checked.wc -l unhealthy.csv counts the lines. The -l is the lines flag. The answer should be 3, one header row and two bad rows. If it is not 3, the filter picked the wrong rows.Check it: run cat unhealthy.csv and wc -l unhealthy.csv. The file should hold two bad rows and the count should be 3.
Definition: fillna replaces missing values. fillna(median) fills with the middle value. fillna(mean) fills with the average. The median is honest when one value is far from the rest, because the middle does not move when one value moves.
Types:
Uses: I use fillna(median) when the data has an outlier, and I say so in the report. Try it now: compute the median and the mean of the four latencies by hand and check they are 230 and 418.8.
Dimensions: As the file grows, the fill stays one line. What breaks is a fill nobody wrote down: a report that says 230 without saying median is a number nobody can reproduce.
Flags in the next block, letter by letter. I read every flag out loud before I run a command I copied. A flag I cannot explain is a flag I do not run yet.
python3 clean_checks.py runs the script again after a change. No flags. The output should be the same every time, because the script reads the same file and does the same math. A script whose answer changes between runs is a script I do not trust yet.Check it: run the script twice and compare the outputs. They should match line for line.
Definition: groupby splits the table by a column and summarises each group. A boolean column marks rows that meet a rule, like slower than 500 ms. The flag is a column, not a filter, so the row stays and the judgement is visible.
Types:
Uses: I use groupby for the summary and flags for the judgement. Try it now: group the lab file by status and check the ok mean is 185.0.
Dimensions: At ten times the rows, groupby still works, and the time grows with the rows. What breaks is a group with one row: its mean is just that row, and it looks like a fact about a group.
Flags in the next block, letter by letter. I read every flag out loud before I run a command I copied. A flag I cannot explain is a flag I do not run yet.
python3 clean_checks.py runs the whole pipeline one more time. No flags. The mean and the p95 together are the answer, and the counts per status are the proof.Check it: run the script and read the mean and the p95. Then change one latency in the file, run it again, and watch both numbers move.
Definition: At this level the answer is not one number. It is the mean and the p95 together, the counts per group, and the output file with exactly the right rows. A report that holds one number hides the tail, and a tail is where the incidents live.
Types:
Uses: I use this when the answer leaves my screen: a ticket, a handover, a review. Try it now: write unhealthy.csv and check it holds exactly two rows.
Dimensions: At a hundred times the rows, the report is the same shape, and the script is the same. What breaks is trust: a report nobody can reproduce from the file is a guess with formatting.
Flags in the next block, letter by letter. I read every flag out loud before I run a command I copied. A flag I cannot explain is a flag I do not run yet.
mkdir -p ~/lab-python && cd ~/lab-python makes the lab folder and moves into it. The -p means make the parents too, and do not complain if the folder already exists. The && means run the next command only if this one worked.wc -l checks.csv counts the lines. The -l is the lines flag. The answer should be 6.cat unhealthy.csv prints the bad rows the script wrote. No flags. I read it to check the filter picked the right rows.Check it: run wc -l checks.csv and see 6. Then run the script and wc -l unhealthy.csv and see 3.
The data file, all five rows. Do this: save it as checks.csv in your lab folder, then run wc -l checks.csv. Expected output: 6 checks.csv, five rows plus the header.
service,status,latency_ms
web,ok,120
api,ok,340
db,slow,1120
worker,down,
cache,ok,95
The cleaning script, the whole file. Do this: save it as clean_checks.py and run python3 clean_checks.py. Expected output: the worker latency is 230.0, the group means are ok 185.0, slow 1120.0, down 230.0, and the last lines read mean latency: 381.0 p95: 964.0 and unhealthy rows written: 2.
import pandas as pd, numpy as np
df = pd.read_csv("checks.csv")
df["latency_ms"] = df["latency_ms"].fillna(df["latency_ms"].median())
df["latency_s"] = df["latency_ms"] / 1000
df["slow"] = df["latency_ms"] > 500
print(df.to_string(index=False))
print(df.groupby("status")["latency_ms"].agg(["count", "mean"]).to_string())
print("mean latency:", round(df["latency_ms"].mean(), 1), "p95:", round(np.percentile(df["latency_ms"], 95), 1))
bad = df[df["slow"] | (df["status"] == "down")]
bad.to_csv("unhealthy.csv", index=False)
print("unhealthy rows written:", len(bad))
What the output file holds. Do this: run cat unhealthy.csv. Expected output: two rows, db and worker, with the same columns as the input plus latency_s and slow. The db row is slow at 1120 ms. The worker row is down, with its filled latency of 230.0 ms.
At 10x the rows, the shape holds and the numbers grow. At 100x, the design changes: the file becomes a table in a database, and the cleaning moves into a pipeline that runs on a schedule. The median never changes, though. One slow service still should not move every blank.
A weekly report said the average latency was 418 ms and rising. The team spent a week hunting a slowdown that did not exist. The report had filled every blank latency with the mean, and the mean was pulled up by one slow service. Every blank became 418 ms, so the average rose with every missing value, and the missing values were the ones the slow service never answered.
Two things. First, every fill is named in the report: median, mean, or dropped, with the count of filled values. Second, a fill is never chosen by default. The person writing the report picks it and writes down why. Try it now: change median to mean in your copy, rerun, and watch the worker latency move from 230.0 to 418.8.
Build a cleaning script for a new file you make yourself with ten rows, two blanks, and two slow services. It passes when it fills the blanks with the median, flags the slow rows over 500 ms, and writes an output file with exactly the unhealthy rows.
Success looks like this: the output file holds exactly the unhealthy rows, the filled values equal the median you computed by hand, and the group means match your hand calculation for one group. If the output holds too many rows, the filter is too loose. If the filled value equals the mean, the fill is wrong.
If read_csv cannot find the file, check the folder: the script and the file must be in the same place. If the median looks wrong, sort the values by hand and pick the middle. If groupby gives one row per group, the group column has a typo. If the output file is empty, the filter uses and where it should use or.