Data Manipulation with pandas

Introduction

Data science rarely begins with a perfectly organized table. Real datasets may contain missing values, inconsistent labels, duplicated records, incorrect data types, mixed date formats, and very large numbers of rows. The Python library pandas provides a flexible framework for importing, inspecting, cleaning, transforming, combining, analysing, and exporting tabular data.

The central objects in pandas are the Series and the DataFrame. A Series is a one-dimensional labelled array, while a DataFrame is a two-dimensional labelled table whose columns may have different data types. Together, they provide the practical foundation for exploratory data analysis and preparation of data for statistical modelling and machine learning.

A useful data-manipulation workflow is:

Import → Inspect → Clean → Select → Transform → Combine → Aggregate → Reshape → Validate → Export

This workflow is not merely a sequence of programming operations. Each stage can affect the validity of later analysis. For example, calculating an average before identifying missing or duplicated records may produce a misleading result.

Learning Objectives

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

1. Explain the structure and uses of Series and DataFrames.
2. Import and export common tabular data formats.
3. Inspect data structure, values, types, indexes, and missingness.
4. Select rows and columns safely using labels and positions.
5. Transform columns, handle data types, dates, categorical variables, and missing values.
6. Join, concatenate, group, aggregate, and reshape tables.
7. Work responsibly with large and imperfect datasets.
8. Validate a data-manipulation process and identify governance risks.

1. The pandas Data Model

1.1 Series

A Series contains values and an associated index. The index identifies observations and does not have to be a sequence of integers.

import pandas as pd

sales = pd.Series([1250, 980, 1430],
                  index=["North", "South", "East"],
                  name="revenue")
print(sales)

The values are accessible through sales.to_numpy(), while the labels are accessible through sales.index. A Series can contain numeric, string, Boolean, datetime, categorical, or other compatible values.

1.2 DataFrames

A DataFrame is a collection of aligned Series. Each column has a name, and all columns share the same row index.

data = {
    "customer": ["A01", "A02", "A03"],
    "region": ["North", "South", "North"],
    "units": [4, 2, 7],
    "price": [25.0, 40.0, 18.0]
}
orders = pd.DataFrame(data)

The expression orders.shape returns a pair (number_of_rows, number_of_columns). The expression orders.dtypes reports the data type of each column. orders.columns returns the column labels, and orders.index returns the row labels.

The index is important because pandas aligns data by labels during many operations. This differs from systems that combine values solely according to physical position. Label alignment reduces some errors but can also create unexpected missing values if labels do not match.

1.3 Indexes and their meaning

An index may represent:

• an observation identifier;
• a date or time period;
• a hierarchical key using a MultiIndex; or
• a default integer sequence.

An index should have a meaningful interpretation. Setting a customer identifier as an index can make lookups convenient, but it may be inappropriate if customer identifiers are not unique. Indexing is not the same as establishing a database primary key: pandas does not automatically enforce uniqueness.

orders = orders.set_index("customer")
record = orders.loc["A02"]

Use reset_index() to return index labels to ordinary columns.

2. Importing and Inspecting Data

2.1 Importing common formats

Common functions include:

Example:

orders = pd.read_csv(
    "orders.csv",
    encoding="utf-8",
    na_values=["", "NA", "N/A", "unknown"]
)

When importing data, explicitly consider the delimiter, encoding, decimal symbol, date format, missing-value conventions, and whether the first row contains column names. Automatic inference is convenient but should be checked.

2.2 Initial inspection

A disciplined inspection sequence is often more valuable than immediately applying transformations.

orders.head()
orders.tail()
orders.shape
orders.info()
orders.describe(numeric_only=True)
orders.isna().sum()
orders.nunique()

head() and tail() reveal representative records. info() displays non-null counts and inferred data types. describe() provides numerical summaries such as count, mean, standard deviation, minimum, quartiles, and maximum. These summaries are descriptive, not proof that the data is valid.

For categorical columns:

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

This can reveal spelling inconsistencies such as North, north, and NORTH. A high maximum in a numerical column may indicate an outlier, a unit error, or a legitimate large transaction; inspection alone cannot determine which explanation is correct.

3. Selecting Rows and Columns

3.1 Column selection

orders["price"]                 # Series
orders[["region", "units"]]]    # DataFrame

The single-bracket form returns a Series, while the double-bracket form returns a DataFrame.

3.2 Label-based selection with loc

loc selects by labels and supports Boolean conditions.

orders.loc[orders["units"] > 3, ["region", "units", "price"]]

The general pattern is:

frame.loc[row_condition, column_labels]

3.3 Position-based selection with iloc

iloc selects by integer position.

orders.iloc[0:3, 1:4]

The stop position in a slice is excluded, following ordinary Python slicing conventions.

3.4 Filtering and the Boolean mask

A Boolean mask is a Series of True and False values.

high_value = orders[orders["units"] * orders["price"] > 100]

For multiple conditions, use & for AND and | for OR, placing each condition in parentheses:

selected = orders[
    (orders["region"] == "North") &
    (orders["units"] >= 5)
]

Python's and and or are not substitutes for pandas' element-wise operators.

4. Transforming Data

4.1 Creating and modifying columns

A derived column can be created using vectorized arithmetic:

orders["gross_value"] = orders["units"] * orders["price"]

Vectorized operations generally express the intended operation more clearly and efficiently than explicit Python loops.

A conditional classification can use np.select:

import numpy as np

orders["size"] = np.select(
    [orders["units"] >= 10, orders["units"] >= 5],
    ["large", "medium"],
    default="small"
)

The conditions are evaluated in order. Overlapping conditions therefore require deliberate ordering.

4.2 Applying functions

For simple transformations, methods such as .str, .dt, and arithmetic operators are preferable. For example:

orders["region"] = orders["region"].str.strip().str.title()
orders["customer"] = orders["customer"].str.upper()

apply() can be useful for a function that is difficult to express otherwise, but extensive row-wise apply() may be slower and less transparent than vectorized alternatives.

4.3 Assignment and the copy principle

A filtered object may be a view or a copy depending on the operation and pandas version. To make intent explicit:

north = orders.loc[orders["region"] == "North"].copy()
north["discounted_price"] = north["price"] * 0.95

Using .copy() helps prevent accidental modification of the original table and avoids ambiguous chained assignment.

5. Data Types, Missing Values, and Imperfect Data

5.1 Data types

Data types affect memory use, available operations, and analytical correctness. Common types include integer, floating-point, Boolean, string, datetime, and categorical data.

orders["units"] = pd.to_numeric(orders["units"], errors="coerce")
orders["price"] = pd.to_numeric(orders["price"], errors="coerce")

With errors="coerce", values that cannot be converted become missing. This is useful for detection, but conversion should be followed by an investigation of the affected rows.

5.2 Missing values

Missingness may mean an unavailable measurement, an inapplicable field, a failed collection process, or an unknown value. These meanings should not be treated as identical.

orders.isna().sum()
orders["price"] = orders["price"].fillna(orders["price"].median())
orders = orders.dropna(subset=["customer", "units"])

Median imputation may be appropriate for some skewed numerical variables, but it changes the data distribution and can reduce natural variability. It is not automatically appropriate for every dataset. Missing categorical values may be retained, labelled explicitly, or imputed using domain rules.

5.3 Duplicates and validation rules

orders.duplicated().sum()
orders = orders.drop_duplicates()

Before removing duplicates, determine whether repeated rows are genuine repeated events or accidental copies. Domain checks are essential:

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

Assertions should be complemented by reports that identify invalid records, especially in production systems where silently stopping a pipeline may be undesirable.

5.4 String and categorical data

Categorical data represents a finite set of labels, such as region, product type, or risk class.

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

Categorical storage may reduce memory usage when a column has relatively few distinct values. It also documents that values belong to a controlled set. Categories can be ordered:

orders["priority"] = pd.Categorical(
    orders["priority"],
    categories=["low", "medium", "high"],
    ordered=True
)

Category labels should not be assigned numerical meanings merely because they can be encoded as numbers. A machine-learning model may require a separate encoding strategy, such as one-hot encoding or an ordinal encoding justified by the domain.

6. Dates and Time Series

Dates should normally be converted to a datetime type rather than retained as arbitrary strings.

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

After conversion, datetime components can be extracted:

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

Time zones matter when records originate in different locations. A timestamp without a defined time zone may be ambiguous around daylight-saving transitions. For cross-region analysis, storing timestamps in UTC and converting for presentation is often safer.

Resampling requires a datetime-like index or an appropriate time column:

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

The exact frequency aliases supported may vary by pandas version; consult the installed version's documentation. Time aggregation must also respect the business meaning of a period. A month-end total is not equivalent to an average of daily totals if days have different numbers of transactions.

7. Combining Tables

7.1 Concatenation

Concatenation stacks tables along an axis.

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

Vertical concatenation assumes that the tables represent compatible columns. If schemas differ, missing cells are introduced. Horizontal concatenation combines columns and should be used only when row alignment is meaningful.

7.2 Relational joins

merge() implements database-style joins.

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

The validate argument is valuable because it checks an expected relationship. A many-to-one relationship means many order rows may match one customer row. If the customer table unexpectedly contains duplicate customer identifiers, the join can multiply rows and inflate totals.

A join should be evaluated using row counts, unmatched-key counts, and duplicate-key checks. The presence of a successful merge does not prove that it was semantically correct.

7.3 Joining on indexes

join() is convenient when the relevant keys are indexes, while merge() is generally clearer for explicit relational keys. In both cases, inspect the resulting index and columns after the operation.

8. Grouping and Aggregation

The split–apply–combine model divides data into groups, applies a function to each group, and combines the results.

summary = (
    orders.groupby("region", as_index=False)
          .agg(
              total_value=("gross_value", "sum"),
              average_price=("price", "mean"),
              order_count=("customer", "size")
          )
)

The mean of a variable is calculated as:

Here, xᵢ is the value for observation i, n is the number of included non-missing observations, and x̄ is the arithmetic mean. The formula assumes that the observations being averaged are defined on a comparable scale and that the treatment of missing values is understood. A mean can be strongly affected by extreme values.

A weighted average is appropriate when observations contribute unequally. If wᵢ is a non-negative weight and the total weight is non-zero:

For example, average selling price across transactions is often better represented by total revenue divided by total units than by the unweighted mean of transaction prices:

weighted_price = orders["gross_value"].sum() / orders["units"].sum()

The numerator and denominator must be compatible: revenue divided by units has price units. This calculation assumes that gross_value and units refer to the same transactions and that units are positive and meaningful.

9. Reshaping Data

Data may be stored in wide form, where measurements appear in several columns, or long form, where one column identifies the measurement variable and another stores its value.

long = pd.melt(
    orders,
    id_vars=["customer", "region"],
    value_vars=["units", "price"],
    var_name="measure",
    value_name="value"
)

A pivot converts long data into a summary table:

pivot = pd.pivot_table(
    orders,
    index="region",
    columns="size",
    values="gross_value",
    aggfunc="sum",
    fill_value=0
)

pivot() requires combinations of index and column labels to be unique. pivot_table() can aggregate duplicate combinations. A reshape should preserve the intended grain of the data; otherwise, aggregation may conceal duplicate or conflicting observations.

10. Exporting Results

summary.to_csv("regional_summary.csv", index=False)
summary.to_parquet("regional_summary.parquet", index=False)

Export decisions should document the file format, encoding, index policy, date representation, column definitions, and applied transformations. CSV is portable but does not preserve all data types and may create ambiguity around delimiters, decimal marks, and encodings. Parquet generally preserves types more effectively and can be more efficient for analytical workloads.

Do not export sensitive fields unnecessarily. Use controlled directories, access permissions, and suitable retention policies. An exported dataset may be copied beyond the original governance environment.

11. Working with Large Datasets

A DataFrame loads data into memory, so memory capacity can constrain the size of a dataset. Practical approaches include:

for chunk in pd.read_csv("large_orders.csv", chunksize=100_000,
                         usecols=["region", "units", "price"]):
    chunk["value"] = chunk["units"] * chunk["price"]
    # update an incremental result

Important techniques include selecting only necessary columns with usecols, specifying types with dtype, reading in chunks, filtering at the source through SQL, and using columnar formats. Chunk processing requires an associative aggregation strategy. For example, sums and counts can be accumulated across chunks, but a global mean requires retaining or combining both total and count rather than averaging chunk means equally.

For datasets beyond the practical scope of pandas, technologies such as databases, DuckDB, Dask, or distributed processing systems may be more suitable. pandas remains useful for sampling, validation, and final analysis.

12. Detailed Applied Case: Retail Order Quality Pipeline

The following is an illustrative case.

Background

A retailer receives monthly order files from several branches and a separate customer master table. Management wants regional revenue, units sold, and customer-segment comparisons.

Problem

The files contain inconsistent region capitalization, prices stored as strings, missing customer identifiers, repeated rows, and a customer master table with accidental duplicate customer keys. A direct aggregation would risk overstating revenue.

Analysis and approach

First, the analyst imports each file and records the source file name. The schema and row counts are inspected. Region values are standardized using string operations. Numeric fields are converted with errors="coerce", and conversion failures are reported rather than silently ignored.

Next, invalid rows are separated for review. Exact duplicates are examined against the source system before removal. The customer master table is tested for duplicate keys:

duplicate_customers = customers[
    customers.duplicated("customer", keep=False)
]

The business owner confirms that the duplicate records represent corrections, so the latest approved record is retained according to a documented rule. The order table is then merged with validate="many_to_one". If validation fails, the pipeline stops for investigation instead of producing a potentially inflated report.

Revenue is calculated as units multiplied by unit price. Regional totals are grouped and compared with source-system control totals. A reconciliation report records input rows, rejected rows, unmatched customers, output rows, and total revenue before and after each major step.

Outcome

The final report distinguishes valid revenue from records requiring remediation. Management receives a regional summary, while data-quality staff receive an exception file. The result is more defensible than a single cleaned table because it preserves evidence of what was changed and why.

Learning points

1. Data cleaning is a reasoning process, not merely a collection of commands.
2. Join cardinality must be tested before aggregation.
3. Exception records are valuable outputs, not disposable errors.
4. Reconciliation checks should accompany important business metrics.
5. Reproducible code and documented assumptions support auditability.

13. Best Practices, Limitations, and Governance

A professional pandas workflow should:

• preserve raw data and create transformed copies;
• use explicit column names and documented units;
• inspect data before and after major operations;
• avoid chained assignment;
• validate keys and expected row counts;
• distinguish missing, zero, and not-applicable values;
• record software versions and transformation assumptions;
• use tests for critical data-quality rules;
• separate exploratory analysis from repeatable production pipelines.

pandas is powerful but not a complete data-governance system. It does not by itself establish lineage, enforce business definitions, guarantee statistical validity, or prevent disclosure of personal information. A technically correct transformation can still be inappropriate if the data was collected without a lawful basis, if a sensitive attribute is exposed, or if a proxy variable produces unfair outcomes.

When preparing data for AI or machine learning, analysts should also watch for target leakage, sampling bias, duplicated entities across training and test data, and transformations that use future information. For example, imputing a value using information from the entire dataset before a train–test split can allow information from the test set to influence model training. Transformations should be fitted only on training data when they are part of a predictive modelling pipeline.

Contemporary Developments

Modern pandas practice increasingly involves nullable data types, efficient string handling, copy-on-write behaviour, Arrow-compatible data exchange, and integration with Parquet and SQL engines. These developments improve type fidelity and performance, but code should still be tested against the installed pandas version. APIs and frequency aliases can change over time; reproducible environments and explicit dependency management are therefore important.

The broader trend is toward hybrid workflows: pandas for interactive analysis and moderate-sized data, SQL or columnar query engines for scalable filtering and aggregation, and validated pipelines for repeated operational use.

Chapter Summary

pandas provides a coherent framework for manipulating labelled tabular data through Series and DataFrames. Reliable work begins with controlled import and inspection, followed by explicit handling of indexes, data types, missing values, duplicates, dates, and categorical variables. Selection is performed by labels with loc or by positions with iloc; transformations should generally use vectorized operations. Tables can be concatenated or joined, but joins must be checked for key uniqueness and expected cardinality. Grouping implements the split–apply–combine pattern, while melting and pivoting change data between long and wide forms. Export requires attention to types, encoding, indexes, privacy, and reproducibility. For large datasets, column selection, chunking, source-side filtering, and suitable storage formats are essential. The central professional principle is that every transformation should be interpretable, validated, and appropriate to the meaning and governance of the data.

Key Terms

Series: A one-dimensional labelled pandas data structure.

DataFrame: A two-dimensional labelled table with potentially different column types.

Index: Labels used to identify and align rows or columns.

Boolean mask: A sequence of Boolean values used to filter observations.

Vectorization: Applying an operation to an array or column without explicit row-by-row Python loops.

Data type: The representation and operational category of stored values.

Missing value: An unavailable, unknown, or inapplicable value.

Categorical variable: A variable whose values belong to a finite set of labels.

Datetime: A data type representing dates and times.

Concatenation: Combining objects along rows or columns.

Merge: A database-style operation that combines tables using keys.

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

GroupBy: pandas' split–apply–combine mechanism for grouped analysis.

Aggregation: Summarising multiple observations with functions such as sum, mean, or count.

Pivot table: A reshaped and usually aggregated summary table.

Long format: A structure with identifier columns, a variable column, and a value column.

Wide format: A structure in which different measures or categories occupy separate columns.

Vectorized transformation: A column-level computation applied efficiently across values.

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

Target leakage: Use of information in model preparation that would not be available at prediction time.

Review Questions

Short Answer Questions

1. What is the difference between a Series and a DataFrame?
2. When should loc be used instead of iloc?
3. Why can an unvalidated many-to-many merge overstate an aggregate?
4. Give two methods for handling missing values and one limitation of each.
5. What is the difference between long and wide data formats?

Long Answer Questions

1. Explain a complete pandas workflow for importing, inspecting, cleaning, transforming, and exporting a CSV dataset.
2. Discuss how indexes influence selection and alignment in pandas, including situations in which an index should not be treated as a unique identifier.
3. Explain the split–apply–combine model and show how grouped aggregation supports business reporting.
4. Compare concatenation, merging, joining, melting, and pivoting, identifying the type of structural change produced by each.
5. Evaluate the technical and governance considerations involved in preparing a pandas dataset for machine-learning use.

Analytical / Application Questions

1. A customer table contains 10,000 rows but only 9,700 unique customer identifiers. Explain how you would diagnose the problem and prevent an order-to-customer merge from inflating revenue.
2. A monthly report averages the mean price from each branch. Explain why this may differ from the overall average price and design a pandas calculation that accounts for different branch volumes.
3. Design a robust processing plan for a 20-million-row CSV containing dates, categories, missing prices, duplicate transactions, and sensitive customer identifiers. Justify your choices for memory management, validation, privacy, and export.

Multiple Choice Questions with Answers

Question 1

Which pandas object is one-dimensional and labelled?

A. Series

B. DataFrame

C. IndexJoin

D. GroupTable

Correct Answer: A. Series

Question 2

Which accessor performs label-based row and column selection?

A. iloc

B. loc

C. dtype

D. shape

Correct Answer: B. loc

Question 3

What does pd.to_numeric(values, errors="coerce") do to values that cannot be converted?

A. Deletes the entire column

B. Converts them to zero

C. Converts them to missing values

D. Converts them to strings

Correct Answer: C. Converts them to missing values

Question 4

Which merge type retains every row from the left DataFrame?

A. inner

B. right

C. outer

D. left

Correct Answer: D. left

Question 5

Which pandas operation is most directly associated with split–apply–combine analysis?

A. groupby

B. astype

C. reset_index

D. to_csv

Correct Answer: A. groupby

Question 6

Why is validate="many_to_one" useful in a merge?

A. It sorts all columns alphabetically

B. It checks that the right-side key has at most one match per key

C. It converts all values to strings

D. It removes every unmatched row

Correct Answer: B. It checks that the right-side key has at most one match per key

Question 7

Which operation converts selected columns from wide form into variable–value pairs?

A. melt

B. merge

C. describe

D. drop_duplicates

Correct Answer: A. melt

Question 8

If revenue is 12,000 and units sold are 300, what is revenue per unit?

A. 4

B. 40

C. 400

D. 3,600

Correct Answer: B. 40

Question 9

Which technique is most appropriate when a CSV is too large to fit comfortably in memory?

A. Read it repeatedly without filtering

B. Use chunksize and process incremental results

C. Convert every column to Python objects

D. Duplicate the DataFrame before processing

Correct Answer: B. Use chunksize and process incremental results

Question 10

Why can averaging branch-level means be misleading?

A. Means cannot be calculated in pandas

B. Branch means always contain strings

C. Branches may contribute different numbers of observations

D. A mean is only defined for categorical data

Correct Answer: C. Branches may contribute different numbers of observations