This is a test version of Biostars. For the public version, visit https://www.biostars.org.
R: How to separate column into multiple ones? How to export created table from R to Excel

Hello folks, hope I can get some help here. Pretty much a newbie with R. For microbiome analysis I produced this OTU frequency table for my samples (see picture) which allready has taxonomy assigned. Unfortunatley, qiime2 gave me taxonomic classification as 1 single row, individual levels divided by "__"

I want to split these single line rows into seperate columns, one column per taxonomic level, for example:

"D_0__Archaea;D_1__Asgardaeota;D_2__Lokiarchaeia;__;__;__;__" to "Archaea" "Asgardaeota" "Lokiarchaeia" and so on, preferably without having to cut out my samples if possible. How can I do that? Also, How do I export it then to view it in Excel?

data table https://ibb.co/d47t85N

r parse_taxonomy qiime2 split_row_to_columns
• 2,125 views • link
written by monkeystrohhutdruffy • monkeystrohhutdruffy

A linux solution:

 cat yourfile |sed 's/D....//g' | sed 's/;/" "/g'| sed 's/_.*//g' | sed 's/"$//g'

output is

 "Archaea" "Asgardaeota" "Lokiarchaeia"
• 0 views • link
written by Mehmet Mehmet

Hello monkeystrohhutdruffy!

We believe that this post does not fit the main topic of this site.

Thanks! :) I got it now

For this reason we have closed your question. This allows us to keep the site focused on the topics that the community can help with.

If you disagree please tell us why in a reply below, we'll be happy to talk about it.

Cheers!

• 0 views • link
written by monkeystrohhutdruffy • monkeystrohhutdruffy

Thanks! :) I got it now

Did @Mehmet's answer work? If so I can move move the comment to an answer so you can accept it. If you came up with your own answer you can post it here and accept it.

Closing posts is an action used by moderators to close off-topic posts. It is not meant for closing posts because the issue was solved.

• 0 views • link
written by GenoMax GenoMax 
 $ cat test.txt 
D_0__Archaea;D_1__Asgardaeota;D_2__Lokiarchaeia;__;__;__;__

$ awk -F ";" -v OFS="\t"  '{gsub("D_[0-9]__","",$0); print $1,$2,$3}' test.txt 
Archaea Asgardaeota Lokiarchaeia

$ sed -e 's/;/\t/g;s/D_[0-9]__//g' test.txt| cut -f1-3
Archaea Asgardaeota Lokiarchaeia
• 0 views • link
written by cpad0112 cpad0112

0 answers

No answers yet.

Log in to answer this question.

More posts like this