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
  • You really want to use awk for this? – Braiam Jun 5 '14 at 16:06
  • Hi I is difficult in awk?? I can try do it in perl but I assume it is not that difficult in awk though I am struggling to write an awk statement for it :( – user3138373 Jun 5 '14 at 16:07
  • This sounds quite trivial in awk. – jw013 Jun 5 '14 at 16:08
  • @jw013 : Any help!!! – user3138373 Jun 5 '14 at 16:09
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
  • Awesome Thanks for the code. Just one thing. Since my header is tab separated, I would want my fields in the subsequent lines to be tab separated too so i guess adding awk 'BEGIN{OFS = "\t"};NR > 1 { for (i = 6; i <= NF; i++) if ($i < 100) $i = "NA" }; 1' yourfile.txt' – user3138373 Jun 5 '14 at 16:14
  • :Can you explain a little what 1 actually does. I am referring the manual as well. Thanks – user3138373 Jun 5 '14 at 16:23
  • 1
    @user3138373 Using the POSIX spec, the Extended Description says, "An awk program is composed of pairs of the form: pattern { action }. Either the pattern or the action (including the enclosing brace characters) can be omitted." The 1 is simply a pattern, and the action is omitted. Further, "... a missing action shall be equivalent to: { print }." – jw013 Jun 5 '14 at 16:26
  • So in the code above we have pattern as NR > 1 and action is enclosed in the braces. But within braces you have if condition (without the then-body part). Is this 1 used here for printing since it was not explicitly mentioned in the code. Sorry for asking such a question but I guess I already learnt a lot today. Thanks – user3138373 Jun 5 '14 at 16:38
  • 1
    if ($i < 100) $i = "NA" has a body. The body is the $i = "NA" part. Braces { $i = "NA" } are unnecessary since it is only one statement. – jw013 Jun 5 '14 at 16:44

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 


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

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.

  • @jw013 - good point. I should probably be inserting a newline anyway with s/ \([^ ])/\n\1/6 or something, maybe I'll rearrange it... – mikeserv Jun 5 '14 at 18:48
  • @jw013 - nah, just inserted the space. Good catch, and thanks for the comment. – mikeserv Jun 5 '14 at 18:58

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.