写入csv，然后检查列中的值并写入附加的数据

Question

到目前为止，我已经为csv文件编写了一长串（约45000行）ID号以及附加的参考值。数据结构如下：

12345678 | 2
56789012 | 10
90123456 | 46
...

到目前为止，我为此编写的代码如下所示：

def list_writer():
    with open (csv_dir + '/' + csv_filename, mode = "w", newline='') as csvfile:
        writer = csv.writer(csvfile, lineterminator='\n', delimiter=';')
        for row in ID_list:
            writer.writerow(row)

list_writer()

每个ID号（左列）都与1-100范围内的参考号（右列）相关联。我有几个附加列表，将每个参考号与附加信息（价格、数量等）相关联

我现在的目标是遍历我编写的长csv文件第二列中的所有引用号，并将其他属性写入下一列。我在StackExchange上做了一些挖掘，但到目前为止没有任何效果。提前谢谢

Answer 1

这听起来像是我在关系型（即SQL）数据库中做的事情，那里有很多工具来验证数据并确保数据保持一致

如果要在Python中执行此操作，可以执行以下操作：

# put your "lists of prices" into a dictionary, keyed by the reference number
# assuming the prices is in the form [(ref1, price1), (ref2, price2)]
ref_prices = {}
for ref, price in PRICE_list:
  ref_prices[ref] = price

# do the same for each additional list:
# shorter syntax than the above
ref_quantity = {ref: qty for ref, qty in QTY_list}

# combine all of the above and write into a file
with open(filename, 'w') as fd:
  out = csv.writer(fd, delimiter=';')
  for id, ref in ID_list:
    out.writerow((id, ref, ref_prices[ref], ref_quantity[ref]))

Answer 2

这是一个完美的SQL用例。如果您想在Python中实现类似SQL的函数，使用pandas通常是一个好主意。它方便、易读、易写、快捷。对于您的情况，假设附加值将存储在元组列表或字典中：

import pandas as pd


csv = [
    (1, 10),
    (2, 20),
    (3, 30),
]

csv_df = pd.DataFrame(csv, columns=["id", "reference"])

# This would be the data you have in your csv. For actually loading them from your 
# csv located at `filepath`, use 
#
#      pd.DataFrame.read_csv(filepath)

additional_data = [
    (1, "a"),
    (2, "b"),
    (3, "c"),
]  # This could also be a dictionary

additional_df = pd.DataFrame(additional_data, columns=["id", "name"])

final_df = csv_df.merge(additional_df, on="id")

然后我们得到

>>> final_df
   id  reference name
0   1         10    a
1   2         20    b
2   3         30    c