Data Pipeline: Cleaning, Transformation, and Exploratory Analysis

Data Pipeline: Cleaning, Transformation, and Exploratory Analysis

A data pipeline is the structured sequence of steps that moves raw data from its source to a form suitable for analysis. In professional practice, every step must be documented, version-controlled, and reproducible—meaning a colleague running your script on the same raw files should get the identical output. This lesson walks you through building that pipeline for your capstone project.

Why Reproducibility Matters

Reproducibility is a non-negotiable professional standard. If your analysis cannot be re-run by someone else, your conclusions cannot be trusted or audited. Store every script in a Git repository with meaningful commit messages. Commit after each major milestone—not just at the end. A repository with five or more commits shows reviewers that you worked iteratively, not all at once.

Step 1 — Data Cleaning

Raw data almost always contains errors. Your cleaning script must address four categories of problems:

Missing values. Decide whether to drop rows, impute with the mean or median, or flag the gap with a sentinel value. Document your choice and reason in a comment. In pandas: `df.dropna()`, `df.fillna()`, or `df['col'].median()`. In R tidyverse: `drop_na()`, `replace_na()`.

Outliers. Identify values that fall far outside the expected range. Common approaches include the interquartile range (IQR) method—flagging values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR—and domain-knowledge thresholds. Never silently delete outliers; log them in a separate file and state your decision in a comment.

Data-type mismatches. A column of prices stored as strings will break arithmetic. Cast columns explicitly: `df['price'] = pd.to_numeric(df['price'], errors='coerce')` in pandas, or `as.numeric()` in R. After casting, re-check for newly introduced missing values.

Duplicate records. Duplicates inflate counts and skew aggregations. Use `df.duplicated().sum()` to find them and `df.drop_duplicates()` to remove them, then verify the row count matches expectations.

Write every decision as a code comment. A classmate will attempt to re-run your script as a peer code review. If your script fails or produces different output on their machine, that is a reproducibility failure—not their problem.

Step 2 — Merging Datasets

Most real projects combine more than one source. You will perform SQL joins either inside Python using SQLAlchemy or psycopg2, or directly in PostgreSQL.

Choose the correct join type for your business question:

• INNER JOIN — keeps only rows with matching keys in both tables.
• LEFT JOIN — keeps all rows from the left table; fills unmatched right-side columns with NULL.
• FULL OUTER JOIN — keeps all rows from both tables.

After the join, write a data dictionary: a spreadsheet that lists every retained column, its data type, valid value range, and any transformation applied. A complete data dictionary—100% of retained columns defined—is required to pass this lab. This directly supports CompTIA Data+ domain 2 (Data Mining) competencies and Google Data Analytics Certificate data-cleaning skills.

Step 3 — Exploratory Data Analysis (EDA)

EDA is the process of summarizing and visualizing data to understand its structure before modeling. Work in a Jupyter Notebook or R Markdown file so that code, output, and prose live together.

Descriptive statistics must include:

• Central tendency: mean, median, mode.
• Spread: standard deviation, variance, range, IQR.
• Shape: skewness (positive skew = tail extends right; negative skew = tail extends left).

In pandas: `df.describe()` gives count, mean, std, min, quartiles, and max. Add `df.skew()` for skewness.

Required visualizations (at least six):

1. Histogram — distribution of a single numeric variable.

2. Box plot — spread and outliers for one or more groups.

3. Scatter plot — relationship between two numeric variables.

4. Correlation heat map — pairwise correlations across numeric columns.

5. Bar chart — comparison of a numeric measure across categories.

6. Your choice — any additional chart that reveals something meaningful in your data.

Use Matplotlib, Seaborn, ggplot2, or a comparable library. Every chart must be publication quality: labeled axes, a title, a legend where applicable, and readable font sizes. Beneath each chart, write one paragraph interpreting what the chart reveals—not just what it shows, but what it means for your research question.

Data Safety and Ethical Handling

Data pipelines can expose personally identifiable information (PII). Before cleaning, check whether your dataset contains names, addresses, Social Security numbers, or other PII. If it does, apply masking or anonymization before sharing files with classmates during peer review. Never commit raw files containing PII to a public repository. Follow your institution's data-use policy and any applicable regulations governing the data source.

Passing This Lab

The instructor will run your cleaning script independently on the raw source files. It must execute without errors and produce a validated output file. Your data dictionary must define every retained column. Your EDA notebook must contain at least six charts, each with a written interpretation. The lab is graded on a 20-point rubric; you need 15/20 to advance. Your Git repository must show a minimum of five meaningful commits.