1

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

6
  • Where are the last two lines with Carbon metabolism coming from. The command that you are using gives a syntax error in several versions of sed so I don't see how it's producing the output that you have. What OS is this and what version of sed and awk? Are they the GNU versions? Nov 28, 2021 at 15:26
  • I'm using Windows Linux sub-system and the awk I have is version 4.1.3 and sed version is 4.2.2. These commands work for me
    – KBwonder
    Nov 28, 2021 at 15:41
  • My sed and awk are GNU versions
    – KBwonder
    Nov 28, 2021 at 15:47
  • Then you've typed it incorrectly because that command does not produce the output that you have. Like it says, the sed command has a syntax error. It needs to be sed -r 's/ +/;/' test.txt whereas you have sed -r 's/ +/;' test.txt which doesn't work with any version of sed. Nov 28, 2021 at 15:52
  • Do you want to remove the numbers in parentheses at the end of the lines, or do you want to make a separate column with these?
    – they
    Nov 28, 2021 at 15:53

4 Answers 4

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
7
  • This command did not give the same output as yours. For me, the two header rows renamed but only had the semi colon in the front of them (which honestly is okay with me I am glad the rows remained in full text). But only the last row separated the bracketed value of (44) into its own column, all the other ones remained. Quotes only remained at the start of the text in column two, there were no closing double quotes.
    – KBwonder
    Nov 29, 2021 at 14:00
  • @KBwonder Do you have spaces at the very end of the lines in the input? If so, I can easily accommodate for that. (EDIT: See updated code in the answer now.)
    – they
    Nov 29, 2021 at 14:18
  • Nope there are no whitespaces after the bracketed numbers or the header rows.
    – KBwonder
    Nov 29, 2021 at 14:58
  • The edited command you made worked for the sample file I had but for some reason when I use the longer file (With the exact same style as my file.txt example), the sed command fails to work. Let me tinker with it a bit more...
    – KBwonder
    Nov 29, 2021 at 15:00
  • Okay your code still works - I had to use dos2unix on my larger files for the GNU sed to work properly again.
    – KBwonder
    Nov 29, 2021 at 15:04
1

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
4
  • 1
    The "more compatible" variant is still GNU only though as you would need to use {p;d;} in place of {p;d} and as \+ is not a BRE (you would use \{1,\} in a BRE instead of +). The command also fails to insert ; at the start of the first two lines.
    – they
    Nov 29, 2021 at 10:27
  • The first command half worked, it wouldn't add quotes to the end of the lines and did not get separate the bracketed numbers. It did leave the first two header rows though which was good. The second command strangely gave me the exact same output as the first one.
    – KBwonder
    Nov 29, 2021 at 13:59
  • @they The desired output does not have a ; in the first two lines. My BRE knowledge is from the grep man page and it does mention \+ but maybe that is GNU stuff without being mentioned. Nov 29, 2021 at 14:53
  • Ah, I see that the output in the question changed some time ago, and I did not re-read it. My apologies. Yes, the GNU manuals sometimes fail to mention extensions.
    – they
    Nov 29, 2021 at 14:55
1

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
-3

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
1
  • 1
    This does not seem to be the same as the expected output.
    – they
    Nov 29, 2021 at 10:29

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.