Data Cleaning and Multi-Source Integration Pipeline

Why a Reproducible Cleaning Pipeline Matters

Real-world city and urbanicity datasets are rarely clean. They arrive from multiple agencies — a county assessor, a transit authority, a census bureau — each with its own formatting conventions, update schedules, and quality standards. Your job as a data analyst is not just to fix the problems you can see; it is to fix them in a way that anyone else on your team can replicate exactly, starting from the same raw files, and arrive at the same result. That reproducibility is the difference between a professional deliverable and a one-time patch.

This lesson walks you through building a multi-source integration pipeline in Python (pandas / pyjanitor) or R (tidyverse). By the end, your raw data sources will be joined into a single analysis-ready table, every transformation will be recorded in a changelog, and three data-quality assertions will confirm the table is safe to analyze.

Understanding the Data-Quality Issues You Are Fixing

Before writing a single line of code, re-read your EDA notes. Common problems in city datasets include:

• Inconsistent address strings — "123 Main St", "123 Main Street", "123 MAIN ST" all refer to the same location but will not join on a string match.
• FIPS code misalignment — Federal Information Processing Standards (FIPS) codes identify states, counties, and census tracts. A leading zero dropped from a code stored as an integer (e.g., `6037` instead of `06037` for Los Angeles County) will break a join silently.
• Duplicate rows — the same record ingested twice, sometimes from overlapping extract dates.
• Missing values — nulls in key join columns make rows undiscoverable after a merge; nulls in numeric fields must be imputed or flagged with a documented strategy.
• Out-of-range values — a recorded population density of −5 or a year value of 9999 signals a data-entry error, not a real observation.

Document every issue you find before you fix it. That documentation becomes your changelog.

Building the Pipeline Step by Step

Step 1 — Load raw data. Read each source file with an explicit `dtype` argument where possible. Force FIPS codes to string immediately on load to preserve leading zeros: `dtype={'fips': str}`. Never overwrite raw files; always write outputs to a separate `clean/` directory.

Step 2 — Standardize address and identifier fields. Use `str.strip()`, `str.upper()`, and a consistent abbreviation map ("Street" → "ST") before any join. The pyjanitor function `clean_names()` normalizes column names to lowercase snake_case in one call. In R, `janitor::clean_names()` does the same.

Step 3 — Align FIPS codes. After loading, apply `str.zfill(5)` to any five-digit county FIPS column and `str.zfill(11)` to any eleven-digit census-tract column. Check that every value in the join key exists in a known-good reference list (e.g., the Census Bureau's FIPS reference file) before you merge.

Step 4 — Remove duplicates. Call `DataFrame.drop_duplicates(subset=[key_columns])` and record how many rows were removed. Investigate before dropping: if two rows share a key but differ in a value column, that is a conflict requiring a business rule, not a simple drop.

Step 5 — Handle missing values with a documented strategy. Choose one of three approaches for each column and write your reasoning in the changelog:

• Drop — acceptable when missingness is random and the affected rows are a small fraction (often cited as under 5 percent, but justify your threshold).
• Impute with a statistic — mean or median for symmetric or skewed numeric columns; mode for categorical. Use training data only if you are building a model later.
• Flag and retain — add a Boolean indicator column (e.g., `income_imputed`) so downstream users know which values were filled.

Step 6 — Join the sources. Use an explicit merge type (`inner`, `left`, `outer`) and state which you chose and why. After the merge, confirm the row count matches your expectation: an inner join on a key with no nulls and no duplicates on either side should produce a predictable number of rows.

Step 7 — Validate with assertions. Write at least three checks using `assert` statements or `pandas .query()`. Examples:

```python

assert clean_df['tract_fips'].isna().sum() == 0, "Null FIPS codes remain"

assert clean_df['year'].between(2015, 2024).all(), "Year out of project scope"

assert (clean_df['pop_density'] >= 0).all(), "Negative population density found"

```

A failed assertion is not a grade penalty — it is an early warning. Fix the root cause, rerun from the top, and confirm all assertions pass before submission.

Writing the Changelog

The changelog is a Markdown file (e.g., `CHANGELOG.md`) stored alongside your notebook. Each entry must include:

1. What changed — the specific column or rows affected.

2. Why — the data-quality reason.

3. Row counts before and after — so a reviewer can verify the math.

A classmate should be able to open your raw data, run your notebook top to bottom, and land on the exact final row count you documented. If they cannot, your pipeline is not reproducible.

Data Safety and Professional Practice

City datasets often contain personally identifiable information (PII) — names, addresses, parcel IDs that can be linked back to individuals. Before you share a notebook or push it to a public repository, confirm that your data-sharing agreement permits it and that any PII columns are masked or aggregated. Treat data governance as seriously as a physical safety rule: a breach cannot be undone. Never commit raw data files containing PII to a public version-control repository.

Connecting to Your Credentials

The skills in this lesson map directly to the Google Data Analytics Certificate Process phase (data cleaning and verification), the CompTIA Data+ domain covering data preparation and quality, and the IBM Data Science Professional Certificate modules on data wrangling. The reproducibility standard you practice here is exactly what a hiring manager expects when they ask you to "show your work."