2f:[{"id":"c5d97b9ea309b025c1e598ffb6278663dd7e301246a814a2f5059f634465e0ddc5c12f2fcc8cd9810dce201f060b6aeb27f9693d80499a2ba415936e724512f8","href":"https://static.observableusercontent.com/files/c5d97b9ea309b025c1e598ffb6278663dd7e301246a814a2f5059f634465e0ddc5c12f2fcc8cd9810dce201f060b6aeb27f9693d80499a2ba415936e724512f8","name":"oom_enter_text2.gif","type":"image/gif","lastModified":1686321120272.366,"size":529502}] 2d:{"title":"Out of Market","cells":[{"id":0,"value":"md `# Out of Market \n*Weekly Report*\n\n
`","pinned":false,"hidden":false,"mode":"ojs"},{"id":881,"value":"md `## Step 1\n\nGo to this [spreadsheet](https://stepstonecore.sharepoint.com/:x:/s/AppcastServiceDeliveryTraining/sckc/EcCeQBAFvYJPiwll6neqaNABNhITKUMY8MkdJ8o7bxxdBw?e=PU0G7E) and download it as an \".xlsx\"`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1039,"value":"md `## Step 2`","pinned":false,"hidden":false,"mode":"ojs"},{"id":884,"value":"viewof file1 = Object.assign(Inputs.file({label: \"Load the 'Lyft OOM Database' file\", required: true}), {style: '--label-width: 225px'})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1045,"value":"md `## Step 3`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Object.assign(Inputs.file({label: \"Load Lyft Distance File\", required: true}), {style: '--label-width: 200px'})","pinned":false,"hidden":false,"mode":"ojs"},{"id":187,"value":"md `## Step 4\n#### Updated data\n*(a mini table should appear below)*\n\n

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":918,"value":"table_test_copy = Inputs.table(clean_oom_historical.slice(-1), {\n width: 400,\n layout: 'auto',\n format: {\n Year: d3.format('')\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1050,"value":"md `

\n\nThe data above is the new data extracted from the distance file (i.e. what percent of the applies came from out of market), you'll need to add it to the [Lyft OOM Database](https://stepstonecore.sharepoint.com/:x:/s/AppcastServiceDeliveryTraining/sckc/EcCeQBAFvYJPiwll6neqaNABNhITKUMY8MkdJ8o7bxxdBw?e=PU0G7E) as a new row:\n

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1071,"value":"oom_enter_text2 = FileAttachment(\"oom_enter_text2.gif\").image({width: 400})","pinned":false,"hidden":false,"mode":"ojs"},{"id":772,"value":"md `
\n## Step 5\n### Chart\n*(right-click > copy image)*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":524,"value":"vl.markBar()\n .data(\n clean_oom_historical\n .filter(d => d.Year === 2022 || d.Year === 2023)\n .slice(barchart_slicer)\n )\n .encode(\n vl.column().fieldO('Year'),\n vl.x().fieldO('Date Range')\n .title(null)\n .sort(vl.field('index').order('ascending')),\n vl.y().fieldQ('oom_applies')\n .title('OOM Apply Percent')\n .axis({format: '.0%', tickCount: {interval: 4}})\n )\n .config({\n view: {stroke: null},\n bar: {fill: appcast_color_palette[2], width: {band: 0.75}},\n axis: {labelFontWeight: 200, titleFontWeight: 25},\n axisY: {domain: false, gridOpacity: 0.5, titlePadding: 15},\n axisX: {labelFontSize: 8}\n })\n .resolve({\n scale: {x: 'independent'}\n })\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1091,"value":"md `

\n\n### Control Panel for Chart`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1013,"value":"viewof barchart_slicer = Inputs.range([0, 50], {label: \"Slice...\", step: 1})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1107,"value":"md `

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1110,"value":"md `# by Market`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1124,"value":"vl.markBar({tooltip: true})\n .data(by_market)\n .encode(\n vl.row().fieldN('ACX'),\n vl.x().fieldQ('distance from user to applied city')\n .bin({step: 100})\n .scale({\n domain: [0,3000]\n }),\n vl.y().count()\n )\n .height(200)\n .spacing(100)\n .config({\n view: {stroke: null},\n bar: {fill: appcast_color_palette[5]},\n })\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":88,"value":"
\n
\n
\n

","pinned":false,"hidden":false,"mode":"html"},{"id":20,"value":"md `## Appendix\n\n_Implementation & data stuff_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":198,"value":"### *Data*","pinned":false,"hidden":false,"mode":"md"},{"id":932,"value":"current_year = (await op.year(new Date()))","pinned":true,"hidden":false,"mode":"ojs"},{"id":581,"value":"new_row = await ([\n {\n Year: current_year, \n 'Date Range': current_dateRange, \n oom_applies: oom_percent,\n }\n])","pinned":true,"hidden":false,"mode":"ojs"},{"id":576,"value":"// calculating the percentage out of the total\noom_percent = clean_loaded_file[1].market_count / ( clean_loaded_file[0].market_count + clean_loaded_file[1].market_count)","pinned":true,"hidden":false,"mode":"ojs"},{"id":508,"value":"// below is the calculation of the percent of markets that are out vs in market\nclean_loaded_file = aq.from(raw_data)\n .spread({\n user_city: d => op.split(d['user location'], ',')}, {as: ['city', 'state'] // split the location column into 2 columns\n })\n .filter(\n d => d.city != '' // exclude those that have a city = 'blank'\n )\n .derive({\n market: d => d['distance from user to applied city'] > 40 ? 'oom' : 'im' // tag distances as out of market (oom) and in market (im)\n })\n .groupby('market')\n .rollup({\n market_count: d => op.count() // compute the summary\n })\n .orderby('market')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"raw_data = file.csv()","pinned":true,"hidden":false,"mode":"ojs"},{"id":517,"value":"clean_oom_historical = aq.from(raw_oom_historical)\n .concat(aq.from(new_row))\n .derive({ index: d => op.row_number() - 1 }) // <-- add row indices\n //.orderby(aq.desc('index'))\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":514,"value":"raw_oom_xlsx = file1.xlsx()","pinned":true,"hidden":false,"mode":"ojs"},{"id":646,"value":"raw_oom_historical = raw_oom_xlsx.sheet('in', {headers: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":1114,"value":"md `### *by market*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1117,"value":"viewof by_market = aq.from(raw_data)\n .derive({'distance from user to applied city': d => op.parse_int(d['distance from user to applied city'])})\n .spread({\n user_city: d => op.split(d['user location'], ',')}, {as: ['city', 'state'] // split the location column into 2 columns\n })\n .filter(\n d => d.city != '' // exclude those that have a city = 'blank'\n )\n .derive({\n market: d => d['distance from user to applied city'] > 40 ? 'oom' : 'im' // tag distances as out of market (oom) and in market (im)\n })\n .filter(d => d.market == 'oom')\n .orderby(aq.desc('distance from user to applied city'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":815,"value":"md `### *Other*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":795,"value":"// // blob to download file above\n// blob = new Blob([d3.csvFormat(sc2_database)], {type: \"text/csv\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":69,"value":"// all vega data-sets\n\n// to 'extract' a dataset as an array (only for .csv and .json)\n// use this format: football = v_data['football.json']()\n// for some .csv, it's better to use the url format: zipcodes = v_data['zipcodes.csv'].url\n// this is due to the fact that parsing it as an array of objects results in zipcodes of the form \"00313\" turning into \"313\"\n\nv_data = require('vega-datasets@2')","pinned":false,"hidden":false,"mode":"ojs"},{"id":98,"value":"zips = v_data['zipcodes.csv'].url","pinned":false,"hidden":false,"mode":"ojs"},{"id":159,"value":"us_holidays_2021 = USholidays.filter(d => op.utcyear(date_parser(d.date)) == 2021)","pinned":false,"hidden":false,"mode":"ojs"},{"id":590,"value":"md `### *Weekly File Date Extract*`","pinned":true,"hidden":false,"mode":"ojs"},{"id":588,"value":"dateRange_string_from_file = file.name.slice(14,-4)","pinned":true,"hidden":false,"mode":"ojs"},{"id":598,"value":"dateRange_from_file = op.split(dateRange_string_from_file, '_')","pinned":true,"hidden":false,"mode":"ojs"},{"id":601,"value":"start = date_formatter(date_parser(dateRange_from_file[0]))","pinned":true,"hidden":false,"mode":"ojs"},{"id":614,"value":"end = date_formatter(date_parser(dateRange_from_file[1]))","pinned":true,"hidden":false,"mode":"ojs"},{"id":618,"value":"current_dateRange = start + ' - ' + end","pinned":true,"hidden":false,"mode":"ojs"},{"id":193,"value":"### *Libraries*","pinned":false,"hidden":false,"mode":"md"},{"id":4,"value":"import {vl} from '@vega/vega-lite-api-v5'","pinned":false,"hidden":false,"mode":"ojs"},{"id":36,"value":"import {uniqueValid} from '@uwdata/data-utilities'","pinned":false,"hidden":false,"mode":"ojs"},{"id":59,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"},{"id":137,"value":"// object containing all of the FiPS us county codes\n\nimport {counties} from \"@d3/choropleth\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":391,"value":"// list of US Holidays\n\nimport {USholidays} from \"@observablehq/microsoft-reactor-hackathon\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":480,"value":"import {Legend, Swatches} from \"@d3/color-legend\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":483,"value":"import {DuckDBClient} from \"@cmudig/duckdb\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":486,"value":"import { palettes } from '@makio135/give-me-colors'","pinned":false,"hidden":false,"mode":"ojs"},{"id":496,"value":"// // this is a really cool way of displaying tables. Much more aesthetically pleasing than the defaults of arquero and input.table\n\n// import { formatTable } from \"@saneef/pretty-tables\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":456,"value":"md `### *Functions*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":409,"value":"date_parser = d3.utcParse('%Y-%m-%d')","pinned":true,"hidden":false,"mode":"ojs"},{"id":611,"value":"date_formatter = d3.utcFormat('%m/%d')","pinned":true,"hidden":false,"mode":"ojs"},{"id":208,"value":"### *Chart configuration*","pinned":false,"hidden":false,"mode":"md"},{"id":935,"value":"appcast_color_palette = ['#05062C', '#3138BB','#FF7D6F', '#AF9BFC', '#C7FFE2', '#127FFC', '#FF9B77', '#B7D8FE', '#DAE2FD', '#FAFAFA']","pinned":true,"hidden":false,"mode":"ojs"},{"id":202,"value":"clean_config = ({\n view: { stroke: null },\n axis: {\n gridOpacity: 0.5,\n labelFontWeight: 75,\n domain: false,\n offset: 5,\n titleFontWeight: 350,\n titleFontSize: 10\n },\n axisX: {\n tickCount: 5,\n titlePadding: 10,\n labelPadding: 4\n },\n axisY: {\n ticks: false,\n labelLimit: 300,\n labelFontSize: 7\n },\n title: {\n fontWeight: 300,\n fontSize: 13,\n offset: 25\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":874,"value":"md `## #wip`","pinned":false,"hidden":false,"mode":"ojs"},{"id":747,"value":"md `### Instructions\n\n1. Load the file received by email.\n2. Download the table in the \"Sheet download\" section as a .csv\n3. Go to this [spreadsheet](https://stepstonecore.sharepoint.com/:x:/r/sites/AppcastServiceDeliveryTraining/sckc/_layouts/15/Doc.aspx?sourcedoc=%7B10409EC0-BD05-4F82-8B09-65EA77AA68D0%7D&file=SC2%20OOM%20Database.xlsx&action=default&mobileredirect=true) and replace the content with the content in the sheet you downloaded\n4. On that page, download a copy of the file\n5. In here, replace the attached file with the one you just downloaded\n5. Voila, chart updated!\n\n\n

\n`","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W2f","stdlib":"1","titling":"auto"}