Data Wrangling with pandas

Introduction

Business analytics rarely begins with a clean spreadsheet. Data may arrive from enterprise databases, application programming interfaces, comma-separated files, spreadsheets, surveys, or transaction systems. It may contain inconsistent names, missing values, duplicate records, incorrect data types, unusual observations, and dates stored as text. Data wrangling is the disciplined process of converting such raw data into a reliable, analysis-ready structure.

In Python, the pandas library is the principal tool for tabular data wrangling. It provides two central structures: the Series, a one-dimensional labeled array, and the DataFrame, a two-dimensional labeled table whose rows and columns can have different data types. pandas supports inspection, selection, transformation, reshaping, joining, aggregation, and export.

The purpose of wrangling is not merely to make data look tidy. It is to preserve meaning, document decisions, and create a defensible connection between raw observations and analytical conclusions.

Learning Objectives

By the end of this chapter, you should be able to:

• load data from common file formats and inspect its structure;
• select, filter, sort, and transform DataFrame columns;
• detect and manage missing values, duplicates, outliers, and inconsistent categories;
• parse and analyze dates and times;
• reshape data between wide and long formats;
• merge datasets safely and diagnose unmatched records;
• aggregate data with groupby operations;
• construct reproducible, analysis-ready datasets; and
• evaluate the quality, ethical implications, and limitations of wrangling decisions.

1. The pandas Data Model

A DataFrame consists of labeled rows and columns. Each column is a Series, and the row labels form the index. The index is useful for alignment, but it is not necessarily a business identifier. A customer ID, invoice number, or product code should be stored explicitly as a column unless the analytical design deliberately uses it as an index.

import pandas as pd
import numpy as np

sales = pd.DataFrame({
    "order_id": [101, 102, 103],
    "region": ["North", "South", "North"],
    "revenue": [1200.0, 850.0, 640.0]
})

A useful mental model is that each row represents an observation and each column represents a measured attribute. This is often called tidy data: each variable is a column, each observation is a row, and each observational unit is represented by a table.

Tidy structure is not always the most convenient structure for every task. A pivot table may be better for presentation, while a long format is usually better for statistical modelling and visualization. Therefore, data shape is an analytical choice, not an absolute rule.

2. Loading and Inspecting Data

Common import functions include:

orders = pd.read_csv("orders.csv")
customers = pd.read_excel("customers.xlsx", sheet_name="Current")
transactions = pd.read_json("transactions.json")

Important import parameters include dtype, parse_dates, na_values, usecols, and encoding. Explicitly specifying types can prevent errors, especially when identifiers contain leading zeroes.

orders = pd.read_csv(
    "orders.csv",
    dtype={"customer_id": "string", "order_id": "string"},
    parse_dates=["order_date"],
    na_values=["", "NA", "N/A", "unknown"]
)

Inspection should precede transformation. Typical commands are:

orders.head()
orders.tail()
orders.shape
orders.info()
orders.dtypes
orders.describe(include="all")
orders.nunique()
orders.isna().sum()
orders.sample(5, random_state=42)

shape returns the number of rows and columns. info() shows non-null counts and data types. describe() provides numerical summaries and, when requested, summaries of non-numerical columns. Inspection should also include business checks: Does each order have an order ID? Are revenues non-negative? Are dates within the expected period? Are categories spelled consistently?

A data dictionary should document each field, its meaning, expected type, permitted values, source, and quality rules. This is particularly important when data is shared among analysts or used in regulated decisions.

3. Selecting, Filtering, and Sorting

Column selection can use bracket notation:

orders["revenue"]
orders[["order_id", "customer_id", "revenue"]]

Use .loc for label-based selection and .iloc for positional selection:

orders.loc[orders["region"] == "North", ["order_id", "revenue"]]
orders.iloc[0:5, 0:3]

Boolean conditions should be enclosed in parentheses when combined:

recent = orders.loc[
    (orders["revenue"] > 500) &
    (orders["region"].isin(["North", "West"]))
]

Use ~ for logical negation. Avoid Python’s and and or with pandas Series because pandas performs element-by-element comparisons with & and |.

Sorting and indexing are separate operations:

orders = orders.sort_values("order_date")
orders = orders.set_index("order_id")
orders = orders.reset_index()

A sorted DataFrame is not automatically indexed by the sorted column. Keeping this distinction clear prevents errors in later joins and time-based operations.

4. Cleaning Data Quality Problems

Missing Values

Missingness can mean different things: a value was not collected, does not apply, was lost during transmission, or was deliberately withheld. The appropriate treatment depends on the meaning and analytical purpose.

orders.isna().sum()
orders["discount"] = orders["discount"].fillna(0)
orders["region"] = orders["region"].fillna("Unknown")
orders = orders.dropna(subset=["order_id", "order_date"])

Common strategies include deletion, constant-value replacement, group-based replacement, and statistical imputation. Replacing a missing discount with zero is defensible only if missing means “no discount.” It is not defensible if the field was simply not recorded.

For numerical variables, mean or median imputation may be used, but the choice should reflect the distribution and business meaning. Median replacement is less sensitive to extreme values than mean replacement. Imputation should be performed using training data only when building predictive models; otherwise, information from the test set may leak into the model.

A missingness indicator can preserve information about the fact that a value was missing:

orders["discount_missing"] = orders["discount"].isna().astype("int8")

Duplicates

Duplicates may be exact duplicates or repeated business events. The correct unit of analysis must be established before removing them.

orders.duplicated().sum()
orders = orders.drop_duplicates()
orders.duplicated(subset=["order_id"]).sum()
orders = orders.drop_duplicates(subset=["order_id"], keep="last")

Removing duplicate order IDs is appropriate only if the business rule says one row should represent one order. If an order can contain multiple line items, repeated order IDs are legitimate and should not be removed.

Inconsistent Text and Categories

Text values often differ because of capitalization, whitespace, punctuation, or abbreviations.

orders["region"] = (
    orders["region"].astype("string")
    .str.strip()
    .str.lower()
    .replace({"n": "north", "nrth": "north"})
)

Mappings should be governed by documented business rules rather than guesswork. A category such as “Other” should not be silently converted into a more specific category. For controlled categories, pandas supports CategoricalDtype:

from pandas.api.types import CategoricalDtype

priority_type = CategoricalDtype(
    categories=["Low", "Medium", "High"], ordered=True
)
orders["priority"] = orders["priority"].astype(priority_type)

Categorical types can reduce memory usage and make ordering explicit. Unexpected values become missing when converted to a restricted category, so validation is necessary afterward.

Numerical Validity and Outliers

Outliers are observations that are unusually distant from the rest of the data. They may represent errors, rare but genuine events, fraud, or a different population. An outlier should not be removed merely because it is inconvenient.

Useful checks include:

orders["revenue"].describe()
orders.loc[orders["revenue"] < 0]
orders.boxplot(column="revenue")

A common exploratory rule uses the interquartile range (IQR). If Q₁ is the first quartile and Q₃ is the third quartile, then IQR = Q₃ − Q₁. A conventional flagging interval is:

This is a screening rule, not a universal definition of an error. It assumes that the analyst is using quartiles to identify unusually distant observations; it does not prove that flagged observations are invalid. In financial data, winsorization, transformation, segmentation, or robust statistics may be preferable to deletion.

5. Transforming Columns

New variables should express transparent business logic:

orders["net_revenue"] = orders["revenue"] - orders["discount_amount"]
orders["is_large_order"] = orders["revenue"] >= 1000
orders["revenue_band"] = pd.cut(
    orders["revenue"],
    bins=[-np.inf, 500, 1000, np.inf],
    labels=["Small", "Medium", "Large"]
)

assign() can make pipelines readable:

clean_orders = (
    orders
    .assign(
        region=lambda d: d["region"].str.strip().str.title(),
        net_revenue=lambda d: d["revenue"] - d["discount_amount"]
    )
)

Prefer vectorized operations over row-by-row Python loops. Vectorization is generally clearer and faster because pandas delegates many operations to optimized numerical routines. Use .map() for one-column mappings, .replace() for value substitution, and .apply() cautiously when a vectorized alternative is unavailable.

6. Dates and Time

Dates should be parsed as datetime values rather than retained as text:

orders["order_date"] = pd.to_datetime(
    orders["order_date"], errors="coerce", utc=True
)

errors="coerce" converts invalid dates to missing values; these must then be investigated. Time zones matter when data spans regions. A timestamp without a time zone may be ambiguous around daylight-saving transitions.

Datetime components can be extracted as follows:

orders["year"] = orders["order_date"].dt.year
orders["month"] = orders["order_date"].dt.month
orders["weekday"] = orders["order_date"].dt.day_name()

Time intervals can be calculated using Timedelta values:

orders["delivery_days"] = (
    orders["delivery_date"] - orders["order_date"]
).dt.days

For monthly summaries, grouping by a period or resampling a datetime index is useful:

monthly = (
    orders.set_index("order_date")
    .resample("ME")["net_revenue"]
    .sum()
)

The analyst should confirm whether the date represents an event date, posting date, shipping date, or reporting date. Confusing these concepts can produce a numerically correct but conceptually invalid analysis.

7. Reshaping Data

A wide table stores repeated measures in separate columns, while a long table stores the variable name and value in rows. melt() converts wide data to long form:

wide = pd.DataFrame({
    "product": ["A", "B"],
    "January": [100, 120],
    "February": [110, 125]
})

long = wide.melt(
    id_vars="product",
    var_name="month",
    value_name="units"
)

pivot() converts long data to wide form when each index-column combination is unique. If duplicates exist, use pivot_table() with an aggregation function:

summary = long.pivot_table(
    index="product",
    columns="month",
    values="units",
    aggfunc="sum",
    fill_value=0
)

Reshaping can expose hidden duplication. If a pivot fails because index-column pairs are not unique, investigate whether multiple transactions are being incorrectly treated as one observation.

8. Merging and Relational Thinking

Merging combines tables through key columns. The main join types are:

customer_orders = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True
)

validate="many_to_one" asserts that many order rows may match one customer row. This is a powerful safeguard against accidental row multiplication. The indicator identifies unmatched records:

customer_orders["_merge"].value_counts()

A merge can multiply rows when the join key is duplicated in both tables. If ten rows match ten rows, the result may contain one hundred combinations. Always inspect key uniqueness before merging:

customers["customer_id"].is_unique
orders["customer_id"].nunique()

Use merge_asof() for time-ordered nearest-date matching, such as assigning the most recent price to a transaction. Such matching requires sorted data and a clearly defined tolerance.

9. Aggregation and Grouped Analysis

Grouping divides data into categories and applies an operation to each group:

regional = (
    orders.groupby("region", as_index=False)
    .agg(
        orders=("order_id", "nunique"),
        revenue=("net_revenue", "sum"),
        average_order=("net_revenue", "mean")
    )
)

The function applied must match the question. sum measures total contribution, mean measures an arithmetic average, median describes a central value resistant to extreme observations, and nunique counts distinct entities. Averages can be misleading when customer or order sizes differ; analysts should distinguish order-level, customer-level, and revenue-weighted measures.

Grouped transformations preserve the original row structure:

orders["customer_total"] = orders.groupby("customer_id")["net_revenue"].transform("sum")
orders["share_of_customer_total"] = (
    orders["net_revenue"] / orders["customer_total"]
)

The denominator in a ratio must be defined carefully. A percentage of total revenue is not the same as a percentage of orders, and both can differ from a customer-level percentage.

10. A Reproducible Wrangling Workflow

A robust workflow separates raw data from derived data and records each transformation. A practical sequence is:

1. preserve the raw source unchanged;
2. load data with explicit types and date parsing;
3. profile dimensions, types, missingness, uniqueness, and ranges;
4. define the unit of analysis and business keys;
5. standardize text, categories, dates, and numeric representations;
6. handle missing values and duplicates according to documented rules;
7. merge reference tables with validation;
8. create derived variables and aggregations;
9. run quality checks after every major transformation; and
10. export the analysis-ready dataset with a data dictionary and processing log.

A pipeline may be written as a sequence of functions rather than a long interactive notebook:

def prepare_orders(path):
    df = pd.read_csv(path, dtype={"customer_id": "string"})
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    df["region"] = df["region"].str.strip().str.title()
    df = df.dropna(subset=["order_id", "customer_id", "order_date"])
    df = df.drop_duplicates(subset="order_id")
    return df

Assertions make assumptions visible:

assert prepared["order_id"].notna().all()
assert (prepared["net_revenue"] >= 0).all()

These checks should reflect actual business rules and should not be used to conceal legitimate exceptions.

Applied Case: Retail Order Profitability

Background

An illustrative retailer receives monthly order files and a customer master file. Management wants regional revenue, customer profitability, and delivery performance. The order data contains inconsistent region labels, blank discounts, repeated order IDs, and dates in mixed formats.

Problem

A direct aggregation produces inflated revenue because some order IDs occur twice. A merge with the customer file also duplicates rows because the customer master contains historical versions of some customers.

Analysis and Approach

The analyst first defines the unit of analysis as one row per order. The order file is profiled for missing keys and duplicate order IDs. Duplicate rows are reviewed against the source system rather than automatically deleted. The customer master is reduced to one current record per customer using a documented effective-date rule.

The analyst then standardizes region labels, parses dates, treats blank discount amounts as zero only after confirming that blanks mean “no discount,” and calculates net revenue. The customer table is merged using a left join with validate="many_to_one". Unmatched customer IDs are reported rather than silently discarded.

Finally, grouped summaries are produced by region and month. Quality checks compare the number of unique orders before and after each step, test for negative net revenue, and reconcile the total cleaned revenue with an independently generated finance report.

Outcome

The final dataset contains one validated row per order, an explicit missing-customer flag, standardized categories, parsed dates, and documented derived fields. Management receives regional totals that reconcile to the source system and a separate exception report for unmatched customers and disputed duplicates.

Learning Points

The case demonstrates that wrangling is a reasoning process, not a sequence of cosmetic commands. Key decisions concern the unit of analysis, the meaning of missingness, the cardinality of joins, and the difference between a data-quality exception and an invalid observation.

Limitations, Governance, and Professional Practice

Wrangling cannot repair information that was never collected or resolve an ambiguous business definition by itself. Automated cleaning may encode bias: for example, mapping names, locations, or demographic categories can erase meaningful distinctions. Missing-value treatment may disproportionately affect small businesses or minority groups if data collection is uneven.

Sensitive data should be minimized, access-controlled, and handled according to applicable privacy and data-protection requirements. Analysts should avoid exposing personal identifiers in notebooks, use secure storage, and document legitimate analytical purposes. Data lineage should identify the source, transformations, analyst, date, and version of each output.

AI-assisted coding can suggest pandas syntax, but generated code must be tested. An AI system may invent column meanings, apply inappropriate imputation, or overlook a many-to-many merge. Human review, unit tests, reconciliation, and domain expertise remain essential.

Best Practices Checklist

• Define the observational unit before cleaning.
• Preserve raw data and create versioned derived outputs.
• Treat identifiers as identifiers, not automatically as numbers.
• Inspect before transforming and validate after transforming.
• Use explicit join validation.
• Document every imputation, recoding, deletion, and outlier rule.
• Prefer vectorized operations and readable pipelines.
• Check totals, row counts, key uniqueness, and plausible ranges.
• Separate exploratory cleaning from production pipelines.
• Protect confidential information and report limitations honestly.

Chapter Summary

Data wrangling with pandas converts heterogeneous raw data into trustworthy analytical datasets. DataFrames and Series provide the main structures for tabular work. Reliable workflows begin with explicit loading and inspection, followed by careful selection, filtering, type conversion, and validation. Missing values, duplicates, inconsistent categories, invalid dates, and outliers must be interpreted in their business context rather than handled mechanically. Reshaping supports different analytical and presentation needs, while merges require attention to keys and cardinality. Grouped aggregation and transformation create useful business measures, but denominators and units of analysis must be explicit. Reproducibility depends on preserved raw data, documented rules, assertions, reconciliation, and governance. pandas supplies powerful operations, but analytical judgment determines whether the resulting dataset is valid.

Key Terms

DataFrame: A two-dimensional labeled pandas data structure.

Series: A one-dimensional labeled pandas data structure.

Index: The row labels associated with a pandas object.

Tidy data: Data in which variables are columns and observations are rows.

Data wrangling: The process of preparing raw data for analysis.

Missing value: An absent, unknown, or unavailable data value.

Imputation: Replacing or estimating missing values using a stated method.

Duplicate: A repeated record or repeated business event.

Outlier: An unusually distant observation relative to a reference distribution.

Categorical variable: A variable whose values represent defined groups or labels.

Datetime: A data type representing dates and times.

Reshaping: Changing data between wide and long structures.

Melt: A pandas operation that converts selected columns into variable-value rows.

Pivot table: A summarized wide representation formed from grouped data.

Merge: A relational operation that combines tables using key columns.

Join cardinality: The expected relationship between matching keys in two tables.

Aggregation: Combining multiple observations using functions such as sum or mean.

Vectorization: Applying operations to arrays or columns without explicit row loops.

Data lineage: A record of data origins, transformations, and outputs.

Analysis-ready dataset: A validated dataset structured for a defined analytical purpose.

Review Questions

Short Answer Questions

1. What are the principal differences between a pandas Series and a DataFrame?
2. Why should the unit of analysis be defined before duplicates are removed?
3. What is the difference between loc and iloc?
4. Why can a merge unexpectedly increase the number of rows?
5. When might replacing a missing value with zero be inappropriate?

Long Answer Questions

1. Explain a complete pandas workflow for converting a raw transaction file into an analysis-ready dataset.
2. Compare deletion, constant replacement, and statistical imputation as approaches to missing values.
3. Explain the differences among inner, left, right, and outer joins, including suitable business examples.
4. Discuss why outlier detection rules should not automatically result in deleting observations.
5. Explain how reshaping between wide and long formats supports different analytical tasks.

Analytical / Application Questions

1. A customer table contains repeated customer IDs because of historical records. Design a procedure that produces a current customer table and safely merges it with orders.
2. A regional sales report shows revenue 18% higher after a new merge. Identify the likely causes and specify pandas checks that would diagnose the problem.
3. A dataset has missing delivery dates, negative delivery durations, and several date formats. Propose a cleaning and validation strategy, including decisions that require consultation with the business owner.

Multiple Choice Questions with Answers

Question 1

Which pandas structure is two-dimensional and can contain columns with different data types?

A. Series

B. DataFrame

C. Index

D. CategoricalDtype

Correct Answer: B. DataFrame

Question 2

Which argument helps detect an unintended many-to-many merge?

A. fill_value

B. parse_dates

C. validate

D. na_values

Correct Answer: C. validate

Question 3

Which operation converts columns such as January and February into variable-value rows?

A. melt

B. merge

C. drop_duplicates

D. resample

Correct Answer: A. melt

Question 4

What does a left join preserve?

A. Only matching rows from both tables

B. Every row from the right table

C. Every row from the left table

D. Only keys appearing exactly once

Correct Answer: C. Every row from the left table

Question 5

Which pandas expression selects rows where revenue exceeds 500 and region is North or West?

A. orders[(orders['revenue'] > 500) & (orders['region'].isin(['North','West']))]

B. orders[(orders['revenue'] > 500) and (orders['region'].isin(['North','West']))]

C. orders[orders['revenue'] > 500 & orders['region'] == 'North' or 'West']

D. orders.loc['revenue' > 500]

Correct Answer: A. orders[(orders['revenue'] > 500) & (orders['region'].isin(['North','West']))]

Question 6

What is the main purpose of groupby(...).transform() compared with groupby(...).agg()?

A. It always removes duplicate rows

B. It returns values aligned with the original rows

C. It converts dates to strings

D. It performs only joins

Correct Answer: B. It returns values aligned with the original rows

Question 7

Which method is appropriate for converting a text date column into pandas datetime values?

A. pd.to_datetime

B. pd.to_category

C. pd.to_numeric_only

D. pd.reshape

Correct Answer: A. pd.to_datetime

Question 8

If Q₁ = 10 and Q₃ = 18, what is the IQR?

A. 8

B. 14

C. 28

D. 80

Correct Answer: A. 8

Question 9

Why can dropping all rows with any missing value be problematic?

A. It changes every column to text

B. It may remove many valid observations and introduce bias

C. It guarantees duplicate rows

D. It prevents sorting

Correct Answer: B. It may remove many valid observations and introduce bias

Question 10

Which practice best supports reproducible data wrangling?

A. Overwriting the raw source file

B. Using undocumented manual edits

C. Preserving raw data and documenting transformations

D. Deleting all exception records

Correct Answer: C. Preserving raw data and documenting transformations