10. Data Wrangling with pandas

Introduction and Context

Machine-learning systems are only as reliable as the data supplied to them. In practice, data rarely arrives as a clean matrix ready for modelling. It may be distributed across spreadsheets, databases, application programming interfaces, log files, and semi-structured documents. Columns may have inconsistent names, dates may use different formats, categorical values may contain spelling variations, and missing observations may be confused with legitimate zero values. Before modelling begins, these irregularities must be identified and addressed systematically.

Data wrangling is the process of acquiring, inspecting, cleaning, transforming, combining, and restructuring data so that it becomes suitable for analysis or machine learning. In Python, pandas is the principal library for working with labelled, tabular data. It provides data structures and operations for filtering, joining, grouping, reshaping, time-series analysis, and missing-value treatment. More importantly, it supports an analytical way of thinking: each transformation should be explicit, testable, explainable, and reproducible.

For postgraduate AI and ML students, pandas is not merely a programming utility. It is an important component of the data-preparation layer in an end-to-end machine-learning pipeline. Decisions made during wrangling influence feature quality, model validity, fairness, computational cost, and the interpretation of results.

Learning Objectives

After completing this chapter, the learner should be able to:

• explain the role of pandas in data-wrangling workflows;
• distinguish between Series, DataFrame, indexes, and data types;
• acquire, inspect, select, filter, and validate tabular data;
• handle missing values, duplicates, inconsistent categories, and outliers;
• transform numerical, categorical, textual, and date-time variables;
• join, concatenate, aggregate, and reshape datasets;
• construct reproducible and leakage-resistant preprocessing pipelines;
• evaluate the professional, ethical, and operational implications of data-cleaning decisions.

The pandas Data Model

The central pandas object is the DataFrame, a two-dimensional labelled table whose rows and columns may have different data types. A column of a DataFrame is a Series, a one-dimensional labelled array. Labels are stored in an Index, which identifies rows and supports alignment during operations.

```python

import pandas as pd

import numpy as np

sales = pd.DataFrame({

"customer_id": [101, 102, 103],

"region": ["North", "South", "North"],

"amount": [250.0, 180.0, 310.0]

})

sales["amount"] # Series

sales.shape # (rows, columns)

sales.dtypes # data types

sales.index # row labels

```

Pandas aligns data by labels rather than only by position. This is powerful but can produce unexpected results if indexes are not understood. For example, adding two Series with different index labels aligns matching labels and produces missing values where labels do not overlap. Consequently, an analyst should distinguish carefully between positional operations and label-based operations.

Modern pandas supports explicit data types such as `string`, nullable integer types such as `Int64`, `boolean`, `category`, and date-time types. Appropriate types reduce memory use, improve validation, and communicate the intended meaning of a variable. A column containing customer identifiers should not automatically be treated as a numerical quantity merely because its values are integers.

A Disciplined Wrangling Workflow

A robust workflow usually follows a cycle rather than a one-time sequence:

1. acquire and preserve the raw data;

2. inspect structure, types, ranges, and quality;

3. define data-quality rules;

4. clean and standardize values;

5. transform variables into analytical form;

6. combine and reshape datasets;

7. validate assumptions and outputs;

8. document and save the reproducible process.

The raw input should normally be retained unchanged. Cleaning should produce a new analytical dataset or a documented transformation layer. Overwriting the original file makes it difficult to audit decisions, reproduce results, or investigate errors.

Reading and Inspecting Data

Pandas supports common sources through functions such as `read_csv`, `read_excel`, `read_json`, and `read_sql`. Loading is itself a data-quality decision. For example, dates should be parsed deliberately rather than left as arbitrary strings.

```python

orders = pd.read_csv(

"orders.csv",

parse_dates=["order_date"],

na_values=["", "NA", "N/A", "unknown"]

)

orders.head()

orders.info()

orders.describe(include="all")

orders.isna().mean().sort_values(ascending=False)

orders.nunique()

```

`head()` and `tail()` provide a quick view, while `info()` reveals non-null counts and data types. `describe()` gives statistical summaries, although numerical summaries alone cannot reveal all quality problems. `nunique()` helps detect unexpected cardinality, and `value_counts()` exposes inconsistent category labels.

```python

orders["status"].value_counts(dropna=False)

orders["amount"].quantile([0.01, 0.5, 0.99])

```

Inspection should answer questions such as: Are row identifiers unique? Are amounts non-negative? Do dates fall within the expected period? Are categorical values drawn from an approved set? Are numeric columns accidentally stored as strings?

Selecting, Filtering, and Assigning

Column selection can use labels directly or through `loc` and `iloc`. `loc` is label-based; `iloc` is position-based.

```python

recent = orders.loc[

(orders["order_date"] >= "2024-01-01") &

(orders["status"].eq("completed")),

["customer_id", "order_date", "amount"]

]

```

Parentheses are required around individual Boolean conditions. The operators `&`, `|`, and `~` are used for element-wise logical operations. Using Python's `and` or `or` with a Series produces an error because a whole Series cannot be interpreted as a single Boolean value.

New variables should generally be created with explicit assignments. The `assign()` method supports readable transformation chains:

```python

clean_orders = (

orders

.assign(

amount=lambda d: pd.to_numeric(d["amount"], errors="coerce"),

order_month=lambda d: d["order_date"].dt.to_period("M")

)

)

```

Chained indexing such as `df[df["amount"] > 0]["region"] = "North"` is unsafe because it may modify a temporary view rather than the original object. Use `.loc` for intentional assignment.

Cleaning Imperfect Data

Missing Values

Missingness has several meanings. A value may be absent because it was not collected, not applicable, unavailable at the time, or deliberately withheld. Treating all missing values identically can introduce bias. Missingness should therefore be examined both statistically and substantively.

```python

orders.isna().sum()

orders[orders["customer_id"].isna()]

```

Common strategies include deletion, imputation, explicit category creation, and model-based treatment. Dropping rows may be reasonable when only a small number are missing and the missingness is plausibly random. It is risky when missing cases are systematically different.

```python

orders = orders.dropna(subset=["customer_id", "amount"])

orders["discount"] = orders["discount"].fillna(0)

orders["region"] = orders["region"].fillna("Unknown")

orders["income"] = orders["income"].fillna(orders["income"].median())

```

Mean imputation is sensitive to extreme values; median imputation is often more robust for skewed numerical variables. In machine learning, imputation parameters must be learned from the training set only. Computing a median from the complete dataset before splitting can leak information from the validation or test set.

Pandas' `interpolate()` can be suitable for ordered measurements such as sensor readings, but interpolation is not automatically appropriate for arbitrary business records. Forward-fill and backward-fill also require a meaningful temporal or logical order.

Duplicates and Integrity Rules

Duplicate rows may represent repeated transactions, accidental ingestion, or legitimate repeated events. The correct treatment depends on the business key.

```python

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

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

```

Before removing duplicates, determine whether the selected key should be unique. A customer can have many orders; therefore, `customer_id` is not necessarily a valid uniqueness key for an order table. Integrity checks should be explicit:

```python

assert orders["order_id"].is_unique

assert (orders["amount"] >= 0).all()

assert orders["status"].isin(["pending", "completed", "cancelled"]).all()

```

Assertions can be supplemented with formal validation tools and data-quality reports in production systems.

Standardising Text and Categories

Categorical inconsistencies often arise from capitalization, whitespace, abbreviations, or spelling variation.

```python

orders["region"] = (

orders["region"].astype("string")

.str.strip()

.str.lower()

.replace({"n": "north", "nrth": "north", "s": "south"})

)

```

A mapping should be based on domain knowledge rather than blindly merging similar-looking values. For example, “US” and “United States” may refer to the same category, whereas “UK” and “Ukraine” must not be merged merely because of a superficial similarity. The `category` dtype can improve memory efficiency when a column has a limited, repeated vocabulary, but categories should be assigned only after the vocabulary has been validated.

Numerical Conversion and Outliers

Data imported from files may contain currency symbols, commas, or invalid text. `to_numeric` makes conversion explicit.

```python

orders["amount"] = pd.to_numeric(

orders["amount"].astype("string").str.replace(",", "", regex=False),

errors="coerce"

)

```

An outlier is an observation unusually distant from the rest of a distribution. It may be an error, a rare but valid event, or evidence of a different process. Outliers should not be deleted solely because they are inconvenient for a model.

The interquartile range rule identifies observations below Q1 − 1.5 IQR or above Q3 + 1.5 IQR. A z-score rule may be useful for approximately symmetric distributions, but neither rule establishes that a value is erroneous. Alternatives include correcting source errors, transforming a skewed variable, winsorising extreme values, using robust statistics, or retaining the observation with an explanatory flag.

```python

q1 = orders["amount"].quantile(0.25)

q3 = orders["amount"].quantile(0.75)

iqr = q3 - q1

flag = (orders["amount"] < q1 - 1.5 iqr) | (orders["amount"] > q3 + 1.5 iqr)

orders["amount_outlier"] = flag

```

Transformation for Analysis and Machine Learning

Transformations convert cleaned data into meaningful analytical variables. Date-time data should be parsed before extracting features.

```python

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

orders["year"] = orders["order_date"].dt.year

orders["weekday"] = orders["order_date"].dt.dayofweek

orders["is_weekend"] = orders["weekday"].isin([5, 6])

```

A date-time index supports resampling and rolling calculations:

```python

monthly_sales = (

orders.set_index("order_date")["amount"]

.resample("ME")

.sum()

)

rolling_sales = monthly_sales.rolling(3, min_periods=1).mean()

```

Time-series operations require attention to ordering, frequency, time zones, and leakage. A rolling feature used to predict an event must be calculated only from information available before that event.

Categorical variables may be represented through one-hot encoding, ordinal encoding, target encoding, or learned embeddings. One-hot encoding is appropriate when categories have no natural order. Ordinal encoding may be suitable when an ordered relationship is genuine, such as “low”, “medium”, and “high”, but assigning arbitrary numbers to nominal categories can mislead a model. High-cardinality categories require particular care because one-hot expansion can increase dimensionality and target encoding can leak outcome information if performed improperly.

Text operations can derive useful features such as string length, token counts, or standardized identifiers. However, sensitive information should not be extracted merely because it is technically available.

Combining Tables: Concatenation, Joins, and Validation

Real projects commonly require the integration of multiple tables. `concat` stacks objects vertically or horizontally, while `merge` joins tables using one or more keys.

```python

all_orders = pd.concat([orders_january, orders_february], ignore_index=True)

enriched = orders.merge(

customers[["customer_id", "segment"]],

on="customer_id",

how="left",

validate="many_to_one"

)

```

The `how` argument determines whether unmatched records are retained: `inner` keeps matching keys only, `left` preserves all rows from the left table, `right` preserves all rows from the right table, and `outer` retains the union of keys. The `validate` argument is an important safeguard. A `many_to_one` relationship asserts that many orders may belong to one customer but that each customer key occurs at most once in the customer table.

Joining without checking key uniqueness can silently multiply rows. This is one of the most serious pandas errors because the resulting table may appear plausible while totals and model estimates become distorted. After a join, compare row counts, unmatched-key counts, and aggregate totals.

Aggregation and Reshaping

The split-apply-combine model divides data into groups, applies a calculation, and combines the results.

```python

summary = (

orders.groupby("region", as_index=False)

.agg(

orders=("order_id", "nunique"),

revenue=("amount", "sum"),

average_order=("amount", "mean")

)

)

```

Named aggregation makes the output self-describing. Grouping can involve multiple keys, and `transform` is useful when a group-level statistic must be returned to the original row structure:

```python

orders["customer_mean"] = orders.groupby("customer_id")["amount"].transform("mean")

orders["relative_to_customer"] = orders["amount"] / orders["customer_mean"]

```

Reshaping changes the orientation of data. Long format stores one observation per row and is generally convenient for analysis; wide format places categories or time periods into columns and may be convenient for reporting.

```python

wide = orders.pivot_table(

index="customer_id",

columns="order_month",

values="amount",

aggfunc="sum",

fill_value=0

)

long = wide.reset_index().melt(

id_vars="customer_id",

var_name="order_month",

value_name="amount"

)

```

`pivot` requires unique index-column combinations, whereas `pivot_table` can aggregate duplicates. `melt` is valuable when a report-like wide table must be converted to a tidy analytical form.

Reproducible Data Pipelines

A professional wrangling script should separate acquisition, validation, transformation, and output. Functions are preferable to a long sequence of hidden notebook state. Method chaining can make the order of operations visible, but it should not sacrifice readability.

```python

def prepare_orders(path):

df = pd.read_csv(path, parse_dates=["order_date"])

df = (

df.assign(

status=lambda x: x["status"].astype("string").str.strip().str.lower(),

amount=lambda x: pd.to_numeric(x["amount"], errors="coerce")

)

.dropna(subset=["order_id", "amount"])

.drop_duplicates("order_id")

)

if not df["order_id"].is_unique:

raise ValueError("Order identifiers must be unique")

return df

```

Reproducibility requires recording source versions, assumptions, software versions, parameter choices, and the date of processing. A pipeline should be idempotent where possible: running it twice on the same input should produce the same output. Random operations should use controlled seeds, and transformations learned from data should be fitted only on appropriate training partitions.

For larger datasets, pandas may be combined with SQL, distributed processing frameworks, columnar formats such as Parquet, or workflow orchestration systems. The conceptual principles remain the same: preserve lineage, validate contracts, and make transformations observable.

Applied Case: Preparing Retail Transactions for Demand Forecasting

Background

Consider an illustrative retail company, Meridian Stores, which wants to forecast weekly product demand. Transaction records are exported from point-of-sale systems, while product attributes and store information are maintained in separate tables.

Situation and Problem

The transaction file contains dates in mixed formats, amounts stored as strings, duplicate receipts caused by an export retry, missing product identifiers, and region names with inconsistent capitalization. A direct aggregation produces implausible sales totals. The forecasting team must prepare a reliable weekly dataset without using future information.

Analysis

The team first preserves the raw extracts and profiles each table. It discovers that receipt identifiers should be unique within a store, not necessarily globally. A merge with the product table produces duplicate transaction rows because some products have multiple active attribute records. Missing product identifiers cannot be safely imputed from sales amount, so those records are retained for financial reconciliation but excluded from product-level forecasting.

The team also identifies an extreme transaction amount. Investigation shows that it is a legitimate bulk purchase, not a data-entry error. Removing it would reduce reported revenue and distort demand estimates. Instead, the record is retained and a bulk-purchase indicator is created.

Approach and Solution

The pipeline performs the following operations:

• parses dates with invalid values converted to missing and reports their count;
• standardizes region labels through an approved mapping table;
• converts amounts to numeric values and checks for negative transactions;
• removes only exact duplicate export records;
• validates the store-receipt key;
• selects the single current product record before joining;
• aggregates transactions by product, store, and week;
• sorts by time and creates lag features using historical observations only;
• splits training and evaluation periods chronologically.

A lag feature is created conceptually as follows:

```python

weekly = weekly.sort_values(["product_id", "store_id", "week"])

weekly["previous_week_demand"] = (

weekly.groupby(["product_id", "store_id"])["demand"].shift(1)

)

```

The use of `shift(1)` ensures that the feature for a week is based on the previous week rather than on the target week itself. The final dataset is accompanied by quality reports showing row counts before and after each stage, unmatched product keys, missingness, and aggregate reconciliation totals.

Outcome

The resulting dataset is suitable for modelling because it has a defined grain—one row per product, store, and week—validated joins, documented exclusions, and leakage-resistant temporal features. The process also gives finance and operations teams a traceable explanation of how reported totals relate to modelling data.

Learning Points

The case illustrates that data preparation is not simply the removal of inconvenient records. It involves defining the unit of analysis, understanding source-system behaviour, validating relational assumptions, preserving legitimate extremes, and respecting the temporal boundary between available information and future outcomes.

Common Errors, Risks, and Limitations

Several mistakes recur in pandas projects. Treating identifiers as measurements can lead to inappropriate statistics. Filling every missing numerical value with zero can convert unknown quantities into false observations. Dropping all outliers can remove important minority cases. Using `inplace=True` does not solve conceptual errors and can make complex code harder to reason about. Silent type coercion may convert invalid values into missing values without adequate monitoring.

Data leakage is especially dangerous in machine learning. Leakage occurs when information unavailable at prediction time is used to construct a feature or preprocessing parameter. Examples include imputing with statistics calculated from the complete dataset, using future transactions in a rolling average, and target-encoding categories before a proper split.

Pandas also has limits. Very large datasets may exceed memory, and repeated row-wise `apply` operations can be slow. Vectorized operations, appropriate dtypes, chunked reading, columnar storage, SQL pushdown, or distributed alternatives may be preferable. Performance improvements should not undermine validation or clarity.

Ethical, Governance, and Professional Considerations

Cleaning decisions can alter whose data remains visible. If missing records are concentrated among particular demographic or geographic groups, deletion or imputation may create unequal effects. Analysts should document missingness patterns, exclusions, and the rationale for transformations. Sensitive attributes must be protected according to organizational policy and applicable privacy law. Personally identifiable information should be minimized, access-controlled, and removed when unnecessary for the analytical purpose.

Data lineage and versioning support auditability. A model governance review should be able to identify the source files, transformation logic, data-quality exceptions, and responsible decision-makers. Reproducibility is therefore not only a technical convenience but also a professional and governance requirement.

Best Practices

A dependable pandas workflow generally follows these principles:

• preserve raw data and write transformations as code;
• define the grain and keys of every table before joining;
• inspect types, missingness, distributions, and cardinality;
• use explicit validation rules and post-transformation checks;
• distinguish errors from rare but valid observations;
• fit data-dependent transformations on training data only;
• prefer readable, testable functions over opaque notebook state;
• record assumptions, exclusions, versions, and output schemas;
• monitor row counts and aggregate totals after joins and reshaping;
• choose tools according to data volume, latency, and operational requirements.

Current Developments and Emerging Practice

Pandas workflows increasingly operate within broader data ecosystems. Data contracts specify expected schemas and quality constraints between producers and consumers. Pipeline orchestration and automated data-quality testing make failures visible before model training. Columnar formats and copy-on-write memory behaviour improve efficiency for analytical workloads. Interoperability with libraries such as NumPy, scikit-learn, PyArrow, and alternative dataframe engines allows teams to move between exploratory analysis and production-scale processing.

The important development is not a particular function or library feature but the movement from informal cleaning toward governed, observable, and reproducible data products. Automated systems can suggest mappings, detect anomalies, or generate profiling reports, but domain experts must still determine whether a transformation is substantively justified.

Chapter Summary

Data wrangling with pandas transforms imperfect raw data into structured evidence for analysis and machine learning. DataFrames, Series, indexes, and explicit data types provide the basic data model. A sound workflow begins with acquisition and inspection, followed by documented treatment of missing values, duplicates, inconsistent categories, invalid types, and outliers. Transformations create date-time, numerical, categorical, and historical features, while joins and aggregations integrate information across tables. Reshaping operations move between long and wide representations.

Reliable practice requires validation of keys, row counts, distributions, and business rules. Machine-learning workflows must prevent leakage by learning preprocessing parameters only from appropriate training data and by respecting temporal order. Professional data preparation also includes lineage, privacy, fairness, and reproducibility. Pandas is powerful for in-memory tabular analysis, but its use should be matched to data volume and operational context.

Key Terms

DataFrame: A two-dimensional labelled pandas data structure containing rows and columns.

Series: A one-dimensional labelled pandas data structure, commonly representing a column.

Index: The label structure used to identify and align rows or columns.

Data wrangling: The process of preparing raw data for analysis through inspection, cleaning, transformation, and restructuring.

Missing value: An observation for which the intended value is unavailable, undefined, or not recorded.

Imputation: Replacing missing values using a rule, statistic, or predictive method.

Duplicate: A repeated record or repeated key that may arise from source-system or processing behaviour.

Categorical variable: A variable whose values represent membership in discrete groups.

Outlier: An observation unusually distant from the pattern of the remaining data.

Data leakage: The use of information during preparation or modelling that would not be available at prediction time.

Join: An operation that combines tables using related keys.

Concatenation: Stacking pandas objects along rows or columns.

Aggregation: Computing summaries such as sums, counts, or means over groups.

Transformation: A systematic operation that changes values or structure into an analytical form.

Reshaping: Changing data between wide and long structural representations.

Resampling: Changing the time frequency of time-indexed data, often with aggregation.

Cardinality: The number of distinct values in a variable or key.

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

Data contract: An explicit agreement about a dataset's schema, meaning, and quality expectations.

Review Questions

Short Answer Questions

1. Distinguish between a pandas Series and a DataFrame.

2. Why should raw input data normally be preserved unchanged?

3. Explain the difference between `loc` and `iloc`.

4. What is the purpose of the `validate` parameter in `merge`?

5. Why can filling every missing numerical value with zero be misleading?

Long Answer Questions

1. Describe a complete, reproducible pandas data-wrangling workflow from data acquisition to validated output.

2. Explain alternative strategies for handling missing values and discuss how their suitability depends on the meaning of missingness.

3. Discuss how joins, grouping, and reshaping support the construction of machine-learning datasets.

4. Explain outlier detection and evaluate why an outlier should not automatically be deleted.

5. Discuss data leakage in preprocessing, using temporal features and imputation as examples.

Analytical / Application Questions

1. A customer table contains repeated customer IDs, while an order table contains multiple orders per customer. Design a pandas merge strategy that avoids accidental row multiplication and includes appropriate validation.

2. A forecasting dataset contains weekly sales and a three-week rolling mean. Explain how you would calculate the feature so that sales from the prediction week and later weeks cannot enter the calculation.

3. A transaction dataset has region labels “North”, “ north ”, “N”, and “Nrth”. Propose a cleaning and validation process, and identify risks of automatically merging categories without domain confirmation.

22. Multiple Choice Questions with Answers

Question 1

Which pandas operation is most appropriate for returning a group-level statistic to every original row?

A. pivot

B. groupby with transform

C. drop_duplicates

D. concat

Correct Answer: B. groupby with transform

Question 2

A left merge unexpectedly increases the number of order rows substantially. What is the most likely cause?

A. The left table contains a date column

B. The join key is numeric

C. The right table contains duplicate values for a supposedly unique key

D. The merge uses lowercase column names

Correct Answer: C. The right table contains duplicate values for a supposedly unique key

Question 3

Why should an imputation median used in a machine-learning pipeline be calculated from the training set only?

A. To reduce the number of columns

B. To prevent information leakage from validation or test data

C. To convert the column to categorical type

D. To guarantee that no values are missing

Correct Answer: B. To prevent information leakage from validation or test data

Question 4

Which operation converts a report-like wide table into a long analytical format?

A. melt

B. merge

C. resample

D. duplicated

Correct Answer: A. melt

Question 5

Which statement best describes a legitimate outlier?

A. Every outlier is a data-entry error

B. An outlier may represent a rare but valid event

C. An outlier must always be replaced by the mean

D. An outlier can be identified without considering context

Correct Answer: B. An outlier may represent a rare but valid event

Question 6

What does `validate="many_to_one"` in a pandas merge assert?

A. Each left key occurs once and each right key occurs many times

B. Both tables have identical row counts

C. Many left-side rows may match one right-side row, with right keys unique

D. Only unmatched rows will be retained

Correct Answer: C. Many left-side rows may match one right-side row, with right keys unique

Question 7

Which expression is most suitable for filtering rows where status is completed and amount is positive?

A. df[df.status == 'completed' and df.amount > 0]

B. df[(df['status'] == 'completed') & (df['amount'] > 0)]

C. df[df['status'] = 'completed' & df['amount'] > 0]

D. df.loc['completed', 'amount' > 0]

Correct Answer: B. df[(df['status'] == 'completed') & (df['amount'] > 0)]

Question 8

Why is `shift(1)` useful when constructing a time-series forecasting feature?

A. It alphabetically sorts the data

B. It uses the previous observation and can help avoid using the target period

C. It removes all missing values

D. It converts dates into categories

Correct Answer: B. It uses the previous observation and can help avoid using the target period

Question 9

Which practice best supports reproducibility?

A. Manually editing the final CSV after each run

B. Overwriting the raw input file

C. Recording transformation logic, assumptions, versions, and validation results

D. Using hidden notebook state to store intermediate variables

Correct Answer: C. Recording transformation logic, assumptions, versions, and validation results

Question 10

When is one-hot encoding generally more appropriate than ordinal encoding?

A. When categories have a genuine ordered scale

B. When nominal categories have no meaningful order

C. When the variable contains only missing values

D. When the target variable is continuous

Correct Answer: B. When nominal categories have no meaningful order