Pandas Advanced Masterclass: Merge, Rank, and Window Functions Like SQL

Past the basics, Pandas mirrors SQL — joins, window functions, and index hygiene. This masterclass maps each SQL idiom to its Pandas verb so you stop context-switching.

What SQL parity does the notebook deliver?

Index manipulation (set_index/reset_index), merge joins (inner/left/outer), plus rank, shift (lead/lag), and groupby window patterns identical to ROW_NUMBER, DENSE_RANK, LEAD, LAG. Pair with SQL joins fan-out for the relational twin and Pandas cheatsheet for the one-liners.

Ingredients: two relational tables — 8 employees (EmpID, DeptID, Salary, JoinDate) and 4 departments (DeptID 1,2,3,5), intentionally mismatched so joins visibly drop or keep rows.

How do you set up relational data?

import pandas as pd
import numpy as np
emp_data = {
    'EmpID': [101,102,103,104,105,106,107,108],
    'Name': ['Alice','Bob','Charlie','David','Eva','Frank','Grace','Hank'],
    'DeptID': [1,2,1,3,2,3,1,4],  # 4 has no match; 5 is empty
    'Salary': [70000,80000,70000,110000,82000,55000,95000,60000],
    'JoinDate': ['2020-01-15','2019-05-23','2021-03-12','2018-11-01','2020-08-19','2022-01-05','2019-12-10','2021-06-15']
}
employees = pd.DataFrame(emp_data)
dept_data = {'DeptID':[1,2,3,5], 'DeptName':['HR','Engineering','Marketing','Sales']}
departments = pd.DataFrame(dept_data)
print(employees.head(3)); print(departments)

Rendered output: employees shows Hank in Dept 4 with no department name — the orphan that diagnoses join choice.

How do you manipulate the index correctly?

emp_indexed = employees.set_index('EmpID')
print("Index is now EmpID:")
print(emp_indexed.head(3))
 
emp_reset = emp_indexed.reset_index()
print(emp_reset.head(3))
 
# Exercise: index by Name then .loc
by_name = employees.set_index('Name')
print(by_name.loc['Alice'])

Rendered output: index name flips to EmpID then back to RangeIndex(0..7) on reset — the round-trip you do after groupbys that leave an index behind.

How do you mirror SQL joins with pd.merge?

df_merged = pd.merge(employees, departments, on='DeptID', how='left')
print("--- Left Join Result ---")
print(df_merged)
# Hank survives with DeptName NaN; Sales (Dept 5) absent — correct for left
 
# Inner loses Hank — the exercise prompt
df_inner = pd.merge(employees, departments, on='DeptID', how='inner')
print(f"Inner rows: {len(df_inner)} vs Left rows: {len(df_merged)}")

Rendered output: left = 8 rows (Hank NaN), inner = 7 rows (Hank gone), outer = 9 rows (Sales appears with null employee) — mirrors SQL semantics exactly.

How do you imitate window functions?

df_merged['Row_Number'] = df_merged.groupby('DeptID')['Salary'].rank(method='first', ascending=False)
df_merged['Dense_Rank'] = df_merged.groupby('DeptID')['Salary'].rank(method='dense', ascending=False)
print(df_merged.sort_values(['DeptID','Salary'], ascending=[True, False])[['Name','DeptID','Salary','Row_Number','Dense_Rank']])
 
# LEAD / LAG via shift
df_merged = df_merged.sort_values(['DeptID','Salary'], ascending=[True, False])
df_merged['Prev_Salary'] = df_merged.groupby('DeptID')['Salary'].shift(1)   # LAG
df_merged['Next_Salary'] = df_merged.groupby('DeptID')['Salary'].shift(-1)  # LEAD
print(df_merged[['Name','Salary','Prev_Salary','Next_Salary']].head(6))

Where next?

Run the same joins and windows in SQL per SQL JOIN fan-out and window functions, then store the result via database connectivity when moving to a warehouse.

Download the Notebook and Practise

This article is a walkthrough of a runnable Jupyter notebook. Download the original .ipynb and run it locally or on Colab — every code block above appears in order.

Continue your track: Data Analyst Roadmap · Python and Pandas Guide · SQL NULL Handbook · SQL JOIN Fan-Out · Topfolio Practice · Data Analyst vs Engineer

Dataset generators where applicable are in courses/workbooks/generators/ — see citations atop for the exact *.py source for this notebook.

Frequently Asked Questions

When do you use merge vs join vs concat?

merge for SQL-like joins on columns, join for index-aligned joins, concat for stacking rows or columns. Most analyst work is merge.

What is the difference between rank(method='first') and 'dense'?

'first' gives unique ranks breaking ties by order (SQL ROW_NUMBER); 'dense' gives ties the same rank with no gaps (SQL DENSE_RANK); 'min' gives SQL RANK semantics.

Why set_index before shift/rolling?

Setting a sorted index (e.g., EmpID or date) guarantees groupby shift sees rows in the right order; otherwise lag reads the wrong predecessor.

How do you mimic SQL LEAD/LAG in Pandas?

df.groupby('DeptID')['Salary'].shift(1) is LAG(1), shift(-1) is LEAD(1). Combine with rank for full window-function parity.