Replace values in column that contain numbers

Question

I have a file that looks as so:

chrom   start   stop    strand  isoform mu_codon    mut_codon2  more_info
chr22   43089055    43089055    -   NM_017436   C   300 903delC
chr22   43089715    43089717    -   NM_017436   CTT 79  241_243delTTC
chr12   53701873    53701875    -   NM_015665   TTC A   1292_1294delTTCinsA

I want to replace the values in the 7th column with a space if they contain a number and not a letter.

For example, here is my desired output:

chrom   start   stop    strand  isoform mu_codon    mut_codon2  more_info
chr22   43089055    43089055    -   NM_017436   C       903delC
chr22   43089715    43089717    -   NM_017436   CTT     241_243delTTC
chr12   53701873    53701875    -   NM_015665   TTC A   1292_1294delTTCinsA

I need to keep the space in my file so my columns are not altered. Any idea on how to do this? Maybe awk? I'm still learning these techniques. Can you explain your answers?

Answer 1

As you want to preserve your columns you can change output field separator to be tab not space for example and it will be easier for further processing if you rely on columns count. So you can use following awk:

awk 'BEGIN { OFS = "\t"; }; { if ($7 ~ "^[0-9]*$") $7 = " "; else $7 = $7; }; 1'

In BEGIN section we are changing output field separator(OFS) to tab. In the next section we check if 7^th contains only numbers and if yes we change value to space, if not keep the value. But with $7 = $7 we are sure we reconstruct current line(record) because of changed OFS. Changing OFS ensures further processing with awk that output will contains 8 columns if field separator(FS) is set to tab.

Answer 2

sed -e's/  *[^ 0-9]*/&\n/6;:n'      \
    -e's/\(\n[^ ]*\)[^ ]/\1 /;tn'   \
    -e's/\n//' <infile

Here's some sed to do it. The first thing we do is replace the 6th occurrence of one or more spaces followed by a sequence of zero or more [^ 0-9] not-space or numeric characters with itself followed by a newline.

Basically this means that if the 7th column consists entirely of characters other than spaces or numbers, sed will append a newline to it, else sed will prepend a newline to it.

In the next statement we replace all not-space characters immediately following a newline in pattern space with a space character each. Of course, this only happens at all when the newline is not already followed by a space character, as it will be if the seventh column was not numeric.

Last, we remove the inserted newline delimiter.

OUTPUT

chrom   start   stop    strand  isoform mu_codon    mut_codon   more_info
chr22   43089055    43089055    -   NM_017436   C       903delC
chr22   43089715    43089717    -   NM_017436   CTT     241_243delTTC
chr12   53701873    53701875    -   NM_015665   TTC A   1292_1294delTTCinsA

Answer 3

awk '{gsub("^[0-9]*$"," ",$7);$7=$7;OFS="\t";print}' file.txt

Answer 4

Perl to the rescue:

#!/usr/bin/perl
use warnings;
use strict;

while (<>) {                     # Process line by line.
    my @F = split /(\s+)/;       # Split the line into @F, keep whitespace as members, too.
    $F[12] =~ s/./ /g            # Replace any character with space
        if $F[12] =~ /^[0-9]+$/; # if there are just digits.
    print @F;                    # Print the result.
}