Merge two csv files that have different number of columns and column names

Question

I have two csv files with different numbers of columns. I want to merge these csv files by the first column (they have different column names).

csv 1:

ID, name, A1, A2, A3
1101,台泥,29.19,2998730.0,0.23
1102,亞泥,36.06,933069.0,0.08
1103,嘉泥,23.29,132555.0,-0.17
1104,環球水泥,25.64,139041.0,0.45
1108,幸福水泥,11.8,80854.0,0.05

csv2:

NO, no_name, D1, D2, D3, D4
01003T,兆豐新光R1,14.08,14.08,13.95,14.00
01004T,土銀富邦R2,13.39,13.40,13.30,13.34
01007T,兆豐國泰R2,14.76,14.76,14.76,14.76
1101,台泥,35.45,35.45,34.80,35.15
1102,亞泥,26.45,26.50,26.30,26.50
1103,嘉泥,8.71,8.71,8.61,8.71

If the csv2 NO = csv1 ID, append D1, D2, D3, D4 to csv1, the new csv is like this:

ID, name, A1, A2, A3, D1, D2, D3, D4
1101,台泥,29.19,2998730.0,0.23,35.45,35.45,34.80,35.15
1102,亞泥,36.06,933069.0,0.08,26.45,26.50,26.30,26.50
1103,嘉泥,23.29,132555.0,-0.17,8.71,8.71,8.61,8.71

My thought is: readlines for the two csv file, than use double loop find out the same value of first column, than combine the first csv file and certain columns in the second csv file, than create a merge csv file, below is the code:

`import csv

# readlines for the two csv file
inf = open('csv1.csv', "r").readlines()
inf1 = open('csv2.csv', "r").readlines()

data = {}

# use double loop find out the same value of first column
# combine first csv file and certain columns in second csv file
for line in inf:
    part = line.split(',')
    a = part[0]
    for line1 in inf1:
        part1 = line1.split(',')
        b = part1[0]
        if a == b in data:    
            row = line + ',' + part1[2] + ',' + part1[3] + ',' + part1[4] + ',' + part1[5]    

# create a merge csv file        
with open('m_data.csv', 'w', newline = '') as f_out:
    fieldnames = ['ID', 'name', 'A1', 'A2', 'A3', 'D1', 'D2', 'D3', 'D4']
    writer = csv.DictWriter(f_out, fieldnames = fieldnames)

    writer.writeheader()

for c1, c2, c3, c4, c5, c6, c7, c8, c9 in data.items():
    writer.writerow({'ID': c1, 'name': c2, 'A1': c3, 'A2': c4, 'A3': c5,'D1': c6, 'D2': c7, 'D3': c8, 'D4':c9})

f_out.close()`

I think probably the problem is between "combine the first csv file and certain columns in the second csv file" and "create a merge csv file", but I don't know how to fix it, or is there another way?

Very much thanks for any help!

Please add a few lines of sample input data from each file. for reference see how to post a minimal reproducible example. — Mark Tolonen

skrx skrx · Accepted Answer · 2017-10-05T15:19:36

This solution should work as expected:

import csv
from collections import OrderedDict


data = OrderedDict()

with open('temp.csv', 'r', encoding='utf-8', newline='') as infile1:
    reader1 = csv.reader(infile1)
    next(reader1, None)  # Skip header.
    for row in reader1:
        data[row[0]] = row  # id as the key and row as the value.

file2_NOs = set()  # A set to store the NO fields of file2.

with open('temp2.csv', 'r', encoding='utf-8', newline='') as infile2:
    reader2 = csv.reader(infile2)
    next(reader2, None)  # Skip header.
    for row in reader2:
        file2_NOs.add(row[0])  # Add the NO fields to this set.
        if row[0] in data:  # If the NO/id exists in file1...
            data[row[0]].extend(row[2:])  # ... extend the rows.

with open('temp3.csv', 'w', encoding='utf-8', newline='') as outfile:
    writer = csv.writer(outfile)
    writer.writerow(['ID', 'name', 'A1', 'A2', 'A3', 'D1', 'D2', 'D3', 'D4'])
    for row in data.values():
        if row[0] in file2_NOs:  # Write only if the number/id exists in file 2.
            writer.writerow(row)

I first add the rows of file1 to an OrderedDict with the ids as the keys and the rows as the values. Then I iterate over the second file and if the NO exists as a key in the dict, I extend the row. I also added a set (file2_NOs) to which I add the NOs, so that I can check if the ids are also valid NOs and then just write the rows if the id and NO exist in both files.

Merge two csv files that have different number of columns and column names

1 Answers