3

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.

  • Is or part of the text of the file, or are these 6 different examples of what the column might contain? It would help if you gave a few examples of full lines. – Gilles Apr 5 '15 at 23:10
  • Is there some "specification" of this data format? Which program uses this format? If it is just the first integer, and it is always present, then this is probably easy to do (say, sed -e 's/\([^[:digit:]]*\([[:digit:]]*\).*\)/\2;\1/', although I've probably missed some simpler way to write it) – njsg Apr 5 '15 at 23:31
  • Sorry @Gilles, I should have supplied an example table: – minnimalist Apr 7 '15 at 2:21
4

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
  • I want to wrap the gawk solution into a script like: #!/bin/bash echo read -p "Please enter your file name: " infile echo read -p "Please enter the column number with the mutations: " column echo read -p "Please enter a file name for the results: " outfile gawk -F '\t' -v OFS='\t' 'match($column, /[[:digit:]]+/, m) {$(++NF) = m[0]} 1' $infile > $outfile How do I pass a user defined variable like "$column" to gawk for the column to match? – minnimalist Feb 16 '16 at 5:14
  • Like this : gawk - F '\t' -v OFS='\t' -v col="$column" 'match($col, ... - - you should add some verification the column variable is a number since awk handles non-number looking strings like the number zero so you could be matching against $0 – glenn jackman Feb 16 '16 at 11:34
4

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
  • This also requires GNU awk for the three argument match() function – glenn jackman Feb 16 '16 at 11:39
3

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
1

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.

  • 1
    @don_crissti - well, if you're interested, the austin group has marked as accepted a request for standardizing tail -r, and so probably you'll be able to do that thing I remember you asking about for tac like tail -rn [num-reversed-lines] sooner than later. – mikeserv Apr 6 '15 at 18:04
  • @don_crissti - this? That one was because the asker was actually using csh on an old NeXTstep system - though it wasn't apparent at first. In a csh shell the ! can only be escaped with a backslash. – mikeserv Apr 9 '15 at 12:15
  • @don_crissti - I was wondering if you'd try this out? It's still pretty rough, like I haven't yet got --repeat ironed out to keep all of the args in order - but null_paste --head --sep 'any string' -ts'any string' file1 file2 should get the -s strings applied for every line in order for all named file arguments. --head or --tail or -[ht] will get the string at the head or tail of each line, and repeated occurrences of both or either will get it repeated as many times as they appear. Change the bangline to /bin/sh -xf maybe. – mikeserv Apr 9 '15 at 12:32
  • @don_crissti - yeah, back. I was banned for a week. I'd almost forgotten about that script. I have to look at it again... Strange you get an endless loop though - it seems weird that I would ask you look at it without at least verifying it didn't do that. I know that at one point it didn't - I was able to use it. I know also that before I quit playing with it a coupla weeks ago I had broken it pretty severely. I didn't think I altered the ver online though. I'll look again... – mikeserv Apr 20 '15 at 2:24

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.