Duplicates & Type Conversion

Difficulty: Intermediate

Duplicate rows are another common data quality issue. They can arise from multiple data imports, flawed ETL pipelines, accidental double-submissions in web forms, or merging overlapping datasets. Duplicates inflate counts, skew averages, and can cause misleading results in analysis. Pandas provides the duplicated() method to detect duplicates and drop_duplicates() to remove them, both of which offer fine-grained control over which columns to consider and which occurrence to keep.

The duplicated() method returns a Boolean Series that is True for rows that are duplicates of an earlier row. By default, it considers all columns and marks the first occurrence as False (not a duplicate) while subsequent identical rows are marked True. You can pass a subset parameter to check for duplicates based on specific columns only, which is useful when rows might have different values in non-key columns but represent the same entity. The keep parameter controls which duplicate is considered the 'original': 'first' (default), 'last', or False (mark all duplicates as True).

Once detected, drop_duplicates() removes duplicate rows. It accepts the same subset and keep parameters as duplicated(). A common pattern is to first examine duplicates with df[df.duplicated(keep=False)] to see all rows involved, then decide which to keep. For example, if you have duplicate customer records with slightly different timestamps, you might want to keep the most recent entry using keep='last' after sorting by date. Always verify the count of removed rows to ensure you are not over- or under-removing data.

Type conversion is equally important for data cleaning. Data loaded from CSV files, web APIs, or databases often arrives with incorrect types: numbers stored as strings, dates as plain text, or boolean-like values as integers. Pandas provides astype() for straightforward conversions, pd.to_numeric() for robust string-to-number conversion with error handling, and pd.to_datetime() for parsing dates from a variety of string formats. Using the correct types reduces memory usage, enables proper sorting and filtering, and is required for mathematical operations and time-based analysis.

The pd.to_numeric() function is particularly useful because it provides an errors parameter. Setting errors='coerce' converts unparsable values to NaN rather than raising an exception, which lets you clean dirty numeric columns in a single step. Similarly, pd.to_datetime() can parse dates from strings like '2024-01-15', 'Jan 15, 2024', or '15/01/2024' and supports a format parameter for ambiguous patterns. After conversion, you gain access to the dt accessor for extracting year, month, day, day of week, and other components.

Code examples

Detecting and Removing Duplicates

import pandas as pd

df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Alice', 'Charlie', 'Bob'],
    'Score': [85, 90, 85, 78, 90]
})

print("Original:")
print(df)
print("\nDuplicate mask:")
print(df.duplicated().tolist())
print("\nAfter drop_duplicates:")
print(df.drop_duplicates().reset_index(drop=True))

duplicated() marks rows 2 and 4 as True because they are exact copies of earlier rows. drop_duplicates() removes them, keeping only the first occurrence of each unique row. reset_index(drop=True) re-numbers the index cleanly.

Subset-Based Duplicate Detection

import pandas as pd

df = pd.DataFrame({
    'Email': ['a@test.com', 'b@test.com', 'a@test.com', 'c@test.com'],
    'Name': ['Alice', 'Bob', 'Alice V2', 'Charlie'],
    'SignupDate': ['2024-01-01', '2024-01-02', '2024-01-05', '2024-01-03']
})

print("Duplicates by Email:")
print(df[df.duplicated(subset=['Email'], keep=False)])

print("\nKeep last signup per Email:")
result = df.drop_duplicates(subset=['Email'], keep='last').reset_index(drop=True)
print(result)

Using subset=['Email'] checks only the Email column for duplicates, ignoring differences in Name and SignupDate. With keep=False both duplicates are shown. With keep='last', the later entry is preserved, which is useful when you want the most recent record.

Type Conversion with astype and pd.to_numeric

import pandas as pd

df = pd.DataFrame({
    'Price': ['10.5', '20.3', 'N/A', '15.0'],
    'Quantity': ['3', '5', '2', '8']
})

print("Original types:")
print(df.dtypes)

# Convert Quantity with astype
df['Quantity'] = df['Quantity'].astype(int)

# Convert Price with to_numeric (handles errors)
df['Price'] = pd.to_numeric(df['Price'], errors='coerce')

print("\nConverted types:")
print(df.dtypes)
print("\nDataFrame:")
print(df)

astype(int) works when all values are valid integers. pd.to_numeric with errors='coerce' safely converts 'N/A' to NaN instead of raising a ValueError, making it ideal for dirty data with mixed types.

Parsing Dates with pd.to_datetime

import pandas as pd

df = pd.DataFrame({
    'Date': ['2024-01-15', '2024-02-20', '2024-03-10'],
    'Event': ['Launch', 'Update', 'Release']
})

df['Date'] = pd.to_datetime(df['Date'])

print("Types:")
print(df.dtypes)
print("\nExtracted months:")
print(df['Date'].dt.month.tolist())
print("\nDay names:")
print(df['Date'].dt.day_name().tolist())

pd.to_datetime() parses ISO format strings into proper datetime objects. Once converted, the dt accessor enables extracting components like month, day_name(), year, and performing date arithmetic such as computing time differences.

Key points

Concepts covered

duplicated, drop_duplicates, astype, pd.to_numeric, pd.to_datetime