Duplicates on specific columns in Pandas - Step-by-Step Execution

Concept Flow - Duplicates on specific columns

Start with DataFrame

↓

Select specific columns

↓

Check duplicates in these columns

↓

Mark duplicates True/False

↓

Use result to filter or analyze

↓

End

We start with a DataFrame, select columns to check, find duplicates there, mark them, and then use this info to filter or analyze.

Execution Sample

Pandas

import pandas as pd

df = pd.DataFrame({
    'A': [1, 2, 2, 3],
    'B': ['x', 'y', 'y', 'z'],
    'C': [10, 20, 20, 30]
})

duplicates = df.duplicated(subset=['A', 'B'])

This code creates a DataFrame and finds duplicates based on columns 'A' and 'B'.

Execution Table

Index	Row Data (A,B,C)	Check Duplicates on ['A','B']	Is Duplicate?	Action
0	(1, x, 10)	First occurrence of (1,x)	False	Keep
1	(2, y, 20)	First occurrence of (2,y)	False	Keep
2	(2, y, 20)	Duplicate of index 1 (2,y)	True	Mark duplicate
3	(3, z, 30)	First occurrence of (3,z)	False	Keep

💡 All rows checked; duplicates marked True only if same values in columns 'A' and 'B' appeared before.

Variable Tracker

Variable	Start	After Index 0	After Index 1	After Index 2	After Index 3	Final
duplicates	empty	False	False	True	False	[False, False, True, False]

Key Moments - 2 Insights

Why is the third row marked as duplicate even though column 'C' is the same as the second row?

Duplicates are checked only on columns 'A' and 'B' as per subset=['A','B'], so column 'C' does not affect duplicate marking (see execution_table row 2).

What happens if we do not specify the subset parameter?

If subset is not specified, duplicates are checked on all columns, so rows must match exactly in all columns to be marked duplicate.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of duplicates at index 2?

A False

B True

C None

D Error

💡 Hint

Check the 'Is Duplicate?' column for index 2 in execution_table.

At which index does the duplicate condition become True for the first time?

A Index 2

B Index 1

C Index 0

D Index 3

💡 Hint

Look at the 'Is Duplicate?' column in execution_table to find the first True.

If we add column 'C' to the subset parameter, how would the duplicates array change?

A More duplicates would be marked True

B Duplicates would remain the same

C No duplicates would be marked True

D Code would error

💡 Hint

Check execution_table: rows 1 and 2 have the same value in columns 'A' and 'B' but also 'C'. Adding 'C' means duplicates must match all three columns, so the duplicate at index 2 would be False.

Concept Snapshot

Duplicates on specific columns:
- Use df.duplicated(subset=[col1, col2])
- Checks duplicates only on chosen columns
- Returns Boolean Series marking duplicates
- First occurrence is False, repeats are True
- Useful to filter or clean data by columns

Full Transcript

This visual execution shows how to find duplicates in a pandas DataFrame based on specific columns. We start with a DataFrame of four rows and three columns. We use the duplicated() method with subset=['A','B'] to check duplicates only on columns A and B. The method returns a Boolean list marking True for rows that repeat the same values in these columns. The first row with a unique combination is marked False. For example, the third row has the same values in A and B as the second row, so it is marked True as a duplicate. Column C is ignored in this check. This helps us identify duplicates based on selected columns only. The variable tracker shows how the duplicates list builds up step by step. The key moments clarify common confusions about which columns affect duplicates and what happens if subset is omitted. The quiz questions test understanding of the duplicate marking and effects of changing subset columns.

Practice

1. What does the subset parameter do in the duplicated() method in pandas?

easy

A. It specifies which columns to check for duplicates.

B. It removes duplicates from the DataFrame.

C. It sorts the DataFrame before checking duplicates.

D. It changes the data type of columns before checking duplicates.

Solution

1. Step 1: Understand the purpose of duplicated()

   The duplicated() method identifies duplicate rows in a DataFrame.

2. Step 2: Role of subset parameter

   The subset parameter limits the duplicate check to specific columns instead of all columns.

3. Final Answer:

   It specifies which columns to check for duplicates. -> Option A

4. Quick Check:

   subset = columns to check duplicates [OK]

Hint: Use subset to focus duplicate check on chosen columns [OK]

Common Mistakes:

• Thinking subset removes duplicates
• Confusing subset with sorting
• Assuming subset changes data types

2. Which of the following is the correct syntax to find duplicates based on columns 'A' and 'B' in a pandas DataFrame df?

easy

A. df.duplicated(subset=['A', 'B'])

B. df.duplicates(columns=['A', 'B'])

C. df.duplicated(columns=['A', 'B'])

D. df.find_duplicates(['A', 'B'])

Solution

1. Step 1: Recall pandas method for duplicates

   The correct method is duplicated() to find duplicate rows.

2. Step 2: Correct use of subset parameter

   The subset parameter takes a list of column names to check duplicates on.

3. Final Answer:

   df.duplicated(subset=['A', 'B']) -> Option A

4. Quick Check:

   Correct method and subset syntax [OK]

Hint: Use duplicated(subset=[...]) to specify columns [OK]

Common Mistakes:

• Using non-existent methods like duplicates()
• Passing columns instead of subset
• Wrong method names like find_duplicates()

3. Given the DataFrame:

import pandas as pd
data = {'A': [1, 2, 1, 3], 'B': [4, 5, 4, 6], 'C': [7, 8, 9, 10]}
df = pd.DataFrame(data)
print(df.duplicated(subset=['A', 'B']))

What is the output?

medium

A. [False, True, False, False]

B. [True, False, True, False]

C. [False, False, True, False]

D. [False, False, False, True]

Solution

1. Step 1: Understand the DataFrame and subset

   The DataFrame has rows with columns A and B: (1,4), (2,5), (1,4), (3,6).

2. Step 2: Identify duplicates on columns 'A' and 'B'

   The third row (index 2) duplicates the first row (index 0) on columns A and B.

3. Final Answer:

   [False, False, True, False] -> Option C

4. Quick Check:

   Only third row is duplicate on A and B [OK]

Hint: Check repeated (A,B) pairs for True in duplicated() output [OK]

Common Mistakes:

• Assuming duplicates on all columns
• Marking first occurrence as duplicate
• Confusing index positions

4. You run this code:

df.duplicated(subset=['A', 'B'], keep='last')

but get unexpected results. What is a common mistake causing this?

medium

A. Forgetting to pass a list to subset causes an error.

B. Using keep='last' marks the last occurrence as False, others as True.

C. Using keep='last' marks the first duplicates as True, not the last.

D. The duplicated() method does not accept keep parameter.

Solution

1. Step 1: Understand keep parameter behavior

   keep='last' marks all duplicates except the last occurrence as True.

2. Step 2: Identify common confusion

   Many expect it to mark the last duplicate as True, but it actually keeps the last as False (not duplicate).

3. Final Answer:

   Using keep='last' marks the last occurrence as False, others as True. -> Option B

4. Quick Check:

   keep='last' keeps last duplicate False [OK]

Hint: Remember keep='last' keeps last duplicate False, others True [OK]

Common Mistakes:

• Thinking keep='last' marks last duplicate True
• Passing subset as string instead of list
• Believing duplicated() lacks keep parameter

5. You have a DataFrame df with columns 'Name', 'Age', 'City'. You want to remove rows where 'Name' and 'City' are duplicates but keep the first occurrence. Which code correctly does this?

hard

A. df[~df.duplicated(subset=['Name', 'City'], keep='first')]

B. df = df.duplicated(subset=['Name', 'City'], keep='first')

C. df = df.drop_duplicates(columns=['Name', 'City'], keep='last')

D. df = df.drop_duplicates(subset=['Name', 'City'], keep='first')

Solution

1. Step 1: Identify method to remove duplicates

   Use drop_duplicates() to remove duplicate rows.

2. Step 2: Use subset and keep parameters correctly

   Pass subset=['Name', 'City'] and keep='first' to keep first occurrence and remove others.

3. Step 3: Check options

   df = df.drop_duplicates(subset=['Name', 'City'], keep='first') uses correct method and parameters. df[~df.duplicated(subset=['Name', 'City'], keep='first')] uses duplicated() with negation but does not assign back to df. df = df.drop_duplicates(columns=['Name', 'City'], keep='last') uses wrong parameter name. df = df.duplicated(subset=['Name', 'City'], keep='first') returns boolean Series, not DataFrame.

4. Final Answer:

   df = df.drop_duplicates(subset=['Name', 'City'], keep='first') -> Option D

5. Quick Check:

   drop_duplicates with subset and keep='first' removes duplicates [OK]

Hint: Use drop_duplicates(subset=[...], keep='first') to remove duplicates [OK]

Common Mistakes:

• Using duplicated() instead of drop_duplicates() to remove rows
• Passing columns instead of subset
• Not assigning result back to df