如何通过从另一个CSV文件查找来替换CSV列中的值?

Question

我有两个独立的CSV文件。我想扫描这两个CSV文件并找到匹配的SKU号。如果它们确实匹配，则从表1中与其SKU编号相关的编号更新表2中的编号。

import csv

with open("C:\Users\Owner\OneDrive\Desktop\Test\read.csv", 'r') as file:
# Create a CSV reader
reader = csv.reader(file)
rows1 = [row for row in reader]
with  open("C:\Users\Owner\OneDrive\Desktop\Test\import.csv", 'r') as file:
reader = csv.reader(file)
rows2 = [row for row in reader]
column1_values = [row[7] for row in rows1]
column2_values = [row[8] for row in rows2]
column3_values = [row[8] for row in rows1]
for row in rows1:
value2 = row[7]
for row in rows2:
value1 = row[8]

if value1 in column1_values and value2 in column2_values:
value3 = column3_values
print(value3)

到目前为止，我有能够读取这两个文件的python代码。然而，我在匹配sku号码然后更新库存号码时遇到了麻烦。

第一个我想从中提取数据的文件:

,,kh10,,,,0
,,kh12,,,,21
,,kh13,,,,1
,,kh11,,,,45
,,kh20,,,,26

第二个要更新的文件:

,kh20,,,0
,kh16,,,47
,kh12,,,31
,kh10,,,2
,kh13,,,0

我想匹配这些KH数字，然后从第一个文件中提取第一个文件中最后一列的数字，并用这些数字替换第二个文件中的最后一列。

Answer 1

您希望创建一个字典，其中键是SKU编号，值是您希望在结果中显示的数量。

with open("C:\Users\Owner\OneDrive\Desktop\Test\read.csv", 'r') as file:
# Create a CSV reader
reader = csv.reader(file)
inventory = {row[2].strip(): int(row[7]) for row in reader}

这应该创建一个inventory字典，看起来像:

{'kh10': 0, 'kh12': 21, 'kh13': 1, 'kh11': 45, 'kh20': 26}

现在，当您读取另一个文件时，将其E列替换为该字典中的相应值:

with open("C:\Users\Owner\OneDrive\Desktop\Test\import.csv", 'r') as file:
reader = csv.reader(file)
rows2 = [] # Create an empty list to hold all rows
for row in reader:
sku = row[1]
current_inventory = int(row[4]) # Current value
# Get updated value, keep current value if it doesn't exist in lookup dict
updated_inventory = inventory.get(sku, current_inventory)
# Store updated value
row[4] = updated_inventory
# Append row to master list
rows2.append(row)

现在，您有一个包含第二个CSV文件中的数据的列表列表，但包含了更新的库存编号。

[['', 'kh20', '', '', 26],
['', 'kh16', '', '', 47],
['', 'kh12', '', '', 21],
['', 'kh10', '', '', 0],
['', 'kh13', '', '', 1]]

您可能已经知道如何将其写入CSV文件。如果没有，请看这个答案