The avatar for @udg-master-data-science
UdG | Master's in Data Science
Public
Edited
Dec 15, 2023
Fork of Exercise 2: Data questions, visual answers
1 star
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
daily_orders_product
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
daily_orders
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
stores
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
Insert cell
Insert cell
daily_orders
SELECT YEAR(order_date) as rev_year, MONTH(order_date) as rev_month, sum(revenue) as revenue
FROM daily_orders
GROUP BY rev_month, rev_year
ORDER BY rev_month, rev_year
Insert cell
Insert cell
acumulatedrevenue = {
const datarev = []
var curMonth = 0
var curYear = 0
var valAcum20 = 0
var valAcum21 = 0
var valAcum22 = 0
revenue_per_month.forEach(elemento => {
if (curYear == 0){ //només pel primer
curYear = elemento.rev_year
curMonth= elemento.rev_month
}
if (curMonth != elemento.rev_month) {
//ha canviat el mes, com que està ordenat per mes, agefim registre
const curReg = {Month: curMonth, AcRevenue2020: valAcum20, AcRevenue2021: valAcum21, AcRevenue2022: valAcum22}
datarev.push(curReg)
}
curYear = elemento.rev_year
curMonth= elemento.rev_month
if (curYear == 2020) {
valAcum20 += elemento.revenue
}else if (curYear == 2021){
valAcum21 += elemento.revenue
}else {
valAcum22 += elemento.revenue
}
});
const curReg = {Month: curMonth, AcRevenue2020: valAcum20, AcRevenue2021: valAcum21, AcRevenue2022: valAcum22}
datarev.push(curReg)
return datarev;
}
Insert cell
acumulatedrevenue2 = {
const datarev = []
var curMonth = 0
var curYear = 0
var valAcum20 = 0
var valAcum21 = 0
var valAcum22 = 0
revenue_per_month.forEach(elemento => {
curYear = elemento.rev_year
curMonth= elemento.rev_month
if (curYear == 2020) {
valAcum20 += elemento.revenue
const curReg = {Year: curYear, Month: curMonth, AcumulatedRevenue: valAcum20}
datarev.push(curReg)
}else if (curYear == 2021){
valAcum21 += elemento.revenue
const curReg = {Year: curYear, Month: curMonth, AcumulatedRevenue: valAcum21}
datarev.push(curReg)
}else {
valAcum22 += elemento.revenue
const curReg = {Year: curYear, Month: curMonth, AcumulatedRevenue: valAcum22}
datarev.push(curReg)
}
});

return datarev;
}
Insert cell
Insert cell
daily_orders
SELECT daily_orders.store_id as store, YEAR(daily_orders.order_date) as Year, SUM(daily_orders.revenue) as revenue
FROM daily_orders
WHERE YEAR(daily_orders.order_date) = 2022
GROUP BY YEAR(daily_orders.order_date), daily_orders.store_id
Insert cell
Insert cell
daily_orders_product
SELECT YEAR(order_date) as year, category, name, SUM(orders) as total_units, SUM(revenue) as total_revenue
FROM daily_orders_product
GROUP BY year, category, name
ORDER BY category
Insert cell
Insert cell
product_total_year2 = {
const data = []
product_total_year.forEach(elemento => {
const pizzaparts = elemento.name.split(' Pizza ')
const curReg = {year: elemento.year, pizza: pizzaparts[0], size: pizzaparts[1], total_units: elemento.total_units, total_revenue: elemento.total_revenue}
data.push(curReg)
});
return data;
}
Insert cell
Insert cell
Insert cell
Insert cell
Plot.plot({
title: "Monthly evolution of sales 2020 - 2022",
marginLeft: 75,
marginTop: 50,
y: {label: "Acumulated revenue", grid: true},
x: {tickFormat: (d) => d3.timeFormat("%b")(new Date(2023, d - 1, 1)),
label: "Month"
},
fx: {axis: null},
color: {scheme: "paired", legend: true},
marks: [
Plot.ruleY([0]),
Plot.barY(acumulatedrevenue2, {fx: "Month", x: "Year", y: "AcumulatedRevenue", fill: "Year"})
]
})
Insert cell
Plot.plot({
title: "Monthly revenue difference between 2021 and 2022",
marginLeft: 75,
marginTop: 50,
y: {label: "Revenue difference in $", grid: true},
x: { tickFormat: (d) => d3.timeFormat("%b")(new Date(2023, d - 1, 1))},
marks: [
() => htl.svg`<defs>
<linearGradient id="gradient" gradientTransform="rotate(90)" gradientUnits="userSpaceOnUse">
<stop offset="0.10" stop-color="gold" />
<stop offset="0.25" stop-color="green" />
<stop offset="0.9" stop-color="blue" />
</linearGradient>
</defs>`,
Plot.barY(acumulatedrevenue , {x: "Month", y: d => d["AcRevenue2022"]-d["AcRevenue2021"], fill: "url(#gradient)"}),
Plot.text(acumulatedrevenue, {x: "Month", y: d => (d["AcRevenue2022"]-d["AcRevenue2021"]) + 2000, text: d => d["AcRevenue2022"]-d["AcRevenue2021"]})
]
})
Insert cell
Insert cell
import {BubbleChart} from "@d3/bubble-chart-component"
Insert cell
Insert cell
chart = BubbleChart(stores_revenue, {
label: d => `${stores.find(store => store.id == d["store"]).city}\n Store ID ${d["store"]}\n ${d["revenue"].toLocaleString()} $`,
value: d => d["revenue"],
group: d => Math.floor(d["revenue"]/300000),
title: d => `Store classification in 2022 (by revenue in $)`, //no va
width: 1152
});


Insert cell
Insert cell
Treemap(product_total_year2, {
path: (d) => d.pizza, // e.g. flare/animate/Easing
label: (d) => `${d.pizza} ${d.size}\n\n${d.total_units.toLocaleString()}\n${d.total_revenue.toLocaleString()}$`, // display text
group: (d) => d.size, // for color; e.g. animate
value: (d) => d?.total_units, // area of each rect
title: (d, n) => `${d.pizza} ${d.size}\nUnits sold:${d.total_units.toLocaleString()}\nTotal revenue: ${d.total_revenue.toLocaleString()}$`, // hover text
width,
height: 500
})
Insert cell
Insert cell
Insert cell
Insert cell
import {Treemap} from "@d3/treemap"
Insert cell
import {Pack} from "@d3/pack"
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more