The avatar for @udg-master-data-science
UdG | Master's in Data Science
Public
Edited
Dec 14, 2023
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
ordersWithMonths = daily_orders.map(order => ({
...order,
month: new Date(order.order_date).getMonth()
}));
Insert cell
Insert cell
//Create as many cells as needed
orders2022 = d3.filter(ordersWithMonths, order => order.order_date.getFullYear() == 2022);
Insert cell
Insert cell
// Use D3 to group and sum orders by store_id and month
ordersByStoreAndMonth = d3.rollup(
orders2022,
v => d3.sum(v, d => d.orders),
d => d.store_id,
d => d.month
);
Insert cell
Insert cell
flatTable = Array.from(ordersByStoreAndMonth, ([store_id, monthData]) => (
Array.from(monthData, ([month, totalOrders]) => ({
store_id: store_id,
month: month,
total_orders: totalOrders
}))
)).flat();

Insert cell
Insert cell
totalOrdersByStore = new Map()

Insert cell
flatTable.forEach(entry => {
const { store_id, total_orders } = entry;
totalOrdersByStore.set(store_id, (totalOrdersByStore.get(store_id) || 0) + total_orders);
});
Insert cell
totalOrdersByStore
Insert cell
Insert cell
flatTableWithRatio = flatTable.map(entry => ({
...entry,
monthly_percentage_of_annual_orders: entry.total_orders/totalOrdersByStore.get(entry.store_id) * 100,
}));
Insert cell
Insert cell
flatTableWithRatioMonths = flatTableWithRatio.map(entry => ({ ...entry, month: entry.month + 1 }));

Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
daily_orders
SELECT
store_id,
CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0')) AS months_date,
SUM(revenue) AS monthly_revenue
FROM
daily_orders
GROUP BY
store_id, months_date
ORDER BY
store_id, months_date;
Insert cell
Insert cell
monthly_revenue
WITH MonthlyData AS (
SELECT
store_id,
CONCAT(months_date, '-01') AS months_date, -- Assuming the first day of the month
SUM(monthly_revenue) AS monthly_revenue
FROM
monthly_revenue
GROUP BY
store_id, months_date
)

SELECT
store_id,
months_date,
monthly_revenue,
monthly_revenue / first_month_revenue AS standardized_revenue
FROM (
SELECT
md.store_id,
md.months_date,
md.monthly_revenue,
FIRST_VALUE(md.monthly_revenue) OVER (PARTITION BY md.store_id ORDER BY md.months_date) AS first_month_revenue
FROM
MonthlyData md
) AS subquery
ORDER BY
store_id, months_date;
Insert cell
Insert cell
standardized_revenue.forEach(item => {
item.months_date = new Date(item.months_date);
});
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
//Your third plot here

Plot.plot({
marks: [
Plot.ruleY([0]),
Plot.lineY(standardized_revenue, {x: "months_date", y: "standardized_revenue", stroke: "store_id",strokeOpacity:0.7, tip: "x"})
]
})
Insert cell
Insert cell
Insert cell
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