find and replace with the value in another file

Question

I have two files with different formats with columns tab spaced. I have to compare the columns column1, column2 of file1 with file2. If they matches, I need to replace the value in column6 of file1 with the value in column3 of file2. I have tried using awk but I am not able to replace the value. Could you please advise on the below snippet ?

awk 'FILENAME == ARGV[1] {
    m[$1,$2] = $6;
    next;
}
{
    if (($1,$2) in m) {
        m[$6]= $3; print m[$6];
    }
}' file1 file2


top few lines of file1
1201 12011 1 0 0 0 1
1202 12021 1 0 0 0 1
1203 12031 1 0 0 0 1
1204 12041 1 0 0 0 2
1207 12071 1 0 0 0 2
1209 12091 1 0 0 0 1
1210 12101 1 0 0 0 1
1212 12121 1 0 0 0 1
1213 12131 1 0 0 0 1
1214 12141 1 0 0 0 2

top few lines of file2
1201    12011   1
1202    12021   1
1203    12031   1
1204    12041   1
1206    NA  1
1207    12071   2
1208    NA  1
1209    12091   2
1210    12101   2

I want to assign the values from file2 to file1 column as I would like to write the updated content into another file out.txt

edit

Tried the below code as per the comments

awk '{
    if (FNR==NR) {
        a[FNR]=$1;b[FNR]=$2;c[FNR]=$3}
    else {             
        if (a[FNR] == $1 && b[FNR] ==$2) {
            $6=c[FNR]} else {$6=$6};
           print $0;
        }
    }' file2 file1

Got this output

1201 12011 1 0 0 1 1
1202 12021 1 0 0 1 1
1203 12031 1 0 0 1 1
1204 12041 1 0 0 1 2
1207 12071 1 0 0 0 2
1209 12091 1 0 0 0 1
1210 12101 1 0 0 0 1
1212 12121 1 0 0 0 1
1213 12131 1 0 0 0 1
1214 12141 1 0 0 0 2

Answer 1

awk -F"\t" -v OFS="\t" '{
    if (FNR==NR) {
        a[FNR]=$1;b[FNR]=$2;c[FNR]=$3}
    else {
        if (a[FNR] == $1 && b[FNR] ==$2) {
            $6=c[FNR]} else {$6=$6};
            print $0
        }
    }' file2 file1

I think this will do the work. First it will save the 1st,2nd and 3rd column of file 2 to array a,b and c resp with index as FNR if FNR = NR(true for lines in file2 only). Else(true for lines in file1 only) compare value in array a and b with $1 and $2. if it matches change the 6th field with value in array c else it will reassign the value to same and prints the line with delimiter as tab.

Answer 2

An alternative non-awk sledgehammer approach

while read f1 f2 f3; do 
    sed -E -i "s/^($f1\s+$f2.*)([0-9]+)$/\1$f3/" file1;
done < file2 

Assumes the last number is a integer. If it isn't then ([0-9])$ will need amending to a suitable capture group.

Note that this relies on modifying file1 in-situ so work on a copy.

Answer 3

Looking at your awk script, you've got the right idea about processing each input file differently (although in most cases checking if NR == FNR works better and faster because numeric comparisons are faster than string comparisons...which would be significant if there were many thousands of input lines in either or both files).

There are a few problems, though, that prevent your script from doing what you want.

1. You're reading the files in the wrong order - since you want to output lines from file1 with column 6 replaced by column 3 of file2, you need to read file2 first to build up the m array (with indexes of $1,$2 from file2 and values of $3 from file2).

2. You're modifying m[$6] and then printing it. At best, this will print a single column.

Try something like this:

$ awk 'NR == FNR    { m[$1,$2] = $3; next }
       ($1,$2) in m { $6 = m[$1,$2] }
       1' file2 file1
1201 12011 1 0 0 1 1
1202 12021 1 0 0 1 1
1203 12031 1 0 0 1 1
1204 12041 1 0 0 1 2
1207 12071 1 0 0 2 2
1209 12091 1 0 0 2 1
1210 12101 1 0 0 2 1
1212 12121 1 0 0 0 1
1213 12131 1 0 0 0 1
1214 12141 1 0 0 0 2

Alternatively, replace the NR == FNR on the first line with your original FILENAME == ARGV[1].

For every line of file2 (the first file to be read - note the order of filename arguments on the command line) it stores $3 in the array m and skips to the next input line. Columns $1 and $2 are used as the index for the array.

Then, while reading file1 (and subsequent input files, if any), if ($1,$2) is an index in the array, it replaces $6 with the value stored in the m array.

Then it prints the current input line whether it was modified or not (a pattern of 1 by itself is an awk idiom/shortcut meaning "print the current input line" - "1" evaluates as true, and the default action is "print"...so, effectively "if true is true then print")

Note: you didn't provide sample expected output so I can't verify that the output matches what you want (normally, I like to save an OP's sample output to a file and use diff or cmp to verify that my output matches). My awk script's output does match my interpretation of what you asked for in your question.