Lesson 11 · Data analytics zero to hero
Pandas Merge, Join & Pivot Explained | Data Analytics #11
Combining multiple real tables into one, and reshaping between wide and long formats. We're switching datasets: a real extract of the Olist Brazilian…
- CourseData analytics zero to hero
- Lesson11 of 30
- Video12 min
- FormatJupyter notebook · 9 code cells
- Data6 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- orders_sample.csv preview205.5 KB
- customers_sample.csv preview101.0 KB
- order_items_sample.csv preview176.5 KB
- products_sample.csv preview91.4 KB
- category_translation.csv preview2.5 KB
- payments_sample.csv preview66.1 KB
📓 Full notebook
Download .ipynbData Analytics Zero to Hero, Video 11: Merging, Joining, and Reshaping#
- Video eleven of the 30-part series: combining multiple real tables into one, and reshaping between wide and long formats.
- We're switching datasets: a real extract of the Olist Brazilian E-Commerce dataset, split naturally across separate tables for orders, customers, items, and products, exactly like a real production database.
- Let's jump straight in.
Before You Start#
- Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
- Place orders_sample.csv, customers_sample.csv, order_items_sample.csv, products_sample.csv, category_translation.csv, and payments_sample.csv in the same folder as this notebook.
Part 1: Loading Multiple Real Tables#
import pandas as pd
orders = pd.read_csv('orders_sample.csv')
customers = pd.read_csv('customers_sample.csv')
items = pd.read_csv('order_items_sample.csv')
products = pd.read_csv('products_sample.csv')
print(orders.shape, customers.shape, items.shape, products.shape)
print(orders[['order_id', 'customer_id', 'order_status']].head(3))
print(items[['order_id', 'product_id', 'price']].head(3))
Part 2: merge and Inner Joins#
orders_customers = orders.merge(customers, on='customer_id', how='inner')
print(orders_customers.shape)
print(orders_customers[['order_id', 'customer_id', 'customer_state']].head(3))
Part 3: Left, Right, and Outer Joins#
order_items_full = items.merge(products, on='product_id', how='left')
print(order_items_full.shape)
print(order_items_full['product_category_name'].isna().sum())
translation = pd.read_csv('category_translation.csv')
print(translation.head(3))
order_items_full = order_items_full.merge(translation, on='product_category_name', how='left')
print(order_items_full[['product_category_name', 'product_category_name_english']].drop_duplicates().head(5))
Part 4: Building One Combined Table#
payments = pd.read_csv('payments_sample.csv')
full = (orders
.merge(customers, on='customer_id', how='left')
.merge(items, on='order_id', how='left')
.merge(products, on='product_id', how='left')
.merge(payments, on='order_id', how='left'))
print(full.shape)
print(full[['order_id', 'customer_state', 'price', 'payment_type']].head(5))
Part 5: concat#
delivered = orders[orders['order_status'] == 'delivered'].head(3)
shipped = orders[orders['order_status'] == 'shipped'].head(3)
combined = pd.concat([delivered, shipped])
print(combined[['order_id', 'order_status']])
Part 6: Reshaping melt and pivot#
state_totals = full.groupby('customer_state')['price'].sum().reset_index().head(6)
print(state_totals)
long_form = state_totals.melt(id_vars='customer_state', var_name='metric', value_name='value')
print(long_form)
Wrap-Up: What You Learned#
- merge for combining two tables on a shared key, and the four join types: inner, left, right, and outer.
- Chaining multiple merges together to build one wide table from several real source tables.
- concat for stacking DataFrames by position rather than by key.
- melt for reshaping wide data into long, tidy rows.
- All of it run against a real, multi-table e-commerce dataset, exactly the shape real production data usually comes in.
- Video twelve covers time series analysis with pandas: resampling, rolling windows, and real date-based trends. Subscribe so it lands automatically see you there.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
What does
orders.merge(customers, on='customer_id', how='left')keep?A left join keeps every row from the left table (orders) and fills NaN where the right table has no match.
An inner join of tables A (5 rows) and B (3 rows) where only 2 keys match gives how many rows (assuming unique keys)?
An inner join keeps only rows whose keys appear in both tables.
What does
pd.concat([df1, df2])do by default?concat stacks DataFrames by position, with rows of df2 added under df1, without matching on keys.
Why use
how='left'when merging items with products and then checkproduct_category_name.isna().sum()?After a left join, NaN in a column from the right table reveals rows that had no match.
What does
meltdo?melt reshapes wide data into long, tidy form with id columns kept and other columns stacked into variable/value pairs.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Merge
ordersandcustomerson customer_id with an inner join and print the result.💡 Hint
Use orders.merge(customers, on='customer_id', how='inner').
✅ Show solution
import pandas as pd orders = pd.DataFrame({'order_id': ['o1', 'o2', 'o3'], 'customer_id': ['c1', 'c2', 'c9']}) customers = pd.DataFrame({'customer_id': ['c1', 'c2', 'c3'], 'customer_state': ['SP', 'RJ', 'MG']}) print(orders.merge(customers, on='customer_id', how='inner'))
Left-merge items with products on product_id, then count how many items have no product match (missing category).
💡 Hint
After the merge, use isna().sum() on a product column.
✅ Show solution
import pandas as pd items = pd.DataFrame({'order_id': ['o1', 'o1', 'o2'], 'product_id': ['p1', 'p2', 'p9'], 'price': [10.0, 25.0, 7.5]}) products = pd.DataFrame({'product_id': ['p1', 'p2'], 'product_category_name': ['toys', 'books']}) full = items.merge(products, on='product_id', how='left') print(full) print(full['product_category_name'].isna().sum())
Chain two merges to combine orders, customers and items; then group total price by state; stack two small DataFrames with concat; and melt the state totals into long form.
💡 Hint
Chain .merge(...).merge(...); use groupby(...).sum().reset_index() and melt(id_vars=...).
✅ Show solution
import pandas as pd orders = pd.DataFrame({'order_id': ['o1', 'o2', 'o3'], 'customer_id': ['c1', 'c2', 'c3']}) customers = pd.DataFrame({'customer_id': ['c1', 'c2', 'c3'], 'customer_state': ['SP', 'RJ', 'SP']}) items = pd.DataFrame({'order_id': ['o1', 'o2', 'o3'], 'price': [10.0, 20.0, 5.0]}) full = orders.merge(customers, on='customer_id', how='left').merge(items, on='order_id', how='left') state_totals = full.groupby('customer_state')['price'].sum().reset_index() print(state_totals) stacked = pd.concat([orders.head(1), orders.tail(1)]) print(stacked) long_form = state_totals.melt(id_vars='customer_state', var_name='metric', value_name='value') print(long_form)
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



