Duplicates and delete patterns

Problem¶

How to find duplicate rows?
How to keep only one row for each repeated value?

Notions: Duplicates and delete patterns

SQL and Pandas syntax¶

SELECT
    natural_key,
    COUNT(*) AS cnt
FROM t
GROUP BY natural_key
HAVING COUNT(*) > 1;

DELETE FROM t
WHERE id NOT IN (
    SELECT MIN(id)
    FROM t
    GROUP BY natural_key
);

duplicates = (
    df.groupby("natural_key", as_index=False)
    .size()
    .rename(columns={"size": "cnt"})
    .loc[lambda x: x["cnt"] > 1]
)

deduped = (
    df.sort_values("id")
    .drop_duplicates("natural_key", keep="first")
)

Example¶

import sqlite3
import pandas as pd

## SQL

con = sqlite3.connect(":memory:")

con.executescript("""
CREATE TABLE contacts (
    id INTEGER,
    email TEXT
);

INSERT INTO contacts VALUES
    (1, 'a@example.com'),
    (2, 'b@example.com'),
    (3, 'a@example.com'),
    (4, 'c@example.com'),
    (5, 'b@example.com');
""")

duplicate_sql = """
-- Find email addresses that appear more than once.
SELECT
    email,
    COUNT(*) AS cnt
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email
"""

pd.read_sql_query(duplicate_sql, con)
#            email  cnt
# 0  a@example.com    2
# 1  b@example.com    2

con.execute("""
-- Remove duplicate contacts while keeping the smallest id per email.
DELETE FROM contacts
WHERE id NOT IN (
    SELECT MIN(id)
    FROM contacts
    GROUP BY email
);""")

pd.read_sql_query("SELECT * FROM contacts ORDER BY id;", con)
#    id          email
# 0   1  a@example.com
# 1   2  b@example.com
# 2   4  c@example.com

## Pandas

contacts = pd.DataFrame({
    "id": [1, 2, 3, 4, 5],
    "email": ["a@example.com", "b@example.com", "a@example.com", "c@example.com", "b@example.com"]
})

duplicates = (
    contacts.groupby("email", as_index=False)
    .size()
    .rename(columns={"size": "cnt"})
    .loc[lambda x: x["cnt"] > 1]
    .sort_values("email")
)
duplicates
#            email  cnt
# 0  a@example.com    2
# 1  b@example.com    2

deduped = (
    contacts.sort_values("id")
    .drop_duplicates("email", keep="first")
)
deduped
#    id          email
# 0   1  a@example.com
# 1   2  b@example.com
# 3   4  c@example.com