awk command for parsing a file

Question

I have a following text file. I am showing you the first 3 lines.

chrom   st  end gene    strand  c1  c2  c3  c4  c5  c6  c7  c8  c9  c10 c11 c12 c13 c14
chr6    3345    3543    geneA   +   36  -23 -1  3   1250    946 416 458 475 417 58  80  2   14
chr9    1302    1389    geneB   -   8   -10 -18 -8  2896    2128    635 955 372 385 -20 31  -7  -7

I want to print out first line as it is since it's the header line.

Then for subsequent lines(i.e. from lines 2 and onwards), I want to print the first 5 fields as it is(till the strand info) and after that if the field(from 6th field onwards) has value >= 100 print that value as it is and if the field has value < 100 just replace it with NA.

So my output file should look something like this (ideally, tab-delimited)

chrom   st  end gene    strand  c1  c2  c3  c4  c5  c6  c7  c8  c9  c10 c11 c12 c13 c14
chr6    3345    3543    geneA   +   NA  NA  NA  NA  1250    946 416 458 475 417 NA  NA  NA  NA
chr9    1302    1389    geneB   -   NA  NA  NA  NA  2896    2128    635 955 372 385 NA  NA  NA  NA

Answer 1

awk 'NR > 1 { for (i = 6; i <= NF; i++) if ($i < 100) $i = "NA" }; 1' yourfile.txt

Expanded with comments:

NR > 1 {                         # skipping NR == 1, the first line
    for (i = 6; i <= NF; i++)    # column 6 to the end, skipping first 5
        if ($i < 100) $i = "NA"  # self-explanatory
}

1 # print all lines; 1 evaluates to true, and default action is print

Edit: there are multiple ways to set OFS. The most concise way I can think of is to add OFS='\t' before the filename.

awk '...' OFS='\t' file.txt
awk -v OFS='\t' '...' file.txt
awk 'BEGIN { OFS="\t" }; ...' file.txt

Answer 2

jw013 already gave you a good awk solution but since you mentioned Perl:

perl -lane 'map{$_="NA" if $_<100}@F[5..$#F] if $.>1; print join "\t", "@F"' file 

Explanation

• perl -lane : process each input line (-n) and split it on whitespace into the @F array (-a), then run the script given by -e. The -l removes trailing newlines from each line and adds a \n to each print statement.

• map{$_="NA" if $_<100}@F[5..$#F] : for each element of array @F (the fields) from the 6th until the end, change that element to "NA" if is less than 100.

• if $.>1; : the preceding map{} will only run of this is not the first line.

• print join "\t", "@F"' : join each element of the @F array with a tab (s requested in your comment to jw013's answer) and print it.

Answer 3

sed '1n;s|$| |;:na
    s|\([+-] .*\) [+-]*[0-9]\{1,2\} |\1 NA |
    t na;s| $||'

Judging by the data you show there's no reason this little sed s///;t function shouldn't work, I don't think. (Thanks to jw013 for pointing out the potentially missed last column.) This just replaces every 1 or 2 character numeric string following a +/- then a on a line with everything preceding it and NA until there's nothing more to replace.

Here's another version without recursion that makes use of sed's hold space:

sed '1n;h;s|.*[+-] ||;s|$| |
    s| [+-]*[0-9]\{1,2\} | NA |g
    x;G;s|\([+-] *\).*\n|\1|;s| $||'

It relies on the same marker, and splits the line there - the first half is left untouched in hold space while its deleted entirely from pattern space. Then we do a global replace on all 1,2 numeric character words, append to hold space, exchange pattern and hold spaces, and delete everything between the marker and the \newline inserted as a result of the append operation.