Pandas多索引DataFrame中为部分行修改指定列值的问题

1. Correct Method to Implement the Replacement #

There are two reliable ways to achieve your desired result:

Option A: Fix the Loop-Based Assignment #

The issue with your current loop is index alignment mismatch. To fix it, you can either bypass pandas' index alignment using .values or create a Series with matching MultiIndex columns for the assignment.

Here's the corrected loop code:

import pandas as pd
import numpy as np

# Recreate your example data
midx = pd.MultiIndex.from_product([['A', 'B'], np.arange(0,10)])
df = pd.DataFrame(np.concatenate((np.arange(1.,51.).reshape(5,10), np.arange(-51., -1.).reshape(5,10)), axis=1), 
                  index=np.arange(0,5), columns=midx)
df_sub = pd.DataFrame([[np.nan, np.nan, np.nan, np.nan, np.nan, 0.5, 0.6, 0.7, np.nan, np.nan], 
                       [np.nan, np.nan, np.nan, 0.3, 0.4, 0.5, np.nan, np.nan, np.nan, np.nan]], 
                      index=[1,3], columns=np.arange(0,10))

# Corrected loop
for idx, row in df_sub.iterrows():
    arr = row.index[~row.isnull()]
    # Use .values to bypass index alignment
    df.loc[idx, pd.IndexSlice['B', arr]] = row[arr].values

print(df.loc[:, 'B'])

Option B: Use df.update() (Simpler & More Robust) #

The update() method is built for this exact scenario—it replaces non-NaN values from a secondary DataFrame into the main one, automatically aligning on index and columns. Here's how to use it:

# Align df_sub to df's structure
df_sub_aligned = df_sub.reindex(df.index)  # Add missing rows with NaN
df_sub_aligned.columns = pd.MultiIndex.from_product([['B'], df_sub_aligned.columns])  # Match MultiIndex columns

# Perform the update
df.update(df_sub_aligned)

print(df.loc[:, 'B'])

This will correctly replace the targeted values without any manual looping.

2. Why Your Current Assignment Fails #

The root cause is index alignment mismatch:

• The left-hand side (LHS) df.loc[idx, pd.IndexSlice['B', arr]] uses MultiIndex column labels like ('B', 5) or ('B', 6).
• The right-hand side (RHS) row[arr] uses plain integer column labels like 5 or 6.
• Pandas tries to align these indices, but since they don't match (tuples vs integers), it can't map the values correctly and assigns NaN instead.

3. More Efficient Assignment Method #

The df.update() approach from Option B is the most efficient solution. It's vectorized (no loops) and leverages pandas' built-in alignment logic, making it much faster for large DataFrames compared to row-by-row iteration.

For even larger datasets, you could also use pd.DataFrame.combine_first(), but update() is more straightforward here because it only replaces non-NaN values from the subset, which is exactly what you need.

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