Module 2 · Data Preparation

Data Analysis with pandas

Lesson 5 of 24 10 min read Updated

pandas is the inspection workbench you use before a machine learning model ever sees a row. In this lesson, the outcome is practical: load a table, understand its columns and values, find defects that would distort training, and produce evidence you can act on. Good analysis is not cosmetic reporting. It decides whether a feature is usable, whether rows belong in the training set, and whether evaluation will measure the future or accidentally memorize the past.

Within this machine learning course, pandas sits between raw data collection and modeling. It gives you labeled axes, vectorized operations, grouping, joins, and missing-value semantics so you can reason about observations at dataset scale while still writing readable Python.

How pandas Represents Data

The core pandas object for data analysis is the DataFrame: a two-dimensional table with an index for rows and column labels for variables. Each column is a Series, and each series has a dtype such as integer, floating point, boolean, datetime, category, string, or object. A DataFrame is not just a list of dictionaries. pandas stores column-oriented arrays, aligns operations by labels, and propagates missing values according to dtype-specific rules.

That internal design explains many behaviors. Column operations are fast because pandas can apply array operations to contiguous values. Label alignment is powerful because arithmetic between two series matches index labels before computing. It is also a source of mistakes: if two objects have different indexes, pandas may create missing results rather than combine rows positionally. Missing values are represented differently depending on dtype: floating columns often use NaN, datetime columns use NaT, and newer nullable dtypes can use pd.NA. Treat missingness as data about the collection process, not merely an inconvenience to fill.

For machine learning, the main analysis questions are concrete: What is one row? Is the target available only after prediction time? Which columns are identifiers rather than signals? Are dtypes what the model pipeline expects? Are classes or time periods underrepresented? Are duplicates inflating confidence? pandas gives direct APIs for each question.

API Anatomy for Analysis

A typical pandas analysis starts with ingestion, schema inspection, profiling, filtering, aggregation, and export of a cleaned or diagnosed result. pd.read_csv creates a DataFrame from text files, while constructor calls are useful for small examples and tests. df.head() previews records, df.info() summarizes column counts and dtypes, df.describe() computes numeric distribution statistics, and df.isna() exposes missing cells. df.loc selects by labels and boolean masks; df.iloc selects by integer position. Use groupby when you need to compare distributions by class, segment, date, device, customer, or other grouping key.

The most important design choice is to keep exploratory commands reproducible. A notebook cell that manually eyeballs a table is useful while learning, but a machine learning workflow needs checks that can be rerun when data changes. Turn questions into small computations: percentage missing by column, number of duplicate entity-time pairs, target rate by split, or the range of a feature after type conversion.

Example 1: Schema and Missingness

This first example builds a tiny loan dataset, inspects dtypes, and calculates missingness. It shows the kind of evidence you should gather before encoding features or splitting data.

import pandas as pd

loans = pd.DataFrame({
    "applicant_id": [101, 102, 103, 104],
    "income": [72000, 48000, None, 91000],
    "employment_years": [5, 2, 1, None],
    "approved": [1, 0, 0, 1],
})

print(loans.dtypes.astype(str).to_dict())
print((loans.isna().mean() * 100).round(1).to_dict())

The deterministic output is {'applicant_id': 'int64', 'income': 'float64', 'employment_years': 'float64', 'approved': 'int64'} followed by {'applicant_id': 0.0, 'income': 25.0, 'employment_years': 25.0, 'approved': 0.0}.

The integer-like feature columns became floating point because standard numeric columns need a way to represent missing values. That matters when downstream code expects integers or when missingness itself carries meaning. A correction might be imputation, a missing-indicator feature, or excluding a column whose missingness is tied to a biased collection process. The right choice depends on why the values are missing.

Example 2: Duplicates and Group Behavior

Duplicates can make a model look better than it is. If the same customer appears in both training and validation, the model may learn customer-specific patterns instead of general rules. pandas helps you identify repeated entities and summarize target behavior by group.

import pandas as pd

visits = pd.DataFrame({
    "customer_id": ["A", "A", "B", "C", "C"],
    "visit_month": ["2026-01", "2026-01", "2026-01", "2026-01", "2026-02"],
    "pages_viewed": [4, 4, 2, 9, 3],
    "purchased": [0, 0, 0, 1, 0],
})

duplicate_keys = visits.duplicated(["customer_id", "visit_month"], keep=False)
print(visits.loc[duplicate_keys, ["customer_id", "visit_month"]].to_dict("records"))
print(visits.groupby("customer_id")["purchased"].mean().round(2).to_dict())

The output is [{'customer_id': 'A', 'visit_month': '2026-01'}, {'customer_id': 'A', 'visit_month': '2026-01'}] followed by {'A': 0.0, 'B': 0.0, 'C': 0.5}.

The duplicate key tells you there are two rows for the same customer-month. That may be a true repeated event, or it may be a data ingestion duplication. The group summary shows customer C has mixed outcomes across months, which is useful for deciding whether to split by row, customer, or time. For many ML evaluations, grouping by customer is safer than random row splitting because it tests generalization to unseen customers.

Example 3: Suspicious Values and Type Conversion

Raw data often arrives as strings, especially from CSV files, spreadsheets, and vendor exports. pandas can coerce types and flag values that fail conversion. This is better than silently accepting an object column that contains numbers, placeholders, and text.

import pandas as pd

claims = pd.DataFrame({
    "claim_id": [1, 2, 3, 4],
    "claim_amount": ["1200.50", "unknown", "-99", "840.00"],
    "days_open": [3, 12, 400, 6],
})

claims["claim_amount_num"] = pd.to_numeric(claims["claim_amount"], errors="coerce")
invalid_amounts = claims[claims["claim_amount_num"].isna()]["claim_id"].tolist()
suspicious = claims[(claims["claim_amount_num"] < 0) | (claims["days_open"] > 365)]["claim_id"].tolist()

print(invalid_amounts)
print(suspicious)

The deterministic output is [2] followed by [3]. Claim 2 could not be converted to a number. Claim 3 converted successfully, but the negative amount and long duration violate domain expectations. Both problems require investigation before model training. Coercion is useful because it separates parse failures from valid values; domain rules then catch values that are syntactically valid but semantically suspicious.

Design Choices and Trade-offs

pandas is ideal for medium-sized tabular analysis, fast iteration, and expressive diagnostics. Its trade-off is that many operations happen in memory, so very large datasets may require chunked reads, database-side aggregation, or a distributed engine. Even when data later moves to another tool, pandas remains valuable for prototyping exact checks and expected behavior.

Choose dtypes intentionally. category can reduce memory and make allowed values clearer, but it requires care when new categories appear. Datetime parsing makes time-based leakage checks possible, but ambiguous formats must be resolved explicitly. Indexes can make joins and alignment elegant, but hidden index mismatches can create confusing missing values. For ML preparation, favor explicit columns for entity IDs, timestamps, targets, and split labels so audits are easy.

Another trade-off is how aggressively to clean during analysis. Do not overwrite raw columns too early. Create converted columns, masks, and summary tables first. This preserves evidence, lets you explain exclusions, and supports rollback if an assumption changes.

Failure Modes and Troubleshooting

Symptom: a numeric feature has dtype object and model code fails during scaling. Cause: the column contains nonnumeric strings, currency symbols, commas, or placeholder values. Diagnosis: run df[column].map(type).value_counts(), inspect failed conversions with pd.to_numeric(..., errors="coerce"), and review sample raw values. Correction: normalize formatting, convert with explicit error handling, and keep a count of unparseable rows.

Symptom: validation accuracy is unusually high but production performance drops. Cause: duplicates, repeated users, future-derived features, or target leakage crossed the train-validation boundary. Diagnosis: check duplicate entity keys, compare feature timestamps with target timestamps, and compute overlap of IDs across splits. Correction: split by time or group, remove leakage columns, and rerun evaluation.

Symptom: a join unexpectedly increases row count. Cause: the right-hand table has multiple rows per key, creating a many-to-many merge. Diagnosis: use value_counts() on join keys and pass validate="one_to_one" or validate="many_to_one" to merge. Correction: aggregate the right table to the intended grain before merging, or change the row definition.

Symptom: missing values appear after arithmetic between two series. Cause: pandas aligned by index labels, not row position. Diagnosis: compare left.index and right.index. Correction: reset or align indexes intentionally before computing.

Security, Performance, and Reliability

Data analysis can expose sensitive attributes even before modeling. Avoid printing full records when they contain personal identifiers, and prefer aggregate diagnostics for shared reports. Keep raw data and cleaned data separate so privacy decisions and deletion requests can be honored. When using pandas in scripts, pin input locations and reject unexpected schemas instead of guessing column meanings.

For performance, read only needed columns, parse dates during ingestion when practical, and use vectorized operations rather than Python loops over rows. For reliability, make analysis checks deterministic and fail loudly when required columns are absent. A silent change from approved to is_approved should stop the pipeline, not train a model on the wrong target.

Hands-on Lab: Build a Pretraining Data Audit

Prerequisites: Python with pandas installed, a terminal or notebook, and permission to create a small local script. The lab uses in-memory data, so no external files are required.

  1. Create a DataFrame with an entity ID, timestamp, two candidate features, and a binary target.
  2. Print dtypes and missing percentages.
  3. Convert any numeric-looking string column with pd.to_numeric and record failed rows.
  4. Check duplicates at the entity-timestamp grain.
  5. Group by target and compare feature means to look for extreme separation that could indicate leakage.
  6. Write down one keep, one fix, and one reject decision for the columns.

Verification is concrete: your audit should report every required column, at least one missingness statistic, a duplicate count, and a list of invalid converted values. Cleanup is simply removing the temporary script or clearing the notebook cell output. If you created exported CSV files, delete them or move them to a controlled data directory.

Assessment Exercises

  • A column named last_payment_date predicts loan default extremely well. What pandas checks would you run to determine whether it is leakage?
  • You find 18 percent missingness in income, concentrated among self-employed applicants. Compare two possible treatments and explain how each might affect model bias.
  • A merge between customer profiles and transactions triples the row count. What is the likely key-grain problem, and how would you prove it with pandas?
  • Write a pandas expression that identifies rows where a converted numeric column is missing but the original raw column was not missing. Explain why this distinction matters.
  • Design a validation split for a dataset with repeated patients over time. Which pandas summaries would you inspect before trusting the split?

Summary

Data analysis with pandas is the disciplined process of making tabular data understandable before modeling. DataFrames organize columns with labels and dtypes; masks, groupby operations, type conversion, and joins reveal whether the data matches the row definition and prediction moment. The practical goal is to catch missingness, duplicates, leakage, suspicious values, and schema drift early enough that the machine learning model learns from valid evidence instead of artifacts.

Progress is saved in this browser.