多索引Pandas数据框指定列除以常量返回NaN问题求助

我有一个多级索引的Pandas DataFrame，包含31列，第一级索引对应数据来源文件。需要将特定列的像素值除以整数缩放因子px_to_mm转换为毫米值，这些列原本是浮点类型，但运行代码后目标列全变成了NaN，代码如下：

unique_animals = df.index.get_level_values('File').unique() 
px_to_mm = 791
columns_in_px = ['mouth_x', 'mouth_y', 'stomach_centre_x',
       'stomach_centre_y', 'aboral_organ_x',
       'aboral_organ_y', 'tentacle_1_x',
       'tentacle_1_y', 'tentacle_2_x', 'tentacle_2_y',
        'cilia_1_x', 'cilia_1_y', 
       'cilia_2_x', 'cilia_2_y',  'X_diff_stomach',
       'Y_diff_stomach']

for animal in unique_animals:
    for column in columns_in_px:
        df.loc[animal, column] = df.loc[animal, column] / px_to_mm

DataFrame索引结构 #

MultiIndex([('CtenoEgg230801_',     0),
        ('CtenoEgg230801_',     1),
        ('CtenoEgg230801_',     2),
        ('CtenoEgg230801_',     3),
        ('CtenoEgg230801_',     4),
        ('CtenoEgg230801_',     5),
        ('CtenoEgg230801_',     6),
        ('CtenoEgg230801_',     7),
        ('CtenoEgg230801_',     8),
        ('CtenoEgg230801_',     9),
        ...
        ('CtenoEgg230802_', 66240),
        ('CtenoEgg230802_', 66241),
        ('CtenoEgg230802_', 66242),
        ('CtenoEgg230802_', 66243),
        ('CtenoEgg230802_', 66244),
        ('CtenoEgg230802_', 66245),
        ('CtenoEgg230802_', 66246),
        ('CtenoEgg230802_', 66247),
        ('CtenoEgg230802_', 66248),
        ('CtenoEgg230802_', 66249)],
       names=['File', None], length=106632)

前几行样本数据 #

mouth_x mouth_y mouth_likelihood    stomach_centre_x    stomach_centre_y    stomach_centre_likelihood   aboral_organ_x  aboral_organ_y  aboral_organ_likelihood tentacle_1_x    ... X_diff_stomach  Y_diff_stomach  Velocity_stomach    Acceleration_stomach    Theta_mouth_stomach Theta_Velocity_mouth_stomach    Theta_Acceleration_mouth_stomach    Theta_deg_mouth_stomach Height_Index    Frame
File                                                                                        
0   231.626724  233.873352  0.999196    200.364288  191.369202  0.998929    168.946747  140.374954  0.996564    202.717392  ... NaN NaN NaN NaN 0.936630    NaN NaN 53.692184   86.915467   0
1   230.637405  234.197998  0.999158    200.186630  191.611725  0.998900    169.261520  140.385788  0.997088    203.156342  ... -0.177658   0.242523    NaN NaN 0.950049    0.013419    NaN 54.461426   87.094083   1
2   230.883316  233.928162  0.999064    200.056335  191.886490  0.999025    169.505844  139.894012  0.997208    205.199158  ... -0.130295   0.274765    0.304093    NaN 0.938103    -0.011946   -0.025365   53.776596   87.748322   2
3   229.841034  234.385590  0.999249    199.935638  191.638977  0.999073    170.242477  139.233582  0.995122    203.273712  ... -0.120697   -0.247513   0.275373    -0.028720   0.960341    0.022238    0.034185    55.051394   87.569965   3
4   229.045685  234.782135  0.999314    200.159286  191.692688  0.999104    169.480316  138.349838  0.994976    203.242462  ... 0.223648    0.053711    0.230007    -0.045366   0.980226    0.019885    -0.002353   56.191290   88.285329   4

问题原因 #

直接用df.loc[animal, column]赋值时，Pandas无法正确匹配多级索引的行——因为animal只是第一级索引的值，缺少第二级索引的匹配规则，导致赋值操作没有命中目标数据，最终生成NaN。同时，嵌套循环的方式效率极低，还容易触发索引匹配问题。

解决方法 #

方法1：批量运算（推荐） #

不需要循环，直接选中所有目标列执行除法运算，Pandas会自动处理多级索引：

px_to_mm = 791
columns_in_px = ['mouth_x', 'mouth_y', 'stomach_centre_x',
       'stomach_centre_y', 'aboral_organ_x',
       'aboral_organ_y', 'tentacle_1_x',
       'tentacle_1_y', 'tentacle_2_x', 'tentacle_2_y',
        'cilia_1_x', 'cilia_1_y', 
       'cilia_2_x', 'cilia_2_y',  'X_diff_stomach',
       'Y_diff_stomach']

# 直接对目标列执行运算，自动保留索引结构
df[columns_in_px] = df[columns_in_px] / px_to_mm

方法2：修复索引匹配（不推荐，效率低） #

如果一定要用循环，需要用pd.IndexSlice明确指定多级索引的匹配规则：

import pandas as pd

unique_animals = df.index.get_level_values('File').unique() 
px_to_mm = 791
columns_in_px = ['mouth_x', 'mouth_y', 'stomach_centre_x',
       'stomach_centre_y', 'aboral_organ_x',
       'aboral_organ_y', 'tentacle_1_x',
       'tentacle_1_y', 'tentacle_2_x', 'tentacle_2_y',
        'cilia_1_x', 'cilia_1_y', 
       'cilia_2_x', 'cilia_2_y',  'X_diff_stomach',
       'Y_diff_stomach']

for animal in unique_animals:
    for column in columns_in_px:
        # 使用IndexSlice匹配第一级索引为animal的所有行
        df.loc[pd.IndexSlice[animal, :], column] = df.loc[pd.IndexSlice[animal, :], column] / px_to_mm

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