31:T118f,viewof per_district = { const mywidth = width * 0.8; const chart_height = 150; const x = vl.x().fieldT("report_date").title(null); const y = vl.y().fieldQ('ra'); const brush = vl.selectInterval().encodings('x') const date_selector = vl.markLine() .width(mywidth).height(40) .data(state_avg) .params(brush) .encode( x.axis({"labelExpr": "[timeFormat(datum.value,'%b'),timeFormat(datum.value,'%m')=='01'?timeFormat(datum.value,'%Y'):'']"}), y.title("% pos").scale({domainMax: 40}), vl.color({value: "mediumseagreen"}) ); const district = vl.markLine() .width(mywidth).height(chart_height) .data(district_data) .encode( x.scale({domain: brush}), y.title("% Positivity (7-day rolling avg)"), //.scale({domainMax: 50}), vl.tooltip([ {field: 'report_date', type: "temporal", format: "%Y-%m-%d"}, {field: 'number_of_pcr_testing', type: "quantitative"}, {field: "pcr_percent_positivity", type: "quantitative", format: ".2f"}, {field: 'ra', type: "quantitative", format: ".2f"}]) ); const state = vl.markLine() .data(state_avg_weeks) .encode(x.scale({domain: brush}), y, vl.color({value: "mediumseagreen"}), vl.tooltip([ {field: 'report_date', type: "temporal", format: "%Y-%m-%d"}, {field: 'sum_pcr_tests', type: "quantitative"}, {field: "state_pcr_percent_pos", type: "quantitative", format: ".2f"}, {field: 'ra', type: "quantitative", format: ".2f"}]) ); const cases = vl.markLine() .width(mywidth).height(chart_height) .data(district_weekly) .encode(x.scale({domain: brush}), vl.y().fieldQ('cases_100k').title("Weekly Cases per 100k"), //.scale({domainMax: max_y}), vl.tooltip([ {field: 'report_date', type: "temporal", format: "%Y-%m-%d"}, {field: "cases_100k", type: "quantitative", format: ".2f"}]) ); const tests = vl.markLine() .data(district_data) .encode( x.scale({domain: brush}), vl.y().fieldQ('number_of_pcr_testing').title("Tests Given (gray)"), vl.tooltip([ {field: 'report_date', type: "temporal", format: "%Y-%m-%d"}, {field: 'number_of_pcr_testing', type: "quantitative"} ]), vl.color({value: "gray"}), vl.opacity({value: 0.3}) ); // threshold lines const baseRule = vl.markRule({strokeWidth: 2, opacity: 0.8}) .encode(vl.y().fieldQ('y')) const redRule = baseRule.data([{'y':pos_threshold['red']}]) .encode(vl.color().value(thresh_colors['red'])); const redCases = redRule.data([{'y':cases_threshold['red']}]); const orangeRule = baseRule.data([{'y':pos_threshold['orange']}]) .encode(vl.color().value(thresh_colors['orange'])); const orangeCases = orangeRule.data([{'y':cases_threshold['orange']}]) const blueRule = baseRule.data([{'y':pos_threshold['yellow']}]) .encode(vl.color().value(thresh_colors['blue'])); const blueCases = blueRule.data([{'y':cases_threshold['yellow']}]); // threshold regions const baseRect = vl.markRect({opacity: 0.3}) .encode(vl.y().fieldQ('y'), vl.y2().fieldQ('y2')); const orangeRect = baseRect .data([{'y':pos_threshold['orange'], 'y2': pos_threshold['red']}]) .encode(vl.color().value(thresh_colors['orange'])); const orangeCasesRect = orangeRect .data([{'y':cases_threshold['orange'], 'y2': cases_threshold['red']}]); const yellowRect = baseRect .data([{'y':pos_threshold['yellow'], 'y2': pos_threshold['orange']}]) .encode(vl.color().value(thresh_colors['yellow'])); const yellowCasesRect = yellowRect .data([{'y':cases_threshold['yellow'], 'y2': cases_threshold['orange']}]); // end of daily data (2023-02-28) const end_daily = op.parse_date("2023-02-28") const end_daily_data = vl.markRule({strokeWidth: 2, opacity: 0.8}) .data([{'x':end_daily}]) .encode(vl.x().fieldT('x'), vl.color().value('gray')); return vl.vconcat( vl.layer(date_selector, end_daily_data), vl.layer(redRule, orangeRect, yellowRect, orangeRule, blueRule, district, state), vl.layer(redCases, orangeCasesRect, yellowCasesRect, orangeCases, blueCases, tests, cases) ) .title({text: "Green: State Avg, Blue: " + select_district, subtitle: "Select range in top chart to zoom in larger charts or use radio buttons under charts (Source: VDH, data.virginia.gov)"}) .render(); }32:T431,// filter out Not Reported, Out of State, and dates with < 100 PCR tests, calculate % positivity viewof pcr_data = aq_data .derive({ report_date: d => op.parse_date(d.lab_report_date), // rename as report_date number_of_pcr_testing: d => op.parse_int(d.number_of_pcr_testing), number_of_positive_pcr_testing: d => op.parse_int(d.number_of_positive_pcr_testing) }) .filter(d => d.report_date != "Not Reported") .filter(d => d.health_district != "Out of State") .filter(d => d.health_district != "Out Of State") .filter(d => d.report_date != "Invalid Date") .filter(d => d.health_district != "Unknown") .filter(d => d.number_of_pcr_testing >= 100) .derive({pcr_percent_positivity: d => 100 * (d.number_of_positive_pcr_testing / d.number_of_pcr_testing)}) .select('report_date', 'health_district', 'number_of_pcr_testing', 'number_of_positive_pcr_testing', 'pcr_percent_positivity') .orderby('report_date') .groupby('health_district') .derive({ra: aq.rolling(d => op.average(d.pcr_percent_positivity), [-3, 3])}) .view({height: 150})33:[] 2f:{"title":"Virginia COVID Status (PCR Pos, Cases)","cells":[{"id":0,"value":"# Virginia COVID Status (PCR Pos, Cases)\n*Note: Not all [Virginia health districts](https://www.vdh.virginia.gov/local-health-districts/) included*","pinned":false,"hidden":false,"mode":"md"},{"id":1494,"value":"Colors and thresholds based on [Your Local Epidemiologist's decision chart](https://yourlocalepidemiologist.substack.com/p/riding-the-waves-a-framework-for): \n **red**: > 10% PCR positivity, > 100 weekly cases per 100k \n **orange**: 8-9.99% PCR positivity, 50-99 weekly cases per 100k \n - indoor dining ok, mask indoors, avoid crowded indoor events w/o masks \n **yellow** : 5-7.99% PCR positivity, 10-49 weekly cases per 100k \n - don't need to mask, indoor crowded events ok, caution around the vulnerable \n **blue** : < 5% PCR, < 10 weekly cases per 100k ","pinned":false,"hidden":false,"mode":"md"},{"id":1452,"value":"viewof select_district = Inputs.select([\"Chesapeake\", \"Norfolk\", \"Virginia Beach\", \"Portsmouth\", \"Western Tidewater\", \"Peninsula\", \"Richmond\", \"Fairfax\", \"Alexandria\", \"Alleghany\", \"Roanoke\", \"West Piedmont\"], {value: \"Chesapeake\", label: \"Select district:\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1847,"value":"**Virginia** at ${state_pcr_latest.state_pcr_percent_pos.toFixed(2)}% positivity (rolling avg: ${state_pcr_latest.ra.toFixed(2)}%) on ${state_pcr_latest.report_date.toLocaleDateString('en-US')} \n**${select_district}** at ${district_pcr_latest.pcr_percent_positivity.toFixed(2)}% positivity (rolling avg: ${district_pcr_latest.ra.toFixed(2)}% \n ) \non ${district_pcr_latest.report_date.toLocaleDateString('en-US')} \n ${district_cases_latest.cases_100k.toFixed(2)} weekly cases/100k \n on ${district_cases_latest.report_date.toLocaleDateString('en-US')} \n*gray vertical line indicates end of daily reported data (2023-02-28)*","pinned":false,"hidden":false,"mode":"md"},{"id":1045,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":2426,"value":"viewof x_weeks = Inputs.radio(new Map([[\"all\", -1], [\"last 20 weeks\", 20], [\"last 12 weeks\", 12], [\"last 6 weeks\", 6]]), {value: -1, label: \"Zoom to\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2530,"value":"[HRSD COVID-19 Surveillance](https://www.hrsd.com/HRSD-COVID-19-Surveillance) - wastewater monitoring","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"## Data\n\nVirginia Department of Health (VDH) data accessed via API from https://data.virginia.gov\n\n* VDH-COVID-19-PublicUseDataset-Tests_by-LabReportDate\nhttps://data.virginia.gov/Government/VDH-COVID-19-PublicUseDataset-Tests_by-LabReportDa/3u5k-c2gr\n* VDH-COVID-19-PublicUseDataset-Cases\nhttps://data.virginia.gov/Government/VDH-COVID-19-PublicUseDataset-Cases/bre9-aqqr","pinned":false,"hidden":false,"mode":"md"},{"id":2082,"value":"## Data Manipulation","pinned":false,"hidden":false,"mode":"md"},{"id":1323,"value":"import {buildURI} from \"@weiglemc/using-the-soda-api-to-access-data-from-the-virginia-open-data\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":2232,"value":"Fetch testing data via API from VDH-COVID-19-PublicUseDataset-Tests_by-LabReportDate https://data.virginia.gov/Government/VDH-COVID-19-PublicUseDataset-Tests_by-LabReportDa/3u5k-c2gr","pinned":false,"hidden":false,"mode":"md"},{"id":673,"value":"raw_data = (await fetch(buildURI(\"3u5k-c2gr\", 50000))).json()","pinned":false,"hidden":false,"mode":"ojs"},{"id":791,"value":"viewof aq_data = aq.from(raw_data).view({height:75})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2362,"value":"Making sure that the data is still coming in daily (*daily data ended 2023-02-28*)","pinned":false,"hidden":false,"mode":"md"},{"id":2366,"value":"pcr_data.filter(aq.escape(d => d.health_district == select_district)).orderby(aq.desc('report_date')).view({height:125})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2213,"value":"Filtering out reports with < 100 tests, calculating % PCR positivity","pinned":false,"hidden":false,"mode":"md"},{"id":678,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":2220,"value":"Data for the selected district (from dropdown menu) only for selected previous weeks","pinned":false,"hidden":false,"mode":"md"},{"id":1470,"value":"viewof district_data = pcr_data\n .filter(aq.escape(d => d.health_district == select_district))\n .orderby('report_date')\n .filter(aq.escape(d => d.report_date > first_date))\n .view({height:125})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1090,"value":"Calculating 7-day rolling average (ra) of state % PCR positivity","pinned":false,"hidden":false,"mode":"md"},{"id":1094,"value":"viewof state_avg = pcr_data\n .groupby('report_date')\n .rollup({\n sum_pcr_tests: d => op.sum(d.number_of_pcr_testing),\n sum_pcr_pos: d => op.sum(d.number_of_positive_pcr_testing),\n })\n .derive({state_pcr_percent_pos: d => 100 * (d.sum_pcr_pos / d.sum_pcr_tests)})\n .orderby('report_date')\n .derive({ra: aq.rolling(d => op.average(d.state_pcr_percent_pos), [-3, 3])})\n .view({height: 150})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2507,"value":"Trim for selected number of previous weeks (radio)","pinned":false,"hidden":false,"mode":"md"},{"id":2504,"value":"viewof state_avg_weeks = state_avg.filter(aq.escape(d => d.report_date > first_date)).view({height:100})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1134,"value":"districts = pcr_data.orderby('health_district').dedupe('health_district').array('health_district')","pinned":false,"hidden":false,"mode":"ojs"},{"id":2050,"value":"Fetch cumulative case data via API from VDH-COVID-19-PublicUseDataset-Cases https://data.virginia.gov/Government/VDH-COVID-19-PublicUseDataset-Cases/bre9-aqqr","pinned":false,"hidden":false,"mode":"md"},{"id":1501,"value":"case_rawdata = (await fetch(buildURI(\"bre9-aqqr\", 150000))).json()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1503,"value":"viewof case_data = aq.from(case_rawdata).view({height:75})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1532,"value":"Next step is to join case data with population","pinned":false,"hidden":false,"mode":"md"},{"id":1517,"value":"import {state_population} from \"@weiglemc/cs725-s22-virginia-population-data-manipulation\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1520,"value":"state_population.view({height:75})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2246,"value":"Joined dataset","pinned":false,"hidden":false,"mode":"md"},{"id":1526,"value":"viewof locality_cases = case_data.join(state_population)\n .select(aq.not('fips','vdh_health_district', 'year', 'hospitalizations', 'deaths'))\n .derive({\n report_date: d => op.parse_date(d.report_date),\n total_cases: d => op.parse_int(d.total_cases),\n daily_cases: 0\n })\n .orderby('locality','report_date')\n .view(5)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1511,"value":"Next, need to calculate new cases for each locality per reporting date. Then, calculate new cases per 100k each week. ","pinned":false,"hidden":false,"mode":"md"},{"id":2253,"value":"First, calculate daily cases from the cumulative case counts using `calcNewDailyCounts()`","pinned":false,"hidden":false,"mode":"md"},{"id":1621,"value":"daily_cases = calcNewDailyCounts(locality_cases.objects())","pinned":false,"hidden":false,"mode":"ojs"},{"id":1669,"value":"viewof aq_daily = aq.from(daily_cases).view({height:75})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2260,"value":"Sum cases for each day over the entire health district","pinned":false,"hidden":false,"mode":"md"},{"id":1671,"value":"viewof daily_district_data = aq_daily\n .groupby('report_date', 'health_district') // sum for each day over the entire district\n .rollup({\n dist_daily_cases: d => op.sum(d.daily_cases),\n dist_population: d => op.sum(d.population)\n })\n.view({height:150})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2265,"value":"Calculate weekly district cases using `calcDistrictWeeklyCounts()`","pinned":false,"hidden":false,"mode":"md"},{"id":2182,"value":"weekly_cases = calcDistrictWeeklyCounts(daily_district_data.objects())","pinned":false,"hidden":false,"mode":"ojs"},{"id":2183,"value":"viewof aq_weekly = aq.from(weekly_cases).view({height:75})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2272,"value":"Use district population data to calculate weekly cases per 100,000","pinned":false,"hidden":false,"mode":"md"},{"id":2185,"value":"viewof weekly_data = aq_weekly\n .derive({cases_100k: d => d.weekly_cases > 0 ? d.weekly_cases * 100000 / d.dist_population : 0})\n .view({height:150})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2486,"value":"Find first date for selected number of previous weeks (radio)","pinned":false,"hidden":false,"mode":"md"},{"id":2472,"value":"first_date = weekly_data\n .filter(aq.escape(d => d.health_district == select_district))\n .orderby(aq.desc('report_date'))\n .slice(x_weeks).get('report_date', 0) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":2278,"value":"Weekly case rates for selected health district (dropdown) and number of previous weeks (radio)","pinned":false,"hidden":false,"mode":"md"},{"id":2187,"value":"viewof district_weekly = weekly_data\n .filter(aq.escape(d => d.health_district == select_district))\n .filter(aq.escape(d => d.report_date > first_date))\n .view({height:150})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2285,"value":"## Helper Functions","pinned":false,"hidden":false,"mode":"md"},{"id":2289,"value":"Calculate daily counts from cumulative data","pinned":false,"hidden":false,"mode":"md"},{"id":1620,"value":"function calcNewDailyCounts(arr) {\n let daily = [];\n\n let current_locality = \"\";\n let prev_cases = 0;//, prev_hosp = 0, prev_deaths = 0;\n arr.forEach(function(row) {\n if (current_locality != row.locality) {\n // new locality, first element\n row.daily_cases = row.total_cases;\n// row.daily_hosp = row.hospitalizations;\n// row.daily_deaths = row.deaths;\n current_locality = row.locality;\n } else {\n // next date, same locality\n let diff = row.total_cases - prev_cases;\n row.daily_cases = (diff >=0 ? diff : 0); // negatives get assigned to 0\n// diff = row.hospitalizations - prev_hosp;\n// row.daily_hosp = (diff >=0 ? diff : 0);\n// diff = row.deaths - prev_deaths;\n// row.daily_deaths = (diff >=0 ? diff : 0);\n }\n daily.push(row);\n prev_cases = row.total_cases;\n// prev_hosp = row.hospitalizations;\n// prev_deaths = row.deaths;\n }) \n return daily;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2293,"value":"Calculate per-district weekly counts from daily data","pinned":false,"hidden":false,"mode":"md"},{"id":2190,"value":"function calcDistrictWeeklyCounts(arr) {\n let weekly = [];\n let current_district = \"\";\n let week_start = new Date();\n let weekly_cases = 0;\n \n arr.forEach(function(row) {\n if (current_district != row.health_district) {\n // new district, first date\n current_district = row.health_district;\n week_start = row.report_date;\n weekly_cases = row.dist_daily_cases;\n } else {\n // next date, same district - is this a new week?\n let days = dateDiffInDays(week_start, row.report_date);\n if (days >= 7) {\n // new week, same district, output data\n row.weekly_cases = weekly_cases;\n weekly.push(row); // push previous week\n\n // setup new week's data\n week_start = row.report_date;\n weekly_cases = row.dist_daily_cases;\n } else {\n // same week, same district\n weekly_cases += row.dist_daily_cases;\n }\n }\n }) \n return weekly;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2298,"value":"Calculate days between two dates (from https://stackoverflow.com/questions/3224834/get-difference-between-2-dates-in-javascript)","pinned":false,"hidden":false,"mode":"md"},{"id":2192,"value":"// from https://stackoverflow.com/questions/3224834/get-difference-between-2-dates-in-javascript\n// a and b are javascript Date objects\nfunction dateDiffInDays(a, b) {\n const _MS_PER_DAY = 1000 * 60 * 60 * 24;\n // Discard the time and time-zone information.\n const utc1 = Date.UTC(a.getFullYear(), a.getMonth(), a.getDate());\n const utc2 = Date.UTC(b.getFullYear(), b.getMonth(), b.getDate());\n\n return Math.floor((utc2 - utc1) / _MS_PER_DAY);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2302,"value":"Selected district's PCR data for the latest report date","pinned":false,"hidden":false,"mode":"md"},{"id":1798,"value":"district_pcr_latest = district_data.orderby(aq.desc('report_date')).object()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2306,"value":"State PCR data for the latest report date","pinned":false,"hidden":false,"mode":"md"},{"id":1810,"value":"state_pcr_latest = state_avg.orderby(aq.desc('report_date')).object()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2310,"value":"Selected district's case data for the latest report date ","pinned":false,"hidden":false,"mode":"md"},{"id":1822,"value":"district_cases_latest = district_weekly.orderby(aq.desc('report_date')).object()","pinned":false,"hidden":false,"mode":"ojs"},{"id":2319,"value":"Percent positivity thresholds (from YLE)","pinned":false,"hidden":false,"mode":"md"},{"id":1914,"value":"pos_threshold = {return {red:10, orange:8, yellow:5};}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2323,"value":"Weekly cases per 100k thresholds (from YLE)","pinned":false,"hidden":false,"mode":"md"},{"id":1939,"value":"cases_threshold = {return {red:100, orange:50, yellow:10};}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2327,"value":"Threshold colors","pinned":false,"hidden":false,"mode":"md"},{"id":1940,"value":"thresh_colors = {return {red:'firebrick', orange:'darkorange', yellow:'yellow', blue:'steelblue'}};","pinned":false,"hidden":false,"mode":"ojs"},{"id":2331,"value":"Given a value and threshold, return appropriate color","pinned":false,"hidden":false,"mode":"md"},{"id":2116,"value":"function getColor(value, thresholds) {\n // given data value and set of thresholds (red, orange, yellow), return color\n if (value > thresholds.red) {return thresh_colors.red;}\n if (value >= thresholds.orange) {return thresh_colors.orange;}\n if (value >= thresholds.yellow) {return thresh_colors.yellow;}\n return thresh_colors.blue;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":784,"value":"# References\n\n* Introducing Arquero - https://observablehq.com/@uwdata/introducing-arquero\n* Arquero API Reference - https://uwdata.github.io/arquero/api/\n* For multi-line x-axis label (w/year under month) - https://vega.github.io/vega-lite/docs/axis.html\n* For tooltip formatting - https://observablehq.com/@ee2dev/vega-lite-custom-formatting-with-locale\n* For selection - https://observablehq.com/@vega/vega-lite-api-v5\n* Get difference between two dates in JavaScript - https://stackoverflow.com/questions/3224834/get-difference-between-2-dates-in-javascript","pinned":false,"hidden":false,"mode":"md"},{"id":24,"value":"md`# Appendix`","pinned":false,"hidden":false,"mode":"ojs"},{"id":783,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"},{"id":87,"value":"import { vl } from \"@vega/vega-lite-api-v5\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}