Data Explorer

query_text_left = generate_query_text(category_left, metric_group_left == "Cancer Data")

query_text_right = generate_query_text(category_right)

query_inputs_left = generate_input_list(category_left, "left")

query_inputs_right = generate_input_list(category_right, "right")

op = (  
  aq.addFunction(
  "format_number", 
    (x, unit) => {
      if(unit == "%") return `${Math.round(x * 10) / 10}${unit}`
      if(unit === null) return(Math.round(x * 10)/10)
      else return `${Math.round(x * 10)/10} ${unit}`}),
  aq.addFunction(
  "divider_line", function (length) {
  var underscore = "_"
  return underscore.repeat(length * 1.5)
}
),
  aq.op
)

data_left = db.query(query_text_left, query_inputs_left)

data_left_fmt_values = aq
                      .from(data_left)
                      .params({unit: metric_unit_left[0]}).derive({disp_value: (x, b) => op.format_number(x.value, b.unit)})
                      .derive({divider_line: (x) => op.divider_line(x.disp_value.length * 2)})
                      .objects()

data_left_geo = bertin.merge(county_geo, "GEOID", data_left_fmt_values, "geo_id")

metrics_left_obj = db.query("SELECT DISTINCT metric FROM menu_structure WHERE metric_group = ?", [metric_group_left])
metrics_left = metrics_left_obj.map(d => d.metric)

categories_left_obj = db.query("SELECT DISTINCT category FROM menu_structure WHERE metric = ?",
[metric_left])
categories_left = categories_left_obj.map(d => d.category)

category_label_left_obj = db.query("SELECT DISTINCT category_label FROM menu_structure WHERE metric = ?", [metric_left])
category_label_left = category_label_left_obj.map(d => d.category_label)

title_left = generate_title(metric_group_left, metric_left, category_left, year_left)

data_right = db.query(query_text_right, query_inputs_right)

data_right_fmt_values = aq
                          .from(data_right)
                          .params({unit: metric_unit_right[0]})
                          .derive({disp_value: (x, b) => op.format_number(x.value, b.unit)})
                          .derive({divider_line: (x) => op.divider_line(x.disp_value.length * 2)})
                          .objects()

data_right_geo = bertin.merge(county_geo, "GEOID", data_right_fmt_values, "geo_id")

metrics_right_obj = db.query("SELECT DISTINCT metric FROM menu_structure WHERE metric_group = ?", [metric_group_right])
metrics_right = metrics_right_obj.map(d => d.metric)

categories_right_obj = db.query("SELECT DISTINCT category FROM menu_structure WHERE metric = ?",
[metric_right])
categories_right = categories_right_obj.map(d => d.category)

category_label_right_obj = db.query("SELECT DISTINCT category_label FROM menu_structure WHERE metric = ?", [metric_right])
category_label_right = category_label_right_obj.map(d => d.category_label)

trend_data_left = db.query("SELECT * FROM scan360_data WHERE metric = $1 AND category = $2 AND geo_unit = 'county' AND geo_name = 'FL' AND sex = $3 AND race_ethnicity = $4 AND age_group = $5", [metric_left, category_left, filter_sex_left, filter_race_ethnicity_left, filter_age_left])

trend_data_right = db.query("SELECT * FROM scan360_data WHERE metric = $1 AND category = $2 AND geo_unit = 'county' AND geo_name = 'FL' AND sex = $3 AND race_ethnicity = $4 AND age_group = $5", [metric_right, category_right, filter_sex_right, filter_race_ethnicity_right, filter_age_right])

title_right = generate_title(metric_group_right, metric_right, category_right, year_right)

scan_map = function scan_map(geo_data, value, primary_color, colors, title, source, metric, unit) {
  
  return bertin.draw({
    params: {
      extent: geo_data,
      margin: 30,
      background: "#bde1f0"
    },
    layers: [
      {
        type: "header",
        text: title,
        background: primary_color,
        fill: "white",
        fontSize: 40,
        anchor: "start"
      },
     //{
     //  type: "footer",
     //  text: `SCAN360, 2023
     //  Data: Florida Cancer Data System; Calculations SCAN360`
     //},
      {
        type: "layer",
        geojson: geo_data,
        strokeWidth: 1.5,
        fill: {
          type: "choro",
          values: value,
          nbreaks: 5,
          //breaks: [1, 2, 3],
          method: "equal",
          colors: colors,
          leg_x: 180,
          leg_y: 550,
          leg_w: 45,
          leg_h: 30,
          leg_round: 1,
          leg_title: `${metric} ${unit}`,
          leg_fontSize: 22,
          leg_fontSize2: 18,
      },
        
      tooltip: {
          fields: [
            `$disp_value`,
            //"per 100,000",
            //"Age-Adjusted Rate",
            `$divider_line`,
            "$geo_name"
          ],
          fill: "#ffffff",
          stroke: primary_color,
          strokeWidth: 3,
          fillOpacity: 0.85,
          strokeOpacity: 1,
          fontWeight: ["bold", "lighter", "lighter", "lighter", "bold"],
          fontSize: [50, 16, 32, 25, 30],
          fontStyle: ["normal", "normal", "normal", "normal", "bold"],
          col: primary_color
      }
        },
      { type: "shadow", geojson: geo_data, dx: 5, dy: 5 },
     // {
     //   type: "layer",
     //   geojson: us,
     //   fill: "#e0d0b6",
     // },
      { type: "scalebar", units: "miles"}
    ]
  })
}

viewof map1 = scan_map(
        data_left_geo,
        `value`,
         style.primary_orange, 
         style.choro_orange, 
         title_left,
         "Data: ...",
         metric_left,
         metric_unit_left
         )

viewof metric_group_left = Inputs.select(metric_groups, {value: metric_groups[0]})

viewof metric_left = Inputs.select(metrics_left, {value: metrics_left[0]})

{if(categories_left.length>1){return html`${viewof category_left}`} else {return html``}}

viewof category_left = Inputs.select(categories_left, {label: `${category_label_left}`, value: categories_left[0]})

function conditional_radio_input(cond, true_choices, options) {
  if(cond) {
   var choices =  ["All"]
  } else {
   var choices =  true_choices
  }
  return Inputs.radio(choices, options)
}

viewof filter_age_left = 
  conditional_radio_input(metric_group_left != "Cancer Data", ["All", "<20", "20-64", "65+"], {label: "Age", value: "All"})

viewof filter_sex_left = 
  conditional_radio_input(metric_group_left != "Cancer Data",  ["All", "Female", "Male"], {label: "Sex", value: "All"})

viewof filter_race_ethnicity_left = 
  conditional_radio_input(metric_group_left != "Cancer Data",  ["All", "Black", "Hispanic", "White"], {label: "Race/Ethnicity", value: "All"})

viewof map2 = scan_map(data_right_geo,
        `value`,
         style.primary_green, 
         style.choro_green, 
         title_right,
         "Data: ...",
         metric_right,
         metric_unit_right)

viewof metric_group_right = Inputs.select(metric_groups, {value: metric_groups[0]})

viewof metric_right = Inputs.select(metrics_right, {value: metrics_right[1]})

{if(categories_right.length>1){return html`${viewof category_right}`} else {return html``}}

viewof category_right = Inputs.select(categories_right, {label: `${category_label_right}`, value: categories_right[0]})

viewof filter_age_right = 
  conditional_radio_input(metric_group_right != "Cancer Data", ["All", "<20", "20-64", "65+"], {label: "Age", value: "All"})

viewof filter_sex_right = 
  conditional_radio_input(metric_group_right != "Cancer Data",  ["All", "Female", "Male"], {label: "Sex", value: "All"})

viewof filter_race_ethnicity_right = 
  conditional_radio_input(metric_group_right != "Cancer Data",  ["All", "Black", "Hispanic", "White"], {label: "Race/Ethnicity", value: "All"})

function sparkbar(max, color) {
  return x => htl.html`<div style="
    background: ${color};
    width: ${100 * x / max}%;
    float: right;
    padding-right: 3px;
    box-sizing: border-box;
    overflow: visible;
    display: flex;
    justify-content: end;">${x.toLocaleString("en", {minimumFractionDigits: 2, maximumFractionDigits: 2})}`
}

table_data_left = aq
             .from(data_left)
             .select("geo_name", "geo_id", "value")             

table_data_right = aq
             .from(data_right)
             .select("geo_id", "value")
             .rename({ value: 'value_right' })
             
table_data = table_data_left
              .join_left(table_data_right)
              .select(aq.not("geo_id"))
              .rename({geo_name: "Area"})
//_ = Reactable.setData('ttbl', table_data.objects())
//table_data.toHTML().cat()
Inputs.table(table_data, {
  rows: 100,
  format: {
  value: sparkbar(d3.max(table_data, d => d.value), "orange"),
 value_right: sparkbar(d3.max(table_data, d => d.value_right), "green")},
 header : {
 location: "Area",
 value: html`${metric_group_left} - ${metric_left} (${metric_unit_left})`,
 value_right: `${metric_group_right} - ${metric_right} (${metric_unit_right})`
}})

Appendix

import { get_magnitude_order, get_lower_pad, get_upper_pad } from "./www/helper.js"
//import { sparkbar } from "./www/helper.js"

bertin = require("bertin@latest")
import { aq } from '@uwdata/arquero'
//db = await FileAttachment("./www/scan360_2.sqlite").sqlite()
db = DuckDBClient.of({
  scan360_data: FileAttachment("data/scan360_www.parquet"),
  menu_structure: FileAttachment("data/menu_structure.parquet")
})

menu_structure_obj = db.query(`SELECT * FROM menu_structure`)

metric_groups_obj = db.query(`SELECT DISTINCT metric_group FROM menu_structure`)
metric_groups = metric_groups_obj.map(d => d.metric_group)

metric_unit_left_obj = db.query(`SELECT DISTINCT unit FROM menu_structure WHERE metric = $1`, [metric_left])
metric_unit_left = metric_unit_left_obj.map(d => d.unit)

metric_unit_right_obj = db.query(`SELECT DISTINCT unit FROM menu_structure WHERE metric = $1`, [metric_right])
metric_unit_right = metric_unit_right_obj.map(d => d.unit)

year_left_obj = db.query(`SELECT DISTINCT year FROM menu_structure WHERE metric = $1`, [metric_left])
year_left = year_left_obj.map(d => d.year)[0]

year_right_obj = db.query(`SELECT DISTINCT year FROM menu_structure WHERE metric = $1`, [metric_right])
year_right = year_right_obj.map(d => d.year)[0]

county_geo = d3.json("./data/county_fl.geojson")

style = ({
  width: 550,
  projection: "NellHammer",
  fill: "#65bcdb",
  fillOpacity: 1,
  stroke: "white",
  strokeWidth: 1.5,
  strokeOpacity: 1,
  choro: ["#a5d8ea", "#65bcdb", "#2a8eb2"],
  primary_orange: "#ed6b05",
  primary_green: "#00502e",
  choro_orange: ["#f7bc7cff", "#f49d4eff", "#f47321ff", "#d45504ff", "#b52e18ff"],
  choro_green: ["#c3ddcfff", "#77b792ff", "#369e66ff", "#016d3cff", "#004f2eff"],
  basemap: {
    fill: "white",
    fillOpacity: 1,
    strokeWidth: 0,
    outline: "#f2f2f2"
  }
})

function waitForElem(selector) {
      return new Promise(resolve => {
          if (document.querySelector(selector)) {
              return resolve(document.querySelector(selector));
          }

          const observer = new MutationObserver(mutations => {
              if (document.querySelector(selector)) {
                  resolve(document.querySelector(selector));
                  observer.disconnect();
              }
          });

          observer.observe(document.body, {
              childList: true,
              subtree: true
          });
      });
  }

  waitForElem('#bertinmap').then((elm) => {
    console.log('Element is ready');
    //console.log(elm.textContent);
    document.getElementById('bertinmap').addEventListener('click', (event) => {
      console.log(event.target.__data__.properties)
  });
});

function format_number(x, unit) {
  if(unit == "%") return `${Math.round(x * 10) / 10}${unit}`
  else return `${Math.round(x * 10)/10} ${unit}`
}

function generate_title(metric_group, metric, category, year) {
  if(category == null) {
      return `${metric_group} - ${metric} \n ${year}`
    } else {
      return `${metric_group} - ${metric}: ${category} \n ${year}`
      }
    }

function generate_query_text2(category, filter_switch) {
  var base_query = "SELECT * FROM scan360_data WHERE metric = $1 AND geo_unit = 'county'"
    
    if(category == null) {
      var category_query = ""
    } else {
      var category_query = "AND category = $2"
    }
    
    if(!filter_switch) {
      var filter_query = ""
    } else {
      var filter_query = "AND sex = $3 AND race_ethnicity = $4 AND age_group = $5"
    }
  
  return `${base_query} ${category_query} ${filter_query}`
    
    }

function generate_query_text(category) {
   if(category == null) {
      return "SELECT * FROM scan360_data WHERE metric = $1 AND geo_unit = 'county' AND sex = $2 AND race_ethnicity = $3 AND age_group = $4"
    } else  {
      return "SELECT * FROM scan360_data WHERE metric = $1 AND category = $2 AND geo_unit = 'county' AND sex = $3 AND race_ethnicity = $4 AND age_group = $5"
      }
    
    }

generate_query_elem(category_is_null, filter_switch, all_potential_inputs)

function generate_input_list(category, side) {
  if(side == "left") {
    if(category === null) {
        return [metric_left, filter_sex_left, filter_race_ethnicity_left, filter_age_left]
      } else {
        return [metric_left, category_left, filter_sex_left, filter_race_ethnicity_left, filter_age_left]
      } 
    } else {
        if(category == null) {
        return [metric_right, filter_sex_right, filter_race_ethnicity_right, filter_age_right]
      } else {
        return [metric_right, category_right, filter_sex_right, filter_race_ethnicity_right, filter_age_right]
      }
    }
}