如何移除数据集中互为镜像的重复ID组合记录

Hey there! Let's figure out how to eliminate those mirrored duplicate pairs from your dataset. Since (idA, idB) and (idB, idA) represent the same combination with identical values, here are practical solutions using common tools:

If you're working with Python, Pandas offers straightforward options for both small and large datasets.

Efficient approach for large datasets (avoids slow apply): #

import pandas as pd
import numpy as np

# Load your space-separated dataset
df = pd.read_csv("your_dataset.txt", sep=" ")

# Create temporary columns with sorted IDs to identify mirror pairs
df[["min_id", "max_id"]] = np.sort(df[["idA", "idB"]], axis=1)

# Keep only the first occurrence of each unique pair, then clean up temp columns
df_unique = df.drop_duplicates(subset=["min_id", "max_id"], keep="first").drop(columns=["min_id", "max_id"])

# Save the result to a new file
df_unique.to_csv("unique_pairs.txt", sep=" ", index=False)

Alternative with explicit pair key: #

If you prefer a more readable approach, generate a sorted tuple as a unique identifier for each pair:

import pandas as pd

df = pd.read_csv("your_dataset.txt", sep=" ")
df["pair_key"] = df.apply(lambda row: tuple(sorted([row["idA"], row["idB"]])), axis=1)
df_unique = df.drop_duplicates(subset="pair_key", keep="first").drop(columns="pair_key")
df_unique.to_csv("unique_pairs.txt", sep=" ", index=False)

If your data lives in a database, you can filter duplicates directly with SQL queries.

Basic approach (returns pairs with sorted IDs): #

SELECT DISTINCT
    LEAST(idA, idB) AS idA,
    GREATEST(idA, idB) AS idB,
    value
FROM your_table;

Preserve original pair order (keep first occurrence): #

If you want to retain the original (idA, idB) order from the first instance of the pair, use a window function:

WITH ranked_pairs AS (
    SELECT
        idA,
        idB,
        value,
        ROW_NUMBER() OVER (
            PARTITION BY LEAST(idA, idB), GREATEST(idA, idB)
            ORDER BY idA  -- Adjust this to control which row gets kept
        ) AS rn
    FROM your_table
)
SELECT idA, idB, value
FROM ranked_pairs
WHERE rn = 1;

For quick text file processing without heavy tools, awk is a lightweight, fast option:

awk '{
    # Swap IDs to ensure idA <= idB for consistent key generation
    if ($1 > $2) { temp = $1; $1 = $2; $2 = temp }
    key = $1 "," $2
    # Print only if we haven't seen this pair before
    if (!(key in seen)) {
        seen[key] = 1
        print $0
    }
}' your_dataset.txt > unique_pairs.txt

All these methods work by creating a consistent, order-agnostic identifier for each pair, then keeping only one instance of each unique identifier.

内容的提问来源于stack exchange，提问作者ianux22