如何用Excel数据填充Pandas MultiIndex DataFrame的指定列？

我已经通过.stack()方法为MultiIndex添加了下层索引，现在需要将当前的长格式结构调整为宽表形式，并从Excel文件中填充'p'和'q'列的数据，但卡在了数据赋值环节。

已使用代码 #

lists = [
    ('C5P', 'CI3'), ('C2P', 'CI4'), ('C1', 'CI3'), ('C7', 'CI4'),
    ('C3P', 'CI1'), ('C3P', 'CI2'), ('C4', 'CI1'), ('C3P', 'CI3'),
    ('C4P', 'CI4'), ('C5P', 'CI2'), ('C4P', 'CI3'), ('C3P', 'CI4'),
    ('C2P', 'CI2'), ('C6', 'CI2'), ('C7', 'CI2'), ('C7', 'CI3'),
    ('C1P', 'CI1'), ('C2P', 'CI3'), ('C2', 'CI1'), ('C4P', 'CI2'),
    ('C2', 'CI2'), ('C3', 'CI2'), ('C1P', 'CI2'), ('C6', 'CI1'),
    ('C3', 'CI3'), ('C1', 'CI1'), ('C5P', 'CI1'), ('C3P', 'CI4'),
    ('C5', 'CI2'), ('C4', 'CI2')
]

index = pd.MultiIndex.from_tuples(lists, names=['borehole','sample'])

d = pd.DataFrame(data=1, index=index, columns=['1','2','3']).stack()
e = pd.DataFrame(data=1, index=d.index, columns=['p','q']).stack()

当前输出结果 #

borehole  sample      
C4        CI1     1  p    1
                     q    1
                  2  p    1
                     q    1
                  3  p    1
                     q    1
          CI2     1  p    1
                     q    1
                  2  p    1
                     q    1
                  3  p    1
                     q    1
C5P       CI3     1  p    1
                     q    1
                  2  p    1
                     q    1
                  3  p    1
                     q    1
          CI2     1  p    1
                     q    1
                  2  p    1
                     q    1
                  3  p    1
                     q    1
          CI1     1  p    1
                     q    1
                  2  p    1
                     q    1
                  3  p    1
                     q    1

目标结构（示例数据） #

borehole    C5P                     
sample      CI2                     
specimen    1                       2                       3       
            p           q           p           q           p           q   
            140.5745654 90.9496963  157.5959379 101.2858137 236.882037  105.4921111 
            140.8908259 91.01647765 157.4825549 101.4976646 236.6523714 105.2591143 
            141.1076296 91.08488883 157.3080705 101.8022116         
            141.3524628 91.17138846 157.3539987 102.167996          
            143.2526588 92.28797636                 
            143.776291  92.62887289

解决步骤 #

1. 将长格式转换为宽表结构 #

当前的e是4级索引的Series，通过unstack()逐步展开索引层级，还原成目标的多列结构：

# 展开最后一层索引（p/q），得到包含p、q列的DataFrame
df = e.unstack(level=-1)
# 再展开倒数第二层索引（1/2/3），生成多级列头
df = df.unstack(level=-1)
# 调整列的层级顺序，匹配目标结构
df = df.swaplevel(axis=1).sort_index(axis=1)

2. 读取Excel数据并赋值 #

利用Pandas MultiIndex的自动对齐特性，读取Excel数据后直接定位赋值：

# 读取Excel，header参数指定多级表头的行数（示例中是4行表头）
excel_data = pd.read_excel('your_file_path.xlsx', header=[0,1,2,3])

# 遍历Excel中的钻孔和样本，匹配后赋值
for borehole in excel_data.columns.get_level_values(0).unique():
    for sample in excel_data[borehole].columns.get_level_values(0).unique():
        # 定位对应行，将Excel数据填充进去
        df.loc[(borehole, sample)] = excel_data[borehole][sample].values

关键提示 #

• unstack()的level参数可以精准控制展开的索引层级，逐步构建目标列结构。
• Pandas会自动对齐MultiIndex的行索引和列索引，无需手动匹配位置，直接赋值即可完成数据填充。

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