remove everything before a character in a column

Question

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?

Answer 1

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 

Answer 2

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.

Answer 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).

Answer 4

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

---

Answer 5

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/'

Answer 6

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