Essential Pandas Wrangling: Merging, GroupBy & reset_index

Combining tables and summarizing data across dimensions is the core engine of business analytics. This lesson covers modern table concatenation with pd.concat(), multi-table joins with pd.merge(), and GroupBy aggregation best practices.

1. Combining Tables: pd.concat() vs df.append()

import pandas as pd

q1_sales = pd.DataFrame({'rep': ['Alice', 'Bob'], 'sales': [100, 150]})
q2_sales = pd.DataFrame({'rep': ['Charlie', 'Diana'], 'sales': [200, 175]})

# Vertical Stacking (Row-wise):
full_year = pd.concat([q1_sales, q2_sales], axis=0, ignore_index=True)
# ignore_index=True ensures the output index is a clean 0, 1, 2, 3

2. Merging Tables with pd.merge() (SQL-Style Joins)

employees = pd.DataFrame({
    'emp_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
    'dept_id': [10, 20, 10, 30]
})

departments = pd.DataFrame({
    'id': [10, 20, 40],
    'dept_name': ['Engineering', 'Marketing', 'Finance']
})

# Left Join (Keep all employees, attach department name):
merged = pd.merge(
    employees,
    departments,
    left_on='dept_id',
    right_on='id',
    how='left'
)

Join Types Visualized:

• how='inner' (Default): Only keeps rows with matching keys in BOTH DataFrames.
• how='left': Keeps ALL rows from left DataFrame, fills unmatched right columns with NaN.
• how='right': Keeps ALL rows from right DataFrame.
• how='outer': Keeps all records from both DataFrames, filling unmatched fields with NaN.

3. GroupBy Aggregation Best Practices & reset_index()

When you run a groupby() aggregation in Pandas, the grouped columns become the Index of the resulting Series or DataFrame. To keep your results in a standard, flat tabular format, always append .reset_index().

sales_data = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South', 'East'],
    'product': ['Widget A', 'Widget B', 'Widget A', 'Widget B', 'Widget A'],
    'revenue': [1200, 850, 1400, 920, 1100],
    'units': [12, 8, 14, 9, 11]
})

# Multi-column GroupBy with .reset_index():
summary = sales_data.groupby('region')['revenue'].sum().reset_index()
# Output: Standard DataFrame with 'region' and 'revenue' columns

# Multiple aggregations with custom column names:
dept_summary = sales_data.groupby('region').agg(
    total_rev=('revenue', 'sum'),
    avg_rev=('revenue', 'mean'),
    total_units=('units', 'sum'),
    order_count=('product', 'count')
).reset_index()