Removing Duplicates from DataFrames

1. What are Duplicates and Why Do They Matter?

A duplicate record is a row in your dataset that is either entirely identical to another row or identical based on a specific set of key columns.

Let's create an example DataFrame relevant to your AdTech background. Suppose we have data on user interactions with different ad creatives.

import pandas as pd

ad_interactions = {
    'user_id': [101, 102, 103, 101, 104, 102],
    'campaign_id': ['A', 'A', 'B', 'A', 'C', 'A'],
    'creative_id': ['c1', 'c2', 'c3', 'c1', 'c4', 'c2'],
    'event_timestamp': [
        '2023-10-01 10:00:00', '2023-10-01 10:05:00', '2023-10-01 10:06:00',
        '2023-10-01 10:00:00', '2023-10-01 10:10:00', '2023-10-01 10:12:00'
    ]
}
df = pd.DataFrame(ad_interactions)
df

If you look closely, the first row (index 0) and the fourth row (index 3) are identical. This is a clear duplicate. The second row (index 1) and last row (index 5) are duplicates if we only consider the user_id, campaign_id, and creative_id, even though their timestamps differ.

2. Identifying and Counting Duplicates

Before removing anything, you first need to confirm if duplicates exist and which rows they are. The .duplicated() method is your primary tool for this.

This video from Data School provides a clear demonstration of how to use .duplicated() to both identify and view duplicate rows.

As the video explained, .duplicated() returns True for each row that is a duplicate of a row that has already appeared earlier in the DataFrame.

Let's apply this to our df:

# Identify duplicate rows
df.duplicated()

Output:

0    False
1    False
2    False
3     True  # This row is a duplicate of row 0
4    False
5    False  # Note: This is NOT True because timestamps differ
dtype: bool

Just like we used .isnull().sum() to count missing values, we can use .duplicated().sum() to quickly count the number of duplicate rows.

# Count the number of complete duplicate rows
df.duplicated().sum() # Output: 1

To see the actual duplicate rows, you can use boolean indexing with .loc:

# Show the rows that were marked as duplicates
df.loc[df.duplicated()]

This will display the row at index 3.

3. Removing Duplicates with drop_duplicates()

Now that you can find duplicates, let's remove them using the drop_duplicates() method. This method is highly versatile thanks to its parameters, which allow for very precise control over the cleaning process.

This next video is a fantastic guide that walks through all the essential parameters you'll need.

Let's break down the key parameters from the video with our AdTech example.

Basic Usage

If you call drop_duplicates() with no arguments, it removes all rows that are exact duplicates of a previous one, keeping the first occurrence.

# Remove rows that are identical across all columns
df_no_exact_dupes = df.drop_duplicates()
print(df_no_exact_dupes)

This will remove the row at index 3, as it's an exact copy of the row at index 0.

The subset Parameter: Defining What a "Duplicate" Means

This is the most important parameter. It lets you specify which columns to consider when identifying duplicates. This is crucial for business logic. For example, you might decide that one user interacting with the same creative in the same campaign is a duplicate interaction, regardless of the timestamp.

# Define a duplicate as a row with the same user_id, campaign_id, and creative_id
key_columns = ['user_id', 'campaign_id', 'creative_id']

df_no_interaction_dupes = df.drop_duplicates(subset=key_columns)
print(df_no_interaction_dupes)

Now, the row at index 5 will also be dropped because its values in the key_columns are identical to the row at index 1. This is a far more powerful and common use case.

The keep Parameter: Deciding Which Record to Keep

Once a duplicate is found, keep tells pandas which one to retain.

• keep='first' (default): Keeps the first occurrence.
• keep='last': Keeps the last occurrence. This is useful if you want the most recent record.
• keep=False: Drops all instances of any duplicated record. This can be useful for isolating problematic data for investigation.

Let's say for our user_id 102, we only want to keep the most recent interaction.

# Keep the last interaction for any duplicate defined by our key columns
df_latest_interaction = df.drop_duplicates(subset=key_columns, keep='last')
print(df_latest_interaction)

In this case, the row at index 1 would be dropped, and the row at index 5 (the later one) would be kept.

The inplace=True Parameter

Just like with dropna() and fillna(), drop_duplicates() returns a new DataFrame by default. To modify your original DataFrame directly, you can use inplace=True.

# This will modify 'df' directly
df.drop_duplicates(subset=key_columns, keep='first', inplace=True)

Be careful with inplace=True, as it's a permanent change. It's often safer to assign the result to a new DataFrame until you're sure the operation is correct.

Here is a helpful image that summarizes the syntax we just discussed.

unique_users_first_interaction = df.drop_duplicates(subset=['user_id'], keep='first')
print(unique_users_first_interaction)

From Practice to Interview

The techniques you've learned are not just for daily tasks; they are standard topics in BA and Data Analyst interviews. Being able to clearly explain how you would handle duplicates shows that you understand the fundamentals of data integrity.

To help you prepare, here is a resource that frames this topic as an interview question.

Conclusion

You've now added another essential data cleaning skill to your toolkit. Along with handling missing data, managing duplicates is a foundational step that ensures the reliability of all your future analysis. Rushing this step is a common mistake that can lead to flawed conclusions.

Key Takeaways:

• Always begin by checking for duplicates with df.duplicated().sum().
• Use df.loc[df.duplicated()] to inspect the duplicate rows before taking action.
• The df.drop_duplicates() method is your main tool for removal.
• Use the subset parameter to define what constitutes a duplicate based on your specific business logic.
• Use the keep parameter ('first', 'last', or False) to control which records are retained.
• Remember that these operations create a new DataFrame unless you specify inplace=True.

Preview of the Next Lesson:
Now that we've learned how to clean our data by handling missing values and duplicates, we're ready to start enriching it. In the next lesson, we will focus on manipulating DataFrames by adding or modifying columns. This will allow us to create new, valuable information from our existing data, such as calculating metrics like Click-Through Rate (CTR) or Cost Per Click (CPC).