使用python在两个csv文件之间匹配列值时输出错误

Question

我有两个csv文件，其结构如下：

Fil1.csv:

66054,14.7065,42.1115
66054,14.7085,42.106
66054,14.7268,42.0937
66054,14.6739,42.125
66054,14.7268,42.0937
66100,14.116,42.3301
66100,14.1405,42.3392
88067,16.431,38.7287
88068,16.5339,38.6899
88068,16.5499,38.685
88068,16.5419,38.6875
87076,16.4795,39.7905
87076,16.4743,39.8161
87100,16.2531,39.2989
87100,16.2944,39.2674
87100,16.3039,39.2709
87052,16.43,39.3449
87053,16.3399,39.3101
87054,16.3171,39.1784

文件2.csv:

ABC,66100
"CDF",65125
"123",65125
1234,64100
0123,75025
lmn,85025
abc,88046
"Random",88068
"Raond2",87100
"Raondm3",87100
Raondom4,87054

现在我要做的是，为file2.csv中的每个row2[1]，找到它在row1[0]中的第一个出现，并从该行中提取row1[1]和row1[2]，与row2[0]和row2[1]一起插入，然后将其写入另一个csv文件。以下是我为此编写的代码：

updated_list = []

with open("file1.csv","r") as in_file1, open("file2.csv", "r") as in_file2, open("file3.csv", "w", newline='') as out_file:
    reader1 = csv.reader(in_file1)
    reader2 = csv.reader(in_file2)
    writer_final = csv.writer(out_file)
    for row2 in reader2: #reader2 is for file2

        for row1 in reader1:#reader1 is for file1
            if str(row2[1].strip()) == str(row1[0].strip()):
                print("Found match for {}".format(row2[1]))
                updated_list.append([row2[0],row2[1],row1[1],row1[2]])
                break
            else:
                continue


    writer_final.writerows(updated_geo_list)

上面的代码能够匹配一些，但是对于file2.csv中的大量row2[1]，即使它存在，也不能匹配file1.csv中的row1[0]。例如，在上面的示例数据中，尽管file1.csv包含这两个值，但代码无法从file2.csv到file1.csv匹配87100和87054。我认为这些字符串中可能会有一些额外的空格，所以我也使用了split()，但它仍然不起作用。为什么没有进行匹配

Answer 1

正如我在评论中提到的：文件对象是流，一旦过了某个点，就再也看不到它了—您需要将一个文件放入内存中，以便将其中一个对象的所有行与另一个对象的所有行进行比较

此代码将较小的文件读入内存，并逐行处理较大的文件

第一个匹配行请求较小文件中所有行的数据，之后较小文件的行从内存中删除，因此它与后面的行不匹配：

创建文件：

with open("f1.txt","w") as f:
    f.write("""66054,14.7065,42.1115
66054,14.7085,42.106
66054,14.7268,42.0937
66054,14.6739,42.125
66054,14.7268,42.0937
66100,14.116,42.3301
66100,14.1405,42.3392
88067,16.431,38.7287
88068,16.5339,38.6899
88068,16.5499,38.685
88068,16.5419,38.6875
87076,16.4795,39.7905
87076,16.4743,39.8161
87100,16.2531,39.2989
87100,16.2944,39.2674
87100,16.3039,39.2709
87052,16.43,39.3449
87053,16.3399,39.3101
87054,16.3171,39.1784""")

with open ("f2.txt","w") as f:
    f.write("""ABC,66100
"CDF",65125
"123",65125
1234,64100
0123,75025
lmn,85025
abc,88046
"Random",88068
"Raond2",87100
"Raondm3",87100
Raondom4,87054""")

程序

import csv

d2 ={}
# smaller file: load in memory
with open("f2.txt") as f:
    cr = csv.reader(f)
    for row in cr:
        # store under same key as list of rows to keep same order and 
        # allow multiple rows with same row[1] value
        k = d2.setdefault(row[1],[])
        k.append(row)

# process larger file 
with open("f1.txt") as f, open("f3.txt","w",newline="") as nf:
    cr = csv.reader(f)
    writer = csv.writer(nf)
    for row in cr:
        if d2.get(row[0],[]):
            for sl in d2.get(row[0]):
                writer.writerow( (sl + [row[1],row[2]]) )
            # remove from d2 so no reappearing rows will be written
            del d2[row[0]] 

with open("f3.txt") as f:
    print(f.read())

输出：

ABC,66100,14.116,42.3301
Random,88068,16.5339,38.6899
Raond2,87100,16.2531,39.2989
Raondm3,87100,16.2531,39.2989
Raondom4,87054,16.3171,39.1784

只有文件2中在文件1中完全匹配的内容才会放入文件3

Answer 2

在运行代码并将print语句放在少数地方之后，观察到只对file2.csv的第一个值，即“ABC，66100”进行比较。Rest代码只是跳过

因为csv.reader返回一个reader对象，它是一个迭代器。
因此，一旦迭代整个对象，它就变成空的。 作为一种解决方法，您需要将阅读器保存为列表，以便反复迭代

修改行
reader1 = csv.reader(in_file1)

至

reader1 = list(csv.reader(in_file1))

应该会给你想要的结果

import csv

updated_list = []

with open("file1.csv","r") as in_file1, open("file2.csv", "r") as in_file2, open("file3.csv", "w", newline='') as out_file:
    reader2 = csv.reader(in_file2)
    reader1 = list(csv.reader(in_file1))
    writer_final = csv.writer(out_file)
    for row2 in reader2:   #reader2 is for file2
        for row1 in reader1:  #reader1 is for file1
            if str(row2[1].strip()) == str(row1[0].strip()):
                updated_list.append([row2[0],row2[1],row1[1],row1[2]])
                break

    writer_final.writerows(updated_list)

cat file3.csv 
ABC,66100,14.116,42.3301
Random,88068,16.5339,38.6899
Raond2,87100,16.2531,39.2989
Raondm3,87100,16.2531,39.2989
Raondom4,87054,16.3171,39.1784

注意 如果您有大文件，将读取器转换为列表可能是有害的，因为它可能会影响内存。更好的选择是使用openpyxl或将数据加载到pandas数据帧中并在其中进行操作