Duplicates on specific columns in Pandas - Mini Project: Build & Apply

Detecting Duplicates on Specific Columns with pandas

📖 Scenario: You work in a retail company. You have a list of sales records. Sometimes, the same customer buys the same product more than once. You want to find these repeated purchases by checking duplicates only on the CustomerID and ProductID columns.

🎯 Goal: Build a small program that creates a sales data table, sets the columns to check for duplicates, finds the duplicate rows based on those columns, and prints the duplicate rows.

📋 What You'll Learn

Create a pandas DataFrame with sales data including CustomerID, ProductID, and Quantity columns.

Create a list variable with the column names CustomerID and ProductID to check duplicates on.

Use pandas duplicated() method with the subset parameter to find duplicates based on those columns.

Print the duplicate rows from the DataFrame.

💡 Why This Matters

🌍 Real World

Retail companies often want to find repeated purchases by the same customer for the same product to analyze buying patterns or detect errors.

💼 Career

Data analysts and data scientists frequently use pandas to clean and analyze data, including finding duplicates based on specific columns.

1

Create the sales data DataFrame

Import pandas as pd. Create a DataFrame called sales with these exact rows and columns: CustomerID, ProductID, and Quantity. Use this data: (1, 101, 2), (2, 102, 1), (1, 101, 3), (3, 103, 5), (2, 102, 2).

Pandas

import pandas as pd
# Create the sales DataFrame with the exact data
# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of column values.

2

Set the columns to check for duplicates

Create a list variable called cols_to_check that contains the strings 'CustomerID' and 'ProductID'.

Pandas

import pandas as pd
sales = pd.DataFrame({
    'CustomerID': [1, 2, 1, 3, 2],
    'ProductID': [101, 102, 101, 103, 102],
    'Quantity': [2, 1, 3, 5, 2]
})
# Create the list cols_to_check with 'CustomerID' and 'ProductID'
# Your code here

Hint

Just create a list with the two column names as strings.

3

Find duplicate rows based on specific columns

Create a variable called duplicates that stores the rows from sales where duplicates exist based on the columns in cols_to_check. Use sales.duplicated(subset=cols_to_check, keep=False) to find all duplicate rows.

Pandas

import pandas as pd
sales = pd.DataFrame({
    'CustomerID': [1, 2, 1, 3, 2],
    'ProductID': [101, 102, 101, 103, 102],
    'Quantity': [2, 1, 3, 5, 2]
})
cols_to_check = ['CustomerID', 'ProductID']
# Find duplicate rows based on cols_to_check and store in duplicates
# Your code here

Hint

Use sales.duplicated() with subset=cols_to_check and keep=False to mark all duplicates, then filter sales with that boolean mask.

4

Print the duplicate rows

Write a print statement to display the duplicates DataFrame.

Pandas

import pandas as pd
sales = pd.DataFrame({
    'CustomerID': [1, 2, 1, 3, 2],
    'ProductID': [101, 102, 101, 103, 102],
    'Quantity': [2, 1, 3, 5, 2]
})
cols_to_check = ['CustomerID', 'ProductID']
duplicates = sales[sales.duplicated(subset=cols_to_check, keep=False)]
# Print the duplicates DataFrame
# Your code here

Hint

Just use print(duplicates) to show the duplicate rows.

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