Detecting Duplicate Columns and Redundant Features in a Wide Table

Wide tables accumulate duplicates the way warehouses accumulate joins: the same measurement arrives twice under two names, a unit conversion gets stored alongside the original, and a column that was constant from the day it was added never gets removed. Finding them is cheap. Knowing which ones matter is the part worth getting right.

Four kinds of redundancy

These need different tests, and conflating them is why a single correlation heatmap is not a sufficient audit.

• Exact duplicates. Two columns with identical values in every row. Usually a join that brought the same field twice, or a rename that was applied as a copy.
• Deterministic transforms. Celsius and Fahrenheit, price and price-in-cents, a log and its original. Pearson correlation of exactly 1.0 for the linear cases; for the monotone-but-nonlinear ones, Spearman is 1.0 and Pearson is not.
• Constant and near-constant columns. Zero variance carries no information at all. Near-zero variance — 99.8% one value — carries a little, and occasionally that little is the entire signal, which is why an automatic variance threshold is dangerous on rare-event problems.
• Statistical near-duplicates. Two genuinely different measurements that happen to move together in this sample. These are not redundancy in the data-modelling sense at all; they are collinearity, and they are a different problem with different consequences — see detecting multicollinearity.

The order to run them in is the order above, and it is not arbitrary. Constant columns should go first because they are free to identify and removing them shrinks every subsequent pairwise computation. Exact duplicates go next because they will otherwise appear in the correlation matrix as perfect 1.0 entries and crowd out the near- duplicates you actually wanted to look at. Only then is a correlation scan informative.

Exact duplicates, cheaply

The naive approach compares every pair of columns, which is O(n·p²) and becomes uncomfortable somewhere around a thousand columns. The cheap approach hashes each column once and groups by hash, which is O(n·p), then compares only within each hash bucket to rule out collisions.

import pandas as pd
from collections import defaultdict

def duplicate_column_groups(df: pd.DataFrame) -> list[list[str]]:
    buckets = defaultdict(list)
    for col in df.columns:
        # hash the values, not the name; NaN hashes consistently within pandas
        key = pd.util.hash_pandas_object(df[col], index=False).sum()
        buckets[key].append(col)

    groups = []
    for cols in buckets.values():
        if len(cols) < 2:
            continue
        remaining = list(cols)
        while remaining:
            head, rest = remaining[0], remaining[1:]
            same = [c for c in rest if df[head].equals(df[c])]   # guard against collisions
            if same:
                groups.append([head] + same)
            remaining = [c for c in rest if c not in same]
    return groups

for group in duplicate_column_groups(df):
    print("identical:", group)

Two details that catch people. Hashing on values rather than on names is the point — the whole problem is that the names differ. And equals rather than == for the confirmation step, because == compares NaN to NaN as False and will tell you two identical columns with missing values are different.

Near-duplicates and correlation

For everything short of exact equality, compute the correlation matrix and read the upper triangle. Use both Pearson and Spearman: Pearson catches linear rescalings, Spearman catches any monotone transform, including logs, square roots and rank-based recodings. A pair that is Spearman 1.0 and Pearson 0.86 is a nonlinear transform of one variable — genuinely one measurement stored twice, even though the scatterplot is curved.

For categorical columns, correlation does not apply. The equivalent question is whether one column is a function of the other, which you can answer with a crosstab: if every value of column A maps to exactly one value of column B, then A determines B, and B adds nothing on top of A. That catches the very common country_code / country_name / region family, where three columns encode one fact at three granularities.

A third case is worth checking explicitly because neither correlation catches it: one column that is a duplicate of another except for a shift. A field copied from a source system a day late, or an amount recorded once gross and once net of a constant fee, gives Pearson 1.0 and looks like a linear copy — which it is. But a column that duplicates another only on the rows where a third column takes a particular value is a conditional duplicate, and its overall correlation can be modest. Those are found by grouping rather than by a matrix, and they are usually a symptom of two source systems being unioned into one table without a source indicator.

A worked wide table

Take a 400-column feature table assembled from three source systems. The audit below runs in one pass and reports each kind separately rather than merging them into one drop list, because the appropriate action differs by kind.

import numpy as np, pandas as pd

num = df.select_dtypes("number")

# 1. zero and near-zero variance
share_top = num.apply(lambda s: s.value_counts(normalize=True, dropna=False).iloc[0])
constant   = share_top[share_top == 1.0].index.tolist()
near_const = share_top[(share_top >= 0.995) & (share_top < 1.0)].index.tolist()

# 2. deterministic transforms
pear = num.corr(method="pearson").abs()
spear = num.corr(method="spearman").abs()
upper = np.triu(np.ones(pear.shape, dtype=bool), k=1)

def pairs_above(mat, thresh):
    m = mat.where(upper)
    idx = np.argwhere((m.to_numpy() >= thresh))
    return [(mat.index[i], mat.columns[j], round(mat.iat[i, j], 4)) for i, j in idx]

linear_copies  = pairs_above(pear, 0.9999)
monotone_copies = [p for p in pairs_above(spear, 0.9999) if p not in linear_copies]
near_dupes      = [p for p in pairs_above(pear, 0.95) if p not in linear_copies]

print("constant:", len(constant), "near-constant:", len(near_const))
print("linear copies:", linear_copies[:5])
print("monotone-only copies:", monotone_copies[:5])
print("correlated >= 0.95 but not copies:", len(near_dupes))

A typical result on a table like that: 6 constant columns left over from a deprecated source, 11 exact or linear copies from double joins, 2 monotone-only pairs (an amount and its log, added by two different analysts), and perhaps 60 pairs above 0.95 that are simply related measurements. The first three groups are deletions. The last group is not.

What to actually drop

Constant columns: drop, unconditionally. They cannot contribute to any model and they cost memory and split-search time in a boosted tree.

Exact duplicates and deterministic transforms: keep one, drop the rest, and keep the one whose name a human will understand in six months. There is no accuracy argument here in either direction — a tree ensemble is invariant to monotone transforms of a single feature, so the log and the original are interchangeable to it — but there is a real cost argument, since every duplicate is another column the split finder evaluates at every node.

Near-constant columns: check what the rare value means before dropping. On fraud, defect and failure problems the 0.2% is frequently the entire target-relevant signal, and a blanket VarianceThreshold will remove it. Rank it against the target instead, as in ranking features by predictive power.

Statistically correlated but distinct measurements: usually keep both for a tree model, and think much harder for a linear one. Dropping one of a correlated pair is a decision about interpretability and about the stability of coefficients, not about accuracy, and the case for it is made in the multicollinearity page rather than here. If you are dropping columns to shrink the model rather than to clean it, a supervised method belongs in that decision — automated feature selection uses the target, which a redundancy audit deliberately does not.