Pandas DataFrame.align() method (5 examples)

Introduction

The align() method in Pandas is an incredibly useful but often underappreciated tool for data alignment and handling missing values while combining Series or DataFrame objects. This tutorial will guide you through five practical examples, escalating from basic to more advanced uses of the align() method.

When to Use align() Method?

Before diving into examples, let’s briefly understand what align() does. The align() method allows two Series or DataFrame objects to be aligned on their indexes (rows) and/or columns using a specified join method (e.g., 'outer', 'inner', 'left', 'right'). It returns a tuple of the same types after alignment, which can be very handy in ensuring data integrity across multiple datasets.

Example 1: Basic Alignment

In our first example, we’re aligning two simple DataFrames on their indexes, defaulting to an outer join.

import pandas as pd

# Create two DataFrames
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                   'B': ['B0', 'B1', 'B2']},
                  index=[0, 1, 2])

df2 = pd.DataFrame({'A': ['A2', 'A3', 'A4'],
                   'B': ['B2', 'B3', 'B4']},
                  index=[2, 3, 4])

# Align DataFrames
df1_aligned, df2_aligned = df1.align(df2)

print(df1_aligned)
print(df2_aligned)

Output:

     A    B
0   A0   B0
1   A1   B1
2   A2   B2
3  NaN  NaN
4  NaN  NaN

     A    B
0  NaN  NaN
1  NaN  NaN
2   A2   B2
3   A3   B3
4   A4   B4

This will align both DataFrames on their indexes, filling missing values with NaN, where necessary. By default, the method uses an ‘outer’ join, ensuring that the indexes of both DataFrames are included in the result.

Example 2: Column Alignment

Moving onto the second example, we examine how to align two DataFrames on their columns instead of their indexes.

import pandas as pd

# Again, creating two DataFrames
df1 = pd.DataFrame({'A': ['A1', 'A2', 'A3'],
                   'B': ['B1', 'B2', 'B3'],
                   'C': ['C1', 'C2', 'C3']})

df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6'],
                   'D': ['D4', 'D5', 'D6'],
                   'E': ['E4', 'E5', 'E6']})

# Align DataFrames on columns
df1_aligned, df2_aligned = df1.align(df2, axis=1)

print(df1_aligned)
print(df2_aligned)

Output:

    A   B   C   D   E
0  A1  B1  C1 NaN NaN
1  A2  B2  C2 NaN NaN
2  A3  B3  C3 NaN NaN

    A   B   C   D   E
0  A4 NaN NaN  D4  E4
1  A5 NaN NaN  D5  E5
2  A6 NaN NaN  D6  E6

Here, by specifying axis=1, we align the DataFrames on their columns. This operation fills missing columns in each DataFrame with NaN, applying a similar ‘outer’ join logic on columns.

Example 3: Specifying Join Type

In our third example, we explore how specifying a join type can affect the outcome of the alignment.

import pandas as pd

# Example DataFrames
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                   'B': ['B0', 'B1', 'B2']},
                  index=[0, 1, 2])

df2 = pd.DataFrame({'A': ['A2', 'A3', 'A4'],
                   'B': ['B2', 'B3', 'B4']},
                  index=[2, 3, 4])

# Align with 'inner' join




df1_aligned, df2_aligned = df1.align(df2, join='inner')

print(df1_aligned)
print(df2_aligned)

Output:

    A   B
2  A2  B2

    A   B
2  A2  B2

By specifying an 'inner' join, the alignment results only include the indexes (or columns, if axis=1) that are common to both DataFrames, effectively excluding any non-matching elements.

Example 4: Aligning with Different Axes

This more advanced example showcases the utility of aligning DataFrames on one axis while applying a different type of join on another.

import pandas as pd

# More complex DataFrames

df1 = pd.DataFrame({'A': ['A0', 'A1', 'A3', 'A5'],
                   'B': ['B0', 'B1', 'B3', 'B5'],
                   'C': ['C0', 'C1', 'C3', 'C5']},
                  index=[0, 1, 3, 5])


df2 = pd.DataFrame({'A': ['A2', 'A4', 'A6'],
                   'B': ['B2', 'B4', 'B6'],
                   'D': ['D2', 'D4', 'D6']},
                  index=[2, 4, 6])

# Align DataFrames on index using 'outer' join and on columns with 'inner'
df1_aligned, df2_aligned = df1.align(df2, join='inner', axis=1)

print(df1_aligned)
print(df2_aligned)

Output:

    A   B
0  A0  B0
1  A1  B1
3  A3  B3
5  A5  B5

    A   B
2  A2  B2
4  A4  B4
6  A6  B6

In this scenario, we’ve aligned the DataFrames on their columns using an ‘inner’ join. This means only the columns that are common to both DataFrames (A and B in this case) are retained, while others are excluded, demonstrating a selective alignment approach.

Example 5: Filling Missing Values on Alignment

Finally, we’ll cover how to fill missing values during the alignment process. This is particularly useful for maintaining data completeness.

import pandas as pd

# DataFrames with some missing values
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                   'B': ['B0', 'NaN', 'B2']},
                  index=[0, 1, 2])

df2 = pd.DataFrame({'A': ['A2', 'A3', 'A4'],
                   'B': ['B2', 'B3', 'B4']},
                  index=[2, 3, 4])

# Align DataFrames with default outer join, fill NaNs with 'missing'
df1_aligned, df2_aligned = df1.align(df2, fill_value='missing')

print(df1_aligned)
print(df2_aligned)

Output:

         A        B
0       A0       B0
1       A1      NaN
2       A2       B2
3  missing  missing
4  missing  missing

         A        B
0  missing  missing
1  missing  missing
2       A2       B2
3       A3       B3
4       A4       B4

In this example, by specifying a fill_value, we fill missing values with a placeholder (‘missing’ in this case), addressing the issue of NaN values resulting from the alignment without having to perform separate data cleaning steps.

Conclusion

The align() method is a powerful component of the Pandas library, providing flexible options for aligning data while handling missing values efficiently. Through these examples, we’ve seen the versatility of align() in different Alignment scenarios, highlighting its utility in a wide range of data manipulation tasks.