如何为CSV每行的Offnet calls与Traffic值从嵌套列表中查找最接近匹配

前置修正说明 #

你原代码中list_client = [client_payment, client_offnet]存在两处问题：一是未定义client_payment变量，二是字段顺序和common_list子列表的存储顺序（Offnet calls在前、Traffic在后）不匹配，下方代码已修正该问题，同时用预定义映射字典替代locals()查找变量名，避免同值变量返回结果不稳定的问题。

方案1：用Pandas实现（最简便） #

需先安装pandas：pip install pandas

import pandas as pd
from functools import partial

# 预定义匹配模板
tp_usp15 = [10, 200]
tp_usp23 = [15, 250]
tp_usp27 = [20, 300]
list_usp = [tp_usp15,tp_usp23, tp_usp27]

tp_bsnspls_s = [1,30]
tp_bsnspls_steel = [13,250]
tp_bsnspls_chrome = [18,350]
list_bsnspls = [tp_bsnspls_s,tp_bsnspls_steel,tp_bsnspls_chrome]

tp_bsnsrshn10 = [10,200]
tp_bsnsrshn15 = [15,300]
tp_bsnsrshn20 = [20,400]
list_bsnsrshn = [tp_bsnsrshn10,tp_bsnsrshn15,tp_bsnsrshn20]

common_list = list_usp + list_bsnspls + list_bsnsrshn

# 预构建值到变量名的映射，避免locals查找不稳定
value_name_map = {
    tuple(tp_usp15): "tp_usp15",
    tuple(tp_usp23): "tp_usp23",
    tuple(tp_usp27): "tp_usp27",
    tuple(tp_bsnspls_s): "tp_bsnspls_s",
    tuple(tp_bsnspls_steel): "tp_bsnspls_steel",
    tuple(tp_bsnspls_chrome): "tp_bsnspls_chrome",
    tuple(tp_bsnsrshn10): "tp_bsnsrshn10",
    tuple(tp_bsnsrshn15): "tp_bsnsrshn15",
    tuple(tp_bsnsrshn20): "tp_bsnsrshn20"
}

# 欧氏距离平方计算（平方不影响最小值排序，省略开方提升性能）
def distance_squared(x, y):
    return (x[0] - y[0])**2 + (x[1] - y[1])**2

# 读取输入CSV，替换为你本地的文件路径
df = pd.read_csv("input.csv")

# 逐行计算最优匹配
def get_best_match(row):
    client_data = [row["Offnet calls"], row["Traffic"]]
    best_val = min(common_list, key=partial(distance_squared, client_data))
    return value_name_map[tuple(best_val)]

df["Best match"] = df.apply(get_best_match, axis=1)

# 调整列顺序，让Best match紧邻Traffic列
cols = df.columns.tolist()
traffic_idx = cols.index("Traffic")
cols.insert(traffic_idx + 1, cols.pop(cols.index("Best match")))
df = df[cols]

# 保存结果到新CSV，不要直接覆盖原文件
df.to_csv("output.csv", index=False)

方案2：用Python自带csv模块实现（无需安装第三方库） #

import csv
from functools import partial

# 此处复制方案1中所有的预定义模板、value_name_map、distance_squared函数即可

# 替换为本地文件路径
input_path = "input.csv"
output_path = "output.csv"

with open(input_path, 'r', newline='', encoding='utf-8') as infile, \
     open(output_path, 'w', newline='', encoding='utf-8') as outfile:
    reader = csv.DictReader(infile)
    # 调整输出列顺序，插入Best match到Traffic之后
    fieldnames = reader.fieldnames.copy()
    traffic_idx = fieldnames.index("Traffic")
    fieldnames.insert(traffic_idx + 1, "Best match")
    writer = csv.DictWriter(outfile, fieldnames=fieldnames)
    
    writer.writeheader()
    for row in reader:
        offnet = int(row["Offnet calls"])
        traffic = int(row["Traffic"])
        client_data = [offnet, traffic]
        best_val = min(common_list, key=partial(distance_squared, client_data))
        row["Best match"] = value_name_map[tuple(best_val)]
        writer.writerow(row)

注意事项 #

• 替换代码中的输入输出路径为你本地的实际文件路径
• 如果你的CSV列名和样例不一致，需要对应修改代码里的列名字符串
• 同值模板的返回优先级可以通过调整value_name_map的键值对顺序修改

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