CODEWITHSUNDEEP
pythoncsv
user1042891
user1042891

Reputation: 43

How to combine 2 csv files with common column value, but both files have different number of lines

file1.csv contains 2 columns: c11;c12
file2.csv contains 2 columns: c21;c22
Common column: c11, c21

Example:

f1.csv 

a;text_a            
b;text_b            
f;text_f            
x;text_x

f2.csv

a;path_a
c;path_c
d;path_d
k;path_k
l;path_l
m:path_m

Output f1+f2:

a;text_a;path_a
b;text_b,''
c;'';path_c
d;'';path_d
f;text_f;''
k;'';path_k
l;'';path_l
m;'';path_m
x;text_x;''

How to realize it using python?

Upvotes: 1

Views: 9322

Answers (2)

Felix Francis
Felix Francis

Reputation: 141

For merging multiple files (even > 2) based on one or more common columns, one of the best and efficient approaches in python would be to use "brewery". You could even specify what fields need to be considered for merging and what fields need to be saved.

import brewery
from brewery
import ds
import sys

sources = [
    {"file": "grants_2008.csv",
     "fields": ["receiver", "amount", "date"]},
    {"file": "grants_2009.csv",
     "fields": ["id", "receiver", "amount", "contract_number", "date"]},
    {"file": "grants_2010.csv",
     "fields": ["receiver", "subject", "requested_amount", "amount", "date"]}
]

Create list of all fields and add filename to store information about origin of data records.Go through source definitions and collect the fields:

for source in sources:
    for field in source["fields"]:
        if field not in all_fields:

out = ds.CSVDataTarget("merged.csv")
out.fields = brewery.FieldList(all_fields)
out.initialize()

for source in sources:

    path = source["file"]

# Initialize data source: skip reading of headers
# use XLSDataSource for XLS files
# We ignore the fields in the header, because we have set-up fields
# previously. We need to skip the header row.

    src = ds.CSVDataSource(path,read_header=False,skip_rows=1)

    src.fields = ds.FieldList(source["fields"])

    src.initialize()


    for record in src.records():

   # Add file reference into ouput - to know where the row comes from
    record["file"] = path

        out.append(record)

# Close the source stream

    src.finalize()


cat merged.csv | brewery pipe pretty_printer

Upvotes: 1

BrtH
BrtH

Reputation: 2664

This is quite easily done with the csv module:

import csv

with open('file1.csv') as f:
    r = csv.reader(f, delimiter=';')
    dict1 = {row[0]: row[1] for row in r}

with open('file2.csv') as f:
    r = csv.reader(f, delimiter=';')
    dict2 = {row[0]: row[1] for row in r}

keys = set(dict1.keys() + dict2.keys())
with open('output.csv', 'wb') as f:
    w = csv.writer(f, delimiter=';')
    w.writerows([[key, dict1.get(key, "''"), dict2.get(key, "''")]
                 for key in keys])

Upvotes: 4

Related Questions