Concatenation, Merging, and Joining

Real-world data rarely comes neatly packaged in a single file or table. Researchers often work with multiple datasets — different time periods, separate collections, or complementary tables — that must be combined before meaningful analysis can begin.

Pandas provides three main ways to bring datasets together:

• Concatenation, which stacks datasets vertically or horizontally,
• Merging, which combines datasets based on shared keys (similar to SQL joins), and
• Joining, which aligns datasets using their indexes.

Together, these tools allow analysts to construct unified tables from scattered sources — a crucial step in preparing data for exploration, visualization, and modeling.

Concatenation: Stacking DataFrames

Concatenation is the simplest way to combine multiple DataFrames. It “glues” them together along a particular axis — vertically (one below the other) or horizontally (side by side).

To demonstrate, let’s create three small DataFrames that represent separate portions of a dataset.

import pandas as pd

# Example DataFrames
df1 = pd.DataFrame({
    'A': ['A0', 'A1', 'A2', 'A3'],
    'B': ['B0', 'B1', 'B2', 'B3'],
    'C': ['C0', 'C1', 'C2', 'C3'],
    'D': ['D0', 'D1', 'D2', 'D3']
}, index=[0, 1, 2, 3])

df2 = pd.DataFrame({
    'A': ['A4', 'A5', 'A6', 'A7'],
    'B': ['B4', 'B5', 'B6', 'B7'],
    'C': ['C4', 'C5', 'C6', 'C7'],
    'D': ['D4', 'D5', 'D6', 'D7']
}, index=[4, 5, 6, 7])

df3 = pd.DataFrame({
    'A': ['A8', 'A9', 'A10', 'A11'],
    'B': ['B8', 'B9', 'B10', 'B11'],
    'C': ['C8', 'C9', 'C10', 'C11'],
    'D': ['D8', 'D9', 'D10', 'D11']
}, index=[8, 9, 10, 11])

These three DataFrames have identical column structures but represent different segments of the same dataset. To combine them vertically — one after another — we use pd.concat():

result = pd.concat([df1, df2, df3])
print(result)

      A    B    C    D
0    A0   B0   C0   D0
1    A1   B1   C1   D1
2    A2   B2   C2   D2
3    A3   B3   C3   D3
4    A4   B4   C4   D4
5    A5   B5   C5   D5
6    A6   B6   C6   D6
7    A7   B7   C7   D7
8    A8   B8   C8   D8
9    A9   B9   C9   D9
10  A10  B10  C10  D10
11  A11  B11  C11  D11

Here, all rows are stacked vertically because the columns match exactly. This approach is useful when combining datasets from multiple files (e.g., monthly reports or yearly surveys).

To concatenate horizontally, placing DataFrames side by side, we specify axis=1:

pd.concat([df1, df2, df3], axis=1)

This produces a wide table with columns from all three datasets aligned by their index values.

Merging: Combining by Keys

While concatenation simply stacks data, merging combines DataFrames based on common keys, much like joining tables in a database. It’s the most precise way to integrate related information — for example, linking authors to their publication records or countries to their population data.

Let’s begin with two DataFrames that share a common key column named “key”:

left = pd.DataFrame({
    'key': ['K0', 'K1', 'K2', 'K3'],
    'A': ['A0', 'A1', 'A2', 'A3'],
    'B': ['B0', 'B1', 'B2', 'B3']
})

right = pd.DataFrame({
    'key': ['K0', 'K1', 'K2', 'K3'],
    'C': ['C0', 'C1', 'C2', 'C3'],
    'D': ['D0', 'D1', 'D2', 'D3']
})

When merged, Pandas aligns the rows where the “key” values match:

pd.merge(left, right, how='inner', on='key')

Here, both DataFrames had matching keys, so every row merges perfectly. The how parameter controls the type of merge, just like in SQL joins:

• inner → keeps only matching keys (default),
• outer → keeps all keys from both tables, filling gaps with NaN,
• left → keeps all rows from the left DataFrame,
• right → keeps all rows from the right DataFrame.

For example, using an outer join:

pd.merge(left, right, how='outer', on='key')

This includes all keys, even those missing in one of the DataFrames, ensuring that no data is lost — though some cells may contain NaN where information is absent.

Merging is especially powerful when combining datasets from different sources that share a logical connection, such as linking metadata tables with measurement data.

Joining: Aligning by Index

While merging uses key columns, joining works by aligning datasets using their index values. It is a shortcut for combining DataFrames that already share index-based relationships.

Let’s create two small DataFrames with overlapping but not identical indexes:

left = pd.DataFrame({
    'A': ['A0', 'A1', 'A2'],
    'B': ['B0', 'B1', 'B2']
}, index=['K0', 'K1', 'K2'])

right = pd.DataFrame({
    'C': ['C0', 'C2', 'C3'],
    'D': ['D0', 'D2', 'D3']
}, index=['K0', 'K2', 'K3'])

Joining them aligns rows where the index matches:

left.join(right)

Only indexes present in both DataFrames (here K0 and K2) are included by default. To include all indexes from both sides, use an outer join:

left.join(right, how='outer')

Joining is ideal when combining tables that share index-based relationships, such as synchronizing time series or merging pre-aligned textual or image datasets.

Conclusion:

Combining datasets is a core step in any data analysis workflow. Through concatenation, merging, and joining, separate DataFrames can be unified into one coherent structure — enabling richer, more contextual analysis. These techniques form the groundwork for deeper exploration.

In the next chapter, we turn to Pandas operations — learning how to transform, filter, and compute within DataFrames to reveal meaningful patterns in data.

Chapter exercisesWorked answers after an attempt