Duplicate Records

Duplicate Records

Duplicate records are one of the most common problems encountered during
data cleaning. A duplicate occurs when the same real-world observation,
entity, transaction, or event appears more than once in a dataset when it
should only appear once.

Duplicates can distort counts, totals, averages, percentages, dashboards,
reports, and statistical analysis. However, not every repeated value is a
duplicate. A data analyst must first understand what each row represents
before deciding whether records should be removed.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain what a duplicate record is.
• Distinguish between exact and partial duplicates.
• Identify common causes of duplicate records.
• Detect duplicates using Excel.
• Detect duplicates using Python and pandas.
• Use SQL to identify repeated identifiers.
• Understand duplicate IDs and composite keys.
• Investigate near-duplicate records.
• Determine which duplicate record should be retained.
• Avoid blindly deleting records.
• Document duplicate-handling decisions.

1. What Is a Duplicate Record?

A duplicate record is a record that unnecessarily represents the same
real-world observation more than once.

For example, if a student registration dataset contains:

Student ID    Name              Course
1001          Ade John          Data Analysis
1001          Ade John          Data Analysis

and the business rule says that each student can only have one registration
for that course, the second record may be a duplicate.

2. Repeated Values Are Not Always Duplicates

A common mistake is to assume that repeated names or values automatically
mean duplicate records.

For example:

Student ID    Name
1001          John Ade
1002          John Ade

The names are identical, but the students may be different people.
Therefore, the name alone should not be used to determine duplication.

3. Exact Duplicates

An exact duplicate occurs when two or more rows contain the same values
across all relevant columns.

1001 | John | Data Analysis | Paid
1001 | John | Data Analysis | Paid

Exact duplicates are usually easier to identify.

4. Partial Duplicates

A partial duplicate occurs when important identifying fields are repeated
while some other fields differ.

For example:

1001 | John Ade | Data Analysis | Paid
1001 | John Ade | Data Analysis | Pending

This requires investigation. The records could represent an error, a status
change, or two legitimate events depending on the meaning of the dataset.

5. Duplicate Rows vs Duplicate IDs

A duplicate row means the same row appears more than once.

A duplicate ID means the same identifier appears more than once.

Duplicate IDs are not automatically errors because some datasets legitimately
contain multiple records for one entity.

For example, one customer may make many purchases:

Customer ID    Transaction ID
C001           T001
C001           T002
C001           T003

Here, the customer ID repeats, but the transactions are different.

6. Why Duplicate Records Occur

Common causes include:

• Repeated manual data entry.
• Copy-and-paste mistakes.
• Repeated form submissions.
• Importing the same file more than once.
• Database synchronization problems.
• API retry operations.
• System migration errors.
• Combining multiple datasets.
• Incorrect database joins.
• Repeated exports and imports.
• Multiple registrations by the same person.
• Different spelling or formatting of the same information.

7. Duplicate Records After a Database Join

Incorrect joins can accidentally multiply records.

For example, joining a student table to a payment table where a student has
multiple payment records can produce multiple rows for that student.

The analyst must understand the relationship between the tables before
declaring the resulting records to be duplicates.

8. Duplicate Records from Form Submissions

Online registration forms may receive multiple submissions because a user
clicked the submit button several times, refreshed a page, or submitted the
form again.

A timestamp, submission ID, email address, phone number, and other fields
can help investigate such cases.

9. Exact Duplicate Detection in Excel

Excel provides a built-in Remove Duplicates feature.

The general process is:

1. Select the dataset.
2. Open the Data tab.
3. Choose Remove Duplicates.
4. Select the columns that define a duplicate.
5. Review the number of duplicates found.

Do not immediately delete the records without first determining whether the
selected columns correctly define a duplicate.

10. Conditional Formatting

Excel conditional formatting can highlight duplicate values.

For example, duplicate student IDs can be highlighted to make investigation
easier.

11. COUNTIF for Duplicate Detection

The COUNTIF function can help identify repeated values.

=COUNTIF(A:A,A2)

If the result is greater than 1, the value appears more than once in column
A.

This does not automatically prove that the entire record is a duplicate.
It only indicates that the selected value is repeated.

12. Duplicate IDs

Suppose column A contains Student IDs.

Student ID
1001
1002
1003
1001
1004

Student ID 1001 appears twice.
The analyst must determine whether this is an actual duplicate student
record or whether the student legitimately has multiple records.

13. Composite Keys

Sometimes one field is not enough to identify a unique record.
A combination of fields may be required.

For example:

student_id + course_id + registration_date

may define a unique registration.

Such a combination is sometimes called a composite key.

14. Duplicate Detection Using Multiple Columns

Suppose the dataset contains:

student_id
course_name
registration_date

A duplicate may be defined as the same combination of all three fields rather
than the student ID alone.

This is why the analyst must understand the business definition of a unique
record.

15. Near Duplicates

Some duplicates are not exactly identical.
They may differ because of:

• Capitalization.
• Spacing.
• Spelling.
• Abbreviations.
• Phone number formatting.
• Email formatting.
• Different date formats.

For example:

John Ade
john ade
John  Ade
JOHN ADE

These values may refer to the same person but require investigation.

16. Text Normalization Before Duplicate Detection

Standardizing text can make duplicate detection more reliable.

Common operations include:

• TRIM.
• CLEAN.
• LOWER.
• UPPER.
• PROPER.
• Replacing known abbreviations.

For example, Excel can use:

=TRIM(A2)

or:

=LOWER(TRIM(A2))

17. Python and pandas Duplicate Detection

Python’s pandas library provides convenient methods for identifying
duplicates.

df.duplicated().sum()

This returns the number of duplicate rows according to the selected
definition.

18. Display Duplicate Rows

df[df.duplicated(keep=False)]

Using keep=False marks all members of duplicate groups rather
than only the later occurrences.

19. Remove Exact Duplicate Rows

df = df.drop_duplicates()

This removes duplicate rows according to the columns being considered.
Always create a backup or working copy before performing destructive
operations.

20. Remove Duplicates Based on a Specific Field

df = df.drop_duplicates(
    subset=["email"],
    keep="last"
)

This keeps the last record for each email address.

The choice of which record to keep must be based on the business rule.

21. Keep the First Record

df = df.drop_duplicates(
    subset=["student_id"],
    keep="first"
)

This keeps the first occurrence.

22. Keep the Last Record

df = df.drop_duplicates(
    subset=["student_id"],
    keep="last"
)

This is useful when the latest record is considered authoritative, but the
dataset should normally be sorted correctly before applying this rule.

23. Keeping the Most Recent Record

If records contain timestamps, the analyst can sort by the timestamp and
then retain the latest record.

df = df.sort_values("updated_at")

df = df.drop_duplicates(
    subset=["student_id"],
    keep="last"
)

This assumes that the business rule says the latest record should be
retained.

24. Keeping the Most Complete Record

Sometimes duplicate records differ in completeness.

For example:

Record A:
Name = John
Email = blank
Phone = 08100000000

Record B:
Name = John
Email = john@example.com
Phone = 08100000000

Record B may be more complete, but the analyst should verify the source and
business rules before choosing it.

25. Trusted Data Sources

When duplicate records originate from different systems, the organization
may have a trusted-source hierarchy.

For example:

1. Official registration system.
2. Verified administrative database.
3. Imported spreadsheet.
4. Manual temporary record.

The record from the most trusted source may be preferred.

26. Duplicate Records and Business Meaning

A technically identical record may still represent a legitimate repeated
event.

For example, if a customer purchases the same product twice, the two sales
must not be deleted simply because the product and customer are identical.
The transaction IDs and timestamps distinguish the events.

27. Customer Example

Consider:

Customer ID | Product | Amount | Date
C001        | Laptop  | 500000 | 2026-09-01
C001        | Laptop  | 500000 | 2026-09-01

These records might be duplicates, but they could also represent two
legitimate purchases made on the same day.

The transaction ID should be checked before deleting anything.

28. Student Registration Example

Suppose:

Student ID | Course         | Date
DW001      | Data Analysis  | 2026-09-01
DW001      | Data Analysis  | 2026-09-01

If the registration system permits only one active registration for a
student in a particular course, this is likely a duplicate.

If the system records registration attempts, however, both rows may have
meaning.

29. Sales Example

A sales transaction may contain:

Transaction ID
Customer ID
Product
Quantity
Amount
Transaction Date

Transaction ID is normally more useful for determining duplicate
transactions than Customer ID.

30. Duplicate Records and Data Quality

Duplicate records affect data quality because they can cause:

• Inflated record counts.
• Inflated revenue totals.
• Incorrect customer counts.
• Incorrect student counts.
• Incorrect averages.
• Misleading dashboards.
• Incorrect percentages.
• Biased analytical results.

31. Duplicate Percentage

It is useful to measure the proportion of duplicate records.

For example:

Duplicate percentage =
(Number of duplicate records / Total records) × 100

This can be included in a data-quality report.

32. SQL Duplicate Detection

SQL can identify repeated identifiers using GROUP BY and HAVING.

SELECT
    student_id,
    COUNT(*) AS duplicate_count
FROM students
GROUP BY student_id
HAVING COUNT(*) > 1;

This query identifies student IDs that occur more than once.

33. SQL and Composite Duplicates

Multiple columns can also be grouped together.

SELECT
    student_id,
    course_id,
    registration_date,
    COUNT(*) AS duplicate_count
FROM registrations
GROUP BY
    student_id,
    course_id,
    registration_date
HAVING COUNT(*) > 1;

This helps detect repeated combinations of fields.

34. Fuzzy Matching

Fuzzy matching is used when two records may represent the same entity even
though their text is not identical.

Examples include:

Mohammed Bello
Muhammed Bello

Dwallnet Technologies
Dwallnet Technology

08012345678
080-1234-5678

Fuzzy matching should be used carefully because similar records are not
always the same records.

35. Email and Phone Matching

Email addresses and phone numbers can be useful identifiers, but they must
be normalized first.

For example, phone numbers may contain:

08012345678
080-1234-5678
+2348012345678

A standardization process can convert them into a common representation
before matching.

36. Case-Insensitive Duplicate Detection

Case differences should normally not cause otherwise identical categories
to be treated as separate values.

For example:

Data Analysis
data analysis
DATA ANALYSIS

can be normalized before duplicate analysis.

37. Duplicate Detection Workflow

A professional workflow is:

1. Understand the meaning of one row.
2. Identify the expected unique key.
3. Inspect the data.
4. Normalize relevant fields.
5. Detect exact duplicates.
6. Detect partial duplicates.
7. Investigate suspicious groups.
8. Determine the correct business rule.
9. Decide which records to retain.
10. Document the decision.
11. Validate the final dataset.

38. Never Blindly Delete Duplicates

One of the most important rules in data cleaning is:

Do not delete records simply because they look repeated.

Always determine whether the repeated records represent the same real-world
event or legitimate multiple events.

39. Backup Before Deduplication

Always preserve the original dataset before removing records.

A safe workflow is:

Raw Dataset
      ↓
Backup
      ↓
Duplicate Investigation
      ↓
Deduplication
      ↓
Validation

40. Flag Before Delete

Instead of immediately deleting suspicious records, create a flag.

For example:

duplicate_flag = TRUE

This allows the analyst to review the records before making a final
decision.

41. Excel Duplicate Workflow

1. Create a backup of the worksheet.
2. Convert the dataset into an Excel Table.
3. Identify the unique identifier.
4. Use COUNTIF or conditional formatting.
5. Review duplicate groups.
6. Use Remove Duplicates only after confirming the rule.
7. Compare row counts before and after.
8. Document the action.

42. Python Duplicate Workflow

import pandas as pd

df = pd.read_csv("students.csv")

clean_df = df.copy()

print("Original rows:", len(clean_df))

duplicates = clean_df[
    clean_df.duplicated(
        subset=["student_id"],
        keep=False
    )
]

print(duplicates)

clean_df = clean_df.drop_duplicates(
    subset=["student_id"],
    keep="last"
)

print("Final rows:", len(clean_df))

43. Validate After Deduplication

After removing duplicates, verify that the expected uniqueness rule is now
satisfied.

print(
    clean_df["student_id"].is_unique
)

If the result is True, every student ID is unique in that
DataFrame.

44. Duplicate Detection with Data Profiling

Data profiling involves examining the structure, values, frequencies,
missingness, uniqueness, and distributions of a dataset.

Duplicate detection should form part of a broader data-quality process
rather than being treated as an isolated task.

45. Duplicates After Combining Datasets

Appending or merging files can introduce duplicates.

For example:

January.csv
February.csv
March.csv

If January data is accidentally included again when creating the combined
dataset, duplicate transactions may appear.

Unique transaction IDs can help detect this problem.

46. Duplicates and APIs

Automated systems may submit the same transaction more than once if an API
request is retried after a network timeout.

Systems can use unique request IDs or transaction IDs to prevent accidental
duplication.

47. Duplicates and Data Synchronization

When multiple systems synchronize data, duplicate records can occur if the
same record is inserted rather than updated.

Unique constraints and reliable identifiers can help prevent this problem at
the database level.

48. Preventing Duplicates

The best duplicate is often the duplicate that never enters the system.

Preventive controls can include:

• Unique database constraints.
• Unique transaction IDs.
• Form submission controls.
• Idempotent API operations.
• Validation rules.
• Duplicate warnings.
• Master-data management.
• Controlled registration processes.

49. Database Unique Constraints

Databases can enforce uniqueness on fields that must be unique.

For example, a registration ID may be required to be unique.
This prevents the database from accepting two records with the same key.

50. Duplicate Investigation Questions

When you find duplicates, ask:

• What does one row represent?
• What should uniquely identify a row?
• Are these records actually the same event?
• Could the repetition be legitimate?
• Which source is more reliable?
• Which record is newer?
• Which record is more complete?
• Will deleting the record affect another analysis?
• Has the decision been documented?

51. Practical Exercise

Create a small student-registration dataset containing at least 30 records.
Intentionally introduce:

• Exact duplicate rows.
• Repeated student IDs.
• Different capitalization.
• Extra spaces.
• Repeated emails.
• Legitimate repeated transactions.

Then:

1. Identify the expected unique key.
2. Find exact duplicates.
3. Find duplicate IDs.
4. Normalize names and emails.
5. Investigate repeated records.
6. Separate legitimate repetitions from actual duplicates.
7. Remove only confirmed duplicates.
8. Compare the row count before and after cleaning.
9. Document the rules you used.

52. Knowledge Check

1. What is a duplicate record?
2. Are repeated values always duplicates?
3. What is the difference between an exact and partial duplicate?
4. Why can duplicate IDs be legitimate?
5. Name three causes of duplicate records.
6. How can Excel identify duplicates?
7. What does COUNTIF help with?
8. What does df.duplicated() do?
9. What does drop_duplicates() do?
10. Why should raw data be backed up before deduplication?
11. What is a composite key?
12. Why can an incorrect database join create apparent duplicates?
13. What is fuzzy matching?
14. Why should duplicates be investigated before deletion?
15. How can databases prevent duplicates?

53. Key Terms

• Duplicate record: An unnecessary repeated representation of the same real-world observation.
• Exact duplicate: A record that repeats the same relevant values.
• Partial duplicate: A record that repeats important identifying fields while other fields differ.
• Unique identifier: A field intended to uniquely identify a record.
• Composite key: A unique identifier created from multiple fields.
• Fuzzy matching: Matching records that are similar but not exactly identical.
• Deduplication: The process of identifying and handling duplicate records.
• Data profiling: Examining data quality, structure, uniqueness, and distributions.

54. Key Takeaways

• Not every repeated value represents a duplicate.
• Always understand what one row represents before deduplicating.
• Identify the correct unique key or composite key.
• Exact duplicates are easier to detect than partial or near duplicates.
• Normalize text before comparing records when appropriate.
• Excel provides several tools for duplicate detection.
• Python and pandas make duplicate detection and removal programmable.
• SQL can identify repeated identifiers using GROUP BY and HAVING.
• Never blindly delete records.
• Preserve the raw dataset before deduplication.
• Document why records were removed or retained.
• Validate the final dataset after deduplication.

55. Lesson Summary

Duplicate records can significantly affect the reliability of data analysis.
They can inflate counts, totals, averages, and other calculations. However,
identifying duplicates requires more than simply searching for repeated
values.

A professional data analyst first determines what constitutes a unique
record, then detects exact and partial duplicates, investigates their causes,
and applies a clearly defined business rule.

Excel, Python, pandas, and SQL provide useful tools for identifying and
handling duplicates. The most important principle is to avoid deleting
records blindly and to document every important decision.

Next Lesson

In the next lesson, we will study Incorrect Formats and
learn how inconsistent dates, numbers, text, units, currencies, and other
formats can affect data analysis.