1

OK, I will try and explain what I need to do as best as possible. Basically I have two CSV files, as per the examples below:

File 1:

Column 1, Column 2
abc     , 123
def     , 234
adf     , 567

File 2

Column 1, Column 2
abc     , 123
def     , 234
adf     , 578

I need to write either a shell script or simple command that will do the following:

  1. Sort both files by column 1
  2. Row by row, do the following:
    • Using column 1 in file 1, search for this value in column 1 in file 2.
      1. if found, compare the value in column 2 in file 1 against the value in column 2 of file 2
      2. if it matches, write column 1, column 2 and "Validated" in column 3 to a separate file
      3. if it does not match, write column 1, column 2 and "Failed" to a separate file

This results in two output files: the first with everything that was found in column 1 and column 2 matches, and a second file containing either column 1 lookups that failed or where column 1 was found, where column 2 did not match, so, essentially, using column 1 as the key to check column 2.

2
  • Your question would be clearer if you (1) showed exactly what you want the output files to look like for your example input, and (2) described (and illustrated) what you want to happen if a value in column 1 of file 1 is not present in file 2. Also, have you made any attempt to solve this yourself? Done any research? What have you found? What have you tried? Mar 21, 2017 at 18:10
  • Hi, apologies yes I have been looking into this extensively. I first tried the below but this did not give the desired result so had to play around with the columns etc awk -F, 'NR==FNR{a[$1,$3]++;next} (a[$1,$2])' file1.txt file2.txt I have been also looking at whether I should be doing this in SQL but that means importing on eof the files into SQL and running innerjoins which with 150 million lines can take a while. I am still investigating and will obviously share anything I find that works on this post.
    – Veyron
    Mar 22, 2017 at 14:24

3 Answers 3

0

Given the following input files:

$ cat in1 in2
Column 1, Column 2
abc     , 123
def     , 234
adf     , 567
Column 1, Column 2
abc     , 123
def     , 234
adf     , 578

First, we sort them; we can then stitch them together into one file:

$ sort in1 > in1.sorted; sort in2 > in2.sorted; paste in{1,2}.sorted
Column 1, Column 2  Column 1, Column 2
abc     , 123   abc     , 123
adf     , 567   adf     , 578
def     , 234   def     , 234

awk will help us here, but the commas get in our way; we can get rid of them with sed first:

$ paste in{1,2}.sorted | sed s/,//g
Column 1 Column 2   Column 1 Column 2
abc      123    abc      123
adf      567    adf      578
def      234    def      234

And then we can dump that through a quick awk:

$ paste in{1,2}.sorted | sed s/,//g | awk '$2 == $4 {print $1,"Validated"}; $2 != $4 { print $1,"Failed"}'
Column Failed
abc Validated
adf Failed
def Validated

This can also be done with raw awk, with the advantage of being able to strip out the header rows and not rely on the same data being in the same order, thus removing the need for sorting:

$ awk 'FNR != 1 && NR == FNR {data[$1]=$3} FNR != 1 && NR != FNR {if(data[$1]==$3) {print $1, "Validated"} else {print $1, "Failed"} }' in{1,2}
abc Validated
adf Failed
def Validated

This relies on a few magic awk built-in variables and tricks related to them:

  • NR - the total number of records processed
  • FNR - the total number of records in the current file processed
  • FNR != 1 - skips the first row of each file (does not treat the headers as data)
  • NR != FNR - runs only after the first file has been completely read and we have started reading subsequent files. This allows us to prepopulate the data array for testing once we start chewing on the second file.
3
  • Here is a modified version as a one-liner: paste <(sort in1) <(sort in2) | tr -d , | awk '$2 == $4 {print $1,"Validated"}; $2 != $4 { print $1,"Failed"}'
    – hschou
    Mar 21, 2017 at 22:23
  • Thank you, I am working on the final lists to compare and will give this a try and will let you know the end results and if I need to tweak anything. Will update you on progress.
    – Veyron
    Mar 22, 2017 at 14:28
  • OK, so did a test with this. The first file is the complete source list of 15 million lines, the second file is a subset of what has been check, ~22,000 lines, end result is that only about 50 lines were validated. I am looking into another method and will post here shortly.
    – Veyron
    Mar 22, 2017 at 15:48
0

I think I have this sorted out now with the following, in case any else reads this and needs this. Thanks again.

FNR == NR {
    for (i = 2; i <= NF; i++) { a[i,$1] = $i }
    b[$1];
    next;
}
($1 in b) {                   # check if row in file2 existed in file1
    for (i = 2; i <= NF; i++) {
        if (a[i,$1] == $i)
            printf("%s->col%d: %s vs %s: Valid\n", $1, i-1, a[i,$1], $i);
        else
            printf("%s->col%d: %s vs %s: Failure\n", $1, i-1, a[i,$1], $i);
    }
    delete b[$1];   # delete entries which are processed
}

END {
    for (left in b) {   # look which didn't match
        for (i = 2; i <= NF; i++) 
            printf("%s->col%d: %s vs (blank): Not Equal\n", left, i-1, a[i,left])
    }
}
1
  • Further to my email, although I thought I had an answer, I have tested this with a couple of CSR files but it does not work. I have spent hours going through this but cannot seem to work out where the awk statement is wrong. Any help would be appreciated and thanks in advance.
    – Veyron
    Mar 27, 2017 at 4:28
0

Using Raku (formerly known as Perl_6)

#! /usr/bin/env raku
    
    #INPUT AND HEADERS:

    my $csv1 = "Veyron1.txt".IO;
    my $csv2 = "Veyron2.txt".IO;

    my $hdr1 = "Key,Value,Verified";
    my $hdr2 = "Key,Value,Failed";

    #HASH STORAGE (Below, beware of `skip`ping header in headerless file!):

    my %csv1; for $csv1.lines.skip.map( *.split(",").map( *.trim)) {
                  %csv1.push: .[0] => .[1]
              };
    my %csv2; for $csv2.lines.skip.map( *.split(",").map( *.trim)) {
                  %csv2.push: .[0] => .[1]
              };

    #SANITY CHECKS:

    die "multiple values per key in file_1" if any(%csv1.values.map: *.elems > 1).so; 
    die "multiple values per key in file_2" if any(%csv2.values.map: *.elems > 1).so;

    #OUTPUT FILE PREP W/ HEADER:

    !("Veyron_output_verified.txt".IO.e) && (my $fh1 = "Veyron_output_verified.txt".IO.open: :a); 
    !("Veyron_output_failed.txt".IO.e) && (my $fh2 = "Veyron_output_failed.txt".IO.open: :a); 

    $fh1.put: $hdr1;
    $fh2.put: $hdr2;

    #OUTPUT LOOP:

    for %csv1.keys.sort -> $id {
       if  %csv2{$id}:exists {
           if  %csv1{$id}  eq  %csv2{$id} {
               $fh1.put: ($id, %csv1{$id}, "verified").join: ",";
           }
           else {
               $fh2.put: ($id, %csv1{$id}, "mismatch").join: ",";
           }
       }
       else {
          $fh2.put: ($id, %csv1{$id}, "absent").join: ",";
       }
    }

    $fh1.close;
    $fh2.close;

Here's a script written in Raku, a programming language in the Perl-family. Like Perl, Raku has key/value data structures as well as various file-operators, which makes it ideal for a problem of this nature. Briefly:

  • At the top $-sigiled $csv1 and $csv2 denote file-handles (actually .IO objects). Because sigils remain invariant in Raku...
  • Two %-sigiled hashes $csv1 and $csv2 denote the storage location of key/values obtained from each file, respectively.
  • Trimming whitespace, header-manipulation, and sanity checks add niceties for running the code.
  • File operators like .e for "exists" makes sure a pre-existing output file is not over-written. File-handles are opened with :a option, standing for :append.
  • In the output loop, keys from %csv1 are looked up in %csv2, to check for string-equivalence (with eq) of cognate values. One of three strings is returned: "verified", "mismatch", or "absent".
  • Output is appended linewise to the open output file-handles, which are closed at the end of the script.

Note: if you want to check for numeric-equivalence of values per key, replace:

%csv1{$id} eq %csv2{$id} with: %csv1{$id} == %csv2{$id}


Sample Output ("verified"):

Key,Value,Verified
abc,123,verified
def,234,verified

Sample Output ("failed"):

Key,Value,Failed
adf,567,mismatch

Raku references:
https://docs.raku.org
https://raku.org

Related Perl script:
https://www.perlmonks.org/?node_id=805106

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .