0

my file.txt looks like this

variant_id pval_nominal
1_752721_A_G_b37 2.23485e-05
1_900397_C_T_b37 3.04603e-05
1_928297_G_A_b37 2.12455e-05

I am trying to remove everything after the 2nd underscore in the first column so that it looks like this:

variant_id pval_nominal
1_752721 2.23485e-05
1_900397 3.04603e-05
1_928297 2.12455e-05

The reason why I ask everything after the 2nd underscore in the first column to be removes is that instances in the first column can look like this: 1_1025672_GCA_G_b37

I was trying to use this command:

 awk -F _ '{print $1 (NF>1? FS $2 : "")}'  file.txt > file2.txt

but file2.txt looks like this:

variant_id pval
1_752721
1_900397
1_928297

How to run this command so that 2nd column is returned as well?

Thanks

  • (1) Your sample data follow a very rigid pattern.  Each line has two “words”, separated by a single space.  The first word is always 1_, six digits, _, a capital letter, another _, another capital letter, and _b37.  The second word is always a non-zero digit, a ., five digits, and e-05.  Do all the lines in your real data follow that pattern?  Why do you say “keep the other columns” when there’s always only one other column?  Please say what your data look like, and what features answerers are allowed to assume. … (Cont’d) – Scott Jul 16 at 2:44
  • (Cont’d) …  (2) You say ‘‘remove everything after the 2nd underscore (in the first column)’’, but you mean ‘‘remove the 2nd underscore and everything after it’’. Please be precise. – Scott Jul 16 at 2:44
  • Hi Scott, this is just part of the file, the pattern is not always the same. So the first column has sometimes instances like this: 1_1025672_GCA_G_b37 . That is why I thought the safest would be just to remove everything after that 2nd underscore in the first column. – anikaM Jul 16 at 3:39
0

Try this,

sed 's/_[A-Z].* / /g' file

variant_id pval_nominal
1_752721 2.23485e-05
1_900397 3.04603e-05
1_928297 2.12455e-05
1

Leave the main field separator as it is and use awk's split() function on the first field.

$ awk <data '{ split($1,f1,/_/) ; printf("%s_%s %s\n",f1[1],f1[2],$2) }'

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.