Lesson 11 · Data analytics zero to hero

Pandas Merge, Join & Pivot Explained | Data Analytics #11

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…

⬇ Download notebook ⬇ Notebook + data (.zip)Open in Colab ↗

CourseData analytics zero to hero
Lesson11 of 30
Video12 min
FormatJupyter notebook · 9 code cells
Data6 datasets

pandas

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data 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)

(1200, 8) (1200, 5) (1365, 7) (1105, 9)

print(orders[['order_id', 'customer_id', 'order_status']].head(3))
print(items[['order_id', 'product_id', 'price']].head(3))

                           order_id                       customer_id  \
0  e481f51cbdc54678b7cc49136f2d6af7  9ef432eb6251297304e76186b10a928d   
1  53cdb2fc8bc7dce0b6741e2150273451  b0830fb4747a6c6d20dea0b8c802d7ef   
2  47770eb9100c2d0c44946d9cf07ec65d  41ce2a54c0b03bf3443c3d931a367089   

  order_status  
0    delivered  
1    delivered  
2    delivered  
                           order_id                        product_id  price
0  00125cb692d04887809806618a2a145f  1c0c0093a48f13ba70d0c6b0a9157cb7  109.9
1  00571ded73b3c061925584feab0db425  8695c431b31927efef5343e675f279e7  179.9
2  00571ded73b3c061925584feab0db425  8695c431b31927efef5343e675f279e7  179.9

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))

(1200, 12)
                           order_id                       customer_id  \
0  e481f51cbdc54678b7cc49136f2d6af7  9ef432eb6251297304e76186b10a928d   
1  53cdb2fc8bc7dce0b6741e2150273451  b0830fb4747a6c6d20dea0b8c802d7ef   
2  47770eb9100c2d0c44946d9cf07ec65d  41ce2a54c0b03bf3443c3d931a367089   

  customer_state  
0             SP  
1             BA  
2             GO

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())

(1365, 15)
12

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))

    product_category_name product_category_name_english
0            beleza_saude                 health_beauty
1  informatica_acessorios         computers_accessories
2              automotivo                          auto
  product_category_name product_category_name_english
0      moveis_decoracao               furniture_decor
1            perfumaria                     perfumery
3              pet_shop                      pet_shop
5    relogios_presentes                 watches_gifts
7       cama_mesa_banho                bed_bath_table

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))

(1429, 30)
                           order_id customer_state   price payment_type
0  e481f51cbdc54678b7cc49136f2d6af7             SP   29.99  credit_card
1  e481f51cbdc54678b7cc49136f2d6af7             SP   29.99      voucher
2  e481f51cbdc54678b7cc49136f2d6af7             SP   29.99      voucher
3  53cdb2fc8bc7dce0b6741e2150273451             BA  118.70       boleto
4  47770eb9100c2d0c44946d9cf07ec65d             GO  159.90  credit_card

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']])

                             order_id order_status
0    e481f51cbdc54678b7cc49136f2d6af7    delivered
1    53cdb2fc8bc7dce0b6741e2150273451    delivered
2    47770eb9100c2d0c44946d9cf07ec65d    delivered
44   ee64d42b8cf066f35eac1cf57de1aa85      shipped
154  6942b8da583c2f9957e990d028607019      shipped
162  36530871a5e80138db53bcfd8a104d90      shipped

Part 6: Reshaping melt and pivot#

state_totals = full.groupby('customer_state')['price'].sum().reset_index().head(6)
print(state_totals)

  customer_state     price
0             AC    199.00
1             AL    525.58
2             AM     23.99
3             AP    189.90
4             BA  10190.21
5             CE   3642.92

long_form = state_totals.melt(id_vars='customer_state', var_name='metric', value_name='value')
print(long_form)

  customer_state metric     value
0             AC  price    199.00
1             AL  price    525.58
2             AM  price     23.99
3             AP  price    189.90
4             BA  price  10190.21
5             CE  price   3642.92

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.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help