Duplicate Data: Identification and Resolution

Identifying and Handling Duplicate Records

Duplicate records are rows in your dataset that are identical. They can arise from various sources, such as data entry errors, bugs in data collection pipelines, or incorrect table joins. If left unaddressed, they give undue weight to certain observations, which can skew statistical summaries and bias the training of a machine learning model.

Pandas provides a straightforward and powerful way to manage duplicates. The process involves two main methods: duplicated() for identification and drop_duplicates() for removal.

The video below from the Data School channel offers a concise and thorough explanation of these methods. It is an excellent resource for mastering the fundamentals of duplicate handling.

As the video demonstrates, defining a duplicate isn't always about the entire row being identical. Often, you'll define a duplicate based on a key set of columns. For instance, in a dataset of user events, you might consider a record a duplicate if it has the same user_id and timestamp, even if other columns (like session_id) differ. The subset parameter is your tool for handling these more nuanced scenarios.

Let's look at a quick practical example.

import pandas as pd

data = {
    'user_id': [101, 102, 103, 101, 104, 102],
    'product': ['A', 'B', 'A', 'A', 'C', 'B'],
    'timestamp': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-01', '2023-01-03', '2023-01-01']
}
df = pd.DataFrame(data)

print("--- Original DataFrame ---")
print(df)




# Count full duplicates
full_duplicates = df.duplicated().sum()
print(f"\nNumber of full duplicate rows: {full_duplicates}")
print("Viewing the full duplicate row(s):")
print(df[df.duplicated(keep=False)]) # keep=False shows all occurrences of duplicates




# Count duplicates based on a subset of columns
subset_duplicates = df.duplicated(subset=['user_id', 'timestamp']).sum()
print(f"\nNumber of duplicates based on user_id and timestamp: {subset_duplicates}")




# Drop full duplicates, keeping the first instance
df_cleaned = df.drop_duplicates()
print("\n--- DataFrame after dropping full duplicates ---")
print(df_cleaned)




# Drop duplicates based on subset
df_cleaned_subset = df.drop_duplicates(subset=['user_id', 'timestamp'], keep='first')
print("\n--- DataFrame after dropping duplicates based on user_id and timestamp ---")
print(df_cleaned_subset)

Resolving Common Data Inconsistencies

Beyond complete row duplication, datasets are often plagued by more subtle inconsistencies within columns. These can take many forms:

• Formatting issues: Unwanted characters, leading/trailing whitespace, inconsistent capitalization.
• Structural problems: A column of numbers being read as text, or a column of identifiers (like phone numbers) being misinterpreted as numeric.
• Inconsistent representation: "USA" vs "U.S.A.", or "Y" vs "Yes".
• Compound information: A single "Address" column containing street, city, state, and zip code.

Cleaning these inconsistencies is a methodical process of standardization. The following video from Alex The Analyst provides a fantastic, step-by-step walkthrough of cleaning a messy dataset using pandas. It covers many of the most common scenarios you will encounter.

To supplement the video, the following article provides a well-structured written guide to a similar cleaning process. It highlights some key initial inspection steps and reinforces the techniques for fixing data types and cleaning string values.

Advanced Inconsistencies: Record Linkage and Fuzzy Matching

So far, we have dealt with inconsistencies that can be solved with exact rules. But what about cases like "John O'neel" vs. "John Oneel", or "16 M. Street" vs. "Raleigh, 16M St."? These are different string values that refer to the same real-world entity. This is a very common and challenging problem, especially when trying to merge datasets that lack a clean, unique identifier.

This problem is known as record linkage or fuzzy matching. Solving it requires more advanced techniques that calculate the "distance" or "similarity" between strings, rather than relying on exact equality. While a full tutorial is beyond this lesson's scope, it is important for you to be aware of this class of problem and the tools available to solve it. Your background in algorithms will help you appreciate the computational complexity involved in these string-matching algorithms.

The following article provides an excellent introduction to this topic.

Understanding record linkage is a key differentiator for a data scientist. While you won't need to implement it for our course's practicals, knowing that these tools exist is vital for tackling complex, real-world data integration projects.

Conclusion

This lesson concludes our foundational module on data exploration and cleaning. You are now equipped with the essential skills to take a raw dataset and systematically prepare it for analysis.

Here are the key takeaways from this lesson:

• Duplicate Records: Use df.duplicated() and df.drop_duplicates(), along with the subset and keep parameters, to manage identical or partially identical rows.
• Data Inconsistencies: Employ a combination of astype() to fix types, string methods (.str.strip(), .str.replace(), .str.split()) for formatting, and .apply() with lambda functions for complex transformations.
• Methodical Approach: Data cleaning is a step-by-step process of inspection, diagnosis, and treatment.
• Advanced Problems: For complex inconsistencies like fuzzy name and address matching, be aware of specialized tools like the recordlinkage library.

With our data now clean and consistent, we are ready to move from fixing problems to creating value. The next module, Feature Engineering and Preprocessing, will build directly on this foundation. Our first lesson will cover the crucial step of creating a train-test split, a fundamental practice for preventing data leakage and building models that generalize well to new, unseen data.