Merging and Joining DataFrames

Real projects often use data from more than one table. This topic explains how to combine tables correctly without losing or duplicating rows.

a. Concatenating DataFrames

Concatenation stacks tables on top of each other or places them side by side. Use pd.concat() when your tables have the same columns, like monthly files for the same report.

all_data = pd.concat([jan, feb, mar], ignore_index=True)

The ignore_index=True option creates a fresh row numbering. Without it, you will get repeated index values from the original tables.

To place tables side by side, use axis=1. This works when the rows in both tables line up in the same order. If they do not, use a merge instead.

b. Merging on Common Columns

Merging is like a VLOOKUP in Excel, but far more powerful. It combines two tables using a shared column, such as a customer ID.

merged = pd.merge(orders, customers, on="customer_id")

If the key column has different names in each table, use left_on and right_on. For example, pd.merge(orders, customers, left_on="cust_id", right_on="id").

Before merging, check that the key columns have the same data type. A number in one table and text in the other will not match. Also clean extra spaces and capital letters, as these cause silent mismatches.

c. Types of Joins

The how option controls which rows are kept. An inner join keeps only rows that match in both tables. This is the default.

A left join keeps all rows from the left table and adds matching data from the right. Rows with no match get NaN. A right join does the opposite. An outer join keeps everything from both tables.

pd.merge(orders, customers, on="customer_id", how="left")

Left joins are very common in analysis. For example, you can keep every order and add customer details where they exist. Choose the join type based on which rows you cannot afford to lose.

d. Handling Merge Issues

The most common issue is row duplication. If the key repeats in both tables, the merge creates every possible pair, and your row count grows. Always compare the row count before and after merging.

merged = pd.merge(a, b, on="id", how="left", validate="one_to_one")

The validate option tells Pandas to raise an error if the relationship is not what you expected. The indicator=True option adds a column that shows where each row came from. Both help you find problems fast.

When both tables have columns with the same name, Pandas adds suffixes like _x and _y. You can set your own with the suffixes option, such as suffixes=("_order", "_customer").