I have a file like this : enter image description here

and I want the output as:

enter image description here

I tried using awk :

awk 'BEGIN { OFS=FS="\t" } { sub("\\..*", "", $1); print }'

and it prints the first id ENST. But when I change it to:

awk 'BEGIN { OFS=FS="\t" } { sub("\\..*", "", $5); print }'

it doesnt work.

What is wrong that I am doing here. Kindly help.

  • $\begingroup$ could you please provide a text-format toy example (or first few lines) of your input file? that would help to quickly see an application of your tests with awk. $\endgroup$ – aechchiki Jun 26 '20 at 8:07
  • $\begingroup$ done.KIndly see $\endgroup$ – user1738234 Jun 26 '20 at 13:28
  • $\begingroup$ thanks, although i meant more in an easily copy-pastable text format (not a screenshot) in a code snippet for example, same you used to write in your awk lines $\endgroup$ – aechchiki Jun 26 '20 at 23:27

Assuming you want to only show the fifth id from the first field, the following Perl script should work:

perl -pe 's/^(.*?)\t//; @F=split("\\|", $1); print(@F[4]."\t")' input.tsv > output.tsv

Explanation of the Perl code:

  1. Loop over the lines in the input file (this is enabled by the command-line option -p)
  2. Remove everything up to the first tab character from the input line, and save the removed part (excluding tab character) into the grouping variable $1 [identified by brackets].
  3. Split variable $1 at the pipe character, |, saving the split components into the array @F
  4. Print the fifth field [0,1,2,3,4] from the array @F [without a line break at the end]
  5. Print the remainder of the input line (this is also enabled by the command-line option -p)
  • $\begingroup$ DDX11L1-202 is in the very first line of the excel document 5th character in the pipe. $\endgroup$ – user1738234 Jun 26 '20 at 13:18

You could just do this in libreoffice calc using text to columns (data menu)

If you want to use awk (or maybe gawk) you could use capture groups; but rather that mess around with a fairly complex regex I'd probably do it in two stages like below

awk -F"|" '{print $5,$0}' OFS="\t" your_file|\
awk -F"\t" '{print $1,substr($0,index($0,$3))}'

The second awk prints everything except field 2

  • $\begingroup$ DDX11L1-202 1657|lncRNA| it retains the other columns with pipes. $\endgroup$ – user1738234 Jun 25 '20 at 11:27

You need the first column's 6th component when split by | and then the remaining columns of the file where \t is the delimiter. Use awk.

awk -F "\t" -vOFS="\t" 'NR==1 {print} NR>1{split($1,arr,"|"); print arr[6],$2,$3,$4,$5}' your_file

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.