Emeron's Blog

Introduction to Joining and Merging DataFrames in Pandas

Emeron Marcelle's photo
Emeron Marcelle
·

5 min read

Table of contents

When working with data in Python, especially in data analysis, combining datasets is a common task. Pandas offers powerful functions to join, merge, and concatenate DataFrames. Understanding these functions allows for efficient data manipulation, enabling you to align and combine datasets in various ways depending on your analytical needs.

In this blog, we’ll explore different Pandas methods to merge and join DataFrames. We will cover merge(), concat(), merge_ordered(), and merge_asof(), including examples and the resulting outputs.

1. merge(): Combining DataFrames by Columns

The merge() function in Pandas is similar to SQL joins. It is used to combine two DataFrames based on a key column or multiple key columns.

Basic Syntax:

pd.merge(tableName, on='ColumnName', how='inner', suffixes=('suf1', 'suf2'))

Example:

import pandas as pd

df1 = pd.DataFrame({
    'key': ['A', 'B', 'C', 'D'],
    'value1': [1, 2, 3, 4]
})

df2 = pd.DataFrame({
    'key': ['B', 'D', 'E', 'F'],
    'value2': [5, 6, 7, 8]
})

result = pd.merge(df1, df2, on='key')
print(result)

Output:

  key  value1  value2
0   B       2       5
1   D       4       6

Here, only the rows where key matches in both DataFrames (df1 and df2) are returned.

Other Joins:

  • Left Join:

  • A left join returns all rows from the left DataFrame (the first one specified) and the matched rows from the right DataFrame. If there is no match, the result will contain NaN for columns from the right DataFrame.

    How It Works:

    • All rows from the left DataFrame are kept.

    • Only the matching rows from the right DataFrame are included.

    • If no match is found, the columns from the right DataFrame will have NaN.

    result = pd.merge(df1, df2, on='key', how='left')
    print(result)

Output:

      key  value1  value2
    0   A       1     NaN
    1   B       2     5.0
    2   C       3     NaN
    3   D       4     6.0

  • Right Join:

  • A right join is the opposite of a left join. It returns all rows from the right DataFrame and the matched rows from the left DataFrame. If there is no match, the result will contain NaN for columns from the left DataFrame.

    How It Works:

    • All rows from the right DataFrame are kept.

    • Only the matching rows from the left DataFrame are included.

    • If no match is found, the columns from the left DataFrame will have NaN.

    result = pd.merge(df1, df2, on='key', how='right')
    print(result)

Output:

      key  value1  value2
    0   B     2.0       5
    1   D     4.0       6
    2   E     NaN       7
    3   F     NaN       8

  • Outer Join:

  • An outer join returns all rows from both DataFrames. When there is a match between the two DataFrames, it combines the data into one row. If there is no match, NaN is placed in the columns where data is missing from one of the DataFrames.

    How It Works:

    • All rows from both DataFrames are kept.

    • Matched rows from both DataFrames are combined.

    • Unmatched rows from either DataFrame will have NaN in the corresponding columns of the other DataFrame.

    result = pd.merge(df1, df2, on='key', how='outer')
    print(result)

Output:

      key  value1  value2
    0   A     1.0     NaN
    1   B     2.0     5.0
    2   C     3.0     NaN
    3   D     4.0     6.0
    4   E     NaN     7.0
    5   F     NaN     8.0

2. concat(): Combining DataFrames Vertically or Horizontally

The concat() function is used to concatenate two or more DataFrames along a particular axis (rows or columns).

Basic Syntax:

pd.concat([df1, df2], axis=0, join='inner', ignore_index=True)

Example:

df3 = pd.DataFrame({
    'key': ['A', 'B', 'C'],
    'value1': [10, 20, 30]
})

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

Output:

  key  value1
0   A       1
1   B       2
2   C       3
3   D       4
4   A      10
5   B      20
6   C      30

This concatenates df1 and df3 vertically, stacking them on top of each other.

3. merge_ordered(): Merge with Sorting

The merge_ordered() function is similar to merge() but includes an option to maintain order when merging.

Basic Syntax:

pd.merge_ordered(df1, df2, on='key', fill_method='ffill', how='outer')

Example:

df4 = pd.DataFrame({
    'key': ['A', 'B', 'C'],
    'value1': [1, 3, 5]
})

df5 = pd.DataFrame({
    'key': ['B', 'C', 'D'],
    'value2': [2, 4, 6]
})

result = pd.merge_ordered(df4, df5, on='key', fill_method='ffill')
print(result)

Output:

  key  value1  value2
0   A     1.0     NaN
1   B     3.0     2.0
2   C     5.0     4.0
3   D     NaN     6.0

Here, merge_ordered() merges df4 and df5 while maintaining the order of the key column and forward-filling missing values.

4. merge_asof(): Nearest Merge

The merge_asof() function is used to merge DataFrames on a key column, but it allows for merging on the nearest match rather than an exact match.

Basic Syntax:

pd.merge_asof(df1, df2, on='key', direction='nearest')

Example:

df6 = pd.DataFrame({
    'key': [1, 2, 3],
    'value1': [10, 20, 30]
})

df7 = pd.DataFrame({
    'key': [2, 3, 4],
    'value2': [40, 50, 60]
})

result = pd.merge_asof(df6, df7, on='key', direction='forward')
print(result)

Output:

   key  value1  value2
0    1      10    40.0
1    2      20    40.0
2    3      30    50.0

In this example, merge_asof() merges df6 and df7 by finding the nearest match in key, with a forward direction.

Conclusion

Understanding how to merge and join DataFrames is essential for efficient data manipulation in Pandas. These methods—merge(), concat(), merge_ordered(), and merge_asof()—provide powerful tools to align and combine data from multiple sources, each offering flexibility in handling different scenarios. Whether you're performing simple inner joins or complex ordered merges, mastering these functions will significantly enhance your data analysis capabilities.

pandas mergepandaspython beginnerPython 3