diff two large CSV files (each 90GB) and output to another csv

Question

This is a follow up question from Sort large CSV files (90GB), Disk quota exceeded. So now I have two CSV files sorted, as file1.csv and file2.csv each CSV file has 4 columns, e.g.

file 1:

ID Date Feature Value
01 0501 PRCP    150
01 0502 PRCP    120
02 0501 ARMS    5.6
02 0502 ARMS    5.6

file 2:

ID Date Feature Value
01 0501 PRCP    170
01 0502 PRCP    120
02 0501 ARMS    5.6
02 0502 ARMS    5.6

Ideally, I want to diff the two files in such a way that if two rows in the two files have the same ID, Date and Feature, but different values, then output something like:

ID Date Feature Value1 Value2

Of course, this might be asking too much. Something like

ID1 Date1 Feature1 Value1       ID2 Date2 Feature2 Value2

also works. In the above example, I would like to output

01 0501 PRCP    150 170

or

01 0501 PRCP    150 01 0501 PRCP    150

I think the main question is how to compare in such a way and how to output to a csv file. Thanks.

Sample output from Gilles answer: The output from comm is

$ head -20 comm_output.txt ACW00011604,19490101,PRCP,0 AE000041196,20070402,TAVG,239 AE000041196,20070402,TAVG,244 AE000041196,20080817,TMIN,282 AE000041196,20130909,TAVG,350 AE000041196,20130909,TMAX,438 AE000041196,20130909,TMIN,294 AE000041196,20130910,TAVG,339 AE000041196,20130910,TAVG,341 AE000041196,20150910,TAVG,344 The output of awk is

$ head awk_output.csv , ACW00011604,19490101,PRCP,0,,, AE000041196,20070402,TAVG,239,,, AE000041196,20070402,TAVG,244,,, AE000041196,20080817,TMIN,282,,, AE000041196,20130909,TAVG,350,,, AE000041196,20130909,TMAX,438,,, AE000041196,20130909,TMIN,294,,, AE000041196,20130910,TAVG,339,,, AE000041196,20130910,TAVG,341,,, AE000041196,20150910,TAVG,344,,, Here is the sample input, if you insist

head file1.csv

ACW00011604,19490101,PRCP,0 ACW00011604,19490101,SNOW,0 ACW00011604,19490101,SNWD,0 ACW00011604,19490101,TMAX,289 ACW00011604,19490101,TMIN,217 ACW00011604,19490102,PRCP,30 ACW00011604,19490102,SNOW,0 ACW00011604,19490102,SNWD,0 ACW00011604,19490102,TMAX,289 ACW00011604,19490102,TMIN,228

 head file2.csv

ACW00011604,19490101,SNOW,0 ACW00011604,19490101,SNWD,0 ACW00011604,19490101,TMAX,289 ACW00011604,19490101,TMIN,217 ACW00011604,19490102,PRCP,30 ACW00011604,19490102,SNOW,0 ACW00011604,19490102,SNWD,0 ACW00011604,19490102,TMAX,289 ACW00011604,19490102,TMIN,228 ACW00011604,19490102,WT16,1

Answer 1

Let's review tools that combine two files together line by line in some way:

• paste combines two files line by line, without paying attention to the contents.
• comm combines sorted files, paying attention to identical lines. This can weed out identical lines, but subsequently combining the differing line would require a different tool.
• join combines sorted files, matching identical fields together.
• sort can merge two files.
• awk can combine multiple files according to whatever rules you give it. But with such large files, you're likely to get best performance by using the most appropriate special-purpose tools than with generalist tools.

I'll assume that there are no duplicates, i.e. within one files there are no two lines with the same ID, date and feature. If there are duplicates then how to cope with them depends on how you want to treat them. I also assume that the files are sorted. I also assume that your shell has process substitution, e.g. bash or ksh rather than plain sh, and that you have GNU coreutils (which is the case on non-embedded Linux and Cygwin).

I don't know if your separators are whitespace or tabs. I'll assume whitespace; if the separator is always exactly one tab then declaring tab as the separator character (cut -d $'\t', join -t $'\t', sort -t $'\t') and using \t instead of [ \t]\+ should squeeze a tiny bit of performance.

Set the locale to pure ASCII (LC_ALL=C) to avoid any performance loss related to multibyte characters.

Since join can only combine rows based on one field, we need to arrange for fields 1–3 to appear as a single field. To do that, change the separator, either between 1 and 2 and 2 and 3 or between 3 and 4. I'll change 1–3 to use ; instead of whitespace. That way you get all the line combinations, whether they're identical or not. You can then use sed to remove lines with identical values.

join -a 1 -a 2 <(sed 's/[ \t]\+/;/; s/[ \t]\+/;/' file1.csv) <(sed 's/[ \t]\+/;/; s/[ \t]\+/;/' file2.csv) |
sed '/[ \t]\(.*\)[ \t]\+\1$/d' |
tr ';' '\t'

Note that unpairable lines end up as a 4-column line with no indication as to whether they came from file 1 or file 2. Remove -a 1 -a 2 to suppress all unpairable lines.

If you have a majority of identical lines, this wastes time joining them and weeding them out. Another approach would be to use comm -3 to weed out the identical lines. This produces a single output stream where the lines are in order, but lines from file 2 have a leading tab. You can then use awk to combine consecutive lines where the two files have the same fields 1–3. Since this involves awk, it might well end up being slower if there are a lot of non-identical lines.

comm -3 file1.csv file2.csv |
awk '
    $1 "\t" $2 "\t" $3 == k { if ($4 != v) print k "\t" v "\t" $4; next; }
    { print k "\t" v }
    { k=$1 "\t" $2 "\t" $3; v=$4; }
'

Answer 2

I have found a way to quickly compare 2 files with 1 Million rows. My need was that the two files have to be equal. The diff command was slow but to make it faster just sort the files before comparing them.

So basically:

sort file01.txt > file01_sorted.txt
sort file02.txt > file02_sorted.txt

Then run the diff command:

diff file01_sorted.txt file02_sorted.txt

Or you can also do a md5sum on the sorted files

md5sum file01_sorted.txt
md5sum file02_sorted.txt

Answer 3

EDIT: This answer might work OK for anyone with around 200GB free RAM on their system. Oops.

---

diff --side-by-side --suppress-common-lines file1.csv file2.csv

defaults to separating with |, and 130-character wide lines (wrapping if needed). I guess that's not the same as your input format.

You could try something like

diff --old-line-format="%l$(printf '\t')" --new-line-format="%L" --unchanged-line-format="" file1.csv file2.csv