pandas tables

Outcome: inspect, normalize and validate a small table while preserving rejected rows. Prerequisite: Module 7. Profile: Data · NumPy and pandas. Time: 2–3 hours.

Rows, columns and labels

A DataFrame is a labelled table. A Series is one labelled column-like collection. frame["minutes"] selects a Series; frame[["topic", "minutes"]] selects a DataFrame. An index labels rows; it is not automatically a business identifier. After filtering, labels may have gaps. Use .loc for label- or Boolean-based selection and .iloc for positional selection; do not assume position 0 and label 0 always identify the same row.

Before changing data, inspect head(), shape, dtypes and missing-value counts. len(frame) counts all rows; frame["minutes"].count() counts non-missing values in that column, so they need not agree. Ask what one row represents, what fields mean, which keys should be unique and what values are allowed. A tool cannot infer all of these business rules from column names. For our simple session table one row is one recorded practice session, and minutes must be a whole non-negative count.

Inspect first, then decide

Run this five-row fixture before deciding what to clean. head(2) shows two source records, shape gives rows and columns, and dtypes describes the stored column types. Text types are expected because we preserved the source. The first sample reveals whitespace around Python and an empty duration; neither fact can be inferred from a column name alone.

# Profile: Data · NumPy and pandas. Inspect before deciding what to clean.
from io import StringIO
import pandas as pd

text = "topic,minutes\n Python ,20\ndata,\ncharts,unknown\n,10\ndata,-5\n"
raw = pd.read_csv(StringIO(text), dtype="string", keep_default_na=False)
print("Raw shape:", raw.shape)
print("First two source rows:\n" + raw.head(2).to_string())
print("Raw column types:\n" + raw.dtypes.to_string())
print("Raw missing counts:", raw.isna().sum().to_dict())
print("Empty source minute strings:", int(raw["minutes"].eq("").sum()))
clean = raw.copy()
clean["topic"] = clean["topic"].str.strip().str.lower()
clean["minutes"] = pd.to_numeric(clean["minutes"], errors="coerce")
print("Rows / numeric values:", len(clean), int(clean["minutes"].count()))
print("Converted missing minutes:", int(clean["minutes"].isna().sum()))
print("Negative minutes:", int(clean["minutes"].lt(0).sum()))
print("Empty topics:", int(clean["topic"].eq("").sum()))
valid = clean["topic"].ne("") & clean["minutes"].notna() & clean["minutes"].ge(0)
print("Valid mask:", valid.tolist())
print("Inverse mask:", (~valid).tolist())
print("Accepted source labels:", clean.loc[valid].index.tolist())
# A gapped index shows the distinction between labels and positions.
gapped = raw.loc[[0, 3]].copy()
print("Gapped labels:", gapped.index.tolist())
print("Second positional row equals label 3:", gapped.iloc[1].equals(gapped.loc[3]))

Download this worked example

The raw shape is (5, 2). Both columns are string columns. Raw missing counts are zero, yet one minute field is an empty string: keep_default_na=False preserved that string instead of automatically converting it to a missing marker. After numeric conversion, only three of five duration fields are numbers. Empty text and unknown become two missing numeric values; the negative value remains numeric but violates the policy.

There is also one empty topic. In this fixture those three failure sets do not overlap: two failed conversions, one negative duration and one empty topic account for four rejected rows, leaving label 0. In another fixture they may overlap, so adding per-rule counts does not automatically give a unique rejected-row count. Compute the combined mask.

The accepted mask is [True, False, False, False, False]. The ~ operator inverts each Boolean decision, giving [False, True, True, True, True]. Use the first on the working table and the inverse on the original table to retain rejection evidence. Both selections use the same source labels.

The final experiment selects labels 0 and 3. Its second positional row is .iloc[1], while the same row by source label is .loc[3]. .loc[1] would ask for a label that is absent. This distinction matters when a later reason refers back to a source row.

Before the cleaning exercise, complete exercises/08_inspection.py on its different five-row fixture. Write three observations and a next action for each before editing the data. Predict failed conversions, negative values and empty topics, then compare those predictions with the combined rejection mask. Do not merely paste the inspection method names.

Raw data is evidence

Read source fields as strings when you need to preserve their exact text for review. Our example uses dtype="string" and keep_default_na=False; this keeps tokens such as "unknown" and empty fields visible instead of interpreting every source token as a numeric value or default missing marker. Those options are deliberate for this fixture, not a universal ingestion rule.

Make a working copy. Trim and lowercase categories, then convert numeric fields with pd.to_numeric(..., errors="coerce"). Invalid numeric text becomes missing in the working column. That conversion does not decide what to do with the record. A Boolean validity rule separates accepted rows from rejected rows, while the rejection export retains the source values.

Open the diagram at full size

Read the diagram explanation

Preserve raw data, split exact duplicate extras, validate remaining records, retain rejections with reasons, and summarize accepted records only.

Worked example

# Profile: Data · NumPy and pandas
from io import StringIO
import pandas as pd

csv_text = "topic,minutes\n Python ,20\ndata,30\npython,25\ncharts,unknown\n"
raw = pd.read_csv(StringIO(csv_text), dtype="string", keep_default_na=False)
clean = raw.copy()
clean["topic"] = clean["topic"].str.strip().str.lower()
clean["minutes"] = pd.to_numeric(clean["minutes"], errors="coerce")
valid = clean["minutes"].notna() & clean["minutes"].ge(0)
accepted = clean.loc[valid].copy()
rejected = raw.loc[~valid].copy()
summary = accepted.groupby("topic", as_index=False)["minutes"].sum()
summary = summary.sort_values("topic")
print(summary.to_string(index=False))
print("Rejected rows:", len(rejected))
print("Accepted total:", int(accepted["minutes"].sum()))

Download this worked script

The summary has data/30 and python/45. One row is rejected, and accepted minutes total 75. The result describes accepted records only. It cannot tell you how much time the unknown charts session took. Replacing that value with zero would turn an unknown measurement into an invented one.

groupby("topic", as_index=False)["minutes"].sum() groups rows by topic and adds the values within each group. as_index=False keeps the group key as an ordinary column. .sort_values("topic") makes the display order explicit. Aggregation changes the grain: the result now has one row per topic instead of one row per session. Do not describe the number of summary rows as the number of practice sessions.

Build a stronger validity rule

The small worked example checks that minutes are present and non-negative. Your exercise adds two requirements: a nonempty normalized topic and whole-number minutes. The mask combines notna(), .ge(0) and .mod(1).eq(0) for the numeric field, plus .ne("") for the topic. The modulo condition checks that no fractional remainder remains; it does not silently truncate 1.5 to 1.

Use parentheses around comparisons and & or | to combine Series masks. Avoid chained assignment such as frame[mask]["minutes"] = 0. Instead, write a single .loc[mask, "minutes"] assignment to the intended frame, or work on an explicit .copy(). pandas' copy behavior has evolved across versions; the course uses explicit assignments that work in the tested local and browser versions without depending on ambiguous chained edits.

A missing record and a duplicate record are different issues. Exact duplicate removal should use an explicit policy. A repeated identifier with conflicting contents needs investigation rather than an arbitrary “keep first” choice. We address that in the capstone, together with count reconciliation.

Practice

• 8A: complete clean_session_table using the stronger rules above. The seven-row exercise fixture should yield three accepted and four rejected rows. Preserve original text in rejected records.
• 8B: group accepted minutes by normalized topic, sort the result and check data/30 and python/45. Reconcile seven input rows with three accepted plus four rejected.
• 8C: write one limitation and one question for the data owner. For example, ask whether missing durations can be recovered; do not assume they were zero. Explain whether it is safe to compare total learning between topics from this sample.

Exit check

What changed between parsing and validation? What is the grain before and after grouping? Why is count() sometimes different from row count? How could a missing-value default make a plausible but false summary? Explain how your reject list lets another person review the decision.

Further reading

• pandas: introductory table tutorials
• pandas: copy-on-write and assignment