WikiGalaxy

Personalize

Merging DataFrames on Keys

Concept Overview:

Merging DataFrames is a powerful feature in data manipulation, allowing you to combine data from different sources based on common keys. This process is essential for data analysis, enabling the integration of disparate datasets into a cohesive structure for comprehensive insights.

Example 1: Inner Join

Inner Join:

An inner join merges DataFrames based on the intersection of keys, retaining only rows with matching keys in both DataFrames.


import pandas as pd

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

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

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

Explanation:

The above code demonstrates an inner join on the 'key' column, resulting in a DataFrame that includes rows where the 'key' is present in both df1 and df2.

Console Output:

A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 A2 B2 K2 C2 D2

Example 2: Left Join

Left Join:

A left join returns all rows from the left DataFrame and the matched rows from the right DataFrame, filling in NaNs for unmatched rows.


import pandas as pd

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

df2 = pd.DataFrame({'C': ['C0', 'C1'],
                    'D': ['D0', 'D1'],
                    'key': ['K0', 'K1']})

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

Explanation:

In this example, a left join is performed, resulting in all rows from df1 and matching rows from df2. Rows without matches in df2 are filled with NaN.

Console Output:

A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 A2 B2 K2 NaN NaN

Example 3: Right Join

Right Join:

A right join returns all rows from the right DataFrame and the matched rows from the left DataFrame, filling in NaNs for unmatched rows.


import pandas as pd

df1 = pd.DataFrame({'A': ['A0', 'A1'],
                    'B': ['B0', 'B1'],
                    'key': ['K0', 'K1']})

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

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

Explanation:

This right join includes all rows from df2 and matching rows from df1, with unmatched rows in df1 filled with NaN.

Console Output:

A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 NaN NaN K2 C2 D2

Example 4: Outer Join

Outer Join:

An outer join returns all rows from both DataFrames, filling in NaNs for unmatched rows from either DataFrame.


import pandas as pd

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

df2 = pd.DataFrame({'C': ['C0', 'C1'],
                    'D': ['D0', 'D1'],
                    'key': ['K0', 'K1']})

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

Explanation:

The outer join includes all rows from both df1 and df2, filling unmatched rows with NaN.

Console Output:

A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 A2 B2 K2 NaN NaN

Example 5: Cross Join

Cross Join:

A cross join returns the Cartesian product of two DataFrames, resulting in a DataFrame where each row from the first DataFrame is paired with every row from the second DataFrame.


import pandas as pd

df1 = pd.DataFrame({'A': ['A0', 'A1'],
                    'B': ['B0', 'B1']})

df2 = pd.DataFrame({'C': ['C0', 'C1'],
                    'D': ['D0', 'D1']})

result = pd.merge(df1.assign(key=1), df2.assign(key=1), on='key').drop('key', axis=1)
print(result)
        

Explanation:

The cross join results in a DataFrame with every combination of rows from df1 and df2, achieved by assigning a constant key to both DataFrames and merging on this key.

Console Output:

A B C D 0 A0 B0 C0 D0 1 A0 B0 C1 D1 2 A1 B1 C0 D0 3 A1 B1 C1 D1

logo of wikigalaxy

Newsletter

Subscribe to our newsletter for weekly updates and promotions.

Privacy Policy

 • 

Terms of Service

Copyright © WikiGalaxy 2025