Standard method to merge Taxonomy and Feature/ASV data

Hi all,

I'm new to QIIME (1 or 2) for metabarcoding data analysis (I've used OBITools before). Currently I'm working on the Moving Pictures tutorial and my own sequencing data in QIIME2.

How can one easily combine the output from a taxonomy analysis and the ASV/feature table? As an output, I'd like to have a table where each row is a Feature/ASV and the columns are taxonomy, the sample IDs etc. The values are the number of reads/Frequency for each of that ASV in the corresponding sample:

ASV     Kingdom     Phylum   ...   species    Sample 1    Sample 2    
1       Bacteria    x              A          3000        5543
2       Eukaryota   x              B          5467        5478
3       Bacteria    x              C          2234        1376

The format does not need to be exactly like this, but it is important that all the taxonomy is shown (preferably a separate column for each taxonomic level) and that the number of reads per sample for that taxon are shown.

I'll take the moving picture tutorial as an example:

Using qiime dada2 denoise-single you obtain a FeatureTable --> table.qza
Using qiime feature-classifier classify-sklearn you obtain Taxonomy data --> taxonomy.qza

The following command is run to combine both files and visualize them:

qiime taxa barplot \
  --i-table table.qza \
  --i-taxonomy taxonomy.qza \
  --m-metadata-file sample-metadata.tsv \
  --o-visualization taxa-bar-plots.qzv

However, this is just a visualization (barplot) and does not create a new artifact where both are combined.

I've also found the following code on here the forum:

#transpose feature table, so samples are in the columns
qiime feature-table transpose \
--i-table table.qza \
--o-transposed-feature-table \

qiime metadata tabulate \
--m-input-file table_transposed.qza \
--m-input-file taxonomy.qza \
--o-visualization merged.qzv

The format looks quite good in the HTML viewer, but this again is merely a visualization .qzv file. I'd like to get a .qza file etc that I can export to .xlsx or something.

Perhaps it is a silly question, but I still have to get used to the format/syntax of QIIME2. I've searched on the forum, but I did not find a solution. You can export it in the HTML link, but it does not look neatly.

@the developers: It could be interesting to add such a merge (to get a complete ASV/OTU table, including taxonomy and frequency per sample) in the Moving Pictures tutorial.

Thank you for the help!

Welcome to the forum, @Rob_DNA ! Also welcome to QIIME 2 :grin:

Thanks for pulling up that example — that is what I would recommend as the solution to your request as well.


Good news! This is already possible from without the visualization. There is a button at the top of the page to "Download Metadata TSV File". This file can be opened in excel or similar as is (spreadsheet programs all have TSV importers). You could then save as a .xlsx if you want to add any rich formatting etc in excel.

Technically this can also be done programmatically if you are looking to avoid a manual download step (but if you want a programmatic solution then I recommend using QIIME 2's python API to load and view the artifacts as a DataFrame to give maximal control for merging, subsetting, etc).

A QZA would not be practical, because this is an unusual format and would not be interoperable with any downstream software, so defining a special QZA format would make a "dead end" that requires addiitonal steps to process/visualize anyway.

Note: metadata tabulate can be used to merge several metadata-viewable objects into a single QZV.. so you could, e.g., chain together sequences or other feature information as well.

Good luck!

1 Like

Hi Nicholas,

Thank you for the quick reply! Alright, then I can use that option. (I also made a mistake with the text to columns so now it looks good)

One question regarding the way QIIME2 merges objects:
e.g. with the following code:

qiime metadata tabulate \
--m-input-file table_transposed.qza \
--m-input-file taxonomy.qza \
--o-visualization merged.qzv

Does metadata tabulate merge on index/position, or does it merge on FeatureID?
I think it is FeatureID right? Because if I run the same command but NOT on the transposed table I get following error:

There was an issue with merging QIIME 2 Metadata:
Cannot merge because there are no IDs shared across metadata objects.

Yep! Exactly (hence that error message when the indices do not intersect, but also to make this more robust... merging on index would create some very dangerous and cryptic errors!)

merging on index would create some very dangerous and cryptic errors!

Yes exactly :slight_smile: Thanks!

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.