Data Manipulation with pandas

Introduction

Data science depends not only on statistical models but also on the ability to obtain, inspect, transform, and combine data reliably. In Python, pandas is the principal library for tabular and labelled data. It provides data structures, operations, and time-series tools that support the complete preparation stage of a data-science workflow.

A typical dataset may contain missing values, inconsistent types, duplicate records, multiple tables, and observations recorded at different times. pandas allows these issues to be addressed through a common, expressive interface. However, concise code does not remove the need for careful reasoning: every selection, aggregation, and merge should reflect a clearly understood data model.

Learning objectives

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

• create and transform Series and DataFrames;
• select rows and columns safely using labels and positions;
• filter, sort, and handle missing values;
• group observations and calculate aggregations;
• merge and concatenate related datasets;
• reshape data between wide and long forms;
• perform common time-series operations;
• evaluate data-manipulation choices for correctness, efficiency, and governance.

1. pandas data structures

A Series is a one-dimensional labelled array. A DataFrame is a two-dimensional labelled table whose columns may have different data types. An Index supplies labels for rows or columns and supports alignment during operations.

import pandas as pd
import numpy as np

sales = pd.Series([120, 150, 90], index=['East', 'West', 'North'], name='revenue')

df = pd.DataFrame({
    'order_id': [101, 102, 103],
    'region': ['East', 'West', 'East'],
    'units': [4, 6, 3],
    'price': [25.0, 20.0, 25.0]
})

A DataFrame has a shape expressed as (rows, columns). Useful inspection methods include head(), tail(), info(), describe(), shape, columns, index, and dtypes.

df.head()
df.info()
df.describe(numeric_only=True)

A key pandas principle is label alignment. When two Series are combined, values are matched by index labels rather than merely by physical position.

a = pd.Series([10, 20], index=['x', 'y'])
b = pd.Series([1, 2], index=['y', 'z'])
a + b

The result contains NaN for labels not present in both Series. This behaviour is valuable for relational data, but it can also expose accidental index mismatches.

2. Creating, transforming, and assigning data

Columns can be created from expressions involving existing columns:

df['revenue'] = df['units'] * df['price']
df['large_order'] = df['units'] >= 5

A transformation may be vectorized, meaning that it operates on an entire Series without an explicit Python loop. Vectorized operations are generally clearer and faster than row-by-row loops.

df['region_code'] = df['region'].str.upper()
df['price_with_tax'] = df['price'] * 1.15

For conditional classification, np.where() or Series.where() is usually preferable to manually iterating over rows:

df['order_class'] = np.where(df['units'] >= 5, 'bulk', 'standard')

When a transformation depends on several columns, assign() can make a pipeline readable:

result = (df.assign(revenue=lambda x: x['units'] * x['price'])
            .query('revenue >= 100'))

Avoid chained assignment such as df[df['region'] == 'East']['price'] = 30. It may modify a temporary object rather than the intended DataFrame. Use .loc instead:

df.loc[df['region'].eq('East'), 'price'] = 30

3. Selection and filtering

Column selection

A single column selected with bracket notation is a Series; a list of columns produces a DataFrame:

units = df['units']
small = df[['order_id', 'region', 'units']]

Attribute notation, such as df.units, is convenient but should not be relied upon in production code because column names may conflict with methods or contain spaces.

Label and positional selection

.loc selects by labels, whereas .iloc selects by integer positions.

df.loc[0:2, ['order_id', 'units']]
df.iloc[0:2, 0:3]

The stopping behaviour of label slices can differ from ordinary Python positional slicing. Explicit selection is therefore preferable when reproducibility matters.

Boolean filtering

A Boolean mask contains True or False for each row:

high_value = df.loc[df['revenue'] > 100]
selected = df.loc[
    (df['region'] == 'East') & (df['units'] >= 3),
    ['order_id', 'revenue']
]

Each condition should be enclosed in parentheses. Use & for AND, | for OR, and ~ for NOT; Python's and, or, and not do not operate elementwise on Series. isin(), between(), str.contains(), and query() are useful alternatives.

df.loc[df['region'].isin(['East', 'North'])]
df.query('units >= 4 and price < 30')

4. Missing data and data quality

Missing values may be represented by NaN, None, or the newer pandas scalar pd.NA. Their meaning must be distinguished from zero, an empty string, or a genuine category such as “not applicable.”

df.isna().sum()
df['price'] = df['price'].fillna(df['price'].median())
df = df.dropna(subset=['order_id', 'units'])

dropna() removes observations or columns according to a specified policy. fillna() replaces missing values with constants, summary statistics, or forward/backward values. Imputation can change distributions and should be documented, especially when used for modelling.

Data types influence memory use and behaviour. Dates should be converted explicitly:

df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
df['units'] = pd.to_numeric(df['units'], errors='coerce')

The errors='coerce' option converts invalid values to missing values. This is convenient, but invalid records should be counted and investigated rather than silently ignored.

5. Sorting and ranking

df.sort_values(['region', 'revenue'], ascending=[True, False])
df.sort_index()
df['revenue_rank'] = df['revenue'].rank(method='dense', ascending=False)

Sorting is not aggregation: it changes presentation order but does not summarise observations. For reproducible outputs, specify tie-breaking columns where equal values are possible.

6. Grouping and aggregation

The split–apply–combine model divides data into groups, applies a function to each group, and combines the results. In pandas this is expressed with groupby().

summary = (df.groupby('region', as_index=False)
             .agg(total_units=('units', 'sum'),
                  average_price=('price', 'mean'),
                  orders=('order_id', 'nunique')))

The arithmetic mean of values x₁, …, xₙ is:

Here, n is the number of non-missing observations included in the calculation. The mean is appropriate when values are numeric and the missing-data policy is understood; it may be sensitive to outliers.

A weighted mean is:

where xᵢ is a measurement and wᵢ is a non-negative weight. The denominator must be non-zero. In sales analysis, revenue per unit should generally be calculated as total revenue divided by total units, rather than as an unweighted mean of order-level prices.

Group-specific transformations preserve the original row count:

df['region_mean_revenue'] = df.groupby('region')['revenue'].transform('mean')
df['share_of_region'] = (df['revenue'] /
    df.groupby('region')['revenue'].transform('sum'))

agg() usually returns one row per group, whereas transform() returns a value aligned with every original row. This distinction is fundamental.

7. Combining datasets

concat() appends objects along an axis. It is appropriate when tables have comparable structures, such as monthly files.

all_months = pd.concat([jan, feb, mar], ignore_index=True)

merge() performs relational joins using key columns:

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

Common join types are:

The choice of join depends on the analytical question. A left join is often used when enriching a fact table, but unmatched keys produce missing attributes. validate='many_to_one' checks an important assumption: many order rows may match one customer row. Without validation, duplicate dimension keys can multiply rows and inflate totals.

When key names differ, use left_on and right_on. When overlapping non-key columns exist, use suffixes and inspect the result. Always compare row counts and totals before and after a merge.

8. Reshaping data

Data may be stored in wide form, with separate columns for measures or periods, or long form, with one observation per row and variables represented by columns.

long = pd.melt(
    df, id_vars=['order_id', 'region'],
    value_vars=['units', 'revenue'],
    var_name='measure', value_name='value'
)

pivot() reshapes data when each index/column combination is unique. pivot_table() can aggregate duplicate combinations:

regional = pd.pivot_table(
    df, index='region', columns='order_class',
    values='revenue', aggfunc='sum', fill_value=0
)

stack() and unstack() move levels between the index and columns. Reshaping should be guided by the intended grain of the data: one row per customer, order, product-day, or another explicitly defined unit.

9. Time-series operations

Time-series work begins with reliable datetime parsing and a meaningful time index.

traffic['timestamp'] = pd.to_datetime(traffic['timestamp'], utc=True)
traffic = traffic.set_index('timestamp').sort_index()

Resampling changes the frequency and applies an aggregation:

daily = traffic['visits'].resample('D').sum()
monthly_mean = traffic['visits'].resample('MS').mean()

A rolling mean over a fixed number k of observations is:

where xₜ is the observation at time t. At the beginning of a series, fewer than k observations are available unless a minimum window is specified.

traffic['rolling_7'] = traffic['visits'].rolling(window=7, min_periods=1).mean()
traffic['change'] = traffic['visits'].pct_change()

For a previous value xₜ₋₁, percentage change is:

This requires a non-zero previous value; interpretation is problematic when the denominator is zero or values can be negative. Time zones, daylight-saving changes, irregular observations, and data leakage must be considered. In predictive modelling, a rolling statistic must use only information available at the prediction time.

10. Worked example: retail-order preparation

Suppose an analyst receives an orders table and a customer table. The intended grain is one row per order. The analyst should first inspect schemas and key uniqueness, then clean dates and quantities, calculate revenue, aggregate by region, and enrich the result with customer attributes.

orders = pd.DataFrame({
    'order_id': [1, 2, 3, 4],
    'customer_id': ['C1', 'C2', 'C1', 'C3'],
    'region': ['East', 'West', 'East', 'West'],
    'order_date': ['2025-01-02', '2025-01-03', '2025-01-10', 'bad-date'],
    'units': [2, 5, 3, 4],
    'unit_price': [10.0, 8.0, 10.0, 12.0]
})
customers = pd.DataFrame({
    'customer_id': ['C1', 'C2', 'C3'],
    'segment': ['Business', 'Consumer', 'Business']
})

orders['order_date'] = pd.to_datetime(orders['order_date'], errors='coerce')
orders['revenue'] = orders['units'] * orders['unit_price']

regional = (orders.groupby('region', as_index=False)
                  .agg(revenue=('revenue', 'sum'),
                       units=('units', 'sum'),
                       orders=('order_id', 'nunique')))

enriched = orders.merge(
    customers, on='customer_id', how='left',
    validate='many_to_one'
)

The East region has revenue 2×10 + 3×10 = 50 and 5 units. The West region has revenue 5×8 + 4×12 = 88 and 9 units. The invalid date becomes missing and should be investigated before time-based analysis. The merge is safe under the stated one-customer-per-key assumption.

11. Applied case: subscription-service operations

Background. A fictional subscription company stores monthly usage in separate files, customer attributes in a dimension table, and cancellation events in a third table.

Problem. Management wants monthly active users, usage by customer segment, and cancellation rates. A careless analysis could double-count customers because usage contains multiple events per customer per month.

Analysis and approach. The analyst concatenates monthly usage files, parses UTC timestamps, derives a month period, and defines the analytical grain as customer-month. The data is grouped by customer and month before being merged with the customer table. Cancellation events are deduplicated at customer-month level. Join validation is used to detect duplicate customer keys.

usage = pd.concat(monthly_files, ignore_index=True)
usage['timestamp'] = pd.to_datetime(usage['timestamp'], utc=True)
usage['month'] = usage['timestamp'].dt.to_period('M')
customer_month = (usage.groupby(['customer_id', 'month'], as_index=False)
                        .agg(events=('event_id', 'nunique'),
                             minutes=('minutes', 'sum')))
customer_month = customer_month.merge(
    customers[['customer_id', 'segment']],
    on='customer_id', how='left', validate='many_to_one'
)
segment_month = (customer_month.groupby(['month', 'segment'], as_index=False)
                              .agg(active_customers=('customer_id', 'nunique'),
                                   total_minutes=('minutes', 'sum')))

Outcome. The resulting table supports segment comparisons without counting repeated usage events as separate active customers. Missing customer matches become a data-quality exception report rather than being silently discarded.

Learning points. Define grain before coding, validate keys, distinguish event counts from entity counts, preserve audit columns, and record every transformation that affects a business measure.

12. Performance, reproducibility, and governance

For large datasets, select only necessary columns, use appropriate dtypes, avoid repeated row-wise apply(), and consider categorical columns for low-cardinality labels. Chunked reading and query pushdown can reduce memory pressure. pandas is highly useful for in-memory analysis, but databases, distributed frameworks, or columnar engines may be more suitable when data exceeds available memory.

Reproducibility requires explicit parsing rules, stable sorting, documented assumptions, and tests for row counts, uniqueness, null rates, and totals. A pipeline should be rerunnable from raw inputs and should distinguish source data from derived data.

Data manipulation also has ethical implications. Removing missing or anomalous records can systematically exclude groups. Joining personally identifiable information increases privacy risk. Analysts should apply access controls, minimisation, retention policies, and relevant data-protection requirements. Sensitive attributes should not be used or inferred without a legitimate purpose and appropriate oversight.

Chapter Summary

pandas provides Series and DataFrame structures for labelled, heterogeneous data. Reliable manipulation depends on understanding index alignment, explicit selection, Boolean filtering, missing-value policies, data types, and safe assignment. Grouping implements the split–apply–combine model, while merge() and concat() combine data under different structural assumptions. Reshaping changes data between wide and long forms, and time-series methods support parsing, resampling, rolling calculations, and lagged comparisons. Professional practice requires defining data grain, validating keys, checking totals, documenting assumptions, and considering performance, privacy, and bias.

Key Terms

Series: One-dimensional labelled pandas data structure.

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

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

Vectorization: Applying an operation to an array or Series without explicit row loops.

Boolean mask: A sequence of truth values used to select observations.

.loc: Label-based selection interface.

.iloc: Integer-position-based selection interface.

Missing value: An absent, unknown, or invalid observation.

Imputation: Replacing missing values using a defined rule.

GroupBy: pandas mechanism implementing split–apply–combine.

Aggregation: Reducing multiple observations to summary values.

Transform: Group operation that returns values aligned with original rows.

Merge: Relational combination of tables using key columns.

Join key: Column or index used to match records across tables.

Concatenation: Appending objects along rows or columns.

Wide format: Data layout with multiple measurement columns.

Long format: Data layout with variable and value columns.

Resampling: Changing the frequency of time-indexed data.

Rolling window: A moving set of observations used for a time-dependent calculation.

Data grain: The real-world unit represented by one row.

Review Questions

Short Answer Questions

1. What is the difference between a Series and a DataFrame?
2. Why should Boolean conditions in pandas use & rather than and?
3. Distinguish agg() from transform().
4. What does validate='many_to_one' check in a merge?
5. Why must the grain of a dataset be defined before aggregation?

Long Answer Questions

1. Explain .loc, .iloc, Boolean filtering, and safe assignment with examples.
2. Describe the split–apply–combine model and discuss when a weighted mean is preferable to an ordinary mean.
3. Compare concat() and merge(), including suitable use cases and common risks.
4. Explain wide and long data formats and the roles of melt() and pivot_table().
5. Discuss the technical and governance issues involved in preparing time-series data.

Analytical / Application Questions

1. A merge increases a table from 100,000 to 135,000 rows even though the left table contains one row per order. Design a diagnostic procedure to identify the cause and prevent inflated totals.
2. A company reports average order price by region. Explain why averaging order-level prices may differ from total revenue divided by total units, and specify which measure is appropriate for a unit-price decision.
3. Design a pandas workflow that calculates seven-day customer activity without using information recorded after each prediction date. Identify possible sources of leakage.

Multiple Choice Questions with Answers

Question 1

Which pandas interface performs label-based selection?

A. .iloc

B. .loc

C. .values

D. .shape

Correct Answer: B. .loc

Question 2

Which operation normally returns one row per group?

A. groupby().agg()

B. groupby().transform()

C. rolling()

D. sort_values()

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

Question 3

What is the main purpose of validate='many_to_one' in merge()?

A. To sort the merged table

B. To fill unmatched values

C. To check that right-side keys are unique

D. To convert keys to integers

Correct Answer: C. To check that right-side keys are unique

Question 4

Which function is most appropriate for converting a table from wide to long form?

A. melt()

B. describe()

C. rank()

D. resample()

Correct Answer: A. melt()

Question 5

What does a left join retain?

A. Only matching rows from both tables

B. All rows from the left table and matching right-side data

C. All rows from the right table only

D. Only unmatched rows

Correct Answer: B. All rows from the left table and matching right-side data

Question 6

If units are [2, 5] and prices are [10, 8], what is total revenue?

A. 18

B. 40

C. 50

D. 80

Correct Answer: C. 50

Question 7

Which method changes the frequency of a time-indexed Series?

A. resample()

B. isin()

C. rename()

D. duplicated()

Correct Answer: A. resample()

Question 8

Why is chained assignment discouraged?

A. It always deletes the index

B. It may modify a temporary object rather than the intended DataFrame

C. It converts all values to strings

D. It prevents filtering

Correct Answer: B. It may modify a temporary object rather than the intended DataFrame

Question 9

Which expression calculates the number of distinct orders in each region?

A. orders=('order_id', 'sum')

B. orders=('order_id', 'mean')

C. orders=('order_id', 'nunique')

D. orders=('order_id', 'rank')

Correct Answer: C. orders=('order_id', 'nunique')

Question 10

A seven-observation rolling mean at time t uses which values under the standard trailing-window interpretation?

A. Only the future seven observations

B. The current observation and the previous six observations

C. The previous seven observations, excluding the current one

D. All observations in the dataset

Correct Answer: B. The current observation and the previous six observations