Lesson 11

Manipulating, Grouping, Reshaping, and Joining Data

Big question

How can information be reorganized without accidentally changing the number or meaning of observations?

Lesson progress

Complete checkpoints as you learn

0% complete0 checkpoint streak

Progress tracking available after sign-in. Sign in to save your work.

Big question

Concept

Activity

Quiz

Learning objectives

Aggregate with groupby.
Move between wide and long forms.
Merge tables using keys.
Diagnose duplicate keys and many-to-many joins.
Prerequisites: Chapters 9 and 10.
Key terms: groupby, aggregation, pivot, melt, merge, key.

Simple explanation

A grouped summary collapses observations within categories or time periods. The output unit must be stated: an average across households is not a household-level variable, and a monthly average of daily rates is not a month-end rate. groupby is most useful when the grouping keys, aggregation function, and output names are explicit.

Key terms

Aggregate with groupby: A core idea in Chapter 11 that students apply carefully in economic analysis.
Move between wide and long forms: A core idea in Chapter 11 that students apply carefully in economic analysis.
Merge tables using keys: A core idea in Chapter 11 that students apply carefully in economic analysis.
Diagnose duplicate keys and many-to-many joins: A core idea in Chapter 11 that students apply carefully in economic analysis.
Prerequisites: Chapters 9 and 10: A core idea in Chapter 11 that students apply carefully in economic analysis.
Key terms: groupby, aggregation, pivot, melt, merge, key: A core idea in Chapter 11 that students apply carefully in economic analysis.

Analytical workflow

Question + data + assumptions + transparent Python -> evidence

Interpret the expression in words and units before using it in a claim.

Example

Interpretation. The result has one row per province, so its unit differs from the original province-year table.

Prerequisites

Complete the preceding course chapters or review their summaries as needed.

Full theory and examples

11.2

Grouping changes the unit of analysis

11.3

Wide and long are views of the same logic

Wide data place repeated measures in separate columns; long data place the measure name in one column and values in another. Long form is often convenient for plotting and grouped operations, while wide form is useful for matrix models such as VAR. Reshaping should preserve a key that uniquely identifies each observation. A failed pivot often reveals duplicates that need substantive resolution.

11.4

Joins are models of relationships

A merge states how two tables are related. One-to-one and many-to-one joins are usually easiest to verify. An unintended many-to-many join can multiply rows and distort totals. pandas can validate expected cardinality. Analysts should compare row counts, unmatched keys, and duplicate keys before and after every important merge.

11.5

Python demonstrations

11.5.1

Demonstration 11.1: Group and summarize

Verified output

Interpretation. The result has one row per province, so its unit differs from the original province-year table.

11.5.2

Demonstration 11.2: Validate a merge

Verified output

Interpretation. Cardinality validation and the merge indicator confirm that metadata attached without multiplying rows.

11.6

Visual evidence

11.7

Reference table

Why This Matters

Many large analytical errors begin with an unnoticed change in observational unit during aggregation or merging.

Common Mistake

Joining on a variable that is not unique in the lookup table. The output may expand without an obvious error message.

Ceteris LAB Tip

Write the expected join cardinality in words before writing the merge code.

R-to-Python / Source Bridge

Financial and macroeconomic source examples combine dates, identifiers, and multiple series. This chapter supplies the safe restructuring tools needed before time-series modeling.

Table 11. Chapter reference.

Join	Keeps	Use case
inner	matching keys only	complete overlap
left	all left rows	attach metadata
right	all right rows	less common mirror of left
outer	all keys	reconciliation and coverage audit

Visual evidence

Figure 11. Joining two tables requires explicit keys and duplicate-key diagnostics.

Additional Python demonstrations

Live Python

Source demonstration 2

Stdout

Run Python to see results here.

Status / stderr

Ready to run Python in your browser.

Line-by-line guide

Line 1Create or update a Python object used in the analysis.
Line 2Create or update a Python object used in the analysis.
Line 3Display a result so students can inspect the output.
Line 4Display a result so students can inspect the output.

Verified source output

province mean_income 0 ON 61.5 1 QC 56.5

(4, 5) {'both': 4, 'left_only': 0, 'right_only': 0}

province  mean_income
0       ON         61.5
1       QC         56.5

(4, 5)
{'both': 4, 'left_only': 0, 'right_only': 0}

import pandas as pd
df = pd.DataFrame({
summary = df.groupby("province", as_index=False).agg(mean_income=("income", "mean"))
print(summary)
meta = pd.DataFrame({"province": ["ON", "QC"], "region": ["Central", "Central"]})
merged = df.merge(meta, on="province", how="left", validate="many_to_one", indicator=True)

Interpretation. The result has one row per province, so its unit differs from the original province-year table.

Interpretation. Cardinality validation and the merge indicator confirm that metadata attached without multiplying rows.

Guided practice

1Re-run Demonstration 11.1 and change one input while keeping the analytical question fixed.
2Explain in two sentences how the output supports, or fails to support, the chapter opening question.
3Add one validation check that would prevent a plausible error.

Exercises

1Create a grouped mean and count.
2Reshape a two-year wide table to long form.
3Perform a left join with validation.
4Find keys that do not match across two tables.

Source and downloads

Chapter 11 of Fundamentals of Python for Financial Econometrics by Mohammad Safavi, Ph.D.. The lesson is an original Ceteris Lab web adaptation of the supplied publication package.

Download course PDF Download student package

Live Python

Manipulating, Grouping, Reshaping, and Joining Data: live Python

Stdout

Run Python to see results here.

Status / stderr

Ready to run Python in your browser.

Line-by-line guide

Line 1Load a Python library needed for data work or regression.
Line 3Create or update a Python object used in the analysis.
Line 4Run this Python instruction as part of the lesson workflow.
Line 5Run this Python instruction as part of the lesson workflow.
Line 6Run this Python instruction as part of the lesson workflow.
Line 7Run this Python instruction as part of the lesson workflow.
Line 8Create or update a Python object used in the analysis.
Line 9Display a result so students can inspect the output.

Python walkthrough

1`import pandas as pd`: Loads a package or function used by the analysis.
2`df = pd.DataFrame({`: Creates or updates a named object used by later steps.
3`"province": ["ON", "ON", "QC", "QC"],`: Executes the next transparent step in the workflow.
4`"year": [2025, 2026, 2025, 2026],`: Executes the next transparent step in the workflow.
5`"income": [60, 63, 55, 58],`: Executes the next transparent step in the workflow.
6`})`: Executes the next transparent step in the workflow.
7`summary = df.groupby("province", as_index=False).agg(mean_income=("income", "mean"))`: Creates or updates a named object used by later steps.
8`print(summary)`: Displays a result so it can be checked and interpreted.

Live notebook

Run this lesson as a notebook

Open an editable notebook cell-by-cell, run Python in the browser, and download the `.ipynb` file for later.

Open live notebook Download .ipynb Download .py

Related dataset

Ceteris Lab teaching sample

Estimated time

25 to 40 min

Packages

pandas, numpy

Expected output

Printed Python results that can be compared with the lesson explanation.

Learning goals

Load and inspect Ceteris Lab teaching sample.
Run the Python cells connected to Manipulating, Grouping, Reshaping, and Joining Data.
Interpret the output using Aggregate with groupby and Move between wide and long forms.

Common errors

File not found: check that wage_sample.csv is installed or use the course data folder.
Package import error: use the browser notebook first, then download for local Jupyter if your local packages differ.
Column name error: compare your variable names with the dataset variables listed for this notebook.

Dataset path helper

import pandas as pd

df = pd.read_csv("/data/wage_sample.csv")
df.head()

Interactive activity

Chapter 11 interactive

Data evidence planner

Evidence strength: 55%

Unexamined dataValidated evidence

Which choice makes an exploratory result easier to defend?

Record the assumption and limitation in the analysis notes

Immediate feedback

Choose a decision, then test how the claim changes as evidence becomes stronger or weaker.

Try it yourself

Write one plain-English sentence explaining the main idea from this lesson.

Common mistakes

Check these before you move on.

Return to the lesson assumptions, units, diagnostics, and source evidence to replace this shortcut with a defensible interpretation.

Quick quiz

Which statement best answers the Chapter 11 opening question: How can information be reorganized without accidentally changing the number or meaning of observations?

Quick quiz

Which practice should be avoided when applying Manipulating, Grouping, Reshaping, and Joining Data?

Quick quiz

What is the most defensible way to interpret the Python demonstration?

Quick quiz

Why does Chapter 11 matter in an applied econometrics workflow?

Key takeaway

Grouping changes the unit of analysis. Wide and long formats support different tasks. Join cardinality and unmatched keys must be audited.

Download data Download results Download interpretation