如何通过DF2的Part与Week匹配更新DF1的Qty值？（Pandas）

问题背景 #

需要根据DF2的ITM_NO（对应DF1的Part）和WEEK列，更新DF1中对应位置的数值。之前尝试用melt+merge+pivot的流程时，因Part和Week重复出现报错grouper is not 1 dimensional，现提供两种更优实现方式，均满足仅更新对应值、不聚合DF2数据的要求。

原始数据 #

DF1初始数据：

import pandas as pd
import numpy as np

df1 = pd.DataFrame({
    'Part': {0: 'Part1', 1: 'part2', 2: 'Part3'},
    'Week26': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week27': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week28': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week29': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week30': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week31': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week32': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week33': {0: np.nan, 1: np.nan, 2: np.nan},
    'Week34': {0: np.nan, 1: np.nan, 2: np.nan}
})

DF2数据：

df2 = pd.DataFrame({
    'ITM_NO': {0: 'Part1', 1: 'Part1', 2: 'Part1', 3: 'part2', 4: 'part2', 5: 'part2', 6: 'part2', 7: 'Part3', 8: 'Part3', 9: 'Part3', 10: 'Part3'},
    'WEEK': {0: 'Week26', 1: 'Week27', 2: 'Week28', 3: 'Week26', 4: 'Week27', 5: 'Week28', 6: 'Week29', 7: 'Week29', 8: 'Week30', 9: 'Week31', 10: 'Week32'},
    'QTY': {0: 12, 1: 10, 2: 30, 3: 20, 4: 40, 5: 60, 6: 70, 7: 20, 8: 10, 9: 30, 10: 20}
})

方法一：宽表对齐+update（高效推荐） #

利用pivot将DF2转换为与DF1结构一致的宽表，再通过update方法自动匹配索引和列名完成更新，效率高且代码简洁。

# 将DF2转换为宽表，索引为ITM_NO，列为WEEK，值为QTY
df2_wide = df2.pivot(index='ITM_NO', columns='WEEK', values='QTY')

# 把DF1的Part设为索引，执行更新后恢复原结构
df1 = df1.set_index('Part')
df1.update(df2_wide)
df1 = df1.reset_index()

# 查看结果
print(df1)

说明 #

• pivot转换后，DF2的结构和DF1完全对齐，确保每个(ITM_NO, WEEK)唯一（从你的数据看无重复，若有重复需先处理，但需求明确不聚合，所以默认DF2无重复键）。
• update方法只会覆盖DF1中对应位置的NaN值，不会修改其他已有数据，完全符合需求。

方法二：逐行定位赋值（适合小数据量） #

遍历DF2的每一行，通过df.loc精准定位DF1的对应行和列进行赋值，逻辑直观但数据量大时效率较低。

for _, row in df2.iterrows():
    # 定位DF1中Part匹配的行，以及对应的Week列
    df1.loc[df1['Part'] == row['ITM_NO'], row['WEEK']] = row['QTY']

# 查看结果
print(df1)

预期输出 #

两种方法最终都会得到更新后的DF1：

Part  Week26  Week27  Week28  Week29  Week30  Week31  Week32  Week33  Week34
0  Part1    12.0    10.0    30.0     NaN     NaN     NaN     NaN     NaN     NaN
1  part2    20.0    40.0    60.0    70.0     NaN     NaN     NaN     NaN     NaN
2  Part3     NaN     NaN     NaN    20.0    10.0    30.0    20.0     NaN     NaN

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