Compare two csv files and output changes

Question

I have two CSV files, both are different but similar. I would like to compare them and output changes and if a variable has been added or removed. I would like to output the changes in either a new CSV or text file.

Below is some code of what I have already tried as well as the two csv files. I would also be willing to use difflib and output that to a text file.

file1.csv:

name1,2.0001
name2,3.4010
name4,4.0000
name5,1.0000
name6,1.0000
name8,1.9001
name10,2.7654

file2.csv:

name1,3.0000
name2,3.4010
name3,1.0000
name5,1.0901
name6,1.0000
name7,3.4445
name11,8.0009
name12,0.1180

Here is code I have tried:

with open('file1.csv', 'r') as file1, open('file2.csv', 'r') as file2:
    file1 = file1.readlines()
    file2 = file2.readlines()

with open('new_file.csv', 'w') as outFile:
    for line in file2:
        if line not in file1:
            outFile.write(line)

Expected output would be either a csv file or text file that would show things like:

name1 value changed from 2.0001 to 3.0000
name3 value added
name4 value removed
name5 value changed from 1.0000 to 1.0901
name7 value added
name8 value removed
name10 value removed
name11 value added
name12 value added

looks like you should join the two files and analyze the results — mucio, Sep 03 '19 at 20:19
you could put the content of the files in two structures, maybe dict, and iterate on them to see if the value exists or not or is changed in the other file. Or you can use something like pandas (and its joins) to do this — mucio, Sep 03 '19 at 20:32
did you check this answer? https://stackoverflow.com/questions/5268929/compare-two-csv-files-and-search-for-similar-items — ppp, Sep 03 '19 at 20:49

score 1 · Accepted Answer · answered Sep 03 '19 at 21:14

My solution is to turn each csv into a dictionary with the first column as the keys and the second column as the values. After that, I can loop through the keys and determine if the corresponding values were changed, removed, or added.

import csv
import re


def csv2dict(filename):
    with open(filename) as file_handle:
        reader = csv.reader(file_handle)
        dict_object = dict(reader)
        return dict_object


def separate_text_and_number(value):
    text, number = re.match(r'(\D+)(\d+)', value).groups()
    number = int(number)
    return (text, number)


def main():
    """ Entry """
    csv1 = csv2dict('file1.csv')
    csv2 = csv2dict('file2.csv')
    all_keys = csv1.keys() | csv2.keys()

    for key in sorted(all_keys, key=separate_text_and_number):
        if key not in csv2:
            print(f'{key} value removed')
        elif key not in csv1:
            print(f'{key} value added')
        elif csv1[key] != csv2[key]:
            print(f'{key} value changed from {csv1[key]} to {csv2[key]}')


if __name__ == '__main__':
    main()

Output

name1 value changed from 2.0001 to 3.0000
name3 value added
name4 value removed
name5 value changed from 1.0000 to 1.0901
name7 value added
name8 value removed
name10 value removed
name11 value added
name12 value added

Notes

The function csv2dict opens a file and converts the contents into a dictionary
The function separate_text_and_number splits name14 into ('name', 14) to help with sorting the keys
In Python 3, the dict.keys() method returns a set-like object which contains all the keys. I use the | operator to find a union of two sets of keys.
For a more readable output, I sort the keys with the help of separate_text_and_number

I don't want to split up the names because I was just using that as an example. What I am actually dealing with is just different strings. What would I set key equal to if I didn't want to use `separate_text_and_number`? — BKoster, Sep 04 '19 at 17:46
In which case, just use `for key in sorted(all_keys):` and forget about the `key=...` part. If you don't care about sorting, then `for key in all_keys:` — Hai Vu, Sep 04 '19 at 21:09

score 0 · Answer 2 · answered Sep 03 '19 at 20:19

0

Use a file comparison tool, e.g. diff(1) under Unix/Linux.

answered Sep 03 '19 at 20:19

vonbrand

11,412
8
32
52

Adobe · Answer 3 · 2019-09-03T21:20:23.863

You want to compare two tables. The right tool for the job is relational database.

Your code snippets use Python. Python comes with sqlite3 database engine built-in, but I don't see a reason to use python for the simple processing task you request.

Instead, I'd do it in sqlite3 itself, wrapped in a shell script:

#!/bin/bash
# compare-CSVs.bash

sqlite3 <<EOF
.mode csv
.header on
.separator ',' "\n"

-- import data:
.import file1.csv file1
.import file2.csv file2

-- sadly sqlite does not support full joins, so we will augment left join with data missing from file1.csv:
create table data as
select
  file1.*
, file2.*
from (
  select
    name  as file1_name
  , value as file1_value
  from file1
) file1

left join (
  select
    name  as file2_name
  , value as file2_value
  from file2
) file2
on file2.file2_name == file1.file1_name

union all

select
  file1.*
, file2.*
from file2

left join file1
on file1.name == file2.name

where file1.name is null
;


-- output to stdout:
select
  file1_name || ' value removed' as "changes:"
from data

where file2_name is null

union all

select
  file2_name || ' value added'
from data

where file1_name is null

union all

select
  file1_name || ' value changed from ' || file1_value || ' to ' || file2_value
from data

where file1_value != file2_value
;

.exit
EOF

MWE:

cat > file1.csv <<EOF
name,value
name1,2.0001
name2,3.4010
name4,4.0000
name5,1.0000
name6,1.0000
name8,1.9001
name10,2.7654
EOF

cat > file2.csv <<EOF
name,value
name1,3.0000
name2,3.4010
name3,1.0000
name5,1.0901
name6,1.0000
name7,3.4445
name11,8.0009
name12,0.1180
EOF

./compare-CSVs.bash

Output:

changes:
"name4 value removed"
"name8 value removed"
"name10 value removed"
"name3 value added"
"name7 value added"
"name11 value added"
"name12 value added"
"name1 value changed from 2.0001 to 3.0000"
"name5 value changed from 1.0000 to 1.0901"

Compare two csv files and output changes

3 Answers3

Output

Notes