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

Improve this question
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? Commented 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 Commented Nov 28, 2021 at 15:41
  • My sed and awk are GNU versions Commented 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. Commented 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? Commented Nov 28, 2021 at 15:53

4 Answers 4

Reset to default
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
Improve this answer
6
  • 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. Commented 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.) Commented Nov 29, 2021 at 14:18
  • Nope there are no whitespaces after the bracketed numbers or the header rows. Commented 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... Commented 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. Commented 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
Improve this answer
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. Commented 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. Commented 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. Commented 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. Commented 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
Improve this answer
-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
Improve this answer
1
  • 1
    This does not seem to be the same as the expected output. Commented Nov 29, 2021 at 10:29

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.