0

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.

  • why not sort it? if it's too large to fit in memory for sorting, it may be too large for another tool to hold it. – Jeff Schaller Jan 28 at 17:11
  • If there is no other way, it's ok – Lucas Farinazzo Marques Jan 28 at 17:29
  • If I was to make the example explicit, the result omits "transcr_10428 0.128862262957329" because that matches in the two columns between the two files? – Jeff Schaller Jan 28 at 17:36
  • Yes, that one was removed on the final file – Lucas Farinazzo Marques Jan 28 at 18:52
  • Could you please clarify how removing transcr_10428 has anything to do with columns 2 and 3? Your example is not clear at all. – Niko Gambt Jan 29 at 3:21
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.
1

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 ... .

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.