Python Pandas – Merge DataFrame with one-to-one relation

To merge Pandas DataFrames with a one-to-one relation, use the merge() function with the validate="one_to_one" parameter. This ensures merge keys are unique in both DataFrames ?

validate = "one_to_one"
# or
validate = "1:1"

The one-to-one validation checks if merge keys are unique in both left and right datasets, preventing duplicate matches.

Creating Sample DataFrames

Let's create two DataFrames with car information ?

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

Merging with One-to-One Validation

Now merge both DataFrames using one-to-one validation ?

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 with one-to-one validation
mergedRes = pd.merge(dataFrame1, dataFrame2, validate="one_to_one")
print("Merged DataFrame with one-to-one relation:")
print(mergedRes)

Merged DataFrame with one-to-one relation:
       Car  Units  Reg_Price
0      BMW    100       7000
1    Lexus    150       1500
2  Mustang     80       8000
3   Jaguar     90       6000

Key Points

Conclusion

Use validate="one_to_one" to ensure unique merge keys and prevent unexpected duplicates. Only rows with matching keys in both DataFrames will appear in the result.