qiime feature-table filter-samples error SQL format or no ID present --p-where

[fabipc (Fabiola)]

Hi,

I am attempting to use the feature-table filter samples to extract the ectomycorrhizal fungi from my table, and I keep having issues. I have done this before, so I am unsure why it is not working this time.

I have used the script below.

qiime feature-table filter-samples \
--i-table core-metrics-results/rarefied_table.qza \
--m-metadata-file FUNGuild/Fungal-T1-T17-table-with-taxonomy.guilds_matched.tsv \
--p-where "[Guild]='Ectomycorrhizal' AND [Confidence Ranking]='Highly Probable'" \
--o-filtered-table EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qza

and this will output a table, supposedly, but when I try to create the .qzv file, I get the following error.

qiime feature-table summarize \
--i-table EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qza \
--m-sample-metadata-file Metadata/Fungi-MetaRare.tsv \
--o-visualization EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qzv

ValueError: ids_to_keep must contain at least one ID.

I have also ran the code using

qiime feature-table filter-samples \
--i-table core-metrics-results/rarefied_table.qza \
--m-metadata-file FUNGuild/Fungal-T1-T17-table-with-taxonomy.guilds_matched.tsv \
--p-where "Guild='Ectomycorrhizal' AND Confidence Ranking='Highly Probable'" \
--o-filtered-table EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qza

but I get this error

Plugin error from feature-table:

• Selection of IDs failed with query:
• SELECT "#OTUID" FROM metadata WHERE [Guild]='Ectomycorrhizal'' AND [Confidence Ranking]='Highly Probable' GROUP BY "#OTUID" ORDER BY "#OTUID";**
• If one of the metadata column names specified in the where statement is on this list of reserved keywords (SQLite Keywords), please ensure it is quoted appropriately in the where statement.**

The only thing I can think of is that my metadata has #OTU as header, and my feature table has #SampleID, but even when I change the metadata name, it still does not work.

If you could please help me with this issue, I would really appreciate it.

Thanks,
Fabi

[jphagen (Johannah Hagen)]

Hi @fabipc!
It appears that the --p-where function entered into qiime feature-table is filtering away all of your samples, hence the ValueError.
Based on your parameter "Ectomycorrhizal" I am guessing your intention is to filter based on the feature "Ectomycorrhizal", in which case I recommend using qiime feature-table filter-features, documentation can be found here.
If this is not your intention please explain the goal for your metadata analysis so I can help you find the best fit.
Also, The SQL formatting used here is correct:

@fabipc

--p-where "[Guild]='Ectomycorrhizal' AND [Confidence Ranking]='Highly Probable'" \

you will want to use the brackets when you try again.
-Hannah

[fabipc (Fabiola)]

I see.
Yes, I want to keep the fungi classified as Ectomycorrhizal with a highly probable confidence raking.

I went ahead and tried what you suggested, and it worked

Thanks a ton. I will go ahead and share this information with my lab, as they are also going to be doing the same analysis.