如何将多分类原始数据表转换为类别匹配计数邻接矩阵

Got it, let's walk through exactly how to turn your categorical data table into that adjacency matrix you need—whether you prefer using Excel for a visual workflow or Python/Pandas for larger datasets. I’ll cover both methods step by step.

First, let’s assume your raw data looks like this (I’ll use a sample to make concrete):

Step 1: Set up your matrix framework #

Create a new section (or sheet) where you list all your Name values along both the row and column headers—this makes your square adjacency matrix. For example, rows = Name 1, Name 2, Name 3 and columns = the same.

Step 2: Formula to count matching categories #

In the cell where Name 1 row meets Name 2 column, use this formula:

• For Excel 365/2021 (dynamic arrays): =SUM(--(B2:D2=B3:D3))
• For older Excel versions, enter it as an array formula (press Ctrl+Shift+Enter after typing): {=SUM(--(B2:D2=B3:D3))}

Here’s what this does:

• B2:D2 grabs the category values for Name 1, B3:D3 for Name 2
• B2:D2=B3:D3 returns an array of TRUE/FALSE for each category match
• -- converts those boolean values to 1/0
• SUM adds up the 1s to get your total matching count (in the sample, this gives 2 for Name1 vs Name2—exactly what you want!)

To count differing categories instead, subtract the match count from the total number of categories:
=COUNTA(B2:D2)-SUM(--(B2:D2=B3:D3))

Step 3: Fill the entire matrix #

Drag the formula across all cells in your matrix. Note that diagonal cells (a Name compared to itself) will show the total number of categories (since all values match)—that’s expected behavior.

If you’re working with a large dataset or want to automate this process, Python with Pandas is perfect.

Step 1: Import Pandas and load your data #

First, set up your sample data (replace this with loading your actual CSV/Excel file):

import pandas as pd

# Sample data matching your structure
raw_data = {
    "ID": [1, 2, 3],
    "Name": ["Name 1", "Name 2", "Name 3"],
    "Category 1": ["Example 1", "Example 1", "Example 5"],
    "Category 2": ["Example 2", "Example 2", "Example 2"],
    "Category 3": ["Example 3", "Example 4", "Example 3"]
}
df = pd.DataFrame(raw_data)

Step 2: Prep the category data #

Extract just the category columns and set Name as the index for easy referencing:

category_df = df.set_index("Name").drop("ID", axis=1)

Step 3: Build the adjacency matrix (matching counts) #

We’ll initialize an empty matrix and fill it with match counts using vectorized operations (fast even for big data):

# Create empty square matrix with Names as index/columns
adj_matrix = pd.DataFrame(index=category_df.index, columns=category_df.index)

# Fill matrix with matching category counts
for name_row in category_df.index:
    for name_col in category_df.index:
        # Count how many categories match between the two Names
        match_count = (category_df.loc[name_row] == category_df.loc[name_col]).sum()
        adj_matrix.loc[name_row, name_col] = match_count

# Convert values to integers (optional but cleaner)
adj_matrix = adj_matrix.astype(int)

Step 4: Adjust for differing counts (if needed) #

If you want to count differing categories instead, modify the calculation to subtract match counts from the total number of categories:

total_categories = category_df.shape[1]  # Number of category columns

for name_row in category_df.index:
    for name_col in category_df.index:
        diff_count = total_categories - (category_df.loc[name_row] == category_df.loc[name_col]).sum()
        adj_matrix.loc[name_row, name_col] = diff_count

adj_matrix = adj_matrix.astype(int)

Step 5: View your result #

Printing adj_matrix will give you the sample matching matrix:

Quick note for missing values #

If your dataset has missing category values:

• In Excel: Use =SUMPRODUCT((B2:D2=B3:D3)*NOT(ISNA(B2:D2))*NOT(ISNA(B3:D3))) to ignore NaNs when counting matches.
• In Python: The == comparison already ignores NaNs, but if you want explicit handling, use category_df.loc[name_row].eq(category_df.loc[name_col]).sum() which works the same way.

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