Parse metadata to enable coloring by division

[kimandrews]

The SRA metadata output of the tb workflow does not have a specific column for division. However, we could parse division from the column called geo_loc_name_sam, which has entries in this format:

['Italy: Rome']
['Ethiopia: Addis Ababa']
['Australia:Victoria']
['USA:NY']

Parsing this column would allow us to color by division.

[victorlin]

Here is an example DuckDB-compatible query that parses everything after : if available:

.mode markdown
SELECT
  coalesce(
    CASE
      WHEN geo_loc_name_sam[1] IS NOT NULL
        THEN trim(regexp_extract(geo_loc_name_sam[1], '.*:([^:]+)$', 1))
      ELSE ''
    END,
  '') AS location
FROM read_parquet('s3://sra-pub-metadata-us-east-1/sra/metadata/*')
WHERE
  organism = 'Mycobacterium tuberculosis'
  AND geo_loc_name_sam IS NOT NULL
LIMIT 10;

geo_loc_name_sam	location
['India: Tamil Nadu, Chennai']	Tamil Nadu, Chennai
[Moldova]
[Ghana]
['Indonesia: Bandung']	Bandung
[not collected]
[Uganda]
[Eritrea]
['Russia: Ekaterinburg']	Ekaterinburg
[Democratic Republic of the Congo]
['Indonesia: Bandung']	Bandung

---

Note that values under geo_loc_name_sam are lists. I confirmed using an Athena query that the list is either empty or contains just one value across all of the SRA.

SELECT
  cardinality(geo_loc_name_sam) AS cardinality,
  COUNT(*) AS rows
FROM sra.metadata
GROUP BY cardinality(geo_loc_name_sam);
-- 20039091 rows with cardinality = 1
-- 18287136 rows with cardinality = null