Python读取循环的唯一CSV值

Question

我有3个CSV正在生成，我想合并成一个。我只需要从每个文件的某些列，但我需要他们匹配的开关号码和接口

文件1

switch1,Gi1/0/22,connected,716,a-full,a-100,10/100/1000BaseTX
switch2,Fa3/0/8,connected,716,a-full,a-100,10/100BaseTX
switch3,Fa2/0/5,connected,716,a-full,a-100,10/100BaseTX

文件2

switch1,716,0040.0020.0010,DYNAMIC,Gi1/0/22
switch2,716,0030.0020.1010,DYNAMIC,Fa3/0/8
switch3,716,0050.0030.1010,DYNAMIC,Fa2/0/5

文件3

switch1,Gi1/0/22,0,32,0,33,0,9
switch2,Fa3/0/8,0,0,0,0,0,362
switch3,Fa2/0/5,0,10,20,0,0,100

我想让最后的csv看起来像这样：

switch1,Gi1/0/22,0040.0020.0010,0,32,0,33,0,9
switch2,Fa3/0/8,0030.0020.1010,0,0,0,0,0,362
switch3,Fa2/0/5,0050.0030.1010,0,10,20,0,0,100

它是开关名称、接口、File2的第3列和File3的第3-8列

如果你不想给出一个确切的答案，就不要去寻找，而应该是一个大致的想法/方向。对python还是很陌生的。你知道吗

Answer 1

你可以用熊猫或者标准图书馆来做。一般来说，熊猫的阅读速度更快，也更容易阅读。你知道吗

设置：

from textwrap import dedent

def write_file(name, string):
    with open(name, 'w') as f:
        f.write(dedent(string).lstrip())

write_file('File1.csv', """
    switch1,Gi1/0/22,connected,716,a-full,a-100,10/100/1000BaseTX
    switch2,Fa3/0/8,connected,716,a-full,a-100,10/100BaseTX
    switch3,Fa2/0/5,connected,716,a-full,a-100,10/100BaseTX
""")

write_file('File2.csv', """
    switch1,716,0040.0020.0010,DYNAMIC,Gi1/0/22
    switch2,716,0030.0020.1010,DYNAMIC,Fa3/0/8
    switch3,716,0050.0030.1010,DYNAMIC,Fa2/0/5
""")

write_file('File3.csv', """
    switch1,Gi1/0/22,0,32,0,33,0,9
    switch2,Fa3/0/8,0,0,0,0,0,362
    switch3,Fa2/0/5,0,10,20,0,0,100
""")

熊猫代码：

import pandas as pd

t1 = pd.read_csv('File1.csv', names=['switch_name', 'interface', 'col3', 'col4', 'col5', 'col6', 'col7'])
t2 = pd.read_csv('File2.csv', names=['switch_name', 'col2', 'col3', 'col4', 'interface'])
t3 = pd.read_csv('File3.csv', names=['switch_name', 'interface', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8'])

result = t2[['switch_name', 'interface', 'col3']].merge(t3, on=['switch_name', 'interface'])
result.to_csv('Final.csv', header=False, index=False)

with open('Final.csv') as f:
    print f.read()

# switch1,Gi1/0/22,0040.0020.0010,0,32,0,33,0,9
# switch2,Fa3/0/8,0030.0020.1010,0,0,0,0,0,362
# switch3,Fa2/0/5,0050.0030.1010,0,10,20,0,0,100

标准库代码：

import csv

# store data in a dictionary for later reference
with open('File3.csv') as f:
    f3_data = {(r[0], r[1]): r[2:8] for r in csv.reader(f)}

with open('File2.csv') as f2, open('Final.csv', 'w') as f:
    final = csv.writer(f)
    for switch_name, col2, col3, col4, interface in csv.reader(f2):
        if (switch_name, interface) in f3_data:
            final.writerow([switch_name, interface, col3] + f3_data[switch_name, interface])

with open('Final.csv') as f:
    print f.read()

# switch1,Gi1/0/22,0040.0020.0010,0,32,0,33,0,9
# switch2,Fa3/0/8,0030.0020.1010,0,0,0,0,0,362
# switch3,Fa2/0/5,0050.0030.1010,0,10,20,0,0,100

Answer 2

您可以首先一次打开3个文件，用csv库将它们读入嵌套的行列表，然后提取所需的列并将它们写入文件：

from csv import reader

# open all files at once
with open('file1.csv') as f1, \
     open('file2.csv') as f2, \
     open('file3.csv') as f3:

     # convert them to reader objects
     csv_files = reader(f1), reader(f2), reader(f3)

     # open file to write to
     with open('combined.csv', 'w') as out:

         # go over each row from the files at once using zip()
         for row1, row2, row3 in zip(*csv_files):

             # extract columns into a list
             line = row1[:2] +[row2[2]] +  row3[3:]

             # write to the file
             out.write(','.join(line) +'\n')

# print contents of new file
print(open('combined.csv').read())

输出：

switch1,Gi1/0/22,0040.0020.0010,0,32,0,33,0,9
switch2,Fa3/0/8,0030.0020.1010,0,0,0,0,0,362
switch3,Fa2/0/5,0050.0030.1010,0,10,20,0,0,100