4

I have a table like this

start end chr
  1   10  H300Chr01
  10  50  H500Chr02

I would like to replace everything before "Chr" in column3.

My desired output would be

start end chr
  1   10  Chr01
  10  50  Chr02

I know that sed can do things like this:

sed 's/^.*Chr/Chr/' table.txt

Could you please tell me how I could achieve this for a specific column or a couple of columns?

1
  • 2
    > ".... or couple of columns?"- can you show an example of that case too? leading whitespaces matters? or even number of whitespaces in between fields? Apr 16 at 14:13

6 Answers 6

6

With some (all?) awk implementations, certainly the GNU awk and the mawk I have on my Arch Linux system, you can set the field separator to a regular expression which makes awk keep the original separator of the file. To illustrate:

$ awk '{$1=$1;print}' file
start end chr
1 10 H300Chr01
10 50 H500Chr02

$ awk -F'[ ]' '{$1=$1;print}' file
start end chr
  1   10  H300Chr01
  10  50  H500Chr02

With this in mind, we can make the change to the last (I say last and not third because this approach redraws the line and the numbering changes depending on the number of spaces) field without altering the spacing with something like this:

$ awk -F'[ ]' '{sub(/.*Chr/,"Chr",$NF);}1' file
start end chr
  1   10  Chr01
  10  50  Chr02

Alternatively, you can do it with perl:

$ perl -pne 's/(\s*\S+\s+\S+\s+)\S+(Chr)/$1$2/;' file 
start end chr
  1   10  Chr01
  10  50  Chr02

Or, to ensure you only match the first occurrence of Chr in the third field, in case there are more than one:

perl -pe 's/(\s*\S+\s+\S+\s+)\S+?(Chr)/$1$2/;' file 
4
  • 1
    +1, but why both -p and -n ? I think that -p should suffice Apr 16 at 14:20
  • 1
    Thanks, @DanieleGrassini, the -n was left over from when I was trying some different approaches using -lane.
    – terdon
    Apr 16 at 17:55
  • 1
    Using awk -F'[ ]' just for formatting, is changing the number of input fields. So you will have trouble when scaling this oneliner for more columns or for an inner column. column -t should be prefered or defining OFS instead of changing the input content, to keep formatting separated from content.
    – thanasisp
    Apr 17 at 8:03
  • 2
    @thanasisp yes, that's why I point out in the answer that it changes the number of fields and why I used $NF and why I provided a perl solution as well. Neither defining OFS nor column -t would work, unfortunately, since we have leading spaces
    – terdon
    Apr 17 at 11:47
3

For a specific column:

awk '{sub(/.*Chr/,"Chr",$3); print}' file

The first argument of sub is the pattern to match, the second is the replacement and last is the target. The outcome is stored to the target. See also awk string functions.

And this is expandable for more columns, example:

awk '{sub(/.*Chr1/,"Chr1",$1); sub(/.*Chr2/,"Chr2",$2); print}' file

Note that matching is greedy as expected, which is the desired behaviour for your data.

To format the output, it's probably more convenient than defining OFS to pipe the output to column -t, for a tab-like pretty-printing with actual spaces, no tabs.

2
  • This answer does not give the exact output required by the original question. This answer mangles the output by compressing multiple spaces and removing leading spaces, and then suggests using other programs to try to restore formatting similar to (but not identical to) the actual output required. The answer by terdon is better. Apr 25 at 7:21
  • You can use GNU awk printf function and achieve any output you 'd like. Also, please have in mind that keeping the content separated from formatting is a basic principle.
    – thanasisp
    May 1 at 12:24
3

This might work for you:

< file awk '{p=index($3,"Chr"); $3=substr($3,p); print}' | column -t

This looks for the substring "Chr" in the third field, and sets the third field to the remainder starting with Chr. (If "Chr" is not found, the whole field remains.)

Unfortunately, awk merges whitespace when you change part of the line. column -t lines up the columns again.

That results in

start  end  chr
1      10   Chr01
10     50   Chr02

(Notice the columns have changed alignment.)

If that's not good enough, we can adapt this answer on StackOverflow by Håkon Hægland:

awk '{n=split($0,fields," ",seps); p=index(fields[3],"Chr"); fields[3]=substr(fields[3],p); line=seps[0]; for (i=1; i <=n; i++) { line=(line fields[i] seps[i]); } print line; }' file

which is probably worth putting in its own awk script, instead of running on the command line.

#!/usr/bin/awk -f
{
    n = split($0, fields, " ", seps)
    p = index(fields[3], "Chr")
    fields[3] = substr(fields[3], p)
    line = seps[0]
    for (i=1; i <=n; i++) 
        line=(line fields[i] seps[i])
    print line
}

If that's saved in say coledit.awk and made executable, then running ./coledit.awk file results in your desired output (with original whitespace unchanged).

2
  • 1
    why use < file when awk already has the ability to open file?
    – phuclv
    Apr 17 at 10:52
  • 1
    @phuclv some people like seeing the name of the file first on the command line so this is better than cat file | awk... for those people and it allows the shell to open the file instead of the command so you get consistent file-opening-failure error messages if you use that idiom for all commands (including those like tr that can't open a file themselves) and on a file opening failure < file awk '...' > output won't create output create an empty (or overwrite if it exists) file named output . The downside is you can't do that for opening multiple files and you lose FILENAME access.
    – Ed Morton
    Apr 17 at 12:00
3

Using Perl

perl -pe 's/\G\S*?(?=Chr)//
  if m/(?:\S+\s+){2}/cg;
' file

POSIX sed

sed '
  s/[^[:space:]]\{1,\}/\
&/3
  s/\n[^[:space:]]*Chr/Chr/
  s/\n//
' file

Since sed doesn't support non greedy regexes we will implement it using it's constructs in GNU sed in extended regex mode.

sed -E '
  s/\S+/\n&\n/3
  /\n.*Chr.*\n/ta
  s/\n//g;b
  :a
  s/(\n.*)(Chr.*)\n/\1\n\2/
  ta
  s/\n.*\n//
' file

awk while preserving formatting.

s="[[:space:]]" S="[^[:space:]]"

re="^$s*($S+$s+){2}" \
awk -v ss="Chr" '
BEGIN { re = ENVIRON["re"] }
match($0,re) && (p=index($3,ss)) {
  l = RLENGTH
  $0 = substr($0,1,l) \
       substr($0,p+l) ;
}1' file

0

In sed the \s is for white-space characters and \S is for non-white-space. Then the * is just any number of repetition.

sed 's/\S*Chr/Chr/'
4
  • 3
    This isn't targeting the third column, it will replace the first occurrence of non-space followed by Chr no matter where it is. Also, the \s and \S aren't part of the sed specification, while many sed implementations do support them, you can't be sure that all will.
    – terdon
    Apr 16 at 12:39
  • @terdon It seem really unlikely that the first and the second columns have a non-numeric data inside, since they are named start and end, and have only numeric values in the sample showed. Apr 16 at 14:26
  • 1
    @niklassc \S is not standard (as said by @terdon ). If you replace \S with [^[:space:]] your sed will be portable to other sed, while still catch both spaces and tabs. Apr 16 at 16:39
  • 3
    @DanieleGrassini yes, it is unlikely. But the question specified "third column" so I felt it was worth pointing out. Also, the standard format for this sort of data, called a BED file, does have the chromosome as the first field, so it's not inconceivable the OP or the next user to find the question will try to use the solution somewhere else.
    – terdon
    Apr 16 at 17:50
0

Using Raku (formerly known as Perl_6)

raku -ne 'given .words() { put .[0..1] ~ " " ~ .[2].subst: /^ <(.*?)> Chr .* $/ };'

OR

raku -ne 'put .[0..1] ~ " " ~ .[2].subst: /^ <(.*?)> Chr .* $/ given .words;'

Above are answers coded in Raku, a member of the Perl family of programming languages. Briefly, data is read in with the -ne linewise non-autoprinting flags. Input is broken on whitespace using .words, and the resulting array of elements is addressed using the .[0..1] or .[2] postfix index brackets. The .[0..1] (first and second columns) are out-put directly, while the third column is .subst substituted such that any characters seen before the first occurrence of Chr are deleted.

Regarding the regex above: the <(…)> match delimiters ensure that while the entire column is matched between /^ … $/, the elements outside of <(…)> are dropped from the final match object. [ Then, using subst without a replacement instructs Raku to delete the match object, in this case, the <(.*?)> non-greedy regex match before Chr].

Sample Input:

start end chr
  1   10  H300Chr01
  10  50  H500Chr02

Sample Output:

start end chr
1 10 Chr01
10 50 Chr02

The code above seems pretty mechanical, and you can get prettier results (i.e. nicely aligned columns) by piping your output through column -t, as suggested by @NickMatteo. Alternatively a more powerful approach might be to first convert your file to CSV via Raku's Text::CSV module. See link below.

https://github.com/Tux/CSV/blob/master/doc/Text-CSV.pdf (click the Download link)
https://raku.org

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.