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) => {
      let two_digit_number = Math.round(x * 10)/10
      let number = two_digit_number.toLocaleString()
      if(unit == "%") return `${number}${unit}`
      if(unit == "$") return `${unit}${number}`
      else return `${number.toLocaleString()} ${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")

                  
current_area_left = {
    if (geography_left != "Florida") {
        return bertin.
        merge(county_geo, "GEOID", data_left_fmt_values.filter(d => d.geo_name == geography_left), "geo_id", false)
    } else {
        return data_left_geo
    }
}

place_left_geo = {
    if (geography_left != "Florida" & data_left.map(d => d.geo_unit).includes("place")) {
      let place_left_geo_unfiltered = bertin
                  .merge(place_geo, "GEOID", data_left_fmt_values, "geo_id")
      let geography_left_id = get_county_id(geography_left, county_geo)
      return filter_places_by_area(place_left_geo_unfiltered, geography_left_id, place_county)
    } 
}

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")

current_area_right = {
    if (geography_right != "Florida") {
        return bertin.
        merge(county_geo, "GEOID", data_right_fmt_values.filter(d => d.geo_name == geography_right), "geo_id", false)
    } else {
        return data_right_geo
    }
}

place_right_geo = {
    if (geography_right != "Florida" & data_right.map(d => d.geo_unit).includes("place")) {
      let place_right_geo_unfiltered = bertin
                  .merge(place_geo, "GEOID", data_right_fmt_values, "geo_id")
      let geography_right_id = get_county_id(geography_right, county_geo)
      return filter_places_by_area(place_right_geo_unfiltered, geography_right_id, place_county)
    } 
}

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)

function generate_year_details(year, details) {
  if(details == null) {
      return `${year}`
    } else {
      return `${year} (${details})`
      }
    }

subtitle_left = generate_title(metric_left, category_left)
year_details_left = generate_year_details(year_left, details_left)
d3.select("div#titleLeft").text(metric_group_left)

d3.select("div#subtitleLeft").text(subtitle_left)

d3.select("div#yearLeft").text(year_details_left)

viewof map1 = scan_map(
        data_left_geo,
        `value`,
         style.primary_orange, 
         style.choro_orange, 
         title_left,
         "Data: ...",
         metric_left,
         metric_unit_left,
         place_left_geo,
         current_area_left,
         locations_geo_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]})

{if(true){return html`${viewof geography_left}`}else{return html``}}

county_names = aq.from(geo.table(county_geo)).orderby("NAME").objects().map(d => d.NAME)
state_county_names = county_names.toSpliced(0, 0, "Florida")
viewof geography_left = Inputs.select(state_county_names, {label: "Area", value: "Florida"})

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

female_cancers = ["Cervix", "Breast", "Ovary"]

function conditional_radio_input_sex(cond, true_choices, options, cancer) {
  if(cond) {
   var choices =  ["All"]
  } else {
   var choices =  true_choices
  }
  if(cancer == "Prostate") {
    choices = ["Male"]
  }
  
  if(female_cancers.includes(cancer)) {
    choices = ["Female"]
  }
  return Inputs.radio(choices, options)
}

function get_default_value_sex(cancer) {
  if (cancer == "Prostate") {
      return "Male"
    } else if(female_cancers.includes(cancer)) {
    return "Female"
  } else {
    return "All"
    }
  }

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_sex(metric_group_left != "Cancer Data",  ["All", "Female", "Male"], {label: "Sex", value: get_default_value_sex(category_left)}, category_left)

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

viewof locations_left = Inputs.radio(location_types_unique.toSpliced(0, 0, "None"), {value: "None"})

locations_geo_left = {
  if(locations_left == "None"){
    return "none"
  }else{
    return geo.filter(locations_geo, {func: d => d.properties.type == locations_left})
  }
}

subtitle_right = generate_title(metric_right, category_right)
year_details_right = generate_year_details(year_right, details_right)
d3.select("div#titleRight").text(metric_group_right)

d3.select("div#subtitleRight").text(subtitle_right)

d3.select("div#yearRight").text(year_details_right)

viewof map2 = scan_map(data_right_geo,
        `value`,
         style.primary_green, 
         style.choro_green, 
         title_right,
         "Data: ...",
         metric_right,
         metric_unit_right,
         place_right_geo,
         current_area_right,
         locations_geo_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]})

{if(true){return html`${viewof geography_right}`}else{return html``}}

viewof geography_right = Inputs.select(state_county_names, {label: "Area", value: "Florida"})

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_sex(metric_group_right != "Cancer Data",  ["All", "Female", "Male"], {label: "Sex", value: get_default_value_sex(category_right)}, category_right)

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

viewof locations_right = Inputs.radio(location_types_unique.toSpliced(0, 0, "None"), {value: "None"})

locations_geo_right = {
  if(locations_right == "None"){
    return "none"
  }else{
    return geo.filter(locations_geo, {func: d => d.properties.type == locations_right})
  }
}

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)
             .filter(d => d.geo_unit == "state" | d.geo_unit == "county")
             .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 { scan_map } from "./module_scan_map.qmd"
import { get_magnitude_order, get_lower_pad, get_upper_pad } from "./www/helper.js"
//de = import(await FileAttachment("www/js/data_explorer.js").url())
//import { sparkbar } from "./www/helper.js"
//import { scan_map } from "./www/js/data_explorer.js"

bertin = require(await FileAttachment("node_modules/bertin/dist/index.min.js").url());
geo = require(await FileAttachment("node_modules/geotoolbox/dist/index.min.js").url());
aq = require(await FileAttachment("node_modules/arquero/dist/arquero.min.js").url());

db = await DuckDBClient.of({
  scan360_data: await FileAttachment("data/scan360_www.parquet"),
  menu_structure: await FileAttachment("data/menu_structure.parquet"),
  place_county: await FileAttachment("data/places_counties_fl.parquet")
})

county_geo = d3.json("./data/county_fl.geojson")
place_geo = d3.json("./data/places_fl.geojson")
locations_geo = d3.json("./data/locations.geojson")

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

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)

details_left_obj = db.query(`SELECT DISTINCT detail FROM menu_structure WHERE metric = $1`, [metric_left])
details_left = details_left_obj.map(d => d.detail)[0]

details_right_obj = db.query(`SELECT DISTINCT detail FROM menu_structure WHERE metric = $1`, [metric_right])
details_right = details_left_obj.map(d => d.detail)[0]

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]

location_types = locations_geo.features.map(d => d.properties.type)
location_types_unique = [...new Set(location_types)]

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) {
  let two_digit_number = Math.round(x * 10)/10
  let number = two_digit_number.toLocaleString()
  if(unit == "%") return `${number}${unit}`
  else return `${number.toLocaleString()} ${unit}`
}

function generate_title(metric, category) {
  if(category == null) {
      return `${metric}`
    } else {
      return `${metric}: ${category}`
      }
    }

function get_county_id(county_name, counties) {
  let co = geo.filter(counties, {func: d => d.properties.NAME == county_name,
                                 mutate: false})  
  return co.features[0].properties.GEOID
}

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 sex = $2 AND race_ethnicity = $3 AND age_group = $4"
    } else  {
      return "SELECT * FROM scan360_data WHERE metric = $1 AND category = $2 AND sex = $3 AND race_ethnicity = $4 AND age_group = $5"
      }
    }

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]
      }
    }
}

function filter_places_by_area(places, area_id, crosswalk) {
   
    let crosswalk_area_only =
       aq
       .from(place_county)
       .params({area: area_id})
       .filter((d, e) => d.county == e.area)
       .objects()
       
    let result = bertin.merge(places, "GEOID", crosswalk_area_only, "place", false)
    
    return result;
}

function scan_map(geo_data, value, primary_color, colors, title, source, metric, unit, place_data = "none", extent_data = geo_data, locations_data) {
  
  let county_leg_x
  let place_leg_x
  let place_layer
  let place_nbreaks = 5

  // Switching between place/county mode
  if (geo_data == extent_data) {
    county_leg_x = 180
    place_leg_x = null
  } else {
    county_leg_x = null
    place_leg_x = 180
  }
  
  let tooltip_template = {
          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
      }
    
   if (place_data !== "none") {
   
    let valid_place_values = place_data.features.length - place_data.features.filter(d => d.properties.value === null).length
   
     if (valid_place_values < 6) {
      place_nbreaks = valid_place_values - 1
    }
    place_layer =  {
        type: "layer",
        geojson: place_data,
        strokeWidth: .5,
        stroke: "#000000",
        tooltip: tooltip_template,
        fill: {
          type: "choro",
          values: value,
          nbreaks: place_nbreaks,
          //breaks: [1, 2, 3],
          method: "equal",
          colors: colors.slice(0, place_nbreaks),
          leg_x: place_leg_x,
          leg_y: 550,
          leg_w: 45,
          leg_h: 30,
          leg_round: 1,
          leg_title: `${metric} ${unit}`,
          leg_fontSize: 22,
          leg_fontSize2: 18,
          leg_fillOpacity: 0,
          leg_strokeOpacity: 0
              }
      }
  }
  
  let layers = [
     //{
     //  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: county_leg_x,
          leg_y: 550,
          leg_w: 45,
          leg_h: 30,
          leg_round: 1,
          leg_title: `${metric} ${unit}`,
          leg_fontSize: 22,
          leg_fontSize2: 18,
      },
        
      tooltip: tooltip_template
        },
      { type: "shadow", geojson: geo_data, dx: 5, dy: 5 },
     // {
     //   type: "layer",
     //   geojson: us,
     //   fill: "#e0d0b6",
     // },
      { type: "scalebar", units: "miles"}
    ]
  
  if (place_data !== "none") {
    layers.splice(0, 0 , place_layer)
  }
  
  if(locations_data !== "none") {
    let locations_layer = {
        type: "layer",
        geojson: locations_data,
        fill: "blue",
        strokeOpacity: 0,
        fillOpacity: 0.5,
        size: 15,
        strokeWidth: 15,
        tooltip: {
          fields: [
            "$name",
            `--------------`,
            `$type`
          ],
          fill: "#ffffff",
          stroke: primary_color,
          strokeWidth: 3,
          fillOpacity: 0.85,
          strokeOpacity: 1,
          fontWeight: ["bold", "lighter", "lighter", "lighter", "bold"],
          fontSize: [24, 32, 18, 16, 14],
          fontStyle: ["normal", "normal", "normal", "normal", "bold"],
          col: primary_color
      }
      }
    layers.splice(0, 0, locations_layer)
  }
  
  return bertin.draw({
    params: {
      extent: extent_data,
      margin: 30,
      background: "#bde1f0"
    },
    layers: layers
  })
}