Use Case #5 — Detect and Remove Exact Duplicates

Business Purpose

Propósito de negocio

Identify fully duplicated rows, preserve an audit copy, and create a deduplicated dataset.

Este caso continúa el flujo real de trabajo con solicitudes de servicios municipales y documenta cada transformación de forma trazable.

Input and Output

Entrada y salida

Results can vary because the source dataset was generated synthetically and randomly. Run the code to obtain the exact values from your local file.

Los resultados pueden variar porque el dataset de origen fue generado de forma sintética y aleatoria. Ejecuta el código para obtener los valores exactos de tu archivo local.

Step 1 — Load and count exact duplicates

import pandas as pd
from pathlib import Path

base = Path.home() / "Documents" / "Pandas_Use_Cases"
input_file = base / "missing_values_treated_city_service_requests.csv"
output_file = base / "exact_duplicates_removed_city_service_requests.csv"
audit_file = base / "exact_duplicate_rows.csv"

df = pd.read_csv(input_file)

duplicate_mask = df.duplicated(keep=False)
print(f"Rows involved in exact duplicates: {duplicate_mask.sum():,}")
print(f"Removable duplicate copies: {df.duplicated().sum():,}")

The script distinguishes all rows in duplicate groups from the copies that can be removed.

Step 2 — Review duplicate groups

duplicates = df.loc[duplicate_mask].sort_values("Request_ID")
print(duplicates.head(20).to_string(index=False))

Duplicate groups can be reviewed before deletion.

Step 3 — Export duplicate audit file

duplicates.to_csv(audit_file, index=False, encoding="utf-8-sig")
print(f"Audit file: {audit_file}")

An evidence file preserves the removed records for traceability.

Step 4 — Remove exact duplicate copies

df_deduplicated = df.drop_duplicates(keep="first").copy()

print(f"Rows before: {len(df):,}")
print(f"Rows after:  {len(df_deduplicated):,}")
print(f"Rows removed: {len(df) - len(df_deduplicated):,}")

Only exact copies are removed; the first occurrence is retained.

Step 5 — Export deduplicated dataset

df_deduplicated.to_csv(
    output_file,
    index=False,
    encoding="utf-8-sig"
)

print(f"Saved: {output_file}")

A clean working file and a separate duplicate audit file are produced.

Use Case #5 Completed

Caso de uso #5 completado

The output of this module becomes the controlled input for the next stage of the 20-case Pandas workflow.

La salida de este módulo se convierte en la entrada controlada para la siguiente etapa del flujo de 20 casos de Pandas.