0

want to match column 1-5 in 2 files and print column1-5 along with column 6&7 from file1 and column6 from file2. Non-matching rows from both file should also get printed with NA value in non-matching columns

file1

12  800000  900000  66  73  0   0  
12  900000  1000000 73  48  2   2  
13  1000000 1100000 11  11  0   0  
12  1100000 1200000 12  12  0   0  
18  1400000 1600000 33  33  3   3  

file2

12  800000  900000  66  73  145(28.12)  
12  900000  1000000 73  48  703(51.17)  
13  1000000 1100000 11  11  545(43.99)  
12  1100000 1200000 12  12  699(45.30)
14  16100000    16200000    0   0   11(14.50)  
14  16200000    16300000    0   0   0    

Expected output

12  800000  900000  66  73  145(28.12)  0  0   
12  900000  1000000 73  48  703(51.17)  2  2  
13  1000000 1100000 11  11  545(43.99)  0  0  
12  1100000 1200000 12  12  699(45.30)  0  0  
14  16100000  16200000  0  0  11(14.50)  NA  NA 
14  16200000  16300000  0  0  0  NA  NA  
18  1400000 1600000 33  33  NA  3   3

I tried below awk command but not getting columns from both files

awk 'FNR==NR{A[$1 FS $2 FS $3 FS $4 FS $5]=$3;next}{print ($1 FS $3 FS $4 FS $5 in A ) ? $0 OFS A[$1 FS $3 FS $4 FS $5] : $0 OFS "NA"}' file1 file2
0

1 Answer 1

4

Your code doesn't match your description, e.g. it's storing $3 from file1 to print later but you say you want to print $4 and $6, and then your expected output doesn't match either of those and shows $6 and $7 from file1 instead. So, just going by your expected output, I think this is what you want:

$ cat tst.awk
{ key = $1 OFS $2 OFS $3 OFS $4 OFS $5 }
FNR==NR {
    file1[key] = $6 OFS $7
    next
}
{
    print $0, (key in file1 ? file1[key] : "NA" OFS "NA")
    delete file1[key]
}
END {
    for ( key in file1 ) {
        print key, "NA", file1[key]
    }
}

$ awk -f tst.awk file1 file2
12  800000  900000  66  73  145(28.12) 0 0
12  900000  1000000 73  48  703(51.17) 2 2
13  1000000 1100000 11  11  545(43.99) 0 0
12  1100000 1200000 12  12  699(45.30) 0 0
14  16100000    16200000    0   0   11(14.50) NA NA
14  16200000    16300000    0   0   0 NA NA
18 1400000 1600000 33 33 NA 3 3

Note the use of a variable to hold the key index for both files. When you have a key that's a combination of values, don't try to write it multiple times in your code as it's easy to get that wrong as you did where you forgot to include $2 in the lookup of the map when processing file2 (you used $1 FS $3 FS $4 FS $5 in A instead of $1 FS $2 FS $3 FS $4 FS $5 in A and did the same again in A[$1 FS $3 FS $4 FS $5]).

Also - don't use all upper case variable names (e.g. A) to avoid clashing with builtin variable names, and use parentheses to make your code clearer if nothing else, e.g. idk if $1 FS $2 in A means ($1 FS) ($2 in A) (i.e. the concatenation of $1 FS with the result of looking up $2 in A) or ($1 FS $2) in A (i.e. the result of looking up the concatenation of $1 FS $2 in A) or something else but if you simply write it as ($1 FS $2) in A then it's clear and unambiguous.

Finally note that I used OFS instead of FS as the array index separator - that's because in the code we are printing that value in the END section and the thing that should separate output fields is OFS, not FS. In this case they are both the same thing, a blank char, so you won't see a difference but if you wanted to output CSV instead then with my code all you need to do is add -v OFS=',' and it'd work as-is whereas if you used FS between the array index subscripts then you'd need to change that.

0

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.