Awk/sed to separate last column and add quotes around middle column?

Question

I've got a file called test.txt and after some manipulations it looks like this:

Metabolism
Global and overview maps
01100 Metabolic pathways (1689)
01110 Biosynthesis of secondary metabolites (677)
01120 Microbial metabolism in diverse environments (356)
01200 Carbon metabolism (44)
012111 Carbon metabolism (151) test: test test (44)

Now, I want to separate the last column with bracketed numbers so that they are a separate column (using semi colons as my delimiter of choice). I also want to put quotes around all text that are between the bracketed numbers and the ID numbers at the start. Finally, I would like to keep the header rows (the first two in this example).

My code:

 sed -r 's/ +/;/' test.txt | awk 'NF{NF-=1};1' | awk -F ";" '{sub($2, "\"&\""); print}'

My current output:

""
Global;"and overview"
01100;"Metabolic pathways"
01110;"Biosynthesis of secondary metabolites"
01120;"Microbial metabolism in diverse environments"
01200;"Carbon metabolism"
012111;Carbon (151) test: test test

As you can see the "Metabolism" header is gone because it is technically the last value in that row, as well as "maps" in the second row, with a semi colon after "Global" which is not needed. Some rows have bracketed numbers within the text which I should keep but otherwise all rows end with a bracketed value that should be separated into it's only column separated by a semi colon. I also can't get the quotes to go around all of the second column in the last row, whereas the other rows are okay. Finally, I don't know how to separate the bracketed values so that they are a third column.

My desired output (keeping the numbers as a sep column):

"Metabolism"
"Global and overview"
01100:"Metabolic pathways";1689
01110:"Biosynthesis of secondary metabolites";677
01120:"Microbial metabolism in diverse environments";356
01200:"Carbon metabolism";44
012111:"Carbon metabolism (151) test: test test";44

using awk GNU version 4.1.3 and sed GNU version 4.2.2. on Windows Linux Sub-system

Answer 1

$ cat file
Metabolism
Global and overview maps
01100 Metabolic pathways (1689)
01110 Biosynthesis of secondary metabolites (677)
01120 Microbial metabolism in diverse environments (356)
01200 Carbon metabolism (44)
012111 Carbon metabolism (151) test: test test (44)

$ sed -e 's/^\([[:digit:]]*\)[[:blank:]]*/\1;"/' -e 's/[[:blank:]]*\((\([[:digit:]]*\))\)\{0,1\}[[:blank:]]*$/";\2/' file
;"Metabolism";
;"Global and overview maps";
01100;"Metabolic pathways";1689
01110;"Biosynthesis of secondary metabolites";677
01120;"Microbial metabolism in diverse environments";356
01200;"Carbon metabolism";44
012111;"Carbon metabolism (151) test: test test";44

The sed command used here makes two substitutions:

• s/^\([[:digit:]]*\)[[:blank:]]*/\1;"/
  This replaces a possibly empty run of digits at the start of the line followed by zero or more blanks (tabs or spaces) with the digits and a semi-colon. If there were no digits at the start of the line, a semi-colon will be inserted at the start of the line. It also inserts the first double quote character for the second field after the semi-colon.

• s/[[:blank:]]*\((\([[:digit:]]*\))\)\{0,1\}[[:blank:]]*$/";\2/
  This matches any number of digits, their flanking parenthesis, and the initial blanks at the end of the line, if such a parenthesis with digits exists. It also allows for extra blanks at the very extreme end of the line. It replaces the matched text with only the matched digits. The inserted digits are preceded by the second double quote for the second field and the ; delimiter.

  Would you want to remove the digits in the last field, then just modify the replacement text of this second sed expression (probably to just " rather than ";\2).

The sed command

sed -e 's/^\([[:digit:]]*\)[[:blank:]]*/\1;"/' \
    -e 's/[[:blank:]]*\((\([[:digit:]]*\))\)\{0,1\}[[:blank:]]*$/";\2/' file

could be rewritten using extended regular expression and shorter bracket expressions (if we assume we only want to match spaces and no tabs):

sed -E \
    -e 's/^([0-9]*) */\1;"/' \
    -e 's/ *(\(([0-9]*)\))? *$/";\2/' file

Answer 2

With GNU sed:

sed -e '1,2{p;d}' -e 's/ /;"/' -e 's/ ([[:digit:]]\+)$/"/' input

or more compatible:

sed -e '1{p;d;}' -e '2{p;d;}' -e 's/ /;"/' -e 's/ ([[:digit:]]\{1,\})$/"/' input

Answer 3

Using GNU sed in extended regex mode -E where we identify header lines as those which don't end with bracketed numbers. Assuming:

• no leading/trailing whitespace.
• no consecutive whitespace.
• input has Unix line endings (\n)

sed -Ee '
  /\s\(([0-9]+)\)$/!s/.*/;"&";/;t
  s//";\1/;s/\s/;"/
' file
;"Metabolism";
;"Global and overview maps";
01100;"Metabolic pathways";1689
01110;"Biosynthesis of secondary metabolites";677
01120;"Microbial metabolism in diverse environments";356

---

perl -lpe '
  s/\s\K\((\d+)\)$/$1/ ?
    s/\s(.*)\s/;"$1";/ :
    s/(.*)/;"$1";/     ;
' file

---

Usingperl we could do as follows (same assumptions as above).

• store the first n last fields in scalars $a $b , respectively, only for lines ending in bracketed numbers.

perl -slane 'local($a,$b);
  ($a,$b) = (shift(@F),pop(@F))
    if /\s\(\d+\)$/;
  print $a, qq("@F"), $b =~ tr/()//dr;
' -- -,=\; file

---

awk '
$NF ~ /^\([0-9]+)$/ &&
p = match($0,/ .* /) {
  l = length($NF)
  mid = substr($0, p, RLENGTH)
  gsub(/^ | $/, "\"", mid)
  print $1, mid, substr($NF, 2, l-2)
}
!p&&(sub(/.*/, ";\"&\";")||1)
' OFS=\; file

Answer 4

If all you want to do is only delete the "(numbers)" at the end of the line:

sed 's|\(^.*\) ([[:digit:]]*)$|\1|g' test.txt

will give you:

Metabolism  # this header I need to keep
Global and overview maps  # this header I need to keep
01100 Metabolic pathways
01110 Biosynthesis of secondary metabolites
01120 Microbial metabolism in diverse environments