Essential Pandas Wrangling: Merging, GroupBy Best Practices & reset_index

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

Important Note for Modern Pandas: df.append() was deprecated in Pandas 1.4 and completely removed in Pandas 2.0+ . Always use pd.concat() instead.

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