Shell – How to extract the first integer from text string in a column of a tab-delimited file

awkcolumnssedshell-scripttext processing

I work in Medical Genetics and often have delimited text files where in one column (ex. column 5) there is a text string with a "mutation" in our jargon:
c.2458C>T or
c.45_46delAA or
c.749_754delinsTG

Similarly, in another file it might read:
p.Glu34* or p.Ala78_Arg80del or p.L378Ffs*11

The c. and p. are supposed to be there but might be omitted. There could be any number of non-numeric characters. The numbers are always integers and usually 1-14 or so digits long.

I want to add a new column somewhere in my file, which has only the first integer, like 2458 or 45 or 749 in the first example. Then I want to use this integer as a key value for looking up several values in a lookup table.

Some of my files have 70,000 lines so manual editing is not possible…

The more basic the solution the better. Can it be done with bash, sed, or awk?

An example table would be (as interpreted correctly below):

1       2       3       4       c.2458C>T
a   b   c   d   c.45_46delAA
a1  b2  c3  d4  p.Ala78_Arg80del

(Note: the columns are tab-delimited, not space-delimited)

There is a specification to this format by the Human Genome Variation Society. No program uses this format (I hope!) but people use it in publications and medical reports. Newer formats, like the Variant Call Format have been introduced, which are far more parsable.

Best Answer

Based on your description, suppose that we have, as input, a tab-separated file like:

$ cat file
1       2       3       4       c.2458C>T       6
a       b       c       d       c.45_46delAA or f
a1      b2      c3      d4      p.Ala78_Arg80del        f6

Using sed

To find the first integer from the fifth column:

$ sed -r 's/([^\t]*\t){4}[^[:digit:]]*([[:digit:]]+).*/\2/' file
2458
45
78

The above was tested on GNU sed. For OSX or other BSD system, try:

sed -E 's/([^\t]*\t){4}[^[:digit:]]*([[:digit:]][[:digit:]]*).*/\2/' file

Using awk

$ awk '{sub(/^[^[:digit:]]*/, "", $5); sub(/[^[:digit:]].*/, "", $5); print $5;}' file
2458
45
78

Related Solutions

How to adjust numeric fields in a text file

perl has a module called Scalar::Util (included with perl since v5.8) which has a useful function called looks_like_number(), which can be used to detect whether a field is a number or not.

looks_like_number is not perfect, but is pretty good.

The bare outline of a simple perl program to do what you want might look something like this:

#! /usr/bin/perl

use Scalar::Util qw(looks_like_number);

while(<>) {
  chomp;
  my @fields=split("\t");
  foreach my $f (0..scalar @fields-1) {
    if (looks_like_number($fields[$f])) {
      $fields[$f] += 42;
      $fields[$f] *= 7;
      $fields[$f] = sprintf("%.2f",$fields[$f]);
    }
  }
  print join("\t",@fields),"\n";
}

If given your sample data above as input, it prints this:

file name   size    owner    
file1.txt   380.41  root
file2.txt   295.21  user1
file3.txt   2016.00 user2
file4.txt   86709.00    root
file5.txt   441.00  user3
file6.txt   2016.00 user1
file name   owner   last modified   last accessed
text4.txt   root    383.11  388.71
text5.txt   user3   401.33  532.00
file1.txt   root    455.00  511.02

Here's another version of the script that uses Math::BigFloat for all calculations, rounding decimals to 2 digits.

#! /usr/bin/perl

use Scalar::Util qw(looks_like_number);
use Math::BigFloat;

while(<>) {
  chomp;
  my @fields=split("\t");
  foreach my $f (0..scalar @fields-1) {
    if (looks_like_number($fields[$f])) {
      my $BF = Math::BigFloat->new($fields[$f]);
      $BF->badd(42);
      $BF->bmul(7);
      $BF->ffround(-2);

      $fields[$f] = $BF->bstr();
    }
  }
  print join("\t",@fields),"\n";
}

example input:

file name   owner   last modified   last accessed
text4.txt   root    12.73   13.53
text5.txt   user3   15.3333 34
file6.txt   root    903709792518875002.42857142857142857142 903709792518875002
file7.txt   root    6659166111488656281486807152009765625   539422123247359763587428687890625

output:

file name   owner   last modified   last accessed
text4.txt   root    383.11  388.71
text5.txt   user3   401.33  532.00
file6.txt   root    6325968547632125311.00  6325968547632125308.00
file7.txt   root    46614162780420593970407650064068359669.00   3775954862731518345112000815234669.00

Lum – How to use column to delimit on tabs and not spaces

column -t -s '\t'

would separate columns on \ and t characters.

column -s \t is the same as column -s t, as the backslash is interpreted as a quoting operator by the shell.

Here you want to pass a real TAB character to column. With ksh93, zsh, bash, mksh, busybox sh or FreeBSD sh:

column -ts $'\t'

Or enter a real tab character by typing Ctrl-V Tab at the shell prompt (within quotes or preceded by a backslash as the tab character is a token separator in the shell syntax just like space), or use "$(printf '\t')" (those double quotes needed to disable the split+glob operator as the tab character also happens to be in the default value of $IFS).