How to merge two CSV files by Python based on the common information in both files?
Question: Evans, Great thanks. It is almost the expected result. Please kindly take a
hand to modify as follows. We need a little change. Please take a look at the
image. In the present result, each record in fileOne search the similar adv_id
and user_id in the fileTwo and when find a record take it and stop. But
possibility is that there may be several similar records in fileTwo. So, we
need all the similar records from fileTwo. And all records of fileOne must be
available at least once or several times in fileTwo. So, we should include all
record of fileOne and all of their similar records from fileTwo. I think a row
by row search might be helpful. That is take adv_id and user_id of first of
fileOne and search all records in fileTwo to find the similar record. Next use
2nd record of fileOne and search all record in fileTwo. And so on.

[Revised Image For Expected Result](http://i.stack.imgur.com/Zv79G.png)


Answer: The following script will create `result.csv` based on your original sample
data (see past edits to question):

    
    
    import csv
    from collections import defaultdict
    
    d_entries = defaultdict(list)
    
    with open('fileTwo.csv', 'r') as f_fileTwo:
        csv_fileTwo = csv.reader(f_fileTwo)
        header_fileTwo = next(csv_fileTwo)
        for cols in csv_fileTwo:
            d_entries[(cols[0], cols[1])].append([cols[0], ''] + cols[1:])
    
    with open('fileOne.csv', 'r') as f_fileOne, open('result.csv', 'w', newline='') as f_result:
        csv_fileOne = csv.reader(f_fileOne)
        csv_result = csv.writer(f_result)
        header_fileOne = next(csv_fileOne)
        csv_result.writerow(header_fileOne)
    
        for cols in csv_fileOne:
            if (cols[0], cols[2]) in d_entries:
                csv_result.writerow(cols)
                csv_result.writerows(d_entries.pop((cols[0], cols[2])))
    

`result.csv` will then contain the following data when opened in Excel:

[![enter image description
here](http://i.stack.imgur.com/BtRKU.png)](http://i.stack.imgur.com/BtRKU.png)

Tested in Python 3.4.3

To only match on the `adv_id` columns and to have all entries:

    
    
    import csv
    from collections import defaultdict
    
    d_entries = defaultdict(list)
    
    with open('fileTwo.csv', 'r') as f_fileTwo:
        csv_fileTwo = csv.reader(f_fileTwo)
        header_fileTwo = next(csv_fileTwo)
        for cols in csv_fileTwo:
            d_entries[cols[0]].append([cols[0], ''] + cols[1:])
    
    with open('fileOne.csv', 'r') as f_fileOne, open('result.csv', 'w', newline='') as f_result:
        csv_fileOne = csv.reader(f_fileOne)
        csv_result = csv.writer(f_result)
        header_fileOne = next(csv_fileOne)
        csv_result.writerow(header_fileOne)
    
        for cols in csv_fileOne:
            if cols[0] in d_entries:
                csv_result.writerows(d_entries.pop(cols[0]))
            csv_result.writerow(cols)
    

