2f:T712,// clean up process for benefits viewof benefits = aq.from(clean) .select('category', 'company', 'benefits') // select these columns only .derive({ num_benefits: aq.escape( d => d3.count(d.benefits) ) // get a count of benefits by row }) .dedupe('category','company','benefits') // deduplicate rows with the same benefit list .filter(d => d.num_benefits > 0) // remove those without benefits (for other purposes, this might be useful) // below is the part where I very ungracefully work with the arrays // starts here .derive({ benefits: d => op.replace(d.benefits, "['", "") }) .derive({ benefits: d => op.replace(d.benefits, "']", "") }) .derive({ benefits: d => op.replace(d.benefits, /'/g, "") }) //ends here ^ .groupby('category','company') // once the array is clean, group all benefits by company .rollup({ all_benefits: d => op.array_agg(d.benefits) // one company, one array of benefits }) .derive({ all_benefits: d => op.split(d.all_benefits, ',') }) .unroll('all_benefits') // do one company and one benefit per row .derive({ all_benefits: d => op.trim(d.all_benefits) }) .dedupe('company', 'all_benefits') // remove duplicates that resulted from the above .rename({all_benefits: 'benefit'}) .derive({ benefit_category: d => op.includes(op.lower(d.benefit), 'match') === true && op.includes(op.lower(d.benefit), '401') === true ? '401(k) match' : op.includes(d.benefit, '401') === true ? '401(k)' : op.includes(op.lower(d.benefit), 'insurance') === true && op.includes(op.lower(d.benefit), 'health') === true || op.includes(op.lower(d.benefit), 'vision') === true || op.includes(op.lower(d.benefit), 'dental') === true ? 'Health, vision & dental insurance' : d.benefit }) .view()30:T46e,viewof clean = aq.from(raw_data) .derive({date: d => op.parse_date(d.date)}) .derive({ category: d => // Sales op.includes(op.lower(d.keyword), 'account+manager') == true || op.includes(op.lower(d.keyword), 'sales+manager') == true || op.includes(op.lower(d.keyword), 'sales+representative') == true || op.includes(op.lower(d.keyword), 'inside+sales+representative') == true ? 'Sales' : // Customer Service op.includes(op.lower(d.keyword), 'customer+service') == true ? 'Customer Service' : // Corporate op.includes(op.lower(d.keyword), 'business+analyst') == true || op.includes(op.lower(d.keyword), 'accounts+receivable') == true || op.includes(op.lower(d.keyword), 'it+manager') == true ? 'Corporate' : //Warehouse op.includes(op.lower(d.keyword), 'warehouse') == true ? 'Warehouse' : 'Other' }) .view() // Sales: // account+manager // sales+manager // sales+representative // inside+sales+representative // Customer Service: // customer+service // Corporate: // business+analyst // accounts+receivable // it+manager31:[] 2d:{"title":"Uline - Competitive Intel - Benefits","cells":[{"id":0,"value":"md `# Uline - Competitive Intel - Benefits`","pinned":false,"hidden":false,"mode":"ojs"},{"id":503,"value":"viewof file = Inputs.file({label: \"Data\", required: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":895,"value":"vl.markCircle({fill: appcast_color_palette[4], opacity: 1, size: 150})\n .title('Warehouse')\n .data(benefits.objects().filter(d => warehouse_top_companies.includes(d.company)))\n .encode(\n vl.x().fieldN('benefit_category')\n .title('Benefit')\n .axis({orient: 'top'}),\n vl.y().fieldN('company')\n .title('Company')\n )\n .config({\n view: {stroke: null, step: 20},\n //view: {stroke: 'black', strokeOpacity: 0.5, step: 20},\n axis: {\n grid: true, gridOpacity: 0.5, \n tickOpacity: 0.75, domain: false, \n labelFontWeight: 200, labelFontSize: 10,\n titleFontWeight: 50\n },\n axisY: {titlePadding: 50},\n })\n //.width(400)//.height(300)\n .padding({top: 25, bottom: 50, right: 50})\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1568,"value":"vl.markCircle({fill: appcast_color_palette[4], opacity: 1, size: 150})\n .title('Corporate')\n .data(benefits.objects().filter(d => corp_top_companies.includes(d.company)))\n .encode(\n vl.x().fieldN('benefit_category')\n .title('Benefit')\n .axis({orient: 'top'}),\n vl.y().fieldN('company')\n .title('Company')\n )\n .config({\n view: {stroke: null, step: 20},\n axis: {\n grid: true, gridOpacity: 0.5, \n tickOpacity: 0.75, domain: false, \n labelFontWeight: 200, labelFontSize: 10,\n titleFontWeight: 50\n },\n axisY: {titlePadding: 50},\n })\n //.width(400)//.height(300)\n .padding({top: 25, bottom: 50, right: 50})\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1570,"value":"vl.markCircle({fill: appcast_color_palette[4], opacity: 1, size: 150})\n .title('Customer Service')\n .data(benefits.objects().filter(d => cust_service_top_companies.includes(d.company)))\n .encode(\n vl.x().fieldN('benefit_category')\n .title('Benefit')\n .axis({orient: 'top'}),\n vl.y().fieldN('company')\n .title('Company')\n )\n .config({\n view: {stroke: null, step: 20},\n //view: {stroke: 'black', strokeOpacity: 0.5, step: 20},\n axis: {\n grid: true, gridOpacity: 0.5, \n tickOpacity: 0.75, domain: false, \n labelFontWeight: 200, labelFontSize: 10,\n titleFontWeight: 50\n },\n axisY: {titlePadding: 50},\n })\n //.width(400)//.height(300)\n .padding({top: 25, bottom: 50, right: 50})\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1574,"value":"vl.markCircle({fill: appcast_color_palette[4], opacity: 1, size: 150})\n .title('Sales')\n .data(benefits.objects().filter(d => sales_top_companies.includes(d.company)))\n .encode(\n vl.x().fieldN('benefit_category')\n .title('Benefit')\n .axis({orient: 'top'}),\n vl.y().fieldN('company')\n .title('Company')\n )\n .config({\n view: {stroke: null, step: 20},\n //view: {stroke: 'black', strokeOpacity: 0.5, step: 20},\n axis: {\n grid: true, gridOpacity: 0.5, \n tickOpacity: 0.75, domain: false, \n labelFontWeight: 200, labelFontSize: 10,\n titleFontWeight: 50\n },\n axisY: {titlePadding: 50},\n })\n //.width(400)//.height(300)\n .padding({top: 25, bottom: 50, right: 50})\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":834,"value":"htl.html `

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1764,"value":"md `### *work in progress*\n*(finding benefit categories)*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1739,"value":"viewof benefit_category = benefits\n .select('benefit')\n .dedupe()\n .orderby('benefit')\n .derive({\n benefit_category: \n d => op.includes(op.lower(d.benefit), 'match') === true && op.includes(op.lower(d.benefit), '401') === true ? '401(k) match' : \n op.includes(d.benefit, '401') === true ? '401(k)' :\n op.includes(op.lower(d.benefit), 'insurance') === true && op.includes(op.lower(d.benefit), 'health') === true || op.includes(op.lower(d.benefit), 'vision') === true || op.includes(op.lower(d.benefit), 'dental') === true ? 'Health, vision & dental insurance' : d.benefit\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":874,"value":"$2f","pinned":true,"hidden":false,"mode":"ojs"},{"id":1548,"value":"warehouse_top_companies = aq.from(top_companies.get('Warehouse'))\n .filter(d => op.rank() <= 10)\n .select('company')\n .array('company')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1556,"value":"sales_top_companies = aq.from(top_companies.get('Sales'))\n .filter(d => op.rank() <= 10)\n .select('company')\n .array('company')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1558,"value":"cust_service_top_companies = aq.from(top_companies.get('Customer Service'))\n .filter(d => op.rank() <= 15)\n .select('company')\n .array('company')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1560,"value":"corp_top_companies = aq.from(top_companies.get('Corporate'))\n .filter(d => op.rank() <= 15)\n .select('company')\n .dedupe()\n .array('company')","pinned":false,"hidden":false,"mode":"ojs"},{"id":1000,"value":"top_companies = clean_count_overtime\n .groupby('category','company')\n .rollup({\n count: d => op.sum(d.count)\n })\n .orderby('category',aq.desc('count'))\n .groupby('category')\n .objects({grouped: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":88,"value":"
\n
\n
\n

","pinned":false,"hidden":false,"mode":"html"},{"id":1290,"value":"viewof toggle = Inputs.toggle({label: \"Show labels\", value: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1156,"value":"Plot.plot({\n width: 500,\n height: 400,\n marginLeft: 200,\n insetLeft: 15,\n grid: true,\n r: {\n range: [0, 20]\n },\n y: {\n tickSize: 0,\n label: null\n },\n x: {\n label: null,\n type: 'point'\n },\n marks: [\n //Plot.frame(),\n Plot.dot(clean_count_time_week.objects().filter(d => warehouse_top_companies.includes(d.company)), {\n x: 'week_date',\n y: 'company',\n r: 'percent',\n fill: appcast_color_palette[5],\n opacity: 0.9,\n interval: d3.utcMonth\n }),\n toggle ? Plot.text(clean_count_time_week.objects().filter(d => warehouse_top_companies.includes(d.company)), {\n x: 'week_date',\n y: 'company',\n r: 'percent',\n fill: d => d.percent <= 0.05 ? null : 'white',\n fontSize: 11,\n text: d => `${d3.format('.0%')(d.percent)}`,\n interval: d3.utcMonth\n }) : null,\n Plot.axisX({anchor: 'top', tickFormat: '%b %d'})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1850,"value":"Plot.plot({\n width: 500,\n height: 400,\n marginLeft: 200,\n insetLeft: 15,\n y: {\n tickSize: 0,\n label: null\n },\n x: {\n label: null,\n type: 'point'\n },\n marks: [\n Plot.text(clean_count_time_week.objects().filter(d => warehouse_top_companies.includes(d.company)), \n Plot.sort(d => d.company ,{\n x: 'week_date',\n y: 'company',\n fontSize: 11,\n text: d => `${d3.format('.2%')(d.percent)}`,\n interval: d3.utcMonth,\n })),\n Plot.axisX({anchor: 'top', tickFormat: '%b %d'})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1892,"value":"d3.extent(clean_count_overtime, d => d.date)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1966,"value":"viewof end_date = Inputs.date({label: \"End date\", value: \"2023-03-31\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1375,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[0][0]}` + ' Top Competitors (excluding Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n // color: {\n // legend: true\n // },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Warehouse')\n //.filter(d => warehouse_top_companies.includes(d.company))\n .filter(d => d.company != 'Uline'), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1948,"value":"Swatches(d3.scaleOrdinal(['Uline', 'Others'],[appcast_color_palette[4], appcast_color_palette[5]]))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1936,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n // facet: {\n // data: clean_count_overtime.objects(),\n // y: 'market',\n // marginRight: 100\n // },\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[0][0]}` + ' Top Competitors (including Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n color: {\n legend: true\n },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Warehouse')\n .filter(d => warehouse_top_companies.includes(d.company)), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: d => d.company == 'Uline' ? appcast_color_palette[4] : appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1939,"value":"warehouse_top_companies","pinned":false,"hidden":false,"mode":"ojs"},{"id":1914,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[1][0]}` + ' Top Competitors (excluding Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n // color: {\n // legend: true\n // },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Sales')\n .filter(d => sales_top_companies.includes(d.company))\n .filter(d => d.company != 'Uline'), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: appcast_color_palette[5],\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1985,"value":"Swatches(d3.scaleOrdinal(['Uline', 'Others'],[appcast_color_palette[4], appcast_color_palette[5]]))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1982,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[1][0]}` + ' Top Competitors (including Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n color: {\n legend: true\n },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Sales')\n .filter(d => sales_top_companies.includes(d.company)), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: d => d.company == 'Uline' ? appcast_color_palette[4] : appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1992,"value":"sales_top_companies","pinned":false,"hidden":false,"mode":"ojs"},{"id":2001,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[2][0]}` + ' Top Competitors (excluding Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n // color: {\n // legend: true\n // },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Customer Service')\n .filter(d => cust_service_top_companies.includes(d.company))\n .filter(d => d.company != 'Uline'), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1997,"value":"Swatches(d3.scaleOrdinal(['Uline', 'Others'],[appcast_color_palette[4], appcast_color_palette[5]]))","pinned":false,"hidden":false,"mode":"ojs"},{"id":2006,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[2][0]}` + ' Top Competitors (including Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n color: {\n legend: true\n },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Customer Service')\n .filter(d => cust_service_top_companies.includes(d.company)), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: d => d.company == 'Uline' ? appcast_color_palette[4] : appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2012,"value":"cust_service_top_companies","pinned":false,"hidden":false,"mode":"ojs"},{"id":2016,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[3][0]}` + ' Top Competitors (excluding Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n // color: {\n // legend: true\n // },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Corporate')\n .filter(d => corp_top_companies.includes(d.company))\n .filter(d => d.company != 'Uline'), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1999,"value":"Swatches(d3.scaleOrdinal(['Uline', 'Others'],[appcast_color_palette[4], appcast_color_palette[5]]))","pinned":false,"hidden":false,"mode":"ojs"},{"id":2021,"value":"Plot.plot({\n height: 200,\n insetTop: 25,\n y: {\n label: 'Total count of posts by ' + `${Array.from(top_companies)[3][0]}` + ' Top Competitors (including Uline)'\n },\n x: {\n nice: d3.utcMonth\n },\n color: {\n legend: true\n },\n marks: [\n Plot.gridX({interval: 'month'}),\n Plot.gridY(),\n Plot.rectY(clean_count_overtime.objects()\n .filter(d => d.date <= end_date)\n .filter(d => d.category == 'Corporate')\n .filter(d => corp_top_companies.includes(d.company)), \n Plot.binX({y: 'sum'},{\n x: 'date',\n y: 'count',\n interval: d3.utcDay,\n fill: d => d.company == 'Uline' ? appcast_color_palette[4] : appcast_color_palette[5],\n //fill: 'company'\n })),\n Plot.axisX({ticks: 'month'}),\n Plot.frame()\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2027,"value":"corp_top_companies","pinned":false,"hidden":false,"mode":"ojs"},{"id":2087,"value":"Plot.plot({\n width: 1250,\n projection: 'albers',\n facet: {\n data: geo_clean,\n x: 'category'\n },\n color: {\n scheme: 'reds',\n legend: true\n },\n r: {\n range: [1,7.5]\n },\n marks: [\n Plot.geo(states, {clip: 'frame', stroke: 'gray', strokeOpacity: 0.25, strokeWidth: 0.5}), // Add the state boundaries\n Plot.geo(nation, {strokeWidth: 0.75, clip: 'frame'}),\n Plot.dot(geo_clean, Plot.group({r: 'count', fill: 'count'},{\n x: d => geomap.get(d.market).lng, //Provide longitude values\n y: d => geomap.get(d.market).lat, // Provide latitude values\n // title: d => `${d.count} Posts in ${zip_map.get(d.corrected_zip).city}`,\n })),\n // Plot.text(cities_data_collect, {\n // x: 'lng',\n // y: 'lat',\n // text: d => `${d.geo_city}`,\n // stroke: 'white',\n // fill: 'black',\n // dy: -10\n // })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"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":1010,"value":"viewof clean_count_time_week = clean_count_overtime\n .derive({date: d => op.parse_date(d.date)})\n .derive({\n week_date: aq.escape( d => d3.utcMonth(d.date) )\n })\n .groupby('week_date', 'company')\n .rollup({\n count: d => op.sum(d.count)\n })\n .groupby('week_date')\n .derive({\n percent: d => d.count/op.sum(d.count)\n })\n // .filter(d => +d.week_date == +op.utcdatetime(2023,1,27))\n // .rollup({\n // percent: d => op.sum(d.percent)\n // })\n .view()","pinned":false,"hidden":false,"mode":"ojs"},{"id":942,"value":"viewof color_selector = Inputs.color({label: \"Favorite color\", value: \"#4682b4\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":771,"value":"viewof titles = aq.from(raw_data)\n // .derive({\n // date: d => op.parse_date(d.date)\n // })\n .select('job_title', 'company')\n .dedupe()\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":925,"value":"// zips_filtered = zips_clean.filter(d => company_selector.includes(d.company))","pinned":true,"hidden":false,"mode":"ojs"},{"id":673,"value":"// zips_clean = aq.from(raw_data)\n// .derive({\n// corrected_zip: aq.escape( d => d3.count(d.geo_zipcode) == 4 ? `0${d.geo_zipcode}` : d.geo_zipcode ),\n// date: d => op.parse_date(d.date)\n// })\n// .select('date', 'company', 'corrected_zip')\n// .derive({week_date: aq.escape(d => d3.utcMonday(d.date))})\n// .join(aq.from(zip_codes), ['corrected_zip', 'zip_code'])\n// .groupby('week_date', 'company', 'latitude', 'longitude', 'corrected_zip')\n// .count()\n// .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":710,"value":"import {us} from \"@observablehq/build-your-first-choropleth-map-with-observable-plot\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":733,"value":"states = topojson.feature(us, us.objects.states)","pinned":true,"hidden":false,"mode":"ojs"},{"id":793,"value":"nation = topojson.feature(us, us.objects.nation)","pinned":true,"hidden":false,"mode":"ojs"},{"id":857,"value":"counties_ = topojson.feature(us, us.objects.counties)","pinned":true,"hidden":false,"mode":"ojs"},{"id":723,"value":"// latitude_map = new Map(zips_filtered.map(({corrected_zip, latitude}) => [corrected_zip, latitude]))","pinned":true,"hidden":false,"mode":"ojs"},{"id":726,"value":"// longitude_map = new Map(zips_filtered.map(({corrected_zip, longitude}) => [corrected_zip, longitude]))","pinned":true,"hidden":false,"mode":"ojs"},{"id":508,"value":"viewof clean_count_overtime = clean\n .groupby('market','category','date', 'company')\n .count()\n .orderby('market','date', 'category', aq.desc('count'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":871,"value":"md `## Clean data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1979,"value":"clean.select('category').dedupe().view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":869,"value":"$30","pinned":true,"hidden":false,"mode":"ojs"},{"id":2091,"value":"md `# Geographic stuff`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2079,"value":"geo_clean = clean \n .derive({\n market: d => op.lower(d.market)\n })\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2075,"value":"geo_map = aq.from(us_cities_lat_long)\n .derive({\n city_state: d => `${op.lower(d.city)}, ${op.lower(d.state_id)}`\n })\n //.groupby('city_state')\n .objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":2104,"value":"geomap = new Map(geo_map.map(d => [d.city_state, d]))","pinned":true,"hidden":false,"mode":"ojs"},{"id":2073,"value":"import {us_cities_lat_long} from \"346afbc3992b2dbd\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":705,"value":"zip_codes = d3.csvParse(data)","pinned":true,"hidden":false,"mode":"ojs"},{"id":689,"value":"prox_url = 'https://corsproxy.io?' + encodeURIComponent(v_data['zipcodes.csv'].url)","pinned":true,"hidden":false,"mode":"ojs"},{"id":698,"value":"data = fetch(prox_url).then(response => response.text())","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"raw_data = file.csv({typed: false})","pinned":true,"hidden":false,"mode":"ojs"},{"id":187,"value":"### *Secondary data sources*","pinned":false,"hidden":false,"mode":"md"},{"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 = d3.csv(v_data['zipcodes.csv'].url)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1099,"value":"zip_map = new Map(zips.map(d => [d.zip_code, d]))\n\n//broadband_access_2018 = new Map(us_broadband_2018.map(({id, broadband}) => [id, broadband]))","pinned":true,"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":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\nimport { formatTable } from \"@saneef/pretty-tables\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":514,"value":"import {textcolor} from \"@observablehq/text-color-annotations-in-markdown\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":519,"value":"import {viewof fileLog} from \"5b757a31fd4f7329\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":456,"value":"md `### *Functions*`","pinned":false,"hidden":false,"mode":"ojs"},{"id":409,"value":"date_parser = d3.utcParse('%m/%d/%Y')","pinned":true,"hidden":false,"mode":"ojs"},{"id":208,"value":"### *Chart configuration*","pinned":false,"hidden":false,"mode":"md"},{"id":1379,"value":"appcast_color_palette = ['#3138BB', '#FF7D6F', '#AF9BFC', '#C7FFE2', '#127FFC', '#FF9B77']","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":632,"value":"// sample of how to place html blocks in a grid\n\n// html `

\n// ${viewof spend}\n// ${viewof cpl}\n// ${viewof cpc}\n//

`","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W31","stdlib":"1","titling":"auto"}