0

I have two files. I have to get matching name from file1, print the entire content of file1, and add a fourth column (exp) in file 1 using a second file with max value

file1

name             value1          value2          value3
hat_197050       30.5348         37.2617         1.2203
hat_160460       20.8679         21.0134         1.00697
hat_092950       63.875          63.2321         0.989935

file2

hat_160460  AA:0003700,AA:0003707,AA:0005634,AA:0006355,AA:0043401 
hat_160460  AA:0003700,AA:0003707

Desired output:

name             value1          value2          value3  exp
hat_197050       30.5348         37.2617         1.2203
hat_160460       20.8679         21.0134         1.00697 AA:0003700,AA:0003707,AA:0005634,AA:0006355,AA:0043401 
hat_092950       63.875          63.2321         0.989935

I want to use awk. I write this two get matched column but it gives just matched

awk 'FNR==NR{a[$1]=$0;next}{if(b=a[$1]){print }}' file1 file2 > matched
this command to get the file2 with max field
awk 'BEGIN { FS = "," } ;{if (NF > max) {max = NF ; line =$0}} END{print line}' file2

I don't figure out how I combine both to get what I want. Any suggested reading will be appreciated.

1
awk '
    FNR == NR {
        # Read file1
        i=$1;
        sub($1 FS, "");
        a[i]=$0; next;
    }
    (FNR < NR) && (FNR == 1) {
        # add new column header to title row (first line of file1)
        print $0 "exp"; next;
    }
    { 
        # Read file2 and append matching line from file1
        print $0 a[$1];
    }
' file2 file1

Note that file2 is read before file1 in the solution above to extract the first column to match (and therefore file2 is specified before file1). Also, this solution will use the last match in file2 (in your example, hat_160460 occurs twice in file2).

Result:

file1 name value1 value2 value3 exp
hat_197050 30.5348 37.2617 1.2203 
hat_160460 20.8679 21.0134 1.00697 AA:0003700,AA:0003707
hat_092950 63.875 63.2321 0.989935

If you want to use the first match in file2, then replace the FNR == NR section above with

FNR == NR {
    # Read file1
    i=$1;
    sub($1 FS, "");
    if (! a[i]) a[i]=$0;
    next;
}

Result:

file1 name value1 value2 value3 exp
hat_197050 30.5348 37.2617 1.2203 
hat_160460 20.8679 21.0134 1.00697 AA:0003700,AA:0003707,AA:0005634,AA:0006355,AA:0043401 
hat_092950 63.875 63.2321 0.989935
  • one more thing can we use && many times in awk command like this awk '{if($4 >= 0.9 && $4 <= 1.1 && $7 >= 0.9 && $7 <= 1.1) print $0}' – star Apr 15 '16 at 17:53
  • @star Yes, that is ok. – Guido Apr 15 '16 at 19:21

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.