基于二级索引定位MultiIndex DataFrame坐标并高效赋值

Got it, let's solve this problem efficiently—especially since you're working with large datasets. The core goal here is to target cells using only the second level of your row and column MultiIndexes, without needing to specify the first level at all. Here are two optimized approaches tailored to your needs:

Key Context Recap #

Your DataFrame has unique second-level indexes for both rows and columns, so each tuple in str_cord maps to exactly one cell. We can leverage this uniqueness to avoid unnecessary overhead.

Method 1: Numpy-Based Assignment (Fastest for Large Data) #

This approach uses direct numpy array manipulation, which is significantly faster than pandas loop-based operations for big datasets. We first create mappings from second-level index values to their positional indices, then batch-update the cells.

import pandas as pd
import numpy as np

# Replicate your sample MultiIndex DataFrame
row_multi = pd.MultiIndex.from_tuples(
    [('DNA', 'A'), ('DNA', 'B'), ('DNA', 'C'), ('DNA', 'D'), ('DNA', 'E'), ('DNA', 'F'), ('DNA', 'H'), ('DNA', 'I')],
    names=['DNA', 'Item']
)
col_multi = pd.MultiIndex.from_tuples(
    [('Cat2', 'A'), ('Cat2', 'B'), ('Cat2', 'C'), ('Cat2', 'D'), ('Cat2', 'E'), ('Cat2', 'F'), ('Cat2', 'F'), ('Cat2', 'H'), ('Cat2', 'I'), ('Cat2', 'J')],
    names=['DNA', 'Cat2']
)
df = pd.DataFrame(0, index=row_multi, columns=col_multi)

# Your target coordinate list
str_cord = [('A','B'),('A','H'),('A','I'),('B','H'),('B','I'),('H','I')]

# Step 1: Map second-level index values to their positional indices
row_pos_map = {val: idx for idx, val in enumerate(df.index.get_level_values(1))}
col_pos_map = {val: idx for idx, val in enumerate(df.columns.get_level_values(1))}

# Step 2: Convert coordinate tuples to array positions
row_indices = [row_pos_map[r] for r, c in str_cord]
col_indices = [col_pos_map[c] for r, c in str_cord]

# Step 3: Batch-assign values via numpy (bulk operation = maximum speed)
df.values[row_indices, col_indices] = 1

Why this works:

• get_level_values(1) extracts only the second level of your MultiIndex, completely ignoring the first level.
• The position maps let us convert index values directly to array positions, which numpy processes in bulk—no slow per-row/column pandas operations.

Method 2: Pandas .loc with Boolean Filtering (More Intuitive) #

If you prefer a readable pandas-native approach (slightly slower for massive datasets but still efficient), you can use boolean indexing to target rows/columns by their second-level index:

for r_second, c_second in str_cord:
    # Target the single row where second-level index matches r_second
    target_row = df.index[df.index.get_level_values(1) == r_second]
    # Target the single column where second-level index matches c_second
    target_col = df.columns[df.columns.get_level_values(1) == c_second]
    # Assign value directly via .loc
    df.loc[target_row, target_col] = 1

Why this works:

• We filter rows/columns exclusively by their second-level index, so the first level never needs to be specified.
• Since your second-level indexes are unique, each filter returns exactly one row/column, making .loc assignment straightforward.

Verify the Result #

After running either method, your DataFrame will match the df_result you provided—cells at the specified second-level coordinate pairs will be set to 1, while all others remain 0.

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