Pandas中实现按编码、年月匹配的类Vlookup成本列计算

核心实现思路 #

不要直接用宽格式成本表做动态列匹配，先把成本表统一转为「编码+年度+月份+成本」的长表结构，再分两次匹配新、旧编码，覆盖所有行的匹配需求。 #

步骤1：预处理统一数据格式 #

主表处理 #

先把发票日期转为标准时间格式，提取匹配必需的年份、月份字段，注意日期格式是日/月/年，解析时必须指定参数避免月份错位：

import pandas as pd

# 读取主表
df_main = pd.read_excel("主表文件路径.xlsx")
# 解析发票日期，适配日/月/年格式
df_main["Invoice Date"] = pd.to_datetime(df_main["Invoice Date"], dayfirst=True)
# 提取匹配用的年、月辅助字段
df_main["match_year"] = df_main["Invoice Date"].dt.year
df_main["match_month"] = df_main["Invoice Date"].dt.month

成本表处理 #

把2021、2022两张分年成本表合并，再从宽表（1-12月为单独列）转为长表结构，每行对应单月单商品的成本值：

# 读取两张年度成本表
df_cost_2021 = pd.read_excel("2021年成本表路径.xlsx")
df_cost_2022 = pd.read_excel("2022年成本表路径.xlsx")

# 给分年成本表加年度标记
df_cost_2021["match_year"] = 2021
df_cost_2022["match_year"] = 2022

# 合并为总成本表
df_cost_total = pd.concat([df_cost_2021, df_cost_2022], ignore_index=True)

# 宽表转长表，注意替换month_cols为你成本表里1-12月对应的实际列名
month_cols = [1,2,3,4,5,6,7,8,9,10,11,12] # 如果列名是"1月""Jan"之类的直接替换即可
df_cost_long = df_cost_total.melt(
    id_vars=["Old Code", "New Code", "Name", "match_year"],
    value_vars=month_cols,
    var_name="match_month",
    value_name="COST"
)

# 提前去重，避免同维度多条记录导致merge后主表行数膨胀
df_cost_long = df_cost_long.drop_duplicates(subset=["Old Code", "New Code", "match_year", "match_month"], keep="first")

步骤2：双编码补全匹配 #

因为主表部分行仅填新编码、部分仅填旧编码，分两次匹配覆盖所有场景：

1. 优先用新编码+年+月关联匹配成本
2. 第一次没匹配到的空值行，再用旧编码+年+月关联补匹配

# 第一次匹配：关联新编码
df_res = df_main.merge(
    df_cost_long[["New Code", "match_year", "match_month", "COST"]],
    on=["New Code", "match_year", "match_month"],
    how="left"
)

# 提取第一次未匹配到成本的行，用旧编码二次匹配
unmatch_mask = df_res["COST"].isna()
re_match = df_res[unmatch_mask].merge(
    df_cost_long[["Old Code", "match_year", "match_month", "COST"]],
    on=["Old Code", "match_year", "match_month"],
    how="left",
    suffixes=("", "_old")
)

# 把二次匹配到的成本补回结果表
df_res.loc[unmatch_mask, "COST"] = re_match["COST_old"].values

# 清理辅助字段，输出最终结果
df_final = df_res.drop(columns=["match_year", "match_month"])

校验注意点 #

• 日期解析后随机抽几行核对，比如样例里的03/05/2021必须解析为2021-05-03，对应匹配5月成本，若解析为2021-03-05要检查dayfirst参数是否正确设置
• 成本表转长表后，确认match_month字段格式和主表提取的月份格式一致（比如一边是字符串"05"、一边是数字5会匹配失败，统一转成同类型即可）
• 匹配完成后可以筛选COST为空的行单独排查，一般是编码不存在、对应年月无成本数据导致的

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