如何从给定列表中查找CSV列中最接近的值并将结果添加到下一列？

Question

我有一个带有数字列的CSV文件。在这种情况下，检查ARPU列。（检查照片） 另外，我在代码中有一个数字列表，我想从我在代码中创建的列表中的“ARPU”列中找到每个值的最佳匹配。我希望结果记录在CVS文件的下一列中

我现有的代码是

tp_usp15 = 1500
tp_usp23 = 2300
tp_usp27 = 2700
tp_usp40 = 4000
tp_usp60 = 6000
tp_usp80 = 8000
tp_usp100 = 10000
tp_usp150= 15000
tp_usp250= 25000
tp_usp500= 50000
list_usp = [tp_usp15,tp_usp23, tp_usp27, tp_usp40, tp_usp60, tp_usp80, tp_usp100,tp_usp150, tp_usp250, tp_usp500]


tp_bsnspls_s = 600
tp_bsnspls_steel = 1300
tp_bsnspls_chrome = 1800
tp_bsnspls_bronze = 2200
tp_bsnspls_silver = 3700
tp_bsnspls_gold = 5600
tp_bsnspls_gold_plus = 7500
tp_bsnspls_platinum = 10600
tp_bsnspls_platinum_plus = 15600
tp_bsnspls_vip = 25000
tp_bsnspls_vip_plus = 50000
list_bsnspls = [tp_bsnspls_s,tp_bsnspls_steel,tp_bsnspls_chrome, 
tp_bsnspls_bronze,
            tp_bsnspls_silver,tp_bsnspls_gold,tp_bsnspls_gold_plus,
            tp_bsnspls_platinum,tp_bsnspls_platinum_plus,tp_bsnspls_vip, tp_bsnspls_vip_plus]


tp_bsnsrshn10 = 1000
tp_bsnsrshn15 = 1500
tp_bsnsrshn20 = 2000
tp_bsnsrshn25 = 2500
tp_bsnsrshn30 = 3000
tp_bsnsrshn35 = 3500
tp_bsnsrshn40 = 4000
tp_bsnsrshn50 = 5000
tp_bsnsrshn70 = 7000
tp_bsnsrshn100 = 10000
tp_bsnsrshn150 = 15000
list_bsnsrshn = [tp_bsnsrshn10,tp_bsnsrshn15,tp_bsnsrshn20,tp_bsnsrshn25,tp_bsnsrshn30,tp_bsnsrshn35,
            tp_bsnsrshn40,tp_bsnsrshn50,tp_bsnsrshn70,tp_bsnsrshn100,tp_bsnsrshn150]

common_list = list_usp + list_bsnspls + list_bsnsrshn

import csv
with open('ROOT','r') as csvinput:
    with open('ROOT.csv', 'w') as csvoutput:
        writer = csv.writer(csvoutput, lineterminator='\n')
        reader = csv.reader(csvinput, skipinitialspace=False,delimiter=',', quoting=csv.QUOTE_NONE)
    
    all = []
    row = next(reader)
    row.append("Suggested plan")
    all.append(row)

    
    for row in reader:
       
        row.append(min(common_list, key=lambda x:abs(x-float(row[2]))))
        all.append(row)
    writer.writerows(all)

但它给了我错误：ValueError:无法将字符串转换为浮点：“”

Answer 1

由于您的帖子不包含完整值错误，因此很难调试。无论如何，我建议使用pandas来解决这个问题：

import pandas as pd

df = pd.read_csv('ROOT.csv')

def get_plan(arpu):
    return min(common_list, key=lambda x:abs(x-float(arpu)))
    
df["Suggested plan"] = df["ARPU"].apply(get_plan)
df.to_csv('ROOT.csv')

Answer 2

common_list = list_usp + list_bsnspls + list_bsnsrshn

import csv

with open("output.csv", mode='a+') as results:
  ourWriter=csv.writer(results, delimiter=",")
  ourWriter.writerow(["Zone","Number","ARPU","Closest Match"])
index = 0
with open("hisfile.csv", "r") as startingFile:
  reader=csv.reader(startingFile)
  for row in reader:
    if index == 0:
      index = 1
      continue
    tmpNum = row[2]
    resNum = min(common_list, key=lambda x:abs(x-float(tmpNum)))
    with open("output.csv", mode='a+') as results:
      ourWriter=csv.writer(results, delimiter=",")
      ourWriter.writerow(row + [resNum])

如果没有依赖项，也可以使用。最后，我建议您更熟悉运行代码所使用的工具，因为如果不知道将来错误的行号，则很难排除所遇到的问题