如何在Pandas中高效计算分类DataFrame列间重叠占比（三角矩阵）

Alright, let's tackle this problem efficiently—since you're dealing with a 100k+ row categorical DataFrame with 15 columns, we need a solution that's both fast and memory-friendly, while outputting the triangular matrix you want. First, let's clarify two common definitions of "overlap ratio" (pick the one that matches your use case):

Case 1: Row-Level Overlap (Same Value in the Same Row) #

This calculates the percentage of rows where two columns have identical categorical values. This is the most common use case for tabular data.

Optimal Implementation (Vectorized Numpy Operations) #

We leverage pandas' categorical integer encoding and numpy broadcasting to avoid slow Python loops:

import pandas as pd
import numpy as np

# Ensure all columns are categorical (skip if already done)
df = df.astype("category")

# Convert categorical columns to their integer codes (fast numerical comparison)
cat_codes_matrix = df.apply(lambda col: col.cat.codes).values
col_names = df.columns
n_cols = len(col_names)

# Use broadcasting to compute pairwise equality across all columns
# Creates a (n_cols, n_cols, n_rows) boolean matrix where each slice is column pair equality
pairwise_equal = cat_codes_matrix.T[:, :, None] == cat_codes_matrix.T[None, :, :]

# Calculate the mean (overlap ratio) for each column pair
overlap_ratios = pairwise_equal.mean(axis=2)

# Convert to triangular matrix (set lower triangle + diagonal to NaN)
overlap_ratios[np.tril_indices(n_cols)] = np.nan

# Wrap into a DataFrame with column/row labels
triangular_overlap_df = pd.DataFrame(overlap_ratios, index=col_names, columns=col_names)

Why This Works: #

• Speed: Numpy's vectorized operations are orders of magnitude faster than looping through column pairs in Python, critical for 100k+ rows.
• Memory: The boolean matrix for 15 columns is only ~2.25MB (15×15×100,000 boolean values), which is trivial for modern systems.
• Triangular Output: We use np.tril_indices to zero out the lower triangle and diagonal, leaving only the upper triangle of unique column pairs.

Case 2: Category Set Overlap (Jaccard Index) #

If you want the overlap between the sets of categorical values (not row-level matches), this uses the Jaccard Index: (size of category intersection) / (size of category union).

Efficient Implementation #

Since we only have 15 columns, looping through unique pairs is negligible in terms of performance:

import pandas as pd
from itertools import combinations

# Ensure all columns are categorical
df = df.astype("category")

col_names = df.columns
n_cols = len(col_names)

# Initialize empty triangular matrix
jaccard_matrix = pd.DataFrame(np.full((n_cols, n_cols), np.nan), index=col_names, columns=col_names)

# Iterate over all unique column pairs
for col_a, col_b in combinations(col_names, 2):
    # Get category sets for each column
    cats_a = set(df[col_a].cat.categories)
    cats_b = set(df[col_b].cat.categories)
    
    # Calculate Jaccard Index (handle edge case where union is empty)
    union_size = len(cats_a | cats_b)
    jaccard_index = len(cats_a & cats_b) / union_size if union_size != 0 else 0.0
    
    # Assign to upper triangle
    jaccard_matrix.loc[col_a, col_b] = jaccard_index

Why This Works: #

• Simplicity: With only 105 unique column pairs (15×14/2), the loop has minimal overhead.
• Accuracy: Directly uses categorical category sets, avoiding any row-level computation for this specific use case.

Key Notes #

• Always ensure your columns are explicitly converted to category type first—this unlocks fast integer encoding and direct access to category sets.
• For row-level overlap, the broadcasting method is the clear winner for large datasets; it avoids Python loop overhead entirely.

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