This lesson's interactive features are locked, please to keep using them
Understanding Cardinality
Cardinality is a mathematical term that has somewhat different meanings in different contexts. When talking about merging datasets, cardinality describes how many times each key appears in each table. It's a way to understand whether a merge will preserve rows or create extras.
A key is the column, or set of columns, used to link rows between datasets during a merge.
When you have duplicate keys, joins can create extra rows. If product CHL-482 appears twice in your products table and once in your sales table, that single sale will match both product rows and turn into two rows. If both tables have duplicates for the same key, the duplicate explosion gets much worse.
That's why cardinality matters: it tells you whether repeated keys are expected or not.
Click to play video
Types of Cardinality
One-to-One
Each key appears once in both tables.
sales: CHL-482, TST-731, OVN-205 (each appears once)
products: CHL-482, TST-731, OVN-205 (each appears once)
This is the easiest case, since there will be no duplicates after merging.
One-to-Many
Each key appears once in the left table, but can repeat in the right table.
sales: CHL-482, TST-731, OVN-205 (each appears once)
products: CHL-482, CHL-482, TST-731, OVN-205 (CHL-482 appears twice)
Many-to-Many
Both sides have duplicates.
sales: CHL-482, CHL-482, TST-731 (CHL-482 appears twice)
products: CHL-482, CHL-482, TST-731 (CHL-482 appears twice)
This is dangerous. 2 sales × 2 product rows = 4 rows for product CHL-482!
# 3 rows, CHL-482 appears twice
sales = pd.DataFrame(
{"product_id": ["CHL-482", "CHL-482", "TST-731"], "quantity": [10, 15, 20]}
)
# 3 rows, CHL-482 appears twice
products = pd.DataFrame(
{"product_id": ["CHL-482", "CHL-482", "TST-731"], "price": [100, 105, 200]}
)
merged = pd.merge(sales, products, on="product_id", how="left")
print(merged)
# product_id quantity price
# 0 CHL-482 10 100
# 1 CHL-482 10 105
# 2 CHL-482 15 100
# 3 CHL-482 15 105
# 4 TST-731 20 200
We started with 3 sales rows and got 5 merged rows, with 4 of them representing a single product ID.
Hover a sales row in the widget below to watch every result row it produced. Because CHL-482 appears twice on each side, that single key explodes into four result rows.
Many-to-many relationships exist in real data, and they aren't automatically wrong. The problem is joining on them carelessly. You can create duplicate rows that are hard to clean up later.
If you're seeing duplicate keys where there shouldn't be any (a bad export, a key that isn't actually unique, etc.), that's not a Pandas problem. It's a data quality problem worth addressing.