Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Cleaning and Preprocessing with Python

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 3 — Prepare, Report and Reproduce

Period 13 · Building a Rerunnable Cleaning Workflow

Print / Save as PDF
Lecture video — Period 13: Building a Rerunnable Cleaning Workflow

Organising a notebook that reruns from the original data, keeping raw and processed files separate, and checking the outputs.

Lesson overview

In earlier periods, we cleaned columns and created useful variables. In this period, we connect those steps into one workflow. A rerunnable notebook starts with the original data and produces the same results when the input, rules, and software environment stay the same. Another person should be able to run it without asking which cell to execute first. We will organize the notebook, place repeated steps in functions, check the output, and export the results. Our main question is simple: can we explain and repeat every change between the original sales file and the final file?

Study notes

By the end of this lesson you can:

  • Organize a notebook so another person can run it from top to bottom.
  • Write small cleaning functions that take a DataFrame, work on a copy and return a result.
  • Validate outputs with assertions and know what to do when one fails.
  • Keep a processing log in which every input row is accounted for.
  • Check that a rerun gives the same results, then export and read the files back.

Key idea: A rerunnable notebook starts from the original data and produces the same results whenever the input, rules and software stay the same. Every change between the raw file and the final file must be explainable and repeatable.

Organizing the notebook

A clear notebook follows the order of the work:

  1. A short Markdown section: purpose, input file, business rules, and what one row represents (here, one transaction_id = one transaction for one product category).
  2. Imports and file paths near the top.
  3. Load the original data, inspect quality, apply cleaning rules.
  4. Checks, then exports.

Markdown explains why a step is needed; code shows how. Avoid hidden dependencies on cells that were run earlier but no longer appear in the notebook. Keep raw (the loaded data) separate from work (the working copy), and write results to a separate output folder, so the source file never changes.

Agreeing on rules and writing functions

Agree on the rules before cleaning. In the lecture's teaching example (ordinary sales): quantity must be positive, unit price may be zero but not negative, and discount must be between 0 and 1. A returns dataset would need different rules. Remove only complete duplicate rows; if two different rows share a transaction ID, keep them for review instead of choosing one without evidence. Missing required values need an explicit decision too.

Functions make steps reusable and show their inputs and outputs. The lecture's function receives a DataFrame, makes a copy, converts three columns with pd.to_numeric(..., errors="coerce") and returns the new DataFrame, which is assigned back to work. coerce makes conversion failures visible to later checks; it does not solve them. Give each function a narrow purpose and never change a global DataFrame inside a function. The course notebook builds its whole pipeline this way: load_raw, standardize_missing, remove_exact_duplicates, clean_labels, convert_types, flag_issues and run_pipeline.

Checking the output

Keep separate checks in the quality report: missing values in required fields (optional fields need not be complete), repeated transaction IDs with duplicated(subset=["transaction_id"], keep=False) after exact duplicates are removed, and a valid mask for numeric ranges. Domain checks support validation but do not prove accuracy; a price can be in range and still be wrong.

An assertion states a condition that must be true at that point; if it is false, Python raises AssertionError and stops. Check that clean is not empty, required values are present, IDs are unique and numeric domains hold, with a message after each condition. If an assertion fails, inspect the rows and fix the rule or the data with evidence; never delete the assertion to make the notebook finish. For production validation, raise explicit exceptions, because optimized Python (python -O) can disable assert.

Logging, rerunning and exporting

A processing log explains what changed. In the six-row teaching example: one exact duplicate is removed, two of the remaining five rows go to review, and three are accepted, so 6 = 1 + 2 + 3. The groups must not overlap; a row with two problems is still one rejected row with two reasons. Corrected values are recorded separately, because a correction does not change the row count. Also record the input version, rules and run time.

Restart the kernel and run all cells to remove hidden state. Reset the log each run so entries do not pile up. To check repeatability, run the workflow twice and compare outputs sorted by the key with the index reset; compare rejected rows and quality counts too, but not run times, which naturally differ.

Export only after checks pass: accepted data, rejected data and the log, each with index=False. Then read the cleaned file back, check its row count and columns, and read IDs as strings to protect leading zeros. CSV does not keep pandas types, so document date and decimal formats.

Worked example

Values are illustrative but follow the lecture's six-row case (one duplicate, one missing price, one discount above 1).

import pandas as pd
from pathlib import Path

def convert_numeric(frame):
    out = frame.copy()
    for col in ["quantity", "unit_price", "discount_rate"]:
        out[col] = pd.to_numeric(out[col], errors="coerce")
    return out

def run(raw):
    work = convert_numeric(raw)
    n_dup = int(work.duplicated().sum())
    work = work.drop_duplicates().reset_index(drop=True)
    valid = ((work["quantity"] > 0) & (work["unit_price"] >= 0)
             & work["discount_rate"].between(0, 1)
             & ~work.duplicated(subset=["transaction_id"], keep=False))
    clean, review = work[valid].copy(), work[~valid].copy()
    log = pd.DataFrame({"step": ["input rows", "exact duplicates removed", "rows sent to review", "rows accepted"],
                        "rows": [len(raw), n_dup, len(review), len(clean)]})
    return clean, review, log

raw = pd.DataFrame({
    "transaction_id": ["T01", "T02", "T02", "T03", "T04", "T05"],
    "quantity": ["2", "1", "1", "3", "1", "2"],
    "unit_price": ["50000", "80000", "80000", "", "40000", "60000"],
    "discount_rate": ["0.1", "0", "0", "0", "1.5", "0.2"],
})
clean, review, log = run(raw)
assert len(raw) == log["rows"].iloc[1:].sum(), "every input row must be accounted for"
assert len(clean) > 0 and clean["transaction_id"].is_unique
clean2, _, _ = run(raw)
key = lambda d: d.sort_values("transaction_id").reset_index(drop=True)
pd.testing.assert_frame_equal(key(clean), key(clean2))

out = Path("output"); out.mkdir(exist_ok=True)
clean.to_csv(out / "sales_clean.csv", index=False)
back = pd.read_csv(out / "sales_clean.csv", dtype={"transaction_id": str})
assert len(back) == len(clean)
print(log)

Expected output: a log with input rows 6, exact duplicates removed 1, rows sent to review 2 (T03 with a missing price, T04 with discount 1.5) and rows accepted 3; all assertions pass, and the rerun gives an identical clean table.

Common mistakes

  • Relying on cells run out of order. Restart the kernel and run everything from the raw file before you submit.
  • Changing global data inside functions. Copy the input and return a new DataFrame.
  • Deleting a failing assertion. The failure is information; fix the rule or data with evidence.
  • Reading exported IDs as numbers. Pass dtype={"transaction_id": str} so leading zeros survive.

Summary

  • Structure: purpose and rules, imports and paths, load, inspect, clean, check, export.
  • Keep raw, work and outputs separate; never overwrite the source.
  • Small pure functions make each step clear and reusable.
  • Assertions guard the rules; the log must account for every input row.
  • Prove repeatability with a second full run, then export, read back and re-check.

Flashcards · Building a Rerunnable Cleaning Workflow

9 words
WordMeaningExample
  • Rerunnable notebook

    A notebook that starts from the raw data and gives the same results whenever input, rules and software are unchanged.

    Restart kernel, Run All, compare outputs.

  • Hidden state

    Variables left in memory from cells that were run earlier or later deleted, which make results hard to repeat.

    A `df` defined in a cell you removed.

  • Cleaning function

    A function with a narrow purpose that takes a DataFrame, works on a copy and returns a result.

    `def convert_numeric(frame): out = frame.copy(); ...; return out`

  • assert

    A statement that stops execution with AssertionError when a required condition is false.

    `assert clean["transaction_id"].is_unique, "IDs must be unique"`

  • Processing log

    A table recording each step and how many rows it removed, sent to review or accepted.

    6 input = 1 duplicate + 2 review + 3 accepted

  • pd.testing.assert_frame_equal()

    Raises an error if two DataFrames differ, used to confirm a rerun gives the same result.

    `assert_frame_equal(key(clean), key(clean2))`

  • index=False

    Option of `to_csv` that stops pandas writing the row index as an extra column.

    `clean.to_csv("output/sales_clean.csv", index=False)`

  • Read-back check

    Reloading an exported file to confirm it exists, has the right shape and keeps values correctly.

    `pd.read_csv(path, dtype={"transaction_id": str})`

  • Required field

    A column that must be filled for a record to be accepted, unlike optional fields.

    `unit_price` is required; a missing price sends the row to review.

Practice for Session 5

Open notebooks/Session5_Workflow_and_Report.ipynb from the practice pack (dc_practice_pack.zip, attached to the Course Overview lesson) together with sales_transactions_raw.csv. Run it in Google Colab or Jupyter from top to bottom and complete the Your turn exercises.