1

I have file (file.bim) with about 1.5 mill. lines that looks like this:

1   1:819959:C:T    0   819959  T   C
1   1:821249:G:A    0   821249  A   G
1   1:821477:A:G    0   821477  G   A
1   1:821843:C:T    0   821843  T   C
1   1:823963:A:C    0   823963  C   A
1   1:824357:C:T    0   824357  T   C
1   1:824398:A:C    0   824398  C   A
1   1:827972:G:A    0   827972  A   G
1   1:828539:A:T    0   828539  T   A

In the second column I would like to remove the second colon and the three following characters, and keep the remaining columns as they are. The desired output is as following (updated_file.bim):

1   1:819959    0   819959  T   C
1   1:821249    0   821249  A   G
1   1:821477    0   821477  G   A
1   1:821843    0   821843  T   C
1   1:823963    0   823963  C   A
1   1:824357    0   824357  T   C
1   1:824398    0   824398  C   A
1   1:827972    0   827972  A   G
1   1:828539    0   828539  T   A

I tried using awk, but here only the colons are removed, but the letters between them remain:

awk -F":" '{ print $1":"$2,$3,$4,$5,$6 }' file.bim > updated_file.bim
0
2

You can use the following awk program:

awk '{sub(/:[^:]*:[^:]*$/,"",$2)}1' file.bim > updated_file.bim

or

awk '{sub(/(:[^:]*){2}$/,"",$2)}1' file.bim > updated_file.bim

This will use the sub() function to edit the second column ($2) by replacing the last two : and the text following them with "nothing", thus effectively removing that part.

If the output file should be tab-separated, use awk -v OFS="\t" ' ... '.

Alternatively, if your file is delimited by groups of more than one "space" and you want to ensure that the delimiter formatting is completely unchanged, you can use

awk '{sub(/:[^:]*:[^: ]* /," ")}1' file.bim > updated_file.bim

which will look for the pattern ":, followed by text, followed by :, followed by text, followed by space", and replace that one with a single "space". In your input, this pattern only occurs at the end of column 2, so that replacement will only affect that column.

Finally, if at any time in the future the number of :-separated fields in the second column can change, but you still only want to retain the first two, you can resort to my original albeit less portable variant, which replaces the 2nd column by only the text around the first : (instead of the text behind the last two with "nothing"):

awk '{$2=gensub(/([^:]+:[^:]+).*/,"\\1","1",$2)}1' file.bim > updated_file.bim
4
  • This worked as desired! Thank you.
    – jonandet
    Nov 19 '20 at 11:49
  • @jonandet You're welcome, glad it worked!
    – AdminBee
    Nov 19 '20 at 13:29
  • nbd but you could write /(:[^:]*){2}$/ instead of /:[^:]*:[^:]*$/.
    – Ed Morton
    Nov 19 '20 at 23:55
  • 1
    @EdMorton You are right of course (although it is only 1 character shorter ;) ). I had kept the notation because that program had "evolved" by minimal line-editing from my original gensub() approach ...
    – AdminBee
    Nov 20 '20 at 8:19
2

Remove everything after the second colon in the second column and keep everything else:

awk '{ c=split($2, s, ":"); $2=s[1] (c>1?":":"") s[2]; }1' infile

as you noticed this removes the repeated whitespaces when we re-evaluate the second column if that's not the big problem on your side; else use below sed as an alternative:

sed -E 's/^([^ ]* *)([^: ]*:[^: ]*):[^ ]* (.*)/\1\2 \3/' infile

test-date;

::1   1:81995:9:C:T    0   8::199:59  T   C
1:a:  :1821249GA:    0   821:2:4  A   G
111   1:828539::    0   :::828539  T   A

result:

::1   1:81995    0   8::199:59  T   C
1:a:  :1821249GA    0   821:2:4  A   G
111   1:828539    0   :::828539  T   A
1

With GNU sed for \S:

$ sed 's/\(:[^:]*\)\S*/\1/' file
1   1:819959    0   819959  T   C
1   1:821249    0   821249  A   G
1   1:821477    0   821477  G   A
1   1:821843    0   821843  T   C
1   1:823963    0   823963  C   A
1   1:824357    0   824357  T   C
1   1:824398    0   824398  C   A
1   1:827972    0   827972  A   G
1   1:828539    0   828539  T   A

or with any POSIX sed:

$ sed 's/\(:[^:]*\)[^[:space:]]*/\1/' file
1   1:819959    0   819959  T   C
1   1:821249    0   821249  A   G
1   1:821477    0   821477  G   A
1   1:821843    0   821843  T   C
1   1:823963    0   823963  C   A
1   1:824357    0   824357  T   C
1   1:824398    0   824398  C   A
1   1:827972    0   827972  A   G
1   1:828539    0   828539  T   A
2
  • 1
    this doesn't consider removing from the "second column" in case first column was containing one or more colons. example ::1 1:81995:9:C:T 0 8::199:59 T C Nov 20 '20 at 7:23
  • 1
    Absolutely right, it just works for the OPs data. There's no reason to think the first field will ever have colons given the posted example.
    – Ed Morton
    Nov 20 '20 at 14:33
0

Use sed to replace all empty spaces with ":" then dismiss $6 and $7 fields and put the content of $4 and $5 on $5 with awk :

sed  's/ /:/g' bim | awk  -F':' '{ $6=""; $7="" ; $4=$4$5; $5="" }1' > updated_file.bim 

WITHOUT SED :

awk  '{ gsub(/ /,":",$0); FS=":";$6=""; $7="" ; $4=$4$5; $5="" }1' bim > updated_file.bim 
4
  • Unfortunately, this will keep the letter after the last : in the second column.
    – AdminBee
    Nov 19 '20 at 11:22
  • I have made an edit :) I have used to replace spaces by ':' to keep the same width.
    – Reda Salih
    Nov 19 '20 at 11:38
  • this removes all colons from the input as well; test with 1 1:828539:AT:: 0 8:5:3:9:6:7:89:0 T A line as an example; also this will fail if number of whitespaces between columns were vary Nov 19 '20 at 11:59
  • I see i have added one without the use of sed :) Your method is fine ! An upvote from my side ! With your test case the output will be 1 1828539 0 8 5 3 9 6 7 89 0 T A But i think that's what it's wanted on the question !
    – Reda Salih
    Nov 19 '20 at 12:01
0

with awk first with substr($2, 1, 8) you select from $2 what you need. And print the value of s instead of $2. So your code with this can be:

awk 's = substr($2, 1, 8) {print $1, s, $3, $4, $5, $6}' file
1 1:819959 0 819959 T C
1 1:821249 0 821249 A G
1 1:821477 0 821477 G A
1 1:821843 0 821843 T C
1 1:823963 0 823963 C A
1 1:824357 0 824357 T C
1 1:824398 0 824398 C A
1 1:827972 0 827972 A G
1 1:828539 0 828539 T A

Updated, for keeping the spaces:

awk -F '[[:blank:]]{2,}' '$2 = substr($2, 1, 8) {print $1, $2, $3, $4, $5, $6}' file | column -t
1  1:819959  0  819959  T  C
1  1:821249  0  821249  A  G
1  1:821477  0  821477  G  A
1  1:821843  0  821843  T  C
1  1:823963  0  823963  C  A
1  1:824357  0  824357  T  C
1  1:824398  0  824398  C  A
1  1:827972  0  827972  A  G
1  1:828539  0  828539  T  A
3
  • This does not keep spaces :)
    – Reda Salih
    Nov 19 '20 at 12:11
  • @Reda, see updated. Perhaps now this can help. Nov 19 '20 at 12:39
  • Yes it's good )
    – Reda Salih
    Nov 19 '20 at 12:52
0

The following Output can be achieved using sed command

cat file.bim | sed 's/:[a-zA-Z]//g' >> updated_file.bim

or

cat file.bim | sed 's/:[[:alpha:]]//g' >> updated_file.bim

Output :

1   1:819959    0   819959  T   C
1   1:821249    0   821249  A   G
1   1:821477    0   821477  G   A
1   1:821843    0   821843  T   C
1   1:823963    0   823963  C   A
1   1:824357    0   824357  T   C
1   1:824398    0   824398  C   A
1   1:827972    0   827972  A   G
1   1:828539    0   828539  T   A
0

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.