Data Wrangling with pandas

Introduction

Data rarely arrives in a form that can be analysed immediately. A dataset may contain inconsistent labels, missing observations, duplicate records, incorrect data types, outliers, several tables that must be combined, or dates stored as ordinary text. Data wrangling is the systematic process of importing, inspecting, cleaning, transforming, combining, reshaping and validating data so that it is suitable for analysis.

The Python library pandas provides two central structures for this work:

• A Series is a one-dimensional labelled array.
• A DataFrame is a two-dimensional labelled table whose columns may have different data types.

A DataFrame resembles a spreadsheet or a relational database table, but it also supports programmatic transformation, statistical operations and integration with NumPy, Matplotlib, scikit-learn and other tools. Effective pandas work requires more than memorising functions. It requires understanding data types, index alignment, missingness, relational keys, reproducibility and the consequences of each transformation.

Learning Objectives

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

1. Import tabular and time-series data into pandas.
2. Inspect structure, types, distributions and data quality.
3. Detect and treat missing values and duplicate records.
4. Select, filter, create and transform columns safely.
5. Join data from multiple DataFrames.
6. Reshape and aggregate data for analysis.
7. Construct reproducible and auditable data-wrangling pipelines.
8. Recognise limitations, risks and ethical issues in data preparation.

1. The pandas Data Model

A DataFrame has labelled rows and columns. Its row labels form an index, while its columns form a column index. The index is useful for alignment and time-series operations, but it is not automatically a unique identifier. A DataFrame can contain duplicate index labels unless the analyst explicitly checks for them.

import pandas as pd
import numpy as np

sales = pd.DataFrame({
    "order_id": [101, 102, 103],
    "region": ["East", "West", "East"],
    "amount": [250.0, 180.0, 310.0]
})

The expression sales["amount"] returns a Series. The expression sales[["region", "amount"]] returns a DataFrame. This distinction matters because methods and assignment behaviour can differ.

Important terminology

• Observation/record: one row representing an entity, event or measurement.
• Variable/feature: one column containing a characteristic or measurement.
• Key: a column, or group of columns, used to identify records or match tables.
• Tidy data: each variable is a column, each observation is a row, and each observational unit is a table.
• Long format: repeated measurements are stored in rows.
• Wide format: categories or time points are stored as separate columns.

2. Importing Data

pandas provides readers for common formats. The choice of reader depends on the data source and its structure.

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

Useful read_csv() arguments include:

For example:

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

Import decisions are part of data quality. An identifier such as 00125 should generally be read as a string, not an integer, because converting it to an integer removes leading zeros. Monetary values should be checked for currency and units rather than assumed to be comparable.

When importing very large files, process data in chunks:

totals = []
for chunk in pd.read_csv("large_orders.csv", chunksize=100_000):
    chunk["amount"] = pd.to_numeric(chunk["amount"], errors="coerce")
    totals.append(chunk["amount"].sum())

grand_total = sum(totals)

Chunking reduces memory requirements, although transformations requiring all records—such as a global sort—may need a different strategy.

3. Inspecting and Profiling Data

Inspection should occur before cleaning. It establishes what was actually imported and prevents assumptions from becoming silent errors.

orders.head()
orders.tail()
orders.shape
orders.columns
orders.info()
orders.describe(include="all")
orders.nunique()

shape returns a tuple (rows, columns). info() reveals non-null counts and inferred types. describe() provides numerical summaries and, with include="all", summaries for categorical columns where applicable.

A compact missingness profile is:

profile = pd.DataFrame({
    "missing_count": orders.isna().sum(),
    "missing_rate": orders.isna().mean(),
    "dtype": orders.dtypes.astype(str)
})

For a column with n total records and m missing records, the missing rate is:

Here, m and n are counts, so the result is dimensionless and lies between 0 and 1 when 0 ≤ m ≤ n. Multiplying by 100 expresses it as a percentage.

Inspect categories and suspicious values explicitly:

orders["region"].value_counts(dropna=False)
orders["amount"].sort_values().head()
orders["amount"].sort_values().tail()

A numerical summary is not proof that the data is correct. A negative quantity may be valid for a return, or it may be an entry error. Domain knowledge is required.

4. Selecting, Filtering and Assigning

Use .loc for label-based selection and .iloc for position-based selection.

orders.loc[orders["amount"] > 500, ["order_id", "amount"]]
orders.iloc[0:5, 0:3]

Boolean conditions can be combined with & for AND and | for OR. Each condition must be enclosed in parentheses:

large_east = orders.loc[
    (orders["region"] == "East") & (orders["amount"] > 500)
]

Avoid chained assignment such as:

orders[orders["region"] == "East"]["amount"] = 0

Use .loc instead:

orders.loc[orders["region"] == "East", "amount"] = 0

This makes the target of the assignment explicit and avoids ambiguity about whether a view or copy is being modified.

Create derived columns using transparent expressions:

orders["net_amount"] = orders["amount"] - orders["discount"].fillna(0)
orders["is_large"] = orders["net_amount"] >= 500

For multiple categories, prefer vectorised operations such as np.select() or Series.map() over unnecessarily complex row-wise functions:

conditions = [orders["net_amount"] < 100,
              orders["net_amount"].between(100, 499.99),
              orders["net_amount"] >= 500]
choices = ["small", "medium", "large"]
orders["segment"] = np.select(conditions, choices, default="unknown")

Vectorisation generally improves clarity and performance because operations are applied to arrays rather than invoking Python code once per row.

5. Data Types and Standardisation

Data types determine what operations mean. Common types include integer, floating-point, Boolean, string, categorical and datetime. Check and convert types deliberately:

orders["amount"] = pd.to_numeric(orders["amount"], errors="coerce")
orders["order_date"] = pd.to_datetime(orders["order_date"], errors="coerce")
orders["region"] = orders["region"].astype("string").str.strip().str.title()

errors="coerce" converts invalid values to missing values. This is useful for detection, but it should be followed by an investigation of the newly created missing values.

String cleaning often includes whitespace, case and punctuation standardisation:

orders["email"] = orders["email"].astype("string").str.strip().str.lower()
orders["product"] = orders["product"].astype("string").str.replace(r"\s+", " ", regex=True)

Standardisation must not destroy meaningful distinctions. For example, converting all product codes to lowercase may be inappropriate if codes are case-sensitive.

Categorical data can reduce memory use and clarify permitted values:

orders["region"] = orders["region"].astype("category")

Use categories when the number of distinct values is relatively small and the variable represents a defined set of labels.

6. Missing Values

Missingness may arise from non-response, system failure, inapplicable fields, data integration or deliberate suppression. It is not automatically equivalent to zero, false or “not applicable.”

Detect missing values with isna() and remove or replace them according to the analytical meaning of the field:

orders[orders["customer_id"].isna()]
orders = orders.dropna(subset=["order_id", "order_date"])
orders["discount"] = orders["discount"].fillna(0)
orders["amount"] = orders["amount"].fillna(orders["amount"].median())

Possible strategies include:

A median is often more robust than a mean when a numeric variable is skewed, but neither method is automatically valid. In predictive modelling, imputation parameters should be learned from the training set only; otherwise information from the test set can leak into the model.

7. Duplicate Records and Integrity Checks

Duplicates may represent repeated transactions, legitimate repeated measurements or accidental copies. Detect them before deleting them:

duplicates = orders[orders.duplicated(keep=False)]
orders.duplicated(subset=["order_id"]).sum()

Remove exact duplicates only when business rules support it:

orders = orders.drop_duplicates()

If order_id should be unique, validate that assumption:

assert orders["order_id"].is_unique

Other useful checks include:

assert orders["amount"].ge(0).all()
assert orders["order_date"].notna().all()

Assertions should express documented expectations. If legitimate refunds can be negative, the non-negative assertion would be incorrect and should be replaced by a rule that distinguishes sales from refunds.

8. Transforming and Enriching Data

Common transformations include sorting, renaming, conditional creation, aggregation and date extraction.

orders = orders.rename(columns={"amount": "gross_amount"})
orders = orders.sort_values(["customer_id", "order_date"])
orders["year"] = orders["order_date"].dt.year
orders["month"] = orders["order_date"].dt.to_period("M")

The groupby() operation follows the split–apply–combine framework: split records into groups, apply a function, and combine results.

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

For a set of values x₁, x₂, …, xₙ, the arithmetic mean is:

where n is the number of non-missing values included by the operation and x̄ has the same units as xᵢ. The mean is appropriate only when the selected observations and missing-value treatment support that interpretation.

A weighted mean, useful when observations represent different quantities, is:

where wᵢ is a non-negative weight and the denominator is non-zero. The weight must be defined consistently—for example, units sold when calculating an average selling price.

transform() returns group-level information aligned to the original rows:

orders["customer_total"] = (
    orders.groupby("customer_id")["net_amount"].transform("sum")
)
orders["customer_share"] = orders["net_amount"] / orders["customer_total"]

9. Joining DataFrames

Combining tables requires explicit keys and an understanding of cardinality. Consider customer data and order data joined by customer_id.

combined = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one"
)

A left join keeps every row from the left table. Other joins are:

• Inner join: retains keys present in both tables.
• Right join: retains every row from the right table.
• Outer join: retains all keys from both tables.

The validate argument is a valuable safeguard. many_to_one means many order rows may match one customer row. If the customer table contains duplicate IDs, the merge fails rather than silently multiplying orders.

An illustrative example: suppose the orders table contains three rows for customer A and two rows for customer B. If the customer table contains one row per customer, a many-to-one merge remains five rows. If customer A appears twice in the customer table, the three A orders may become six rows. This is a join explosion and can inflate revenue totals.

Always inspect unmatched keys:

check = orders.merge(customers, on="customer_id", how="left", indicator=True)
check["_merge"].value_counts()

Use merge_asof() for ordered time-based matching, such as assigning the latest available exchange rate to a transaction. Both inputs must be sorted according to the relevant key and time column.

10. Reshaping Data

pivot() converts long data to wide form when each index–column combination is unique:

wide = orders.pivot(
    index="order_date", columns="region", values="net_amount"
)

If combinations are repeated, use pivot_table() with an aggregation function:

summary = orders.pivot_table(
    index="month",
    columns="region",
    values="net_amount",
    aggfunc="sum",
    fill_value=0
)

melt() converts wide data to long form:

long = wide.reset_index().melt(
    id_vars="order_date",
    var_name="region",
    value_name="revenue"
)

Long format is often convenient for visualisation and statistical modelling because one column consistently represents a variable and another represents its value. Wide format can be convenient for reports and some matrix operations.

11. Time-Series Wrangling

Dates should be parsed as datetimes, not treated as arbitrary strings:

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

Time zones matter when data originates in different locations. Converting all timestamps to UTC can provide a consistent storage standard, while local time can be used for presentation.

Resampling changes the time frequency and applies an aggregation:

daily = orders["net_amount"].resample("D").sum()
monthly = orders["net_amount"].resample("MS").sum()

A rolling mean over a window of k observations is:

For the first observations, fewer than k values are available unless a minimum window is specified. In pandas:

monthly_rolling = monthly.rolling(window=3, min_periods=1).mean()

A rolling statistic describes the selected window; it does not prove causation or remove all seasonality. Frequency, time-zone, missing-date and boundary decisions should be documented.

12. Reproducible Pipelines

A pipeline is an ordered sequence of transformations that can be rerun on new data. It should be deterministic where possible, explicit about assumptions and separated into stages such as ingestion, validation, cleaning, transformation and output.

def prepare_orders(path):
    df = pd.read_csv(
        path,
        dtype={"order_id": "string", "customer_id": "string"},
        parse_dates=["order_date"],
        na_values=["", "NA", "N/A"]
    )

    df = (
        df.drop_duplicates()
          .assign(
              region=lambda x: x["region"].astype("string").str.strip().str.title(),
              amount=lambda x: pd.to_numeric(x["amount"], errors="coerce")
          )
          .dropna(subset=["order_id", "order_date", "amount"])
    )

    if (df["amount"] < 0).any():
        raise ValueError("Unexpected negative order amount")
    if not df["order_id"].is_unique:
        raise ValueError("Order IDs are not unique")

    return df

Method chaining can improve readability when each step is short and named. For complex projects, separate functions, tests, configuration files and version-controlled notebooks are preferable. Record source-file names, extraction dates, schema assumptions, row counts and changes in key variables.

A reproducible analytical workflow should also:

1. Keep raw data unchanged.
2. Store cleaned outputs separately.
3. Avoid manually editing exported spreadsheets.
4. Pin or document package versions.
5. Test expected row counts, keys, ranges and types.
6. Log warnings and rejected records.
7. Protect credentials and sensitive data.

Applied Case: Retail Order Consolidation

Background

A fictional retailer receives monthly order files from regional systems and maintains a separate customer master table. Management wants monthly revenue by region and customer segment.

Problem

The files use inconsistent region capitalisation, contain blank discounts, include repeated rows from a file-retry process and store dates in mixed formats. A direct total would be unreliable.

Analysis and approach

The analyst first imports the files with explicit string types for identifiers and parses dates. A profile shows that blank discounts mean “no discount,” while missing customer IDs cannot be safely assigned. Exact duplicates are identified and removed after confirming that the source system can resend identical rows. Regions are stripped and standardised. The customer table is checked for unique customer_id values before a many-to-one merge.

orders = pd.read_csv(
    "regional_orders.csv",
    dtype={"order_id": "string", "customer_id": "string"},
    na_values=["", "NA", "unknown"]
)
orders["order_date"] = pd.to_datetime(orders["order_date"], errors="coerce")
orders["discount"] = pd.to_numeric(orders["discount"], errors="coerce").fillna(0)
orders["gross_amount"] = pd.to_numeric(orders["gross_amount"], errors="coerce")
orders["region"] = orders["region"].astype("string").str.strip().str.title()
orders = orders.drop_duplicates()
orders = orders.dropna(subset=["order_id", "customer_id", "order_date", "gross_amount"])
orders["net_amount"] = orders["gross_amount"] - orders["discount"]

customers = pd.read_csv("customers.csv", dtype={"customer_id": "string"})
if not customers["customer_id"].is_unique:
    raise ValueError("Customer master is not unique")

orders = orders.merge(
    customers[["customer_id", "segment"]],
    on="customer_id",
    how="left",
    validate="many_to_one"
)

orders["month"] = orders["order_date"].dt.to_period("M")
report = (
    orders.groupby(["month", "region", "segment"], dropna=False,
                   as_index=False)
          .agg(revenue=("net_amount", "sum"),
               order_count=("order_id", "nunique"))
)

Outcome

The resulting report is traceable: raw rows are preserved, rejected records can be reviewed, duplicate handling is documented, the join cardinality is validated and the aggregation is defined clearly. The nunique count prevents repeated line items with the same order ID from being counted as separate orders.

Learning points

The most important lesson is that a correct pandas expression cannot compensate for an incorrect data model. Keys, units, missing-value meanings and business rules must be established before aggregation. Validation should be treated as part of analysis rather than as an optional final check.

13. Limitations, Ethics and Governance

pandas is powerful for files that fit reasonably into memory, but it is not always the best tool for distributed or very large data. Alternatives include SQL databases, Dask, Polars or Spark, depending on scale and organisational requirements. Moving to another system does not remove the need for schema validation and quality controls.

Cleaning can introduce ethical risks. Deleting missing records may disproportionately remove observations from a vulnerable group. Imputation may conceal unequal data collection. Standardising names may accidentally merge distinct identities. Joining tables can create sensitive profiles even when each source seemed harmless in isolation.

Professional practice requires data minimisation, access control, appropriate retention, documentation, lawful processing and protection of personally identifiable information. Do not expose customer names, addresses or identifiers in diagnostic output unnecessarily. When preparing data for a model, investigate whether cleaning choices change representation across demographic or operational groups.

AI-assisted coding can suggest pandas transformations, but generated code must be reviewed. Particular risks include incorrect join types, silent coercion of invalid values, leakage during imputation and inappropriate deletion of records. Tests and domain review remain necessary.

Best-Practice Checklist

• Inspect before transforming.
• Preserve raw data.
• Make types and units explicit.
• Treat identifiers as identifiers, not measurements.
• Define the meaning of missing values.
• Validate uniqueness before joining.
• Use validate in merges where possible.
• Check row counts before and after major operations.
• Prefer vectorised, readable transformations.
• Document assumptions and business rules.
• Test pipelines with representative edge cases.
• Review privacy, fairness and governance implications.

Chapter Summary

Data wrangling with pandas converts imperfect source data into structured, analysable information. A DataFrame combines labelled rows and columns, while pandas operations support importing, inspection, type conversion, missing-value treatment, duplicate handling, filtering, feature creation, grouping, joining, reshaping and time-series analysis. Reliable work begins with profiling and domain understanding. Missing values and duplicates require decisions based on meaning rather than convenience. Joins must be controlled through keys, cardinality and validation because an unintended many-to-many merge can inflate results. Reshaping and aggregation should preserve the intended observational unit. Finally, reproducible pipelines should retain raw data, make assumptions explicit, validate outputs and address privacy, fairness and security.

Key Terms

DataFrame: A two-dimensional labelled pandas data structure.

Series: A one-dimensional labelled pandas data structure.

Index: Labels used to identify and align DataFrame rows.

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

Tidy data: Data organised so variables are columns and observations are rows.

Key: A field or set of fields used to identify or match records.

Missing value: An absent, unknown or unavailable observation.

Imputation: Replacing missing values using a defined rule or model.

Duplicate: A repeated record or repeated key occurrence.

Vectorisation: Applying array operations without explicit row-by-row Python loops.

GroupBy: A split–apply–combine operation for grouped analysis.

Aggregation: Summarising multiple records using functions such as sum or mean.

Merge: A relational operation that combines DataFrames using matching keys.

Join cardinality: The relationship between matching records in two tables.

Pivot: A transformation from long data to wide data.

Melt: A transformation from wide data to long data.

Resampling: Changing the frequency of time-indexed observations.

Data leakage: Unintended use of information that would not be available at analysis or prediction time.

Pipeline: An ordered, repeatable sequence of data-processing steps.

Data validation: Tests that check whether data meets expected rules.

Review Questions

Short Answer Questions

1. What are the principal differences between a pandas Series and a DataFrame?
2. Why should identifier columns often be imported as strings?
3. What is the difference between .loc and .iloc?
4. Why can an uncontrolled many-to-many merge produce incorrect totals?
5. State two reasons why missing values should not automatically be replaced with zero.

Long Answer Questions

1. Explain a systematic workflow for importing, inspecting and cleaning a CSV file in pandas.
2. Compare deletion, constant replacement, median imputation and group-wise imputation for missing values.
3. Explain the split–apply–combine framework and illustrate how groupby().agg() supports reporting.
4. Compare inner, left and outer joins, including situations in which each may be appropriate.
5. Discuss how reproducibility, privacy and fairness should influence a professional data-wrangling pipeline.

Analytical / Application Questions

1. An orders table has 10,000 rows before a customer merge and 13,500 rows afterward. Propose diagnostic checks to determine whether the increase is legitimate or caused by duplicate customer keys.
2. A monthly revenue series contains missing months and several extreme values. Design a pandas workflow to inspect, resample and calculate a rolling summary without concealing data-quality problems.
3. A predictive model uses customer income, but 35% of incomes are missing mostly for one demographic group. Recommend a cleaning and validation strategy, and explain the ethical risks of a simple overall median imputation.

Multiple Choice Questions with Answers

Question 1

Which pandas structure is designed for two-dimensional labelled tabular data?

A. Series

B. DataFrame

C. Index

D. Scalar

Correct Answer: B. DataFrame

Question 2

Which argument can convert invalid numeric strings to missing values when using pandas numeric conversion?

A. errors="ignore"

B. invalid="missing"

C. errors="coerce"

D. na_action="drop"

Correct Answer: C. errors="coerce"

Question 3

Which operation is most appropriate for selecting rows where amount exceeds 500 and region is East?

A. Use a Boolean mask with parentheses and &

B. Use or between the two conditions

C. Use .iloc with column names

D. Use drop_duplicates()

Correct Answer: A. Use a Boolean mask with parentheses and &

Question 4

What does validate="many_to_one" in merge() help check?

A. That both tables have the same number of rows

B. That every left key matches a right key

C. That many left records match at most one right record

D. That all columns have numeric types

Correct Answer: C. That many left records match at most one right record

Question 5

Which pandas method converts wide data into long form?

A. pivot

B. melt

C. resample

D. merge

Correct Answer: B. melt

Question 6

What is the principal danger of deleting all rows containing any missing value?

A. It always creates duplicate rows

B. It can cause information loss and selection bias

C. It changes strings into dates

D. It guarantees data leakage

Correct Answer: B. It can cause information loss and selection bias

Question 7

Which operation returns group-level values aligned with the original rows?

A. groupby().transform()

B. dropna()

C. pivot()

D. drop_duplicates()

Correct Answer: A. groupby().transform()

Question 8

A table has 200 records, of which 30 have missing customer IDs. What is the missing rate?

A. 6.7%

B. 15%

C. 30%

D. 170%

Correct Answer: B. 15%

Question 9

Which operation is intended to aggregate a time-indexed Series by calendar frequency?

A. resample()

B. astype()

C. duplicated()

D. melt()

Correct Answer: A. resample()

Question 10

Why should raw data normally be preserved unchanged in a reproducible workflow?

A. To prevent any validation from occurring

B. To make manual spreadsheet editing easier

C. To provide an auditable source for repeating and checking transformations

D. To ensure all columns become categorical

Correct Answer: C. To provide an auditable source for repeating and checking transformations