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

  • Can you provide an excerpted example of the two input files and the desired output? – DopeGhoti Mar 10 '17 at 20:57
  • I think I've explained it well enough, but let me do that. – Xuezhou Zhang Mar 10 '17 at 21:00
  • @ DopeGhoti Example added. – Xuezhou Zhang Mar 10 '17 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 '17 at 21:08
  • paste file1 file2 might work. – DopeGhoti Mar 10 '17 at 21:24
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; }
'
  • 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. – Xuezhou Zhang Mar 11 '17 at 18:04
  • and what would the output be like after awk? Like that ID Date Feature Value1 Value2? – Xuezhou Zhang Mar 11 '17 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. – Gilles Mar 12 '17 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 ` – Xuezhou Zhang Mar 12 '17 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). – Gilles Mar 12 '17 at 23:34
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
  • (Serious question, I don't know the answer): How well does diff handle 90GB input files? – derobert Mar 10 '17 at 21:15
  • Differentially? (: – DopeGhoti Mar 10 '17 at 21:24
  • wups, it doesn't (needs RAM). – sourcejedi Mar 10 '17 at 21:33
  • I don't have a 200GB RAM but I do have a 500GB SSD. Does that work? – Xuezhou Zhang Mar 10 '17 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 '17 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.