Python - Merge Pandas DataFrame with Inner Join

To merge Pandas DataFrame, use the merge() function. An inner join returns only the rows that have matching values in both DataFrames. It's implemented by setting the how parameter to "inner".

Syntax

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

How Inner Join Works

An inner join combines rows from two DataFrames where the join condition is met. Only matching records from both DataFrames are included in the result.

Creating Sample DataFrames

Let's create two DataFrames with some common and different car models ?

import pandas as pd

# Create DataFrame1
dataFrame1 = pd.DataFrame({
    "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'],
    "Units": [100, 150, 110, 80, 110, 90]
})

print("DataFrame1:")
print(dataFrame1)

DataFrame1:
       Car  Units
0      BMW    100
1    Lexus    150
2     Audi    110
3  Mustang     80
4  Bentley    110
5   Jaguar     90

import pandas as pd

# Create DataFrame2
dataFrame2 = pd.DataFrame({
    "Car": ['BMW', 'Lexus', 'Tesla', 'Mustang', 'Mercedes', 'Jaguar'],
    "Reg_Price": [7000, 1500, 5000, 8000, 9000, 6000]
})

print("DataFrame2:")
print(dataFrame2)

DataFrame2:
       Car  Reg_Price
0      BMW       7000
1    Lexus       1500
2    Tesla       5000
3  Mustang       8000
4 Mercedes       9000
5   Jaguar       6000

Performing Inner Join

Now we'll merge both DataFrames using inner join on the 'Car' column ?

import pandas as pd

# Create DataFrames
dataFrame1 = pd.DataFrame({
    "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'],
    "Units": [100, 150, 110, 80, 110, 90]
})

dataFrame2 = pd.DataFrame({
    "Car": ['BMW', 'Lexus', 'Tesla', 'Mustang', 'Mercedes', 'Jaguar'],
    "Reg_Price": [7000, 1500, 5000, 8000, 9000, 6000]
})

# Merge DataFrames with inner join
mergedRes = pd.merge(dataFrame1, dataFrame2, on='Car', how='inner')
print("Merged DataFrame with inner join:")
print(mergedRes)

Merged DataFrame with inner join:
       Car  Units  Reg_Price
0      BMW    100       7000
1    Lexus    150       1500
2  Mustang     80       8000
3   Jaguar     90       6000

Key Points

• Inner join returns only matching rows from both DataFrames
• Cars like 'Audi', 'Bentley' (only in DataFrame1) and 'Tesla', 'Mercedes' (only in DataFrame2) are excluded
• Only 'BMW', 'Lexus', 'Mustang', and 'Jaguar' appear in the result as they exist in both DataFrames
• The merged DataFrame contains all columns from both original DataFrames

Conclusion

Inner join with pd.merge() combines DataFrames by keeping only rows with matching values in the specified column. Use how='inner' when you need data that exists in both DataFrames.