Compare 2 .csv files with Python then output results
Question: I'm fairly new at programming and I am trying to write a python program that
will compare 2 .csv files by specific columns and check for additions,
removals, and modifications. The .csv files are both in the following format,
contain the same amount of columns, and use BillingNumber as the key:

    
    
    BillingNumber,CustomerName,IsActive,IsCreditHold,IsPayScan,City,State
    "2","CHARLIE RYAN","Yes","No","Yes","Reading","PA"
    "3","INSURANCE BILLS","","","","",""
    "4","AAA","","","","",""
    

I need to compare only columns 0, 1, 2, and 4. I have tried many different
ways to accomplish this but I haven't had any luck. I understand that I can
load them into dictionaries using `csv.DictReader` or `csv.reader`, but after
that I get stuck. I'm not sure exactly where or how to start after loading
them into memory.

I tried this previously:

    
    
    import time
    old_lines = set((line.strip() for line in open(r'Old/file1.csv', 'r+')))
    file_new = open(r'New/file2.csv', 'r+')
    
    choice = 0
    choice = int( input('\nPlease choose your result format.\nEnter 1 for .txt, 2 for .csv or 3 for .json\n') )
    time.sleep(1)
    print(".")
    time.sleep(1)
    print("..")
    time.sleep(1)
    print("...")
    time.sleep(1)
    print("....")
    time.sleep(1)
    print('Done! Check "Different" folder for results.\n')
    if choice == 1:
        file_diff = open(r'Different/diff.txt', 'w')
    elif choice == 2:
        file_diff = open(r'Different/diff.csv', 'w')
    elif choice == 3:
        file_diff = open(r'Different/diff.json', "w")
    else: 
        print ("You MUST enter 1, 2 or 3")
        exit()
    
    for line in file_new:
        if line.strip() not in old_lines:
            file_diff.write("** ERROR! Entry "+ line + "** Does not match previous file\n\n")
    file_new.close()
    file_diff.close()
    

It doesn't work properly because if there is an additional line, or one is
missing, it logs everything after that line as different. Also it compares the
whole line which is not what I want to do. This was basically just a starting
point and although it kind of worked, it isn't specific enough for what I
need. I'm really just looking for a good place to start. Thanks!


Answer: I think you were on the right track using the csv module. Since
'BillingNumber' is a unique key, I would create one dict for the "old" billing
file, and another for the "new" billing file:

    
    
    import csv
    
    def make_billing_dict(csv_dict_reader):
        bdict = {}
        for entry in csv_dict_reader:
            key = entry['BillingNumber']
            bdict[key] = entry
        return bdict
    
    with open('old.csv') as csv_file:
        old = csv.DictReader(csv_file)
        old_bills = make_billing_dict(old)
    

That results in this data structure for `old_bills`:

    
    
    {'2': {'BillingNumber': '2',
           'City': 'Reading',
           'CustomerName': 'CHARLIE RYAN',
           'IsActive': 'Yes',
           'IsCreditHold': 'No',
           'IsPayScan': 'Yes',
           'State': 'PA'},
     '3': {'BillingNumber': '3',
           'City': '',
           'CustomerName': 'INSURANCE BILLS',
           'IsActive': '',
           'IsCreditHold': '',
           'IsPayScan': '',
           'State': ''},
     '4': {'BillingNumber': '4',
           'City': '',
           'CustomerName': 'AAA',
           'IsActive': '',
           'IsCreditHold': '',
           'IsPayScan': '',
           'State': ''}}
    

Once you create the same data structure for the "new" billing file, you can
easily find the differences:

    
    
    # Keys that are in old_bills, but not new_bills
    print set(old_bills.keys()) - set(new_bills.keys())
    
    # Keys that are in new_bills, but not old_bills
    print set(new_bills.keys()) - set(old_bills.keys())
    
    # Compare columns for same billing records
    # Will print True or False
    print old_bills['2']['CustomerName'] == new_bills['2']['CustomerName']
    print old_bills['2']['IsActive'] == new_bills['2']['IsActive']
    

Obviously, you wouldn't write a separate print statement for each comparison.
I'm just demonstrating how to use the data structures to find differences.
Next, you should write a function to loop through all possible BillingNumbers
and check for differences between old and new...but I'll leave that part for
you.

