R: How to separate column into multiple ones? How to export created table from R to Excel

Question

[monkeystrohhutdruffy]

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?

https://ibb.co/d47t85N

[Mehmet]

A linux solution:

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

output is

 "Archaea" "Asgardaeota" "Lokiarchaeia"

[monkeystrohhutdruffy]

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!

[GenoMax]

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.

[cpad0112]

 $ 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