1

Here's a sample of my source file:

USC00020958       -7777S   656S   721S   803S   891S   975S   972S   941S   906S   809S   695S   605S
USC00021001         378S   389R   448R   522R   628R   736S   774S   742S   674S   562S   454R   376R
USC00021026         689R   729R   792R   873R   969R  1060R  1082R  1068R  1015R   903R   775R   682R
USC00021050         663S   713S   796S   885S   984S  1079S  1122S  1103S  1036S   904C   754S   651S
USC00501466         -77R     8R   168R   408R   593R   723R   733R   660R   529R   273R    54R    -7R
USW00501492         -43R    18R   132R   308R   510R   666R   687R   605R   466R   239R    45R    12R
USW00501497           3Q    -1Q     0P   203Q   388Q   539Q   558Q   493Q   362Q   175Q    51Q    27Q
USW00501684        -118R     0P   211R   413R   573R   700R   710R   644R   520R   294R    35R   -67R
USW00501926         184S   223S   303S   418S   544S   651S   661R   617S   519S   353S   227S -7777S

I'd like to remove C,S,R,P,Q from the end of 1-4 Digits, while preserving negative (-) numbers, and without modifying the first column (USxxxxxxxxx) in this example). I'd also like to change the formatting to have a single space (or comma) between each value. Here's what I tried, but it's not producing the results I was hoping for:

[user@host1 ~]# cat sample.txt | sed -E 's/(\S+)\s+(.[0-9]{1,4})[C,S,R,P,Q]/\1 \2/g'
USC00020958 -7777   656S 721   803S 891   975S 972   941S 906   809S 695   605S
USC00021001 378   389R 448   522R 628   736S 774   742S 674   562S 454   376R
USC00021026 689   729R 792   873R 969  1060R 1082  1068R 1015   903R 775   682R
USC00021050 663   713S 796   885S 984  1079S 1122  1103S 1036   904C 754   651S
USC00501466 -77     8R 168   408R 593   723R 733   660R 529   273R 54    -7R
USW00501492 -43    18R 132   308R 510   666R 687   605R 466   239R 45    12R
USW00501497  3    -1Q  0   203Q 388   539Q 558   493Q 362   175Q 51    27Q
USW00501684 -118     0P 211   413R 573   700R 710   644R 520   294R 35   -67R
USW00501926 184   223S 303   418S 544   651S 661   617S 519   353S 227 -7777S
2

A couple things I noticed about your regular expression:

  1. When specifying a character class, you do not use commas between letters. You can use dashes to specify ranges, but in this case, instead of [C,S,R,P,Q], it should be [CSRPQ].
  2. sed doesn't require that you match the entire line, so you can just build a pattern to match the numbers with the trailing letter, and then just substitute it for the number by itself (dropping the letter).

I took the input file that you provided and tested out the following sed command:

sed -E 's/([0-9]+)[CSRPQ]/\1/g' sample.txt

This yields the following output:

USC00020958       -7777   656   721   803   891   975   972   941   906   809   695   605
USC00021001         378   389   448   522   628   736   774   742   674   562   454   376
USC00021026         689   729   792   873   969  1060  1082  1068  1015   903   775   682
USC00021050         663   713   796   885   984  1079  1122  1103  1036   904   754   651
USC00501466         -77     8   168   408   593   723   733   660   529   273    54    -7
USW00501492         -43    18   132   308   510   666   687   605   466   239    45    12
USW00501497           3    -1     0   203   388   539   558   493   362   175    51    27
USW00501684        -118     0   211   413   573   700   710   644   520   294    35   -67
USW00501926         184   223   303   418   544   651   661   617   519   353   227 -7777

The command is matching any number that has a trailing [CSRPQ] and then stripping the letter.

Also, if you want to remove all of the spaces and replace with a comma to turn this into a CSV, sed lets you chain expressions. However, when specifying multiple scripts, you need to put -e in front of them to tell sed that a script is going to follow. From the help:

Usage: sed [OPTION]... {script-only-if-no-other-script} [input-file]...

  -e script, --expression=script
                 add the script to the commands to be executed

Here is an example:

sed -E -e 's/([0-9]+)[CSRPQ]/\1/g' -e 's/\s+/,/g' sample.txt

This says run the first substitution, then run the second substitution, replacing repeating space characters with a single comma. This yields the following output:

USC00020958,-7777,656,721,803,891,975,972,941,906,809,695,605
USC00021001,378,389,448,522,628,736,774,742,674,562,454,376
USC00021026,689,729,792,873,969,1060,1082,1068,1015,903,775,682
USC00021050,663,713,796,885,984,1079,1122,1103,1036,904,754,651
USC00501466,-77,8,168,408,593,723,733,660,529,273,54,-7
USW00501492,-43,18,132,308,510,666,687,605,466,239,45,12
USW00501497,3,-1,0,203,388,539,558,493,362,175,51,27
USW00501684,-118,0,211,413,573,700,710,644,520,294,35,-67
USW00501926,184,223,303,418,544,651,661,617,519,353,227,-7777

Hope this helps!

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.