2

I want to compare two CSV files with the following format. They not have headers. I want to compare them by a specific column (in this case, the 2nd one).

The source CSV files around 4-5GB, so loading them into memory is won't work.

If there's no matching column in old.csv, than every new line written into out.csv.

This 2nd column will be a html link, for the shake of simplicity, here one word only.

My question is it possible to achieve the same result with sed, awk, join, or grep?

old.csv

"person"|"john"|"smith"
"person"|"anne"|"frank"
"person"|"bob"|"macdonald"
"fruit"|"orange"|"banana"
"fruit"|"strawberry"|"fields"
"fruit"|"ringring"|"banana"

new.csv

"person"|"john"|"smith"
"person"|"anne"|"frank"
"person"|"bob"|"macdonald"
"fruit"|"orange"|"banana"
"fruit"|"strawberry"|"fields"
"glider"|"person"|"airport"
"fruit"|"ringring"|"banana"
"glider"|"person2"|"airport"

diff.py

#!/usr/bin/env python3

"""
Source: https://gist.github.com/davidrleonard/4dbeebf749248a956e44
Usage: $ ./csv-difference.py -d new.csv -s old.csv -o out.csv -c 1
"""

import sys
import argparse
import csv

def main():
    parser = argparse.ArgumentParser(description='Output difference in CSVs.')
    parser.add_argument('-d', '--dataset', help='A CSV file of the full dataset', required=True)
    parser.add_argument('-s', '--subset', help='A CSV file that is a subset of the full dataset', required=True)
    parser.add_argument('-o', '--output', help='The CSV file we should write to (will be overwritten if it exists', required=True)
    parser.add_argument('-c', '--column', help='A number of the column to be compared (0 is column 1, 1 is column 2, etc.)', required=True, type=int)

    args = parser.parse_args()
    dataset_file = args.dataset
    subset_file = args.subset
    output_file = args.output
    column_num = args.column

    with open(dataset_file, 'r') as datafile, open(subset_file, 'r') as subsetfile, open(output_file, 'w') as outputfile:
        data = {row[column_num]: row for row in csv.reader(datafile, delimiter='|', quotechar='"')}
        subset = {row[column_num]: row for row in csv.reader(subsetfile, delimiter='|', quotechar='"')}

        data_keys = set(data.keys())
        subset_keys = set(subset.keys())
        output_keys = data_keys - subset_keys

        output = [data[key] for key in output_keys]
        output_csv = csv.writer(outputfile, delimiter='|', quotechar='"', quoting=csv.QUOTE_ALL)
        for row in output:
            output_csv.writerow(row)

if __name__ == '__main__':
    main()

sys.stdout.flush()

Which is generating out.csv

"glider"|"person"|"airport"
"glider"|"person2"|"airport"
  • One a separate note, your code is somewhat overwrought output_keys should simply be data.keys() - subset.keys(). Or, even better, output = {v for k, v in data.items() if k not in subset} – iruvar Nov 4 '16 at 22:20
3

Super simple with awk:

$ awk -F'|' 'NR == FNR {old[$2]; next} !($2 in old)' old.csv new.csv
"glider"|"person"|"airport"
"glider"|"person2"|"airport"

That stores the 2nd field of the old.csv file in the array named "old", and then for the new.csv file, it will print records where the 2nd field is not in the "old" array.

It is true that this will not respect any pipe character within quotes. For that, I like ruby's csv module:

ruby -rcsv  -e '
  old_col2 = []
  old_data = CSV.foreach("./old.csv", :col_sep => "|") do |row|
    old_col2 << row[1]
  end

  CSV.foreach("./new.csv", :col_sep => "|") do |row|
    if not old_col2.include?(row[1])
      puts CSV.generate_line(row, :col_sep => "|", :force_quotes => true)
    end
  end
'
|improve this answer|||||
  • You say that awk thinks the pipes are within quotes when they actually doesn't? What problems it can cause? – Lanti Nov 4 '16 at 19:36
  • Imagine you have a record in your file like "hello"|"foo|bar|baz"|"world" -- awk will think that line has 5 fields; a proper CSV parser will see 3 fields. – glenn jackman Nov 4 '16 at 19:37
  • Luckily the CSV sources formatted in a fashion that this will not happen (although bad formatting errors always can happen). So every column is inside double quotes. What I'm courious about is that the columns inside "" have html embed codes with lots of "". After the first run I will know it will work or not. – Lanti Nov 4 '16 at 19:41
  • 1
    The nice thing about awk is that your data is just data: only the field separator is special. awk won't care about quotes. – glenn jackman Nov 4 '16 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.