0

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

  • Thank you. It's not working for my files. I could see the comparison is being done by 1st line file1 with 1st line of file2. It is not matching the lines if their line numbers are different. I am not sure if my observation is right. – Prradep Aug 6 '15 at 13:31
0

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.

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.