对称矩阵高效读取：如何提取上三角区域达标数据标签？

The problem with your second approach is that it checks all elements in each row, which includes both the (i,j) and (j,i) positions of the symmetric matrix—leading to duplicate pairs. To fix this, we can focus only on one triangle of the matrix (either upper or lower, excluding the diagonal if needed) using vectorized operations (no loops!) which are way faster for large datasets.

Here are two optimal solutions:

Solution 1: Numpy Mask-Based Approach (Fastest for Large Data) #

This method uses numpy to create a mask for the lower triangle (where row index > column index, i.e., j > i) and combines it with your value threshold filter:

import pandas as pd
import numpy as np
from scipy.spatial.distance import squareform

value = 0.6
# Create sample symmetric DataFrame
df = pd.DataFrame(squareform(np.random.rand(10)))

# 1. Create mask for lower triangle (exclude diagonal with k=-1)
lower_triangle = np.tril(np.ones(df.shape, dtype=bool), k=-1)
# 2. Create mask for values >= threshold
value_mask = df >= value
# 3. Combine masks to get only relevant positions
combined_mask = lower_triangle & value_mask

# Get row and column indices where mask is True
rows, cols = np.where(combined_mask)

# Map indices to your DataFrame's actual index labels
unique_pairs = list(zip(df.index[rows], df.index[cols]))

# Process each pair (example: print them)
for idx1, idx2 in unique_pairs:
    print(f"Pair: {idx1}, {idx2}")

Solution 2: Pandas Stack-Based Approach (More Readable) #

If you prefer a pandas-idiomatic method, stacking the DataFrame into a MultiIndex series lets you easily filter and deduplicate pairs:

import pandas as pd
import numpy as np
from scipy.spatial.distance import squareform

value = 0.6
df = pd.DataFrame(squareform(np.random.rand(10)))

# Stack the DataFrame to get (row_idx, col_idx) as MultiIndex
stacked_df = df.stack()

# Filter values >= threshold AND only keep pairs where row index > column index
# This ensures we only get each symmetric pair once
filtered_pairs = stacked_df[
    (stacked_df >= value) & 
    (stacked_df.index.get_level_values(0) > stacked_df.index.get_level_values(1))
]

# The index of filtered_pairs contains your unique (row, column) label pairs
for (idx1, idx2), val in filtered_pairs.items():
    print(f"Pair: {idx1}, {idx2} (value: {val:.2f})")

Why These Work Better: #

• No loops: Both methods use vectorized operations, which are optimized in numpy/pandas and will handle thousands of rows in milliseconds (vs. minutes for nested loops).
• No duplicates: By focusing on only one triangle of the symmetric matrix, we avoid processing both (i,j) and (j,i) pairs.
• Scalable: These approaches maintain performance even as your DataFrame grows to thousands of rows.

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