6

I have a tab delimited file with a combination of text and numbers. I want to keep each line as is, but I want to keep only the six digit numbers in the 5th column if present. For example:

gene1   NM_033629   598G>A  P912    syndrome  1, 192315 syndrome 2,  225750 syndrome 3 610448   score   AD  hom user    123456  Source
gene2   NM_000459   613G>A  V115I   syndrome 1 600195   score   AD  rec user    234567  Source

(Syndrome # is used as an example, this can be any text so not a pattern I can search and remove)

I want the output to be:

gene1   NM_033629   598G>A  P912    192315 225750 610448    score   AD  hom user    123456  Source
gene2   NM_000459   613G>A  V115I   600195  score   AD  rec user    234567  Source

I have 4 ways to extract the 6 digit number, however, I cannot

a. output the number in the line it originated from

b. successfully print the entire line with the one edited field. The options I have used to extract the digits are:

cat inputfile | cut -f 5 |grep -P '(? < !\d)\d{6}(?!\d)'
cat inputfile | cut -f 5 |grep -Po '(?< !\d)\d{6}(?!\d)'
cat inputfile | cut -f 5 |grep -o '[[:digit:]]*'
cat inputfile | cut -f 5 |grep -o "[0-9]\{6\}"

I know using cut for the column is incorrect but I wanted to ensure I had the extract correct as there is also a six digit number in field 9. I'm stuck on putting this all together. Thanks in advance for any suggestions

2 Answers 2

3

If I understand correctly, you want the 5th column to become the concatenation with space of all the 6 digit numbers in it.

Maybe:

perl -F'\t' -lape '
   $F[4] = join " ", grep {length == 6} ($F[4] =~ /\d+/g);
   $_ = join "\t", @F' < file

Or reusing your negative look around operators:

perl -F'\t' -lape '
   $F[4] = join " ", ($F[4] =~ /(?<!\d)\d{6}(?!\d)/g);
   $_ = join "\t", @F' < file

With awk:

awk -F'\t' -v OFS='\t' '
  {
    repl = sep = ""
    while (match($5, /[0-9]+/)) {
      if (RLENGTH == 6) {
        repl = repl sep substr($5, RSTART, RLENGTH)
        sep = " "
      }
      $5 = substr($5, RSTART+RLENGTH)
    }
    $5 = repl
    print
  }' < file

grep itself is not very adequate for the task. grep is meant to print the lines that match a pattern. Some implementations like GNU or ast-open grep, or pcregrep can extract strings from the matching lines, but that's quite limited.

The only cut+grep+paste approach I can think of that could work with some restrictions would be with the pcregrep grep implementation:

n='(?:.*?((?1)))?'
paste <(< file cut -f1-4) <(< file cut -f5 |
  pcregrep --om-separator=" " -o1 -o2 -o3 -o4 -o5 -o6 -o7 -o8 -o9 \
    "((?<!\d)\d{6}(?!\d))$n$n$n$n$n$n$n$n"
  ) <(< file cut -f6-)

That assumes that every line of input has at least 6 fields and that the 5th field of each has in between 1 and 9 6-digit numbers.

2
  • Hi, thanks for your feedback. I have not yet had the opportunity to tese however, the output for line 1 field 5 should read: 192315 225750 610448
    – DemosW
    Oct 11, 2017 at 18:17
  • I tested the perl solution and it delivered the desired output. Thanks!
    – DemosW
    Oct 12, 2017 at 21:59
0
awk '
BEGIN {
    FS = "\t";
    OFS = "\t";
}
{
    cnt = patsplit($5, arr, /[0-9]{6}/);
    $5 = arr[1];
    for(i = 2; i <= cnt; i++) {
        $5 = $5 " " arr[i];
    }
    print;
}' input.txt

patsplit(s, a [, r [, seps] ]) - Split the string s into the array a and the separators array seps on the regular expression r, and return the number of fields. Element values are the portions of s that matched r.

Input:

gene1   NM_033629   598G>A  P912    syndrome 1, 192315 syndrome 2, 225750 syndrome 3 610448 score   AD  hom user    123456  Source
gene2   NM_000459   613G>A  V115I   syndrome 1 600195   score   AD  rec user    234567  Source

Output:

gene1   NM_033629   598G>A  P912    192315 225750 610448    score   AD  hom user    123456  Source
gene2   NM_000459   613G>A  V115I   600195  score   AD  rec user    234567  Source
5
  • 1
    That would take groups of 6 digits from numbers with more than 6 digits. Oct 12, 2017 at 5:27
  • 1
    Note that patsplit is GNU specific Oct 12, 2017 at 5:28
  • @StéphaneChazelas I think, the pattern like /\b[0-9]{6}\b/ should solve the problem with taking groups from numbers with more than 6 digits. Not tested, though. "patsplit is GNU specific" - yes, it is assumed that gawk will be used.
    – MiniMax
    Oct 12, 2017 at 9:39
  • 1
    gawk supports \<, and \> but not \b (as \b is backspace). That would also not do what we want for input like ABC123456DEF. You could search for /[0-9]+/ and filter out the matches that are not of length 6 like in my answer. Oct 12, 2017 at 11:21
  • Thank you! This discussion is very educational, I will also look at this solution.
    – DemosW
    Oct 12, 2017 at 22:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .