基于MultiIndex与DataFrame创建MultiIndex DataFrame及对比矩阵

Got it, let's break down your two requirements step by step with code examples that fit your exact data structure.

First, let's recap your setup to align on the starting point:
You have this base DataFrame after setting the row column as index:

import pandas as pd

# Your original DataFrame setup
df = pd.DataFrame({'row' : ['a','b','c','d'], 
                   'col_A' : [1,2,3,4], 
                   'col_B' : [1,2,3,4], 
                   'col_C' : [1,2,3,4], 
                   'col_D' : [1,2,3,4]})
df = df.set_index('row')

Which outputs:

col_A  col_B  col_C  col_D
row                            
a        1      1      1      1
b        2      2      2      2
c        3      3      3      3
d        4      4      4      4

And your mapping defines entity groups (you noted a/b are the same entity, c/d are the same). Let's complete that mapping properly for our code:

mapping = pd.DataFrame({'row': ['a','b','c','d'],
                        'entity': ['Entity_X', 'Entity_X', 'Entity_Y', 'Entity_Y']})
mapping = mapping.set_index('row')

1. Create a Comparison Matrix from the Given DataFrame df #

We'll build a matrix that compares rows within their assigned entity groups. For this example, we'll use the mean of absolute differences as the comparison metric, but you can swap this for any logic you need (ratios, sum of differences, etc.).

Step 1: Generate valid row pairs per entity #

First, we'll get all combinations of rows that belong to the same entity:

from itertools import combinations

# Merge df with mapping to link rows to their entities
df_with_entity = df.join(mapping)

# Generate row pairs for each entity
entity_pairs = []
for entity in df_with_entity['entity'].unique():
    entity_rows = df_with_entity[df_with_entity['entity'] == entity].index.tolist()
    entity_pairs.extend(combinations(entity_rows, 2))  # Produces (a,b), (c,d)

Step 2: Build and populate the comparison matrix #

Now we'll create an empty matrix and fill it with our comparison values:

# Initialize empty matrix with df's index for rows/columns
comparison_matrix = pd.DataFrame(index=df.index, columns=df.index)

# Fill matrix with comparison values
for (row1, row2) in entity_pairs:
    # Calculate mean of absolute differences across all columns
    diff_mean = (df.loc[row1] - df.loc[row2]).abs().mean()
    # Assign to both (row1, row2) and (row2, row1) for symmetry
    comparison_matrix.loc[row1, row2] = diff_mean
    comparison_matrix.loc[row2, row1] = diff_mean

# Fill diagonal with 0 (comparing a row to itself)
for row in df.index:
    comparison_matrix.loc[row, row] = 0

The resulting matrix looks like this:

row    a    b    c    d
row                    
a    0.0  1.0  NaN  NaN
b    1.0  0.0  NaN  NaN
c    NaN  NaN  0.0  1.0
d    NaN  NaN  1.0  0.0

Note: Cross-entity comparisons are left as NaN since you only specified grouping a/b and c/d. You can fill these with a default value (e.g., 999) or calculate cross-entity differences if needed.

2. Create a MultiIndex DataFrame from MultiIndex & Comparison Matrix #

Next, we'll convert the comparison matrix into a MultiIndex DataFrame to include entity context and make grouped analysis easier.

Step 1: Reshape matrix to long format and add entity data #

First, we'll melt the matrix into a long format, then merge in entity information for both rows in each pair:

# Convert matrix to long format (row1, row2, comparison_value)
long_comparison = comparison_matrix.stack().reset_index()
long_comparison.columns = ['row1', 'row2', 'comparison_value']

# Add entity info for both rows in the pair
long_comparison = long_comparison.merge(mapping, left_on='row1', right_index=True)
long_comparison = long_comparison.merge(mapping, left_on='row2', right_index=True, suffixes=('_1', '_2'))

Step 2: Set up the MultiIndex #

Now we'll define our MultiIndex to include entity and row labels. You can choose a structure that fits your use case—here are two common options:

Option 1: 4-level MultiIndex (entity + row for both sides of the comparison)

multiindex_df = long_comparison.set_index(['entity_1', 'row1', 'entity_2', 'row2'])
# Optional: Keep only same-entity comparisons
multiindex_df = multiindex_df[multiindex_df['entity_1'] == multiindex_df['entity_2']]

This gives a structured index for deep grouping:

comparison_value
entity_1  row1 entity_2  row2                     
Entity_X  a    Entity_X  b                      1.0
          b    Entity_X  a                      1.0
Entity_Y  c    Entity_Y  d                      1.0
          d    Entity_Y  c                      1.0

Option 2: 2-level MultiIndex (row pairs) with entity columns

If you prefer a simpler index with entity info as columns:

multiindex_simple = long_comparison.set_index(['row1', 'row2'])

Result:

comparison_value entity_1  entity_2
row1 row2                                     
a    b                  1.0  Entity_X Entity_X
b    a                  1.0  Entity_X Entity_X
c    d                  1.0  Entity_Y Entity_Y
d    c                  1.0  Entity_Y Entity_Y

Quick Customization Tips #

• Swap Comparison Logic: Replace (df.loc[row1] - df.loc[row2]).abs().mean() with any metric you need—like df.loc[row1] / df.loc[row2] for ratios, or (df.loc[row1] == df.loc[row2]).sum() for matching columns count.
• Include All Pairs: To compare every row to every other (not just same entity), remove the entity filter when generating pairs.
• Fill Missing Values: Use comparison_matrix.fillna(0, inplace=True) to set cross-entity comparisons to 0, or any other value that makes sense for your use case.

内容的提问来源于stack exchange，提问作者A.Papa