Joining & Combining Data

Overview

Combine datasets vertically and horizontally using merge() joins and concat() stacking.

Learning Objectives

• Perform SQL-style joins on key columns using merge().
• Combine DataFrames along axes using concat().
• Manage join keys and indexes.

Concept Explanation

Use merge() to combine DataFrames on key columns (similar to SQL JOINs). Specify the join type using the how parameter ('inner', 'left', 'right', 'outer'). Use concat() to stack datasets (similar to SQL UNION).

Code Examples

Example 1 — Basics

This example introduces the fundamental syntax and concepts.

import pandas as pd
# combined = pd.merge(df1, df2, on='user_id', how='inner')
# stacked = pd.concat([df1, df2], axis=0)

Example 2 — Everyday Usage

This example demonstrates a realistic scenario handling business parameters.

# Merge customer and order tables
users = pd.DataFrame({'user_id': [1, 2], 'name': ['Alice', 'Bob']})
orders = pd.DataFrame({'user_id': [1, 1], 'amount': [50, 100]})
merged = pd.merge(users, orders, on='user_id', how='left')
print(merged)

Example 3 — Practical Data Engineering Example

This example shows clean, production-grade code structure following senior development standards.

# Concat (stack) tables vertically
month1 = pd.DataFrame({'user': ['Alice'], 'sales': [100]})
month2 = pd.DataFrame({'user': ['Bob'], 'sales': [200]})
# Stack tables and reset indices
total_sales = pd.concat([month1, month2], axis=0).reset_index(drop=True)
print(total_sales)

Visual Flow

The following execution flow represents the step-by-step evaluation inside the interpreter:

Table A + Table B → Match key values (merge) OR Stack rows (concat) → Combined Table

Common Mistakes

Review these common pitfalls when working with this topic:

• Merging DataFrames with different column data types (raises conversion or lookup errors).
• Not specifying the merge key with the 'on' parameter, which causes Pandas to guess keys based on column names.
• Creating duplicate rows when merging on keys with duplicate entries.
• Concatenating tables with mismatched column names, which introduces unexpected NaN values.

Best Practices

Enforce these Pythonic best practices in your codebase:

pd.merge(df1, df2) # Missing key columns declarations

pd.merge(df1, df2, on='id', how='inner') # Explicit keys and join types

Performance Notes

Keep these optimization guidelines in mind for performance-sensitive hotpaths:

• Merging large datasets can cause memory spikes. Specify join keys explicitly and filter rows beforehand.

Quick Revision

Use these key summaries for last-minute revision:

• merge() combines tables on key columns (like SQL joins).
• concat() stacks tables vertically (axis=0) or horizontally (axis=1).
• how parameter defaults to 'inner'.
• Use reset_index(drop=True) after stacking vertically.
• Ensure join keys have matching data types.