2c:null 2d:["$","title",null,{"children":"Exercise 2: Data questions, visual answers | Albert Teixidó's Workspace | Observable"}] 32:T4c8,storesMap = Plot.plot({ projection: { type: "albers-usa", domain: {type: "MultiPoint", coordinates: [[-122.409591,32.534156], [-116.131211, 42.009518]]}, }, width: width, height: 650, fx: { tickFormat: (d) => `${d.getUTCFullYear()}`, label: null, axis: "top" }, color: {scheme: "Cool", legend: true}, r: {range: [0, 25]}, marks: [ Plot.geo(counties, {strokeOpacity: 0.1}), Plot.geo(california), Plot.dot(total_revenue_per_city, { fx: "date", x: "longitude", y: "latitude", r: "revenue", fill: "revenue", fillOpacity: 0.75, stroke: "#08306b", strokeWidth: .5 }), Plot.dot(total_revenue_per_city, {fx: "date", x: "longitude", y: "latitude", r: .75}), Plot.tip(total_revenue_per_store, Plot.pointer({ fx: "date", x: "longitude", y: "latitude", anchor: "top", title: d => { const city_stores = total_revenue_per_store.filter(store => store.city === d.city && store.date.getUTCFullYear() === d.date.getUTCFullYear()); const tooltipText = city_stores.map(store => `Store: ${store.store_id}, Revenue: $${store.revenue.toLocaleString("en")}`).join('\n'); return `City: ${d.city}\n${tooltipText}`; } })), ] })33:T455,Plot.plot({ subtitle: "Ammount of orders and revenue of pizzas, mean orders per week shown in blue and a regression of the data shown in red.", x: { axis: "bottom", label: "Date", ticks: "1 month"}, y: { axis: "left", label: "Orders" }, grid: true, width: width, marginLeft: 60, marginRight: 0, color: { scheme: "Magma", legend: true, }, marks: [ Plot.lineY(revenue_by_pizza_per_week, { x: "date", y: "orders", z: "name", stroke: "revenue", strokeOpacity: .75, strokeWidth: .9, channels: { name: {value: "name", label: "Name"}, revenue: {value: "revenue", label: "Revenue"}}, tip: {format: { name: true, y: true, revenue: d => `$${d.toLocaleString("en")}`, x: d => `week ${d3.timeFormat('%U')(d)} of ${d.toLocaleString("en", {year: "numeric"})}`, z: false, stroke: false, }} }), Plot.linearRegressionY(revenue_by_pizza_per_week, {x: "date", y: "orders", z: null, stroke: "red"}), Plot.dotY(mean_revenue_per_week, {x: "date", y: "mean", z: null, fill: "blue", r: 2.5, stroke: "white"}), Plot.ruleY([0]), ], })34:[{"id":"ff73290f3fccc5db55f09031bb845e639a07e444ca9fbcf83f2dbb4600a38bf4baccb2a85df3c24dba57a4ed64c230dcf1a87604453969495db835c5f3cbebf2","href":"https://static.observableusercontent.com/files/ff73290f3fccc5db55f09031bb845e639a07e444ca9fbcf83f2dbb4600a38bf4baccb2a85df3c24dba57a4ed64c230dcf1a87604453969495db835c5f3cbebf2","name":"us-counties-10m.json","type":"application/json","lastModified":1704385312628.259,"size":842177}] 30:{"id":"dbec8873fa1a7c61","slug":null,"title":"Exercise 2: Data questions, visual answers","thumbnail":null,"access_level":"public","create_time":"$D2024-01-04T16:21:52.628Z","update_time":"$D2024-01-04T16:21:52.628Z","view_time":null,"first_public_time":"$D2024-01-04T16:21:52.628Z","has_unpublished_edits":null,"fork":{"id":"56f7c9059e8ab6d1","slug":null,"title":"Exercise 2: Data questions, visual answers","owner":{"id":"f6d094e91910d678","login":"udg-master-data-science","name":"UdG | Master's in Data Science"}},"stars":0,"starred":false,"owner":{"type":"team","id":"be28382f76819dc3","login":"albert-teixido-ws","name":"Albert Teixidó's Workspace","avatar_url":"https://avatars-next.observableusercontent.com/bb37ae9e7a846801373145cb7f7a79bfc4647a40a6b3f0fffdc8653987b1a770","tier":"starter_2024"},"version":2506,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"5276b422101e1a12","login":"albert-teixido","name":"Albert Teixidó","avatar_url":"https://avatars-next.observableusercontent.com/3a403d40b3fff1559ad975290add47cbd2e6f663f1e9f6bd4ac407036721cc4f","tier":null}],"members":[{"type":"individual","id":"5276b422101e1a12","login":"albert-teixido","name":"Albert Teixidó","avatar_url":"https://avatars-next.observableusercontent.com/3a403d40b3fff1559ad975290add47cbd2e6f663f1e9f6bd4ac407036721cc4f","tier":null,"role":"editor"},{"type":"team","id":"be28382f76819dc3","login":"albert-teixido-ws","name":"Albert Teixidó's Workspace","avatar_url":"https://avatars-next.observableusercontent.com/bb37ae9e7a846801373145cb7f7a79bfc4647a40a6b3f0fffdc8653987b1a770","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Exercise 2: Data questions, visual answers","cells":[{"id":0,"value":"# Exercise 2: Data questions, visual answers\n## Welcome to the data team at Bake Union Pizza —a cooperative of pizza-makers!\n\nThe elected board of directors has hired a new manager of operations. She is very data-driven —great news for your team. She wants to understand how the business is doing and has come to you for answers.\n\n**You'll need to:**\n* Come up with three simple questions for your data\n* Transform the data to answer those questions.\n* Plot it to answer each question with a visualization.\n\n**Extra credit:**\n* Re-read [`Plot` transforms](https://observablehq.com/plot/features/transforms) and use them to arrange the data for one of the questions.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1012,"value":"Plot.plot({\n width: width,\n height: 100,\n x: {\n label: \"Weight in the grading (%)\",\n transform: (d) => d * 100\n },\n color: {\n type: \"ordinal\",\n domain: grading.map((d) => d.label),\n range: [\"#f0f0f0\",\"#80d6c2\",\"#00ad85\"]\n },\n marks: [\n Plot.barX(grading, {x1: 0, x2: \"value\", fill: \"label\"}),\n Plot.ruleX([0, 0.5, 1]),\n Plot.ruleX([0.5], { stroke: \"#dcdcdc\" }),\n Plot.text(grading.filter(d=> d.label !== \"This\"), {x: \"x\", text: \"label\", fill: \"#000\" }),\n Plot.text(grading.filter(d=> d.label === \"This\"), {x: \"x\", text: \"label\", fill: \"#FFF\", fontWeight: \"bold\", fontSize: \"1rem\" }),\n\n ],\n});","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":365,"value":"---\n## Data\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":948,"value":"import {daily_orders_product, daily_orders, stores} from \"@udg-master-data-science/data-playground\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1008,"value":"daily_orders_product","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":955,"value":"daily_orders","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":957,"value":"stores","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":945,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2050,"value":"questions = md`

Questions

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2013,"value":"{return htl.html`\n

\n`}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":924,"value":"---\n## Data transformations","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":905,"value":"Transform the data and explain why you're doing so.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":959,"value":"

NOTE
If you have trouble transforming your data using JavaScript, you can do those transformations somewhere else and then bring your data back here. I strongly recommend you not doing so, but I won't penalize you if you do.

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1626,"value":"## Ease of use functions\n1. joinArrays tries to accomplish what LEFT_JOIN in SQL does.\n2. filterFields aims to be a SELECT in SQL.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1052,"value":"function joinArrays(arr1, arr2, first_key, second_key = \"\") {\n if(second_key == \"\") second_key = first_key;\n return arr1.map(item1 => {\n const matchingItem = arr2.find(item2 => item1[first_key] === item2[second_key]);\n if (matchingItem) return { ...item1, ...matchingItem };\n return item1;\n });\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1618,"value":"function filterFields(arr, fields) {\n const result = arr.map(item => {\n const newItem = {};\n fields.forEach(field => {\n if (item.hasOwnProperty(field)) newItem[field] = item[field];\n });\n return newItem;\n });\n\n return result;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1980,"value":"question_1 = md`

1. What's the total distribution of revenue for Bake Union Pizza per year? [Visualize]

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1616,"value":"What this question is aimed at is to be able to see the progression of the stores of Bake Union Pizza, know how much they made and contributed to the total revenue on that year. Also to see what cities generate the most revenue.\n\nFor this question the daily orders and the stores will be needed, getting the dates of the orders, the strore(city), the revenue and the location of the store(city).\n\nThis question will be acomplished by grouping by year and store(city) and add the revenue for each of these per year.\n\nTo be able to acomplish the expected results, the total ammount of revenue Bake Union Pizza produced each year.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1708,"value":"daily_orders_stores = filterFields(joinArrays(daily_orders, stores, \"store_id\", \"id\"), [\"order_date\", \"city\", \"store_id\", \"revenue\", \"latitude\", \"longitude\"]);","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1030,"value":"total_revenue_per_city = Object.values(daily_orders_stores.reduce((accumulator, current) => {\n const year = current.order_date.toLocaleString(undefined, {year: \"numeric\"})\n const city = current.city;\n const store = current.store_id;\n const revenue = current.revenue;\n\n const key = `${year}-${city}`;\n\n if(!accumulator[key]) \n accumulator[key] = {date: new Date(year), city: city, store_id: store, revenue: revenue, latitude: current.latitude, longitude: current.longitude};\n else accumulator[key].revenue += current.revenue;\n \n return accumulator;\n}, {}));","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2444,"value":"total_revenue_per_store = Object.values(daily_orders_stores.reduce((accumulator, current) => {\n const year = current.order_date.toLocaleString(undefined, {year: \"numeric\"})\n const city = current.city;\n const store = current.store_id;\n const revenue = current.revenue;\n\n const key = `${year}-${city}-${store}`;\n\n if(!accumulator[key]) \n accumulator[key] = {date: new Date(year), city: city, store_id: store, revenue: revenue, latitude: current.latitude, longitude: current.longitude};\n else accumulator[key].revenue += current.revenue;\n \n return accumulator;\n}, {}));","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1562,"value":"total_revenue_per_year = total_revenue_per_city.reduce((accumulator, current) => {\n const year = current.date.toLocaleString(undefined, {year: \"numeric\"});\n const revenue = current.revenue;\n\n if(!accumulator[year]) accumulator[year] = {year: year, revenue: revenue};\n else accumulator[year].revenue += revenue;\n\n return accumulator;\n}, {});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1043,"value":"total_revenue_per_city","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1993,"value":"question_2 = md`

2. What has been the popularity for every pizza over time? [Visualize]

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1630,"value":"What is expected to be seen in this question is the ammount of orders generated by each pizza, this is calculated per week to get a more accurate reading.\n\nThe data that is aimed to show are the curves each pizza do for the ammount of orders per each week, also showing the revenue they generated.\n\nThis question will be acomplished by grouping by the week of the year the order was placed, the name of the pizza, the revenue and orders for each of the pizzas.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1277,"value":"revenue_by_pizza_per_week = Object.values(daily_orders_product.reduce((accumulator, current) => {\n const week = d3.timeWeek(current.order_date);\n const name = current.name;\n const revenue = current.revenue;\n const orders = current.orders;\n \n const key = `${week}-${name}`;\n\n if(!accumulator[key]) accumulator[key] = {date: week, name: name, revenue: revenue, orders: orders};\n else {\n accumulator[key].revenue += revenue;\n accumulator[key].orders += orders;\n }\n \n return accumulator;\n}, {})).sort((a, b) => {return a.date > b.date});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1661,"value":"mean_revenue_per_week = Object.values(revenue_by_pizza_per_week.reduce((mean, current) => {\n const date = current.date;\n\n if(!mean[date]) mean[date] = {date: date, mean: current.orders, count: 1};\n else {\n mean[date].mean += current.orders;\n mean[date].count += 1;\n }\n \n return mean;\n}, {})).map((x) => ({date: x.date, mean: x.mean / x.count, revenue: x.mean}));","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1287,"value":"data = revenue_by_pizza_per_week","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"data","database":"$undefined"},{"id":2006,"value":"question_3 = md`

3. What's the revenue per category for every year and their pizzas? [Visualize]

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1958,"value":"This question will resolve, not only the revenue each pizza made over the years, it will show at which category they belong, to be able to show their distribution.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1790,"value":"pizza_revenue_category = filterFields(daily_orders_product, [\"category\", \"name\", \"order_date\", \"revenue\"]);","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1793,"value":"pizza_revenue_category_year = Object.values(pizza_revenue_category.reduce((accumulator, current) => {\n const year = current.order_date.toLocaleString(undefined, {year: \"numeric\"})\n const category = current.category;\n const name = current.name;\n\n const key = `${year}-${category}-${name}`;\n\n if(!accumulator[key]) accumulator[key] = {date: year, category: category, name: name, revenue: current.revenue};\n else accumulator[key].revenue += current.revenue;\n \n return accumulator;\n}, {})).sort((a, b) => {return b.revenue - a.revenue});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2296,"value":"revenue_category_year = Object.values(pizza_revenue_category_year.reduce((accumulator, current) => {\n const year = current.date;\n const category = current.category;\n\n const key = `${year}-${category}`;\n\n if(!accumulator[key]) accumulator[key] = {date: year, category: category, revenue: current.revenue};\n else accumulator[key].revenue += current.revenue;\n \n return accumulator;\n}, {})).sort((a, b) => {return b.category.localeCompare(a.category)});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1895,"value":"pizza_revenue_category_year","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":360,"value":"---\n## Visual answers","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":2024,"value":"visual_1 = md`

1. Distribution of revenue for Bake Union Pizza per year [Question]

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2404,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2033,"value":"visual_2 = md`

2. Popularity for pizza over time [Question]

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":913,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2037,"value":"visual_3 = md`

3. Revenue per category each year by pizza [Question]

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2243,"value":"viewof year = Inputs.radio([\"2020\", \"2021\", \"2022\"], {label: \"Select year to plot:\", value: \"2020\", labelWidth: 200, width: 100})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2222,"value":"Plot.plot({\n subtitle: \"Total ammount of revenue per pizza, stacked by category and sorted by year. Also shows the total revenue per category per year.\",\n fx: {label: null},\n grid: true,\n width: width,\n marginLeft: 55,\n marginRight: 0,\n color: {scheme: \"Viridis\"},\n y: {domain: [0, 9500000]},\n marks: [\n Plot.barY(pizza_revenue_category_year, Plot.stackY(\n {order: \"y\", reverse: true},\n Plot.groupZ({y: \"sum\"}, {\n filter: (d) => d.date == year, y: \"revenue\", z: \"name\", fill: \"name\", fx: \"category\", inset: 0.5, \n tip: {format: {fill: true, y: d => `$${d.toLocaleString(\"en\")}`, z: false, fx: false}}\n })\n )),\n Plot.tip(revenue_category_year, {\n filter: (d) => d.date == year, y: \"revenue\", fx: \"category\", \n text: d => `$${d.revenue.toLocaleString(\"en\")}`, dy: -7, anchor: \"bottom\",\n title: d => `${d.category}: $${d.revenue.toLocaleString(\"en\")}`,\n \n }),\n Plot.ruleY([0])\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1212,"value":"## Getting the data for the geographic data of california","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1058,"value":"us = FileAttachment(\"us-counties-10m.json\").json()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1061,"value":"california = new Map(topojson.feature(us, us.objects.states).features.map(d => [d.id, d])).get(\"06\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1185,"value":"counties = ({\n type: \"FeatureCollection\",\n features: Object.values(topojson.feature(us, us.objects.counties).features.reduce((accumulator, current) => {\n if(current.id.slice(0, 2) == \"06\") accumulator[current.id] = current;\n \n return accumulator;\n }, {}))\n});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3,"value":"---\n#### Addendum: data, libraries, other ...","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":982,"value":"import {boxStyles} from \"@udg-master-data-science/data-playground\" ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1013,"value":"grading = [\n {value: 1, label: \"Total grade\", x: 0.65},\n {value: .3, label: \"Weekly exercises\", x: 0.2},\n {value: .1, label: \"This\", x: 0.05}\n]","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W34","stdlib":"1","titling":"auto"}} 31:"$30:body"