Loading and Preparing Data

Topic - Machine learning algorithms assume input matrices are clean, homogeneous, and numeric. Real-world data arrives as messy text files filled with hidden sentinels, currency formatting, duplicate records, and corrupted types. This chapter establishes the defensive data loading practices, automated audits, and slicing rules required before any model fitting can begin.

A single corrupted character turns an entire numeric column into generic text objects. Catching these defects at the ingestion boundary saves hours of downstream debugging.

---

1. The Canonical Imports & Storage Formats

Every data preprocessing workflow in Python begins with three fundamental imports:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

Alias	Library	Operational Responsibility
np	NumPy	N-dimensional arrays — the raw contiguous numeric memory underneath everything
pd	Pandas	DataFrames — two-dimensional labeled tables for data ingestion, cleaning, and slicing
plt	Matplotlib (Pyplot)	Diagnostic data plotting — visualizing distributions, missingness, and outliers

Row-Based (CSV/JSON) vs. Columnar (Parquet) Architecture

Before calling pd.read_csv, understand how data serialization layouts affect computational efficiency:

• Row-Oriented Formats (CSV / JSON): Store records sequentially on disk row by row (Row 1, Row 2...). Ideal for transactional writes, but disastrous for wide analytics: retrieving a single column requires scanning the entire file from disk into RAM.
• Column-Oriented Formats (Apache Parquet): Store data by column chunks on disk (All IDs, All Ages...). Analytical queries read strictly the feature columns requested, skipping unneeded bytes completely. Parquet also enforces strongly typed schemas, Snappy compression, and column-level statistics.

---

2. Silent Type Coercion & Missingness Pandas Cannot See

A CSV file is raw text. Every token is a string until pd.read_csv makes a series of unannounced, column-by-column type inferences.

Consider this representative dirty dataset:

messy.csv

id,Country,Age,Salary,Region,Purchased
1,France,44,72000,EU,No
2,Spain,27,48000,EU,Yes
3,Germany,30,54000,EU,No
4,Spain,38,"61,000",EU,No
5,Germany,40,,EU,Yes
6,France,35,58000,EU,Yes
7,Spain,N/A,52000,EU,No
8,France,48,79000,EU,Yes
9,Germany,-1,83000,EU,No
10,France,37,67000,EU,Yes
11,France,?,999999,EU,No
10,France,37,67000,EU,Yes

A naive load yields immediate silent failures:

naive = pd.read_csv("messy.csv")
print(naive.dtypes)
print("Missing values detected:", naive.isna().sum().sum())

Output

id            int64
Country      object
Age          object
Salary       object
Region       object
Purchased    object
dtype: object

Missing values detected: 2

Two Silent Catastrophes

1. Age and Salary Collapsed to object: Because row 11 has a "?" in Age and row 4 has "61,000" with a comma in Salary, Pandas abandons numeric parsing entirely and converts every single number in those columns into Python string objects. Mathematical operations will crash or silently concatenate strings.
2. Only 2 Missing Values Were Found: The file actually contains 5 missing values. The "?" in Age, the -1 in Age, and the 999999 in Salary were treated as legitimate values.

danger

The Golden Habit: Print .dtypes Immediately After Every Load

df = pd.read_csv("data.csv")
print(df.dtypes)  # Execute immediately on every dataset

If a column that should be numeric appears as object, non-numeric characters are lurking in it. Catch this on line 2, not hours later when a scikit-learn model crashes with a cryptic formatting exception.

---

Interactive Defensive CSV Loader Simulator

Toggle the defensive ingestion parameters below to observe how Pandas resolves type coercion, captures sentinel values, and parses schemas cleanly:

Strip Digit Separators: thousands="," (parses "61,000" as numeric)Declare Domain Sentinels: na_values=["?", "-1", "999999"]Preserve Identifier Types: dtype={"id": str} (preserves leading zeros)

Parsed Schema & Telemetry Readout

Column 'Age'

float64

Column 'Salary'

float64

Column 'id'

int64

Detected NaN Count

5 / 5

Ingestion Clean: All numbers correctly parsed as floats and all 5 missing sentinel coordinates successfully identified.

The 19 Default Missing Values vs. Domain Sentinels

Pandas recognizes 19 distinct string values as NaN automatically (including "", "#N/A", "NaN", "null", "n/a").

However, no library can automatically infer sentinel placeholders. If a database records missing income as 999999 or missing age as -1, Pandas sees legitimate numbers. Declaring them explicitly via na_values requires reviewing the data dictionary:

df = pd.read_csv(
    "messy.csv",
    na_values=["?", "-1", "999999"],  # Domain knowledge, not automatic
    thousands=","
)

---

Interactive Checkpoint: Semicolon Delimiters & Leading Zeros

Scenario: You are handed an export separated by semicolons (;), where missing records are written as unknown, and the column account_id contains leading zeros (e.g., 00938). Which parameter combination loads this defensively?

---

3. The Pre-Flight Data Audit

Rather than checking data quality ad-hoc, run an automated pre-flight audit function on every raw DataFrame immediately after ingestion:

def audit(df, name):
    print(f"AUDIT - {name}: {df.shape[0]} rows x {df.shape[1]} columns")

    # 1. Duplicate row check
    dups = int(df.duplicated().sum())
    print(f"   duplicate rows:   {dups}")

    # 2. Zero-variance constant column check
    const = [c for c in df.columns if df[c].nunique(dropna=False) <= 1]
    print(f"   constant columns: {const if const else 'none'}")

    # 3. Numeric columns disguised as object
    for c in df.columns:
        note = ""
        if df[c].dtype == object:
            coerced = pd.to_numeric(df[c], errors="coerce")
            if coerced.notna().sum() > 0.5 * df[c].notna().sum():
                note = "LOOKS NUMERIC but is object"
        elif int((df[c] < 0).sum()):
            note = f"{int((df[c] < 0).sum())} negative value(s)"
        print(f"   {c:<12}{str(df[c].dtype):>9}{int(df[c].isna().sum()):>9}{df[c].nunique():>8}   {note}")

---

Interactive Pre-Flight Audit Diagnostic Dashboard

Compare the audit findings between the raw messy load and the cleaned table ready for modeling:

Detected Ingestion Anomalies: 12 rows x 6 columns

Duplicate Row Flagged: Row 10 appears twice. Duplicates straddling train/test splits cause immediate data leakage.

Zero-Variance Constant Column: Region == 'EU' for 100% of rows. Carries zero discriminative information; will cause multicollinearity in linear models.

Coerced Numeric Columns: Age and Salary contain over 90% numbers but are typed as string object.

Unique Identifier: id has 11 unique values across 12 rows. Identifiers overfit models and must be dropped.

---

4. Separating Features and Target: loc vs. iloc

Once the dataset is audited and deduplicated, separate it into features $X$ and target $y$:

clean = df.drop(columns=["id", "Region"]).drop_duplicates()

X_df = clean.iloc[:, :-1]  # All rows, every column but the last (2-D)
y_df = clean.iloc[:, -1]   # All rows, the last column only (1-D)

The Two Differences Between loc and iloc

Everyone remembers that iloc is integer-based and loc is label-based. The second difference is the one that introduces production bugs:

Dimension	iloc	loc
Indexing Criterion	Integer position ($0$-based)	Named index label
Stop Boundary	Excluded (standard Python sequence convention)	Included (both start and stop labels returned)
Slice [1:3] Returns	2 items (indices 1 and 2)	3 items (labels 1, 2, and 3)
Survives Sorting	No — position changes	Yes — label stays attached to the row

---

Interactive loc vs. iloc Slice Visualizer

Observe how executing the exact same slice range [1:3] on s = pd.Series([10, 20, 30, 40, 50]) yields different row counts:

Label: 0

10

Excluded

Label: 1

20

[SELECTED]

Label: 2

30

[SELECTED]

Label: 3

40

Excluded

Label: 4

50

Excluded

Resulting Slice: [20, 30] (2 items returned).
Why: iloc excludes stop offset 3 (standard 0-based Python sequence behavior).

---

5. When to Convert to NumPy: Memory and Speed Trade-offs

Most online tutorials immediately write: X = df.iloc[:, :-1].values. While ubiquitous, converting to NumPy immediately upon ingestion carries severe hidden penalties:

1. Mixed Types Collapse to object (28x Slower Computation)

A NumPy array requires uniform data types. If your feature matrix mixes string categories ('France') with numbers (44.0), NumPy converts every number into a generic Python pointer.

Benchmarking 300,000 floats:

• Contiguous float64 array sum: 0.10 ms
• Pointer-chasing object array sum: 2.91 ms
• Result: object array operations run 28x slower.

2. Loss of feature_names_in_ Schema Validation

When scikit-learn models are fitted on a Pandas DataFrame, they record feature_names_in_. In production, passing features in a scrambled column order raises an immediate exception. If converted to a NumPy array prematurely, that check is lost and the model runs silent invalid dot products.

3. The Power of category Dtype (98.4% Memory Savings)

For text columns with few distinct values, Pandas category dtype compresses memory dramatically compared to raw string object:

• 200,000 rows with 3 distinct values stored as object: 12.6 MB
• Same 200,000 rows stored as category: 200 KB (98.4% reduction)

tip

The Production Rule: Keep your data in Pandas DataFrames throughout loading, auditing, imputation, and categorical encoding. Convert to NumPy using .to_numpy() only when required by specialized tensor libraries.

---

6. Implementation Lab

tip

Implementation Lab: Defensive Data Ingestion in Pandas

Verify the silent hazards of type inference, build automated audit pipelines to capture duplicated rows and zero-variance constant columns, and trace how loc vs. iloc slice models on messy dataset inputs.

How to run the lab:

1. Click the "Open In Colab" badge above to launch the interactive notebook.
2. Click "Runtime" > "Run all" or execute cells individually using Shift + Enter.
3. Follow the guided experiments: test unescaped thousands separators, induce object type degradation, and verify memory usage with category dtypes.

---

7. Common Mistakes

1. Silent Coercion

• The Misconception: "The column contains ages, so Pandas will automatically load it as integers or floats."
• Wrong Thinking: Assuming Age is float64 because the rows look numeric.
• The Right Principle: A single non-numeric token (such as "?" or "N/A") coerces the entire column into string object. Always inspect df.dtypes immediately after every load.

2. Leading-Zero ID Loss

• The Misconception: "Account numbers, ZIP codes, and phone numbers are made of digits, so they should be read as integers."
• Wrong Thinking: Letting id = 01234 parse as integer 1234.
• The Right Principle: Identifiers with leading zeros are categorical keys, not quantities. If arithmetic on a column is meaningless, force it to string via dtype={"id": str}.

3. Slicing Exclusivity (`loc` vs `iloc`)

• The Misconception: "Since iloc[1:3] returns 2 items, loc[1:3] will also return 2 items."
• Wrong Thinking: Expecting loc to exclude the stop index.
• The Right Principle: iloc is integer-exclusive (returns 2 items), while loc is label-inclusive (returns 3 items). Mixing them up produces off-by-one errors in feature matrices.

4. Premature Array Conversion

• The Misconception: "Converting to NumPy immediately with .values makes our code run faster."
• Wrong Thinking: Calling .values on a raw DataFrame containing mixed string and numeric columns.
• The Right Principle: Converting mixed types forces NumPy into object arrays, running 28x slower and discarding feature_names_in_ column alignment guards.

---

8. Summary

Defensive Loader Checklist

• pd.read_csv: Handle delimiters (sep), thousands separators (thousands=","), and sentinels (na_values).
• Parquet Advantage: Columnar layout enables scanning only the requested features, skipping unneeded bytes entirely.
• Identifier Safety: Explicitly declare postcodes and account numbers as strings to preserve leading zeros.

Auditing & Slicing

• Automated Audit: Flag duplicate rows, zero-variance constant columns, and masked object dtypes.
• loc vs. iloc: iloc is 0-based positional (exclusive stop); loc is label-based (inclusive stop).
• Postpone Conversion: Stay in DataFrames until categorical encoding is complete to prevent 28x object array slowdowns.

info

Key Takeaways

• dtypes Fail Silently: A single invalid character transforms numeric columns into string object dtypes. Always check df.dtypes on line 2.
• Sentinels Require Domain Knowledge: Numbers like -1 or 999999 bypass standard NaN checks. Declare them explicitly via na_values.
• loc Includes the Stop: Slicing loc[1:3] returns 3 items; iloc[1:3] returns 2 items.
• Keep DataFrames: Modern scikit-learn preserves feature_names_in_ on DataFrames, preventing silent column reordering bugs.

Next in this section: Missing Values - imputing and modeling the missing data this page has identified.

See also: The Toolkit and the Pipeline · Types of Learning

---

9. Active Recall Flashcards

Attempt each conceptual diagnostic question first, then click to reveal the underlying derivation and explanation.

1. [THEORY] How does pd.read_csv perform column type inference, and why does a single non-numeric token cause silent failure?

• Inference Mechanism: Pandas inspects column values sequentially, attempting to cast each column into integers, floats, or booleans. If it encounters any token that cannot be parsed numerically (such as "?" or "61,000"), it abandons numeric casting and defaults to a Python string object.
• Silent Failure: No error or warning is reported during ingestion. The column appears to load cleanly, but downstream mathematical operations will crash with type exceptions or silently concatenate strings.

2. [THEORY] Differentiate between default missing value strings and domain sentinel values. Why can't libraries detect sentinels automatically?

• Default Missing Strings: Standard placeholders recognized by Pandas (19 strings, including "", "NaN", "null", "n/a").
• Domain Sentinels: Mathematically valid numbers (e.g., -1 for age, 999999 for salary) deliberately used by legacy database systems to represent missingness.
• Why Libraries Can't Detect Them: To an algorithm, -1 and 999999 are legitimate numeric coordinates. Only human domain knowledge can identify them and declare them via na_values.

3. [ANALYZE] Why must postal codes, telephone numbers, and account identifiers be loaded as strings (dtype={"zip": str})? What occurs if they are loaded as integers?

• Leading Zero Loss: A postal code such as 01234 loaded as an integer is converted to 1234. The leading zero is permanently discarded and cannot be recovered.
• The Decision Rule: If arithmetic operations (such as calculating the mean or standard deviation) on a column are meaningless, the feature is a categorical identifier and must be read as a string.

4. [THEORY] Detail the four primary checks executed by an automated pre-flight data audit.

1. Dtype Verification: Identifies numeric features that were coerced into string object due to formatting anomalies.
2. Duplicate Row Detection: Identifies duplicate rows that risk straddling train/test partitions and causing data leakage.
3. Zero-Variance Check: Flags constant columns (where nunique <= 1) that offer zero discriminative power and risk causing singular matrix errors.
4. Domain Anomaly Bounds: Flags out-of-range records, such as negative ages or placeholder salaries.

5. [ANALYZE] Why should unique ID columns always be dropped prior to machine learning modeling, even if they correlate with the target?

Unique IDs possess 100% cardinality (every sample has a distinct integer key). Machine learning models (especially decision trees and high-capacity regressors) will fit directly to the ID integers, achieving 100% training accuracy through pure memorization. When deployed to unseen test IDs, generalization collapses completely.

6. [THEORY] State the two fundamental differences between Pandas loc and iloc indexers.

1. Addressing Scheme: iloc indexes strictly by $0$-based integer position; loc indexes strictly by row/column index labels.
2. Stop Boundary Exclusivity: iloc excludes the stop index (standard Python sequence convention); loc includes the stop label (both boundary keys are returned).

7. [OUT] Given s = pd.Series([10, 20, 30, 40, 50]), what are the exact outputs of s.iloc[1:3] and s.loc[1:3]? Explain the discrepancy.

• s.iloc[1:3]: Returns [20, 30] (2 items, positions 1 and 2; stop position 3 is excluded).
• s.loc[1:3]: Returns [20, 30, 40] (3 items, labels 1, 2, and 3; stop label 3 is included).
• Why: loc includes the stop value because index labels are keys without natural chronological sequence ordering, making an exclusive stop unintuitive for arbitrary categorical labels.

8. [ANALYZE] Why do numerical calculations on NumPy object arrays run 28x slower than on contiguous float64 arrays?

• float64 Memory Layout: Values are stored in a single, contiguous byte buffer in RAM. Mathematical operations run directly in compiled C layers using SIMD hardware instructions.
• object Memory Layout: The array stores memory pointers pointing to fragmented, heap-allocated Python float objects scattered across RAM. Every addition requires pointer chasing and runtime Python type unboxing.

9. [THEORY] How does training scikit-learn estimators on Pandas DataFrames prevent column-reordering production bugs?

When fitted on a DataFrame, scikit-learn models automatically store the column schema in feature_names_in_. If future production requests pass columns in a different order (e.g. [Salary, Age] instead of [Age, Salary]), the estimator raises an immediate schema validation error. With raw NumPy arrays, the model processes the scrambled columns silently.

10. [PROG] Write a complete, defensive Python snippet loading a CSV where semicolons are delimiters, '?' represents missingness, and account IDs preserve leading zeros.

import pandas as pd

# Defensive Ingestion Pipeline
df = pd.read_csv(
    "customers.csv",
    sep=";",                             # Semicolon delimiter
    na_values=["?", "unknown", "-1"],    # Domain sentinels
    thousands=",",                       # Digit separators
    dtype={"account_id": str}           # Preserve leading zeros
)

# Immediate schema and missingness verification
print(df.dtypes)
print("Total missing entries:", df.isna().sum().sum())