How can I compare two files by using just two columns and print the difference (without sorting)?

Question

I have two files:

File 1:

No  ID  CV  CA1 CA2
1   transcr_10283   0.999023367236861   -0.344113101336184  -0.032235130455987
2   transcr_10371   -0.572755303094372  -0.579145581184253  0.879510598089221
3   transcr_10391   0.999589933675858   -0.379226454955611  -0.302057879326854
4   transcr_10428   0.128862262957329   0.579502720160717   -0.960283285879896
5   transcr_10673   -0.555906836336222  0.996418809959179   0.83927901939441
6   transcr_10719   -0.977601905205625  -0.297994976855801  -0.988480730161833
7   transcr_10805   -0.994387636575223  -0.924363947763111  -0.096820331033279
8   transcr_1084    0.929966893591254   0.994040100421911   0.604483398826667
9   transcr_10892   0.987734223438821   0.822187392097743   0.968727545498998
10  transcr_10892   0.999938729100654   -0.985209499864003  0.958993756142276

File 2:

No  ID  CV  CA1 CA2
1   transcr_8921    0.972442945255909   0.937065785923838   0.999643394568925
2   transcr_10428   0.128862262957329   0.808685528374441   -0.987431892147214
3   transcr_25793   -0.576556453265197  0.956853490465593   -0.712579124289414
4   transcr_1966    0.66610055219078    0.199587132187484   0.47438019134052
5   transcr_10428   -0.770206245250698  -0.434541952574813  0.413082695627957
6   transcr_20649   0.828958672046763   -0.301011711451322  0.85215236415901
7   transcr_11317   0.09699438477018    -0.728279374568874  -0.555587423971877
8   transcr_11317   -0.556544875244594  0.52241898249443    0.361144169769576
9   transcr_7135    0.525796225375268   -0.915309254508446  0.352117890583668
10  transcr_6234    -0.254737326090742  -0.842640701643698  0.435449408114073

I need a resulting file that has the difference between file1 (that has fewer lines) with file2 (that has more lines) using columns $2 and $3. So, I'm looking for something like this:

No  ID  CV  CA1 CA2
1   transcr_10283   0.999023367236861   -0.344113101336184  -0.032235130455987
2   transcr_10371   -0.572755303094372  -0.579145581184253  0.879510598089221
3   transcr_10391   0.999589933675858   -0.379226454955611  -0.302057879326854
5   transcr_10673   -0.555906836336222  0.996418809959179   0.83927901939441
6   transcr_10719   -0.977601905205625  -0.297994976855801  -0.988480730161833
7   transcr_10805   -0.994387636575223  -0.924363947763111  -0.096820331033279
8   transcr_1084    0.929966893591254   0.994040100421911   0.604483398826667
9   transcr_10892   0.987734223438821   0.822187392097743   0.968727545498998
10  transcr_10892   0.999938729100654   -0.985209499864003  0.958993756142276

File2 is not sorted and I was looking for some way to do it without sorting the file.

Thanks!

Edit: Just to make it more simple to be seen, the line with transcr_10428 0.128862262957329 was the removed one in this case.

Answer 1

With awk:

$ awk -v FS="\t" -v OFS="\t" 'NR==FNR {trans[$2"|"$3]++; next;} FNR==1 {print} FNR>1 {if(!trans[$2"|"$3]) print}' file2 file1

• First file2 is read in and the value by column 2 and 3 are used to store thme as a key in a list.
• If file1 is read in, the header line is printed. For each next line, we check if a key with the values of column 2 and 3 exist in our list we've created before. If not we print out the line.

Answer 2

The way files are compared is not clearly explained/defined.

However, it didn't prevented me trying to read your mind ...

From my understanding, file 2 is a kind of data base file or reference. File 1 is supposedly containing new data.

The "comparison" as I understood it: if a value of column 2 or 3 of file 1 is already found into file 2 (i.e. the reference), then do not print/include it. Else print/include it.

The good news is that it doesn't require sorting indeed ... as you requested it ... .

Below is a script taking 2 parameters: First is the new data file (file 1 in your example). Second is the data base file (file 2 in your example).

#!/bin/bash

new_file=$1
db_file=$2

# Just checking the last parameter
if [ "x" = "x$db_file" ]; then
    echo >&2 "[ERROR] This scripts expect 2 file path as parameter."
    exit 1
fi

if [ ! -f $new_file ]; then
    echo >&2 "[ERROR] First parameter file doesn't exist."
    exit 2
fi

if [ ! -f $db_file ]; then
    echo >&2 "[ERROR] First parameter file doesn't exist."
    exit 3
fi


declare -A data_base

# Open both files and assign to file descriptor 10 and 11
exec 10< $new_file
exec 11< $db_file

# Step 1
# Building map of base data first (for the comparison to happen in next step)
first_line=1
while [ /bin/true ]; 
do
    read -u 11 db_file_col1 db_file_col2 db_file_col3 db_file_rest  || {
        break;
    }

    # Skipping the header so that it will appear in the diff as shown in the example
    if [  $first_line -ne 0 ]; then
        first_line=0
        continue
    fi


    # Creating map from Col 2 and Col 3 (keys) to the whole line (value)
    data_base[$db_file_col2]="$db_file_col1 $db_file_col2 $db_file_col3 $db_file_rest"
    data_base[$db_file_col3]="$db_file_col1 $db_file_col2 $db_file_col3 $db_file_rest"
done


# Step 2
# Actual comparison ... 
while [ /bin/true ]; 
do
    read -u 10 new_file_col1 new_file_col2 new_file_col3 new_file_rest  || {
        break;
    }

    if [ -z "${data_base[$new_file_col2]}" ] && [ -z "${data_base[$new_file_col3]}" ]; then
        echo "$new_file_col1 $new_file_col2 $new_file_col3 $new_file_rest"
    fi

done

If you save the script into a file named process.sh for example (and then 'chmod 755 process.sh' to make it executable), then executing:

./process.sh file1 file2 

while lead to your exact expected output/result.

NOTE: This script is holding at least twice the content of file 2 into memory. Ensure you have enough memory ... .