replace values of fields in each row with values in previous fields until the end of each row

Question

I have a file in the following format, where, columns 6 and 7 are allele A and B. All I need to do is make changes in column 9 and onwards based on the alleles in columns 6 and 7. If column 9 field value is 0 then replace it with column 6 and if it is 2 replace it with column 7. If it is -1 then it should be left as such and 1 should be marked as col 6/col 7. This I have to do repeatedly for all the fields in each until the end of each row. Below are a few such rows pasted for your reference. Each row has some 130 fields.

Probe Set ID    Affy SNP ID     Chromosome      Physical Position       Flank   Allele A        Allele B        probeset_id     SunOleic97R     NC94022 S1      S2      S3      S4      S9      S11     S14 
AX-147208720    Affx-152069361  Aradu.A01       5066618 TTTCTTGGCGGCATTGCTGATTTCTTATCATCCAA[A/G]CCATTCTTCTTTGTGTCAGGGTGGAATCTAAAATT     A       G       AX-147208720    0       2       0       0       2
AX-147209428    Affx-152065184  Aradu.A01       9154456 TAGCTGTTGACATGTCAATTGCTAAGGGAGAGTCC[C/T]TTGGAAAGCCCTACATCATTCATCAAATCATTCTC     T       C       AX-147209428    2       0       2       0       0
AX-147209429    Affx-152069061  Aradu.A01       9155638 TCAGCAAATGAACCTCTTAAGAAACCAATTCGGTC[A/G]TTGCTTATCACTAAGCTTTCAATCCCTTTCACTGG     A       G       AX-147209429    2       0       2       0       0
AX-147209430    Affx-152031763  Aradu.A01       9157305 CGGCGCTCTAAAATCCAGATAACAACTCCAACAAC[C/T]AAGAAAAAGGTTGCTGTGACAAACCACATCATTGG     T       C       AX-147209430    2       0       2       0       0
AX-147209432    Affx-152067683  Aradu.A01       9205209 CCCTTAATTGGGGAAGAGAGTTGTTCCACTGTGAG[A/G]ATTGATGTTAGGCTTGCAATGTAGCTTGAATTCAG     A       G       AX-147209432    2       0       2       0       0
AX-147209600    Affx-152035192  Aradu.A01       9873259 CTCCTTCTCTCGGTTTCCAAGACAAAAGAAAGACA[A/G]ATATCTTTTAAGATCTTCCTCAGTTTTGTTCTCCC     A       G       AX-147209600    2       0       0       0       0
AX-147209601    Affx-152067325  Aradu.A01       9873427 TGGCCACATTGGAACCACAACATACACAGTGAAGT[C/T]TTGCTTAGCTTTAATCTTGCTAACAATTTTAAGTG     T       C       AX-147209601    2       0       0       0       0
AX-147209615    Affx-152066978  Aradu.A01       9974460 AAAACTCACAATTCTTCTTTGATGATCTGAGTCCT[C/T]TCCATTTGACAATTTAGCATCCACCACCACAATCT     T       C       AX-147209615    0       2       2       2       2

I initially tried to replace the value of field in column 9 based on its values using:

awk '{if ($9 == 0) print $9 == $6}; 1' file.txt |less -S 

It did not do any change to the original file. Can anyone please help me!

Answer 1

let's have it step by step

If column 9 ($i) field value is 0 then replace it with column 6

if ( $i == 0 ) $i=$6 ;

and if it is 2 replace it with column 7.

if ( $i == 2 ) $i=$7 ;

If it is -1 then it should be left as such

# 

and 1 should be marked as col 6/col 7.

if ( $i == 1 ) $i=$6 "/" $7 ;

This I have to do repeatedly for all the fields in each until the end of each row.

NR>1  {for(i=9;i<=NF;i++)
        {  } ## ifs ;
       ## for loop is done
       print ; ## print to be done at end of loop.
       } 

All together,

put all these line in a file a.awk

NR>1  {for(i=9;i<=NF;i++)
        { if ( $i == 0 ) $i=$6 ;
          if ( $i == 2 ) $i=$7 ;
          if ( $i == 1 ) $i=$6 "/" $7 ;
       } ## if ;
       ## for loop is done
       print ;
       } 

where

• NF is a builtin for 'Number of Field'
• print will print

With your sample data

awk -f a.awk sample.txt 
AX-147208720 Affx-152069361 Aradu.A01 5066618 TTTCTTGGCGGCATTGCTGATTTCTTATCATCCAA[A/G]CCATTCTTCTTTGTGTCAGGGTGGAATCTAAAATT A G AX-147208720 A G A A G
AX-147209428 Affx-152065184 Aradu.A01 9154456 TAGCTGTTGACATGTCAATTGCTAAGGGAGAGTCC[C/T]TTGGAAAGCCCTACATCATTCATCAAATCATTCTC T C AX-147209428 C T C T T
AX-147209429 Affx-152069061 Aradu.A01 9155638 TCAGCAAATGAACCTCTTAAGAAACCAATTCGGTC[A/G]TTGCTTATCACTAAGCTTTCAATCCCTTTCACTGG A G AX-147209429 G A G A A
AX-147209430 Affx-152031763 Aradu.A01 9157305 CGGCGCTCTAAAATCCAGATAACAACTCCAACAAC[C/T]AAGAAAAAGGTTGCTGTGACAAACCACATCATTGG T C AX-147209430 C T C T T
AX-147209432 Affx-152067683 Aradu.A01 9205209 CCCTTAATTGGGGAAGAGAGTTGTTCCACTGTGAG[A/G]ATTGATGTTAGGCTTGCAATGTAGCTTGAATTCAG A G AX-147209432 G A G A A
AX-147209600 Affx-152035192 Aradu.A01 9873259 CTCCTTCTCTCGGTTTCCAAGACAAAAGAAAGACA[A/G]ATATCTTTTAAGATCTTCCTCAGTTTTGTTCTCCC A G AX-147209600 G A A A A
AX-147209601 Affx-152067325 Aradu.A01 9873427 TGGCCACATTGGAACCACAACATACACAGTGAAGT[C/T]TTGCTTAGCTTTAATCTTGCTAACAATTTTAAGTG T C AX-147209601 C T T T T
AX-147209615 Affx-152066978 Aradu.A01 9974460 AAAACTCACAATTCTTCTTTGATGATCTGAGTCCT[C/T]TCCATTTGACAATTTAGCATCCACCACCACAATCT T C AX-147209615 T C C C C

Beware that awk will not edit original file. You have to use :

awk -f a.awk file1.txt > file2.txt
cp file2.txt file1.txt
rm file2.txt