Data rarely comes in a single file. Whether you're combining monthly logs or merging user profiles with purchase history, Pandas gives you the power to unify data sources into a single, cohesive view.
1Concatenation: The Stacker
Concatenation is the process of 'stacking' DataFrames. You can stack them vertically (adding more rows) or horizontally (adding more columns). It's the simplest way to unify datasets with identical structures.
2Merging: SQL-Style Joins
Merging is more precise. It allows you to combine DataFrames based on common values in specific columns (keys). This behaves exactly like SQL joins, supporting inner, outer, left, and right logic.
3Step-by-Step Breakdown
Data rarely comes in a single file. Pandas gives us three main tools to combine datasets: concat, merge, and join.
Let's start with pd.concat(). It literally 'stacks' DataFrames together. By default, it stacks them vertically (row-wise).
The result is a taller DataFrame. Notice how the index resets or duplicates unless you use ignore_index=True.
Checkpoint: Which parameter allows you to stack DataFrames horizontally instead of vertically?
pd.merge() is fundamentally different. It acts exactly like a SQL JOIN. It combines datasets horizontally based on common columns.
It finds matching 'id' values in both tables and merges their rows into one wide row.
What if 'Charlie' made no purchases? An 'inner' join drops him. A 'left' join keeps all users and puts NaN for missing purchases.
Checkpoint: If you want to keep ALL records from BOTH DataFrames, which join type should you use?
Ready to merge? Complete the engineering challenges below to earn your 'Join Juggler' achievement!
Run a Real Left Join. Finish left-joining users with purchases and confirm the unmatched row becomes NaN.
Level Up 🚀
Advanced cheat sheets, SEO tricks, and interview prep for this topic.
Browser Support
Fully supported.
Fully supported.
Fully supported.
Fully supported.
Accessibility (A11y)
1Document Which Join Type Produced a Table Before Displaying It
A merged table shown on a page should state which join type produced it ('Left join: all customers, purchases where available') directly in surrounding text — without this, a screen reader user or anyone unfamiliar with the pipeline has no way to know whether missing rows represent 'no data' or 'intentionally excluded'.
<p>Left join: all customers shown, purchases where available.</p>SEO Implications
- 1
Merged DataFrames Are Pipeline Intermediates, Not Pages
The result of a pd.merge() call is consumed by the rest of a script or notebook, never published as its own URL — this page's SEO value is entirely in its own explanation of concat/merge/join semantics, independent of the specific example datasets used to demonstrate them.
Best Practices
Always Specify the Join Column Explicitly with `on=`
Omitting the `on` parameter lets pd.merge() guess based on matching column names across both DataFrames, which can silently merge on the wrong column (or too many columns) if both tables happen to share an unrelated column name like 'id' or 'name'. Being explicit avoids this entire class of bug.
Check Row Counts Before and After a Merge
An unexpected many-to-many relationship between join keys (duplicate keys on both sides) can cause row counts to balloon far beyond either input table's size. Always compare len(result) against len(left) and len(right) after a merge to catch this early.
Frequent Bugs
Using pd.concat() when the actual intent was a key-based combination, or pd.merge() when simple stacking was intended.
concat() blindly stacks DataFrames by position (rows or columns) with no awareness of matching keys, while merge() combines rows specifically where key columns match. Using concat() to 'align' two tables with different row orders produces silently misaligned data — rows get stacked in whatever order they happen to be in, not matched by any shared identifier.
Real-World Examples
Combining Customer and Order Data for a Report
A monthly report needs every customer listed even if they made no purchases that month, so it uses pd.merge(customers, orders, on='customer_id', how='left') rather than an inner join — customers with no matching orders still appear in the result with NaN in the order-related columns, which the report then displays as '$0 in purchases' instead of silently omitting them.
report = pd.merge(customers, orders, on='customer_id', how='left')
report['total_spent'] = report['total_spent'].fillna(0)