Merging & Joining DataFrames

Difficulty: Intermediate

Real-world data rarely lives in a single table. Customer information might be in one dataset, orders in another, and product details in a third. Combining these datasets is a fundamental operation in data analysis, and pandas provides several powerful tools for this: merge() for SQL-style joins, join() for index-based combining, and concat() for stacking DataFrames vertically or horizontally. Understanding how and when to use each is essential for any data analyst.

The merge() function is the most versatile tool for combining DataFrames. It works like SQL JOIN operations: you specify one or more key columns that exist in both DataFrames, and merge finds matching rows. The how parameter controls the join type: 'inner' (default) returns only rows with matching keys in both DataFrames, 'left' returns all rows from the left DataFrame plus matches from the right, 'right' returns all rows from the right DataFrame plus matches from the left, and 'outer' returns all rows from both DataFrames with NaN where matches are missing. The on parameter specifies the join column when both DataFrames share the same column name; use left_on and right_on when the key columns have different names.

The concat() function is used to stack DataFrames either vertically (axis=0, the default) or horizontally (axis=1). Vertical concatenation is like SQL UNION ALL: it appends rows from one DataFrame below another. This is useful when you have monthly data files with the same columns that need to be combined into a single dataset. Horizontal concatenation aligns DataFrames side by side based on their index. The ignore_index parameter resets the index in the result, which is almost always what you want when stacking vertically.

The join() method is a convenience wrapper around merge() that joins on the index by default. It is most commonly used when your DataFrames already have meaningful indexes (like customer IDs or dates). While join() is more concise for index-based operations, merge() with the on parameter is more explicit and generally preferred in production code because it makes the join keys obvious to anyone reading the code.

Understanding join types is critical for avoiding data loss or unexpected row duplication. An inner join can silently drop rows that do not match, which might mean losing important data. A left join preserves all rows from the primary DataFrame but introduces NaN where the secondary DataFrame has no match. An outer join keeps everything but may produce many NaN values. A common mistake is performing a merge that produces more rows than expected due to many-to-many relationships, so always check the shape of the result after merging. The validate parameter ('one_to_one', 'one_to_many', 'many_to_one') can catch unexpected duplicates during the merge.

Code examples

Inner and Left Merge

import pandas as pd

employees = pd.DataFrame({
    'EmpId': [1, 2, 3, 4],
    'Name': ['Alice', 'Bob', 'Charlie', 'Diana']
})

salaries = pd.DataFrame({
    'EmpId': [1, 2, 5],
    'Salary': [70000, 80000, 90000]
})

print("Inner merge:")
inner = pd.merge(employees, salaries, on='EmpId', how='inner')
print(inner)

print("\nLeft merge:")
left = pd.merge(employees, salaries, on='EmpId', how='left')
print(left)

Inner merge keeps only employees 1 and 2, where both tables have matching EmpId. Left merge keeps all 4 employees, filling in NaN for salary where no match exists. Employee 5 from the salaries table is excluded because it is not in the left (employees) table.

Outer and Right Merge

import pandas as pd

df1 = pd.DataFrame({'Key': ['A', 'B', 'C'], 'Val1': [1, 2, 3]})
df2 = pd.DataFrame({'Key': ['B', 'C', 'D'], 'Val2': [20, 30, 40]})

print("Outer merge:")
outer = pd.merge(df1, df2, on='Key', how='outer')
print(outer)

print("\nRight merge:")
right = pd.merge(df1, df2, on='Key', how='right')
print(right)

Outer merge includes all keys from both DataFrames (A, B, C, D) with NaN where a key is missing from one side. Right merge keeps all keys from df2 (B, C, D) and drops key A which only exists in df1.

Concatenating DataFrames Vertically

import pandas as pd

jan = pd.DataFrame({'Month': ['Jan', 'Jan'], 'Sales': [100, 150]})
feb = pd.DataFrame({'Month': ['Feb', 'Feb'], 'Sales': [200, 180]})
mar = pd.DataFrame({'Month': ['Mar'], 'Sales': [220]})

combined = pd.concat([jan, feb, mar], ignore_index=True)
print(combined)
print(f"\nTotal rows: {len(combined)}")

concat() with axis=0 (default) stacks DataFrames vertically. ignore_index=True resets the index to 0, 1, 2, ... instead of preserving the original indexes. This is the standard way to combine monthly or chunked data files.

Merge with Different Column Names

import pandas as pd

orders = pd.DataFrame({
    'OrderId': [101, 102, 103],
    'CustomerId': [1, 2, 1],
    'Amount': [250, 450, 300]
})

customers = pd.DataFrame({
    'CustId': [1, 2, 3],
    'Name': ['Alice', 'Bob', 'Charlie']
})

result = pd.merge(orders, customers, left_on='CustomerId', right_on='CustId')
result = result.drop(columns=['CustId'])
print(result)

When join key columns have different names, use left_on and right_on. After merging, both key columns appear in the result, so we drop the redundant 'CustId' column. Customer 3 (Charlie) is excluded because no orders reference them (inner join by default).

Key points

Concepts covered

merge, join, concat, inner join, outer join, left join, right join