2c:null 2d:["$","title",null,{"children":"Exercise 2: Data questions, visual answers | UdG | Master's in Data Science | Observable"}] 32:[] 30:{"id":"13db02ebcf8cfc1d","slug":null,"title":"Exercise 2: Data questions, visual answers","thumbnail":"aceccc03acc3a230322de2319cf0029357f5e84a3de72f1a712448e559be01bd","access_level":"public","create_time":"$D2023-12-02T16:12:57.322Z","update_time":"$D2023-12-15T13:35:59.061Z","view_time":null,"first_public_time":"$D2023-12-02T16:12:57.322Z","has_unpublished_edits":null,"fork":{"id":"723babda014c5c9c","slug":"exercise-2-data-questions-visual-answers","title":"Exercise 2: Data questions, visual answers","owner":{"id":"f6d094e91910d678","login":"udg-master-data-science","name":"UdG | Master's in Data Science"}},"stars":1,"starred":false,"owner":{"type":"team","id":"f6d094e91910d678","login":"udg-master-data-science","name":"UdG | Master's in Data Science","avatar_url":"https://avatars-next.observableusercontent.com/43ba1b8a69dd1722287c44e61dfee202393e63b350f5814991b6b0af9418435e","tier":"starter_2024"},"version":1811,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"a59d6e7b0b9bfae4","login":"jordi-fornos","name":"Jordi Fornós","avatar_url":"https://avatars-next.observableusercontent.com/38fcc598550ab1593a12b5a2b4ab980b33f8501289bf10bd9751dfe211dca9ab","tier":null}],"members":[{"type":"team","id":"f6d094e91910d678","login":"udg-master-data-science","name":"UdG | Master's in Data Science","avatar_url":"https://avatars-next.observableusercontent.com/43ba1b8a69dd1722287c44e61dfee202393e63b350f5814991b6b0af9418435e","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":1019,"value":"If I put myself in the general manager's shoes, I would like to know these three first following questions:\n\n**Question 1:** General business evolution. How are sales doing? and how is current year compared to others? (monthly)\n\n**Question 2:** Which are out top five stores and which our bottom five?\n\n**Question 3:** Which are our main sales products? (in units)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","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":"---\n## Questions\n\nIf I put myself in the general manager's shoes, I would like to know these three first following questions:\n\n1. General business evolution. How are sales doing? and how is current year compared to others? (monthly)\n2. Which are out top the best and worst stores?\n3. Which are our main sales products? (revenue and units)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":924,"value":"---\n## Data transformations","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1339,"value":"We create two diffrent datasets. One with the total revenue per month and year, and another to have an acumulated revenue value por each month of every year","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1335,"value":"SELECT YEAR(order_date) as rev_year, MONTH(order_date) as rev_month, sum(revenue) as revenue\n FROM daily_orders\n GROUP BY rev_month, rev_year\n ORDER BY rev_month, rev_year","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"revenue_per_month","database":"var:daily_orders"},{"id":1345,"value":"Now, the one with the acumulated revenue value for each month.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1377,"value":"acumulatedrevenue = {\n const datarev = []\n var curMonth = 0\n var curYear = 0\n var valAcum20 = 0\n var valAcum21 = 0\n var valAcum22 = 0\n revenue_per_month.forEach(elemento => {\n if (curYear == 0){ //només pel primer\n curYear = elemento.rev_year\n curMonth= elemento.rev_month\n }\n if (curMonth != elemento.rev_month) {\n //ha canviat el mes, com que està ordenat per mes, agefim registre\n const curReg = {Month: curMonth, AcRevenue2020: valAcum20, AcRevenue2021: valAcum21, AcRevenue2022: valAcum22}\n datarev.push(curReg)\n } \n curYear = elemento.rev_year\n curMonth= elemento.rev_month\n if (curYear == 2020) {\n valAcum20 += elemento.revenue\n }else if (curYear == 2021){\n valAcum21 += elemento.revenue\n }else {\n valAcum22 += elemento.revenue\n }\n });\n const curReg = {Month: curMonth, AcRevenue2020: valAcum20, AcRevenue2021: valAcum21, AcRevenue2022: valAcum22}\n datarev.push(curReg)\n return datarev; \n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1423,"value":"acumulatedrevenue2 = {\n const datarev = []\n var curMonth = 0\n var curYear = 0\n var valAcum20 = 0\n var valAcum21 = 0\n var valAcum22 = 0\n revenue_per_month.forEach(elemento => {\n curYear = elemento.rev_year\n curMonth= elemento.rev_month\n if (curYear == 2020) {\n valAcum20 += elemento.revenue\n const curReg = {Year: curYear, Month: curMonth, AcumulatedRevenue: valAcum20}\n datarev.push(curReg)\n }else if (curYear == 2021){\n valAcum21 += elemento.revenue\n const curReg = {Year: curYear, Month: curMonth, AcumulatedRevenue: valAcum21}\n datarev.push(curReg)\n }else {\n valAcum22 += elemento.revenue\n const curReg = {Year: curYear, Month: curMonth, AcumulatedRevenue: valAcum22}\n datarev.push(curReg)\n }\n \n });\n\n return datarev; \n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1663,"value":"We get the total revenue per store in 2022","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1531,"value":"SELECT daily_orders.store_id as store, YEAR(daily_orders.order_date) as Year, SUM(daily_orders.revenue) as revenue\nFROM daily_orders\nWHERE YEAR(daily_orders.order_date) = 2022\nGROUP BY YEAR(daily_orders.order_date), daily_orders.store_id","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"stores_revenue","database":"var:daily_orders"},{"id":1690,"value":"Transform data to get the total number and associated revenue of each product for every year.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1692,"value":"SELECT YEAR(order_date) as year, category, name, SUM(orders) as total_units, SUM(revenue) as total_revenue\nFROM daily_orders_product\nGROUP BY year, category, name\nORDER BY category","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"product_total_year","database":"var:daily_orders_product"},{"id":1709,"value":"We break the name down into pizza flavour and size:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1704,"value":"product_total_year2 = {\n const data = []\n product_total_year.forEach(elemento => {\n const pizzaparts = elemento.name.split(' Pizza ')\n const curReg = {year: elemento.year, pizza: pizzaparts[0], size: pizzaparts[1], total_units: elemento.total_units, total_revenue: elemento.total_revenue}\n data.push(curReg)\n \n });\n return data; \n \n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","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":360,"value":"---\n## Visual answers","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":878,"value":"**Question 1: How are sales doing. How's current year compared to others?**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1442,"value":"Plot.plot({\n title: \"Monthly evolution of sales 2020 - 2022\",\n marginLeft: 75,\n marginTop: 50,\n y: {label: \"Acumulated revenue\", grid: true},\n x: {tickFormat: (d) => d3.timeFormat(\"%b\")(new Date(2023, d - 1, 1)),\n label: \"Month\"\n },\n fx: {axis: null},\n color: {scheme: \"paired\", legend: true},\n marks: [\n Plot.ruleY([0]),\n Plot.barY(acumulatedrevenue2, {fx: \"Month\", x: \"Year\", y: \"AcumulatedRevenue\", fill: \"Year\"})\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1559,"value":"Plot.plot({\n title: \"Monthly revenue difference between 2021 and 2022\",\n marginLeft: 75,\n marginTop: 50,\n y: {label: \"Revenue difference in $\", grid: true},\n x: { tickFormat: (d) => d3.timeFormat(\"%b\")(new Date(2023, d - 1, 1))},\n marks: [\n () => htl.svg`\n \n \n \n \n \n `,\n Plot.barY(acumulatedrevenue , {x: \"Month\", y: d => d[\"AcRevenue2022\"]-d[\"AcRevenue2021\"], fill: \"url(#gradient)\"}),\n Plot.text(acumulatedrevenue, {x: \"Month\", y: d => (d[\"AcRevenue2022\"]-d[\"AcRevenue2021\"]) + 2000, text: d => d[\"AcRevenue2022\"]-d[\"AcRevenue2021\"]})\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1524,"value":"**Question 2: Which are the best and the worst stores?**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1636,"value":"import {BubbleChart} from \"@d3/bubble-chart-component\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1683,"value":"## Best and worst stores. Store classification (by revenue in $) in 2022.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1619,"value":"chart = BubbleChart(stores_revenue, {\n label: d => `${stores.find(store => store.id == d[\"store\"]).city}\\n Store ID ${d[\"store\"]}\\n ${d[\"revenue\"].toLocaleString()} $`,\n value: d => d[\"revenue\"],\n group: d => Math.floor(d[\"revenue\"]/300000),\n title: d => `Store classification in 2022 (by revenue in $)`, //no va\n width: 1152\n});\n\n","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1752,"value":"** Question 3. Which are our main sales products? (revenue and units) **","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1711,"value":"Treemap(product_total_year2, {\n path: (d) => d.pizza, // e.g. flare/animate/Easing\n label: (d) => `${d.pizza} ${d.size}\\n\\n${d.total_units.toLocaleString()}\\n${d.total_revenue.toLocaleString()}$`, // display text\n group: (d) => d.size, // for color; e.g. animate\n value: (d) => d?.total_units, // area of each rect\n title: (d, n) => `${d.pizza} ${d.size}\\nUnits sold:${d.total_units.toLocaleString()}\\nTotal revenue: ${d.total_revenue.toLocaleString()}$`, // hover text\n width,\n height: 500\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"},{"id":1712,"value":"import {Treemap} from \"@d3/treemap\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1719,"value":"import {Pack} from \"@d3/pack\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W32","stdlib":"1","titling":"auto"}} 31:"$30:body"