# Matching the values in fields and assigning them the identity based on the match

``````chr:pos1:pos2   Sun     NC      S1      S2      S3      S4      S9      S11     S14     S15     S16     S17     S18     S19     S28     S29     S30     S33     S34     S35     S36     S37     S38     S39
Aradu.A01:100145549:100145556   AG      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA      AA
Aradu.A01:100408119:100408137   CA      TA      0       0       0       TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      TA      0
Aradu.A01:10102206:10102212     TG      TA      TA      TA      TA      0       TG      TA      TA      TA      TG      TG      TG      TG      TG      TA      TG      TG      TA      0       TG      TG
Aradu.A01:10112010:10112029     GA      GG      GG      GG      GG      GG      GA      GG      GG      GG      GA      0       GA      GA      GA      GG      GA      GA      GG      GA      0       GA
Aradu.A01:10112029:10112059     AC      GC      GC      GC      GC      GC      AC      GC      GC      GC      AC      0       AC      AC      AC      GC      AC      0       GC      AC      0       AC
Aradu.A01:101198026:101198058   GT      GC      GC      GC      GT      GC      0       GT      GT      GC      GT      GT      GT      0       GT      GC      GT      GC      GC      GT      0       GT
Aradu.A01:101198058:101198081   TC      CC      CC      CC      TC      CC      0       TC      TC      CC      TC      TC      TC      0       TC      CC      TC      CC      CC      TC      0       TC
Aradu.A01:101306922:101306946   AG      AA      AA      AA      AG      0       AA      AG      AG      AA      0       AG      AG      AG      AG      AA      AG      AG      AA      AG      AG      AG
``````

In the given file, I am trying to match the values in all the fields colum 4 onwards with column 2 and 3. If a value of a field (col 4 and onwards) matches col 2 field then mark it as S, if it matches with col 3 then mark it as N and if it is 0 then assign it -1.

Following is what I tried:

``````NR>1  {for(i=4;i<=NF;i++)
{ if ( \$i == \$2 ) \$i=S ;
if ( \$i == \$3 ) \$i=N ;
if ( \$i == 0 ) \$i=-1 ;
} ## if ;
## for loop is done
print ;
}
``````

This is resulting in assigning all the fields except the first 3 as -1.

• It's hard to be definitive without a minimal working example, but `S` and `N` are variables, which will be zero by default. Hence `\$i == 0` will be true if any of the preceding conditions is true. You probably want `\$i = "S"` and `\$i = "N"` – steeldriver May 4 '17 at 23:32

This appears to do the trick:

``````NR > 1 {
for( i=4; i<NF; i++) {
if( \$i == \$2 ) {
\$i = "S"
}
else if( \$i == \$3 ) {
\$i = "N"
}
else if( \$i == 0 ) {
\$i = -1
}
}
print
}
``````

Put that in a file called e. g. `363142.awk`, and run:

``````\$ awk -f 363142.awk /path/to/input
``````
``````perl -F\\s+ -pale '
\$. > 1 and /(?:\S+\s+){3}/g and
s{\G(\S+)(\s*)}
{
(\$1 eq \$F[1] ? "S" : \$1 eq \$F[2] ? "N" : \$1 eq "0" ? -1 : \$1) . \$2
}cge;
' your_genes_file
``````

## Options

• `-F\\s+` will set the `FS` to one or more whitespaces => `/\s+/`
• `-p` will set up an implicit file read in loop AND the printing is automatic.
• `-a` will carve up afresh each record based on the field splitting delimiter provided by the `-F` option or a solitary space by default. The fields are stored in the array `@F`.
• `-l` will set `ORS=RS=\n`
• `-e` is the `Perl` code to be applied to each record.

## Explanation

We skip the first line from processing. `-p` option will take care to print it. Then we park our regex engine marker at the beginning of the 4th field as that's where all action is supposed to begin. Then we apply the `s///` command in `global`, `cumulative`, and `eval` modes. `/c` => regex marker will start from where parked in the last `m//g` command and NOT from the beginning. `/g` => will look through the whole line for transforming it, and `/e` will look at the `s//repl/` replacement section as a `Perl` code to be executed and it's results are parked in the replacement section.

## GNU sed

This can be written in `POSIX sed` but then the code would get in the way of clarity and is left as an exercise in moderation.

``````sed -e '
1b

# fence the column 2 and go looking for col2 matching fields from col 4 onwards
s/\S\+/\n&\n/2
:c2
s/\(\n\(.*\)\n\s\+\S\+\s.*\)\<\2\>/\1S/g;tc2
s/\n//g

# fence the column 3 and go looking for col3 matching fields from col 4 onwards
s/\S\+/\n&\n/3
:c3
s/\(\n\(.*\)\n.*\)\<\2\>/\1N/g;tc3
s/\n//

# from col 4 onwards look for any lone 0s and change them to -1
:zm1
s/\(\n.*\)\<0\>/\1-1/;tzm1
s/\n//

' gene_file.dat
``````

## Perl

Here we remove any leading whitespace which will interfere with the indexing of the @F array and then reinstate it at the time of going for printing the data out. To the `map` we feed elements 6 onwards (4+2 for whitespaces) and just check whether they equal the 2nd/3rd columns or 0.

``````perl -F'(\s+)' -lane '
print,next if \$. == 1;
my \$dummy = splice @F, 0, 2 if /^\h/;
print \$dummy, @F[0..5], map {
s/^\Q\$F[2]\E\$/S/ or s/^\Q\$F[4]\E\$/N/ or s/^0\$/-1/; \$_
} @F[6 .. \$#F];
' gene_file.dat
``````