How do I extract the first integer from text string in a column of a tab-delimited file?

Question

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.

Answer 1

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

Answer 2

Using @John1024's sample text, this is GNU-awk-specific

gawk -F '\t' -v OFS='\t' 'match($5, /[[:digit:]]+/, m) {$(++NF) = m[0]} 1' file

produces

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

Or perl

perl -F'\t' -lane 'print join "\t", @F, $F[4]=~/(\d+)/' file

Answer 3

glenn jackman's GNU/AWK answer is elegant but a little more simply is

awk 'BEGIN {FS=OFS="\t"} match($5,/[0-9]+/,arr) {print $0,arr[0]}' file

Answer 4

With sed you can substitute by occurrence - so you just ask for the fifth <\tab>-delimited ^[1] field and for any numbers within it by ruling out other possible matches:

sed 's/[^\t0-9]*\([0-9]*\)[^\t]*/\1/5' <infile

After doing a copy to my clipboard of the other examples here I did:

xsel -bo | unexpand -a | sed ...

...to unexpand -all <tab>-sized space sequences into an actual <tab>. And it printed...

1   2   3   4   2458    6
a   b   c   d   45
a1  b2  c3  d4  78  f6

...which just isolates the first integer in the 5th column. I'm not sure if that's what you want, though. If you just want the first integer from the fifth column on a line all its own, that's far easier (and much faster).

<infile \
 cut -f5 | tr -cs '0-9\n' \\t |
 expand -t1,2,4 | cut -d' ' -f-2

...which first cuts the fifth <tab>-delimited ^[2] field of data per line in full (to avoid issues which may be caused by multiple integers per field) and then translates into a single <tab> every -squeezed sequence of characters -complementary to the set of \newlines and 0-9 standard digits ^[3].

This means that in the output the first integer will be in either the first or second field - because the first field is now either empty (led by a <tab>) or your digit sequence depending on whether it was prefixed as you note. So I expand the 1st and 2cd <tab>-stop positions on a line to a single space a piece, and the third to spaces - which effectively pads out a list of space-delimited fields into having either an empty first field or an empty third field. From there I can just cut out the first two fields.

 2458
 45
 78

...were my results for the example I used because they were all led by [cp]. and so all had leading <tab> s but those without would be staggered to the left. To additionally condense all results to a single line with each integer separated by a single space you can just append |xargs to the command and get instead:

2458 45 78

Notes

1. Beware that the \t escape is not a standard one where sed is concerned - and in the context of a [bracket-expression] character class it is arguably even explicitly contrary to the standard as the \backslash and t characters should each represent themselves there. I have used the escape here to more clearly demonstrate a readable intent - but you should probably use a literal <tab> in its place.

2. cut delimits on <tab> characters by default, and so in this case the common -d [delim-char] option is unnecessary - but also added this note to explain why.

3. As is noted in the link, the POSIX-standard requires that the [:digit:] character class include the 0123456789 characters in all locales and in that sorting order and sorted ahead of any other inclusions in that class. Non C-locales may also include other localized numeral sets - which a GNU tr probably will not handle appropriately as they are likely represented by multiple bytes - but only the standard numeral set is more likely the least surprising result in most cases anyway, and so using [:digit:] unless you definitely want to match characters in both the standard Arabic numeral set and some other locale-dependent set of numerals is probably not advisable.