Merge, Reshape & Groupby

ECON 3209 · Week 4, Lecture 2 · Kerala Agricultural University

Department of Development Economics, KAU

Autumn 2026

Learning Outcomes

By the end of this lecture, you will be able to:

1. Merge related tables using keys
2. Understand left, inner, and outer joins conceptually
3. Reshape data using melt() and pivot_table()
4. Aggregate data with groupby()
5. Build tidy tables for agricultural and banking analysis

Why These Tools Matter

• Real projects rarely come in one perfect table
• We often receive separate files for districts, crops, prices, or institutions
• We need to combine them, change their shape, and summarise them
• pandas provides powerful verbs for these tasks
• Good wrangling prevents errors before estimation begins

The Idea of a Merge

• A merge combines tables using one or more common keys
• Typical keys include district, year, branch code, or household ID
• Before merging, always check whether keys are unique and clean
• Duplicate keys can change the meaning of the final dataset
• Missing keys may create unmatched observations

Merged table = observations matched on a common identifier such as (district, year)

Example: District Data Merge

• on= tells pandas which key to match
• how="inner" keeps only matching rows
• This is one of the most common wrangling tasks in applied economics
• Always inspect the merged output immediately

Join Types

Common Options

• inner keeps matched rows only
• left keeps all rows from the left table
• right keeps all rows from the right table
• outer keeps everything from both sides

Why It Matters

• Different joins answer different questions
• A left join is common when adding extra variables
• An inner join can silently drop observations
• An outer join reveals mismatches to investigate

Reshaping with melt()

• Wide data spreads values across many columns
• Long data stacks them into rows
• Long format is often better for plotting and grouped analysis
• melt() is the standard wide-to-long function in pandas

Reshaping with pivot_table()

• pivot_table() often takes long data back to wide format
• It is useful for reporting tables and dashboards
• When duplicates exist, pivot_table() can aggregate them
• Tidy reshaping helps ensure one variable per column and one row per unit-time pair

Grouped Aggregation with groupby()

• groupby() splits data into groups
• Then we apply summary functions such as mean, sum, or count
• This is the split-apply-combine pattern
• It is fundamental for descriptive economics and official statistics

Multiple Aggregations

• .agg() lets us compute several summaries at once
• Named aggregations create clear output column names
• This is very useful for tables in reports and slide decks
• Group summaries should always be interpreted with sample size in mind

A Simple Wrangling Pipeline

• Wrangling steps can be chained cleanly
• Pipelines reduce intermediate clutter
• Sorting after summarising makes interpretation easier
• This style is common in data science notebooks and reports

Wrangling Checklist

• Confirm keys before every merge
• Inspect row counts before and after major operations
• Prefer tidy long data for plotting and grouped models
• Use descriptive variable names after reshaping
• Treat grouping and aggregation as part of economic storytelling, not just coding

A clean merge or groupby table is not only technical — it determines whether your final inference is trustworthy.

Exercise

Create two small DataFrames:

• bank_df with district and deposit_crore
• loan_df with district and loan_crore

Then: 1. Merge them by district. 2. Create a gap column equal to deposits minus loans. 3. Use groupby() on a new region column to compute average gap.

Summary

• ✅ Merges combine related tables using keys
• ✅ Join type affects which observations survive
• ✅ melt() converts wide data to long format
• ✅ pivot_table() helps reshape long data for reports
• ✅ groupby() is the core tool for grouped summaries
• ✅ Careful wrangling is essential before any econometric model

Next Lecture

Real Agricultural Data

• We will simulate a realistic agricultural data cleaning workflow
• You will see missing values, type issues, and simple case-study analysis
• This brings pandas closer to the kinds of datasets used in practice