比较两个csv文件之间的列并使用Python编写差异 [英] Compare a column between 2 csv files and write differences using Python

问题描述

我正在尝试通过比较2个csv文件之间的列来打印出差异.

I am trying to print out the differences by comparing a column between 2 csv files.

CSV1:

SERVER,   FQDN,   IP_ADDRESS,  
serverA, device1.com, 10.10.10.1  
serverA,device2.com,10.11.11.1  
serverC,device3.com,10.12.12.1   
and so on..

CSV2:

FQDN, IP_ADDRESS, SERVER,  LOCATION  
device3.com,10.12.12.1,serverC,xx  
device679.com,20.3.67.1,serverA,we  
device1.com,10.10.10.1,serverA,ac  
device345.com,192.168.2.0,serverA,ad  
device2.com,192.168.6.0,serverB,af  
and so on...

我想要做的是比较FQDN列，并将差异写入新的csv输出文件.所以我的输出看起来像这样:

What I am looking to do is to compare the FQDN column and write the differences to a new csv output file. So my output would look something like this:

Output.csv:

Output.csv:

FQDN, IP_ADDRESS, SERVER, LOCATION  
device679.com,20.3.67.1,serverA,we  
device345.com,192.168.2.0,serverA,ad  
and so on..

我已经尝试过，但是无法获得输出.

I have tried, but not able to get the output.

这是我的代码，请告诉我我要去哪里了

This is my Code, please tell me where i am going wrong;

import csv

data = {}  # creating list to store the data

with open('CSV1.csv', 'r') as lookuplist:
 reader1 = csv.reader(lookuplist)
 for col in reader1:
    DATA[col[0]] = col[1]

with open('CSV2.csv', 'r') as csvinput, open('Output.csv', 'w', newline='') as f_output:
 reader2 = csv.reader(csvinput)
 csv_output = csv.writer(f_output)
 fieldnames = (['FQDN', 'IP_ADDRESS', 'SERVER'])
 csv_output.writerow(fieldnames)  # prints header to the output file

    for col in reader1:
     if col[1] not in reader2:
        csv_output.writerow(col)  

(编辑)这是我使用的另一种方法:

(EDIT) This is another approach that I have used:

import csv

f1 = (open("CSV1.csv"))
f2 = (open("CSV2.csv"))

csv_f1 = csv.reader(f1)
csv_f2 = csv.reader(f2)

for col1, col2 in zip(csv_f1, csv_f2):
    if col2[0] not in col1[1]:
    print(col2[0])

基本上，这里我仅尝试首先确定是否打印了不匹配的FQDN.而是打印出整个CSV1列.请帮忙，很多研究已经投入到这个过程中，但是还没有发现好运！ :(

Basically, here I am only trying to find out first whether the unmatched FQDNs are printed or not. But it is printing out the whole CSV1 column instead. Please help guys, lot of research has went into this, but found no luck yet! :(

推荐答案

import csv

data = {}  # creating dictionary to store the data

with open('CSV1.csv', 'r') as lookuplist:
reader1 = csv.reader(lookuplist)
for col in reader1:
    data[col[1]] = col[1]  # stores the data from column 0 to column 1 in the data list

with open('CSV2.csv', 'r') as csvinput, open('Output.csv', 'w', newline='') as f_output:
reader2 = csv.reader(csvinput)
csv_output = csv.writer(f_output)
fieldnames = (['SERVER', 'FQDN', 'AUTOMATION_ADMINISTRATOR', 'IP_ADDRESS', 'PRIMARY_1', 'MHT_1', 'MHT_2',
               'MHT_3'])
csv_output.writerow(fieldnames)  # prints header to the output file

for col in reader2:
    if col[0] not in data:  # if the column 1 in CSV1 does not match with column 0 in CSV2 Extract
    col = [col[0]]


        csv_output.writerow(col)  # writes all the data that is matched in CMDB WLC Extract

因此，基本上，我只需要在"for loop"下更改"not in"并更改将从我正在创建的CSV1文件中读取的数据列表中的列.

So basically, I only had to change 'not in' under 'for loop' and change the columns in the data list that will be reading from the CSV1 file that I am creating.

这篇关于比较两个csv文件之间的列并使用Python编写差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！