Filtering by Sample ID not working

[sarah_i]

Hi,

I am simply trying to filter out these specific samples by sample ID but I keep getting this error:

Plugin error from feature-table:
  Selection of IDs failed with query:
   SELECT "#SampleID" FROM metadata WHERE #SampleID IN ('Q30_3', 'Q29_7','Q28_3','Q28_11) GROUP BY "#SampleID" ORDER BY "#SampleID";

  If one of the metadata column names specified in the `where` statement is on this list of reserved keywords (http://www.sqlite.org/lang_keywords.html), please ensure it is quoted appropriately in the `where` statement.

Debug info has been saved to /var/folders/yg/wtspl3kd78z1mymc99k3zkl00000gp/T/qiime2-q2cli-err-fmo1txn3.log

The command I have been using is:

qiime feature-table filter-samples \
  --i-table 4R_decontam_no_EBC_frequency_filtered.qza \
  --m-metadata-file Catalyst_Run1_metadata4.tsv \
  --p-where "#SampleID IN ('Q30_3', 'Q29_7','Q28_3','Q28_11)" \
  --p-exclude-ids \
  --o-filtered-table 4R_ID_filtered.qza 

I’m not exactly sure what I have been doing wrong.

[thermokarst]

Hey there @sarah_i!

@sarah_i

I am simply trying to filter out these specific samples by sample ID

The easiest way to do that would be to skip the SQL query and just remove those samples from your metadata, then filter using that reduced metadata file.

For the SQL query to work, you are super close, you just need some quotes around #SampleID:

qiime feature-table filter-samples \
  --i-table 4R_decontam_no_EBC_frequency_filtered.qza \
  --m-metadata-file Catalyst_Run1_metadata4.tsv \
  --p-where "\"#SampleID\" IN ('Q30_3', 'Q29_7','Q28_3','Q28_11)" \
  --p-exclude-ids \
  --o-filtered-table 4R_ID_filtered.qza 

Please note the quotes: \"#SampleID\".

Also, just for the sake of illustration, you can drop the --p-exclude-ids and add a NOT to the query, which to me is even more clear (the --p-exclude-ids flag is primarily intended for pure ID-based filtered without a sql query):

qiime feature-table filter-samples \
  --i-table 4R_decontam_no_EBC_frequency_filtered.qza \
  --m-metadata-file Catalyst_Run1_metadata4.tsv \
  --p-where "\"#SampleID\" NOT IN ('Q30_3', 'Q29_7','Q28_3','Q28_11)" \
  --o-filtered-table 4R_ID_filtered.qza 

Give that a whirl!

Thanks!