Obtain empirical evidence for permissible values to be included the missing data code enumeration

[turbomam]

see also

• https://www.insdc.org/technical-specifications/missing-value-reporting/
• Null value terminology mixs#828

[turbomam]

I will include some counts of NCBI Attribute contents that seem to match INSDC missing value indicators.

First, how many Biosamples are in my latest DuckDB database? (from https://portal.nersc.gov/project/m3408/biosamples_duckdb/)

select
	count(1)
from
	ncbi_biosamples.main.attributes a ;

585,141,773

select
	count(distinct id)
from
	ncbi_biosamples.main.attributes a ;

40,462,422

What is the highest numerical Biosample id in there?

select
	max(id)
from
	ncbi_biosamples.main.attributes a ;

43,280,194

[turbomam]

Examples semi-fuzzy search

select
	count(1)
from
	ncbi_biosamples.main.attributes a
where
	lower("content") like '%control sample%';

[turbomam]

Comprehensive query

SELECT
	"content",
	count(1)
FROM
	ncbi_biosamples.main.attributes a
WHERE
	lower("content") 
	~ '.*(control sample|data agreement established pre-2023|endangered species|human-identifiable|lab stock|missing|not applicable|not collected|not provided|restricted access|sample group|synthetic construct|third party data).*'
GROUP BY
	"content"
HAVING
	count(1) > 1
ORDER BY
	count(1) DESC;

Partial results

Full results attached

content	count(1)
not provided	25533568
missing	14115083
not applicable	8235881
Not provided	5052088
not collected	4019535
Missing	744604
restricted access	669718
Not applicable	455082
Not Provided	189753
Missing: Not provided	180559
Not Applicable	151370
Restricted Access	139848
Not collected	133586
NOT PROVIDED	122423
Not Collected	117148
NOT COLLECTED	85093
missing: data agreement established pre-2023	75688
NOT APPLICABLE	66434
synthetic construct	38754
control sample	36337
MIssing: Not provided	22765
missing: sample group	18911
not applicable'	10872
Missing: Not collected	8731
Missing: Restricted access	8054
Missing: Not recorded	6850
missing: lab stock	6620
Missing: Not reported	6533
Not Collected [GENEPIO:0001620]	6440
MISSING	6335

export_202409091419.csv

[turbomam]

Partial results don't include these other common values:

• NULL: 1,049,472
• unknown: 3,502,884
• na: 2,607,680
• n/a: 381,861