Unit 10 • Lesson 6

The Whole Analysis

Overview

Load, clean, join, summarise, report — in one function, from files.

Intermediate 20–25 min

← Previous Unit 10 test

What You Will Learn in This Lesson

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

Put the steps from this course in the order that keeps each one correct.
Clean types and missing values before joining, not after.
Check row counts between steps, so a silent loss shows up.
Return a result a reader can use, from two raw files.

Why This Matters

Every unit so far taught one step on its own, on data that arrived ready for it. Real work is the chain, and the chain fails at the joints: an id kept as "007" in one file meets "7" in another and matches nothing, a missing marker survives into a mean. Doing the whole thing once, with checks between the steps, is what turns the separate skills into an analysis.

Step 1: The order matters

The steps are the ones you already know. What is new is that each one depends on the one before it being right.

Read, then look

Read each file and check its shape and dtypes before doing anything else. A score column that comes back as object is the first sign of a missing marker, and it is far cheaper to notice here than after a join.

import io
import pandas as pd

scores = pd.read_csv(io.StringIO("id,score\n1,90\n2,n/a\n3,70\n"))
print(scores.shape)
print(scores.dtypes)

Clean before you join

Convert types and deal with missing values on each table while it is still small and on its own. After a merge the bad rows are mixed in with good ones from the other file, and it is much harder to say which table they came from.

import io
import pandas as pd

scores = pd.read_csv(io.StringIO("id,score\n1,90\n2,n/a\n3,70\n"))
scores["score"] = pd.to_numeric(scores["score"], errors="coerce")
print(scores.dtypes)
print(int(scores["score"].isna().sum()), "missing")

Then join, then summarise

Merge on the key, group, and take the summary. Rounding comes last, at the moment the number is shown, so no step downstream computes with a rounded value.

import io
import pandas as pd

people = pd.read_csv(io.StringIO("id,team\n1,red\n2,red\n3,blue\n"))
scores = pd.read_csv(io.StringIO("id,score\n1,90\n2,80\n3,70\n"))
out = pd.merge(people, scores, on="id")
print(out.groupby("team")["score"].mean().round(1))

Mini Practice #1: Spot the missing marker

Try It Yourself

Run this and look at the dtype. Then add na_values=["n/a"] to read_csv and run it again. What changed, and why would the first version have broken a mean?

import io
import pandas as pd

text = "id,score\n1,90\n2,n/a\n3,70\n"
scores = pd.read_csv(io.StringIO(text))
print(scores.dtypes)

Press Run to see output

Step 2: Check between the steps

Each step can lose rows without raising anything. A check is one line that prints what you expect to be true.

How much did cleaning drop?

Count before and after. If a third of the rows vanished at the cleaning step, that belongs in the write-up, not in a surprise later.

import pandas as pd

df = pd.DataFrame({"id": [1, 2, 3], "score": ["90", "n/a", "70"]})
df["score"] = pd.to_numeric(df["score"], errors="coerce")
kept = df.dropna(subset=["score"])
print(len(df) - len(kept), "dropped of", len(df))

Did the join keep what it should?

An inner join drops every row whose key has no partner. Compare the row count after the merge with the table you expected it to match.

import pandas as pd

people = pd.DataFrame({"id": [1, 2, 3], "team": ["red", "red", "blue"]})
scores = pd.DataFrame({"id": [1, 2, 4], "score": [90.0, 80.0, 70.0]})
out = pd.merge(people, scores, on="id")
print("people:", len(people), "joined:", len(out))

Key idea: The checks are not the analysis, and they do not need to stay in the final function as prints. What matters is that you ran them and know the answers, because "how many rows did that rest on" is the first question anyone asks.

Mini Practice #2: Find the lost row

Try It Yourself

The join below loses a row. Print which id from people has no score, using isin.

import pandas as pd

people = pd.DataFrame({"id": [1, 2, 3], "team": ["red", "red", "blue"]})
scores = pd.DataFrame({"id": [1, 3], "score": [90.0, 70.0]})

out = pd.merge(people, scores, on="id")
print(len(people), "->", len(out))
# Which ids in people are not in scores?

Press Run to see output

Step 3: One function, two files

Wrapping the chain in a function with the file paths as arguments makes it rerunnable, which is the point: next term's files should go through the same steps without anybody copying code.

Paths in, answer out

The function reads, cleans, joins and summarises, and returns plain Python values. A caller does not need to know pandas was involved.

import pandas as pd

open("people.csv", "w").write("id,team\n1,red\n2,blue\n")
open("scores.csv", "w").write("id,score\n1,90\n2,70\n")

def best_team(people_path, scores_path):
    people = pd.read_csv(people_path)
    scores = pd.read_csv(scores_path)
    means = pd.merge(people, scores, on="id").groupby("team")["score"].mean()
    return str(means.idxmax())

print(best_team("people.csv", "scores.csv"))

The same function on different data

Run it again on a second pair of files. If the answer changes when the data does and nothing else had to change, the analysis is finished.

import pandas as pd

def best_team(people_path, scores_path):
    people = pd.read_csv(people_path)
    scores = pd.read_csv(scores_path)
    means = pd.merge(people, scores, on="id").groupby("team")["score"].mean()
    return str(means.idxmax())

open("people.csv", "w").write("id,team\n1,red\n2,blue\n")
open("scores.csv", "w").write("id,score\n1,40\n2,95\n")
print(best_team("people.csv", "scores.csv"))

Step 4: When to Use It

A question that needs several files

Read, clean each table, join, summarise, report — in that order.

report("people.csv", "scores.csv")

Analyses you will rerun

A function over the file paths, with checks between the steps, handles next term’s data unchanged.

When Not to Use It

Do not skip the checks between steps because the final number looks plausible. Rows lost at a join or a clean are exactly what makes a plausible number wrong.

End-of-Lesson Exercises

Exercise 1: The best team, from two files

Two files are supplied. people.csv has id and team; scores.csv has id and score, where some scores are n/a. Write report(people_path, scores_path) returning (team, mean) for the highest-scoring team, ignoring rows with no score, with the mean to one decimal place.

Read both, convert the score with to_numeric(errors="coerce"), drop the missing rows, merge on id, group by team, take the mean, and pick the highest with idxmax.

import pandas as pd

def report(people_path, scores_path):
    # (best team, its mean score)
    return None

print(report("people.csv", "scores.csv"))

Press Run to try it, then Check my work to mark it.

Exercise 2: The checks between the steps

Same two files. Write audit(people_path, scores_path) returning a dict with three counts: "missing", the scores that were n/a; "unmatched", the people whose id does not appear in scores.csv at all; and "used", the rows left after dropping missing scores and joining.

missing: to_numeric(errors="coerce") then isna().sum(). unmatched: ~people["id"].isin(scores["id"]), summed. used: len of the merge after dropna. Wrap each count in int() so the dict holds plain numbers.

import pandas as pd

def audit(people_path, scores_path):
    # {"missing": ..., "unmatched": ..., "used": ...}
    return {}

print(audit("people.csv", "scores.csv"))

Press Run to try it, then Check my work to mark it.

← Prev Unit 10 test