CODEWITHSUNDEEP
pythoncsv
Thomas Cuisance
Thomas Cuisance

Reputation: 41

How to find closest match for values in CSV column from a given list and add results to the next column?

I have a CSV file with the column of numbers. In this case, check ARPU column.(Check photo) Also, I have a list of numbers in the code and I want to find the best match for each value from column "ARPU" from the list that I have created in the code. And I want results to be recorded in the next column in CVS file

Check image here

My existing code is

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)

But it gives me error: ValueError: could not convert string to float: ''

Upvotes: 0

Views: 497

Answers (2)

swarles-barkley
swarles-barkley

Reputation: 65

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])

Will also work if you can't have dependencies. Ultimately, I would suggest getting more familiar with the tool you're using to run your code, as it will be extremely difficult to troubleshoot problems you're having without knowing what line number your errors come from in the future

Upvotes: 0

RJ Adriaansen
RJ Adriaansen

Reputation: 9639

As your post does not contain the full value error it is difficult to debug. Regardless, I suggest using pandas to solve the issue:

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')

Upvotes: 1

Related Questions