3

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

7
  • Can you provide an excerpted example of the two input files and the desired output?
    – DopeGhoti
    Mar 10, 2017 at 20:57
  • I think I've explained it well enough, but let me do that.
    – ChubbyRuby
    Mar 10, 2017 at 21:00
  • @ DopeGhoti Example added.
    – ChubbyRuby
    Mar 10, 2017 at 21:05
  • Is (ID,Date,Feature) unique in each file, and each triplet exists in both files? If not, how should duplicates and added/removed lines be handled?
    – derobert
    Mar 10, 2017 at 21:08
  • paste file1 file2 might work.
    – DopeGhoti
    Mar 10, 2017 at 21:24

3 Answers 3

3

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; }
'
10
  • Thanks a lot. I believe the comm & awk approach would work better, since the majority of the lines would be identical. To answer some of your questions, the fields are separated by commas, and yes there are no duplicates between rows. I also have a follow up question. Is this method memory intensive? I have a standard desktop with 32GB memory.
    – ChubbyRuby
    Mar 11, 2017 at 18:04
  • and what would the output be like after awk? Like that ID Date Feature Value1 Value2?
    – ChubbyRuby
    Mar 11, 2017 at 19:04
  • @XuezhouZhang All the methods I propose here handle store only a few lines in memory at a time. Once the files are sorted, there isn't much to be gained by using additional memory. Yes, the awk script outputs “ID Date Feature Value1 Value2” for paired lines and “ID Date Feature Value” for unpaired lines. Mar 12, 2017 at 0:14
  • @ Gilles, that's great, thanks. I tried what you said, but the output is bit confusing. I got something like ID,Date,Feature,Value, , , in a lot of the rows. The command I used is comm -3 file1.csv file2.csv | awk ' $1 "," $2 "," $3 == k { if ($4 != v) print k "," v "," $4; next; }{ print k "," v }{ k=$1 "," $2 "," $3; v=$4; }' > output.txt `
    – ChubbyRuby
    Mar 12, 2017 at 22:49
  • @XuezhouZhang I don't see anything obviously wrong. Please post some sample data that exhibits the problem. Do check that the files have Unix line endings (i.e. no carriage returns). Mar 12, 2017 at 23:34
1

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
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 15, 2021 at 14:58
0

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
5
  • 1
    (Serious question, I don't know the answer): How well does diff handle 90GB input files?
    – derobert
    Mar 10, 2017 at 21:15
  • Differentially? (:
    – DopeGhoti
    Mar 10, 2017 at 21:24
  • wups, it doesn't (needs RAM).
    – sourcejedi
    Mar 10, 2017 at 21:33
  • I don't have a 200GB RAM but I do have a 500GB SSD. Does that work?
    – ChubbyRuby
    Mar 10, 2017 at 22:46
  • make a 200G file with dd if=/dev/zero bs=1M count=200000 of=/swapfile. mkswap the file. swapon the file. it'll be slow, but it'll work.
    – DopeGhoti
    Mar 10, 2017 at 23:08

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.