This is a test version of Biostars. For the public version, visit https://www.biostars.org.
How to remove everything before a specific character in a column

I have a big tsv file with 4 columns in the following format:

ERR435678 contig_1 /home/results/file1.txt /home/results/file1.txt
ERR435678 contig_2 /home/results/file2.txt /home/results/file2.txt
ERR435678 contig_3 /home/results/file3.txt /home/results/file3.txt

How can I manipulate only the elements of the third column in a way that I will get the following:

ERR435678 contig_1 file1.txt /home/results/file1.txt
ERR435678 contig_2 file2.txt /home/results/file2.txt
ERR435678 contig_3 file3.txt /home/results/file3.txt
unix bash awk sed
• 3,693 views • link
updated by GenoMax GenoMax • written by blackadder • blackadder

What have you tried? This is a really simple Google search that does not merit its own post.

• 0 views • link
written by Ram Ram

Hello again! I thought the same but I cannot find anything relevant for manipulating data in a specific column. I am looking in awk but I havent used it before and I cannot understand how to use it

• 0 views • link
written by blackadder • blackadder

All of these column matching and replacing questions you have been asking are not bioinformatics.

If you know no other way of doing it, there is always Excel. The file can be imported, then copy the third column into a separate file, do search+replace, and paste it back to the original file. I estimate it would take less than a minute.

• 0 views • link
written by Mensur Dlakic Mensur Dlakic

Hello there! I am relatively new in the field and I am not very experienced with bash commands. What I am asking I know that I can do it in an excel sheet but I want to stop using excel and focus on bash. I believe that everyone that deals with bioinformatics has to do some kind of data manipulation in tsv or csv files. Thank you

• 0 views • link
written by blackadder • blackadder

2 answers 

$ cat test.txt                       

ERR435678   contig_1    /home/results/file1.txt /home/results/file1.txt
ERR435678   contig_2    /home/results/file2.txt /home/results/file2.txt
ERR435678   contig_3    /home/results/file3.txt /home/results/file3.txt

$ awk '{sub(".*/","",$3)}1' test.txt

ERR435678 contig_1 file1.txt /home/results/file1.txt
ERR435678 contig_2 file2.txt /home/results/file2.txt
ERR435678 contig_3 file3.txt /home/results/file3.txt
• 0 views • link
written by cpad0112 cpad0112

I want to stop using excel and focus on bash.

You said the magic words.

sed 's%/home/results/%%' in.txt
• 0 views • link
updated by GenoMax GenoMax • written by Pierre Lindenbaum Pierre Lindenbaum

For each response here, understand how it works and what would break it. For example, this would not work if you wanted to retain the /home/results/ in col3 but remove them from col4, or if the /home/results part was variable. cpad0112's solution would address those scenarios. That solution can be enhanced by using awk -F "\t" -vOFS="\t" instead of just awk so the input and output field separators are made explicit.

Invest in learning sed and awk - you'll never use Excel (or any GUI tool) for text manipulation again.

• 0 views • link
written by Ram Ram

Thank you for the thorough explanation!

• 0 views • link
written by blackadder • blackadder

Please accept these answers (green check mark) to provide closure to this thread. You can accept more than one answer as correct.

• 0 views • link
written by GenoMax GenoMax

Log in to answer this question.

More posts like this