The end-to-end solution for building and hosting better data apps, dashboards, and reports.
data
// calculate differences for month over month and year over year within industry
data = {
// let modified = []
// get an array of data points for each industry
let bynaics = d3.rollup(allYears, v => v, d => d.naics)
for(let naics of bynaics.values()) {
// we will add to the data points and then add them to the modified array
// modified = modified.concat(naics.map((d,i) => {
naics.forEach((d,i) => {
// calculate the previous month and retrieve it from the lookup
let prevMonthDate = d3.utcMonth.offset(d.date, -1)
let prevMonth = industryDateMap.get(d.naics).get(prevMonthDate)
let mdiff = (d.sales - prevMonth?.sales) / d.sales
d.monthChange = mdiff
// calculate the previous year for the same month
let prevYearDate = d3.utcMonth.offset(d.date, -12)
let prevYear = industryDateMap.get(d.naics).get(prevYearDate)
let ydiff = (d.sales - prevYear?.sales) / d.sales
d.yearChange = ydiff
// return {
// ...d,
// monthChange: mdiff,
// yearChange: ydiff
// }
})
// )
}
// return modified
return allYears
}
adjusted = true // by default we will look at the adjusted for inflation numbers in everything
d3.utcMonth.floor(d3.utcMonth.offset(new Date(), -1))
allYears = mrtssales92Present.sheetNames.flatMap(sheet => parseYear(sheet, adjusted))
.sort((a,b) => d3.ascending(a.date, b.date))
.concat(advanced.filter(d => d.adjusted == adjusted))
industryDateMap = d3.rollup(allYears, v => new d3.InternMap(v.map(d => [d.date, d])), d => d.naics)
parsedYear = parseYear("2021", adjusted)
naicsMap = d3.rollup(parsedYear, v => v[0].business, d => d.naics)
percent = d3.format("0.2%")
// Theoretically we could pull this live from the Census website, but we run the risk of parsing errors.
// For now we can update it monthly, we can easily see the last updated date in the File Attachments menu in the top right
mrtssales92Present = FileAttachment("mrtssales92-present@3.xlsx").xlsx()
function parseYear(year, adjusted = true) {
// we convert the headers on row 5 into dates (if they are valid) and create a lookup
// we then use this to create tidy records of sales estimates for each business in each month
let headerYears = Object.entries(mrtssales92Present.sheet(year, { range: "A5:N5" })[0])
.slice(2)
.map(d => [d[0], tp(d[1].replace("May","May.").replace("(p)","").trim())])
.filter(d => d[1])
/*
The spreadsheet supplies two sets of numbers for each year, those adjusted for inflation and those not adjusted. We can parse them separately, the structure of each year is the same
*/
let data = mrtssales92Present.sheet(year, { range: adjusted ? "A73:N110" : "A7:N71", headers: false })
.flatMap(d => {
return headerYears.map(y => {
let naics = String(d["A"])
let business = d["B"]
if(!naics) naics = historicalTotalNamesToNaics.get(business)
return {
naics,
business,
date: y[1],
sales: d[y[0]],
adjusted
}
})
})
return data.sort((a,b) => d3.ascending(a.date, b.date))
}
tp = d3.utcParse("%b. %Y")
marts_current = FileAttachment("marts_current@3.xlsx").xlsx()
table1 = marts_current.sheet("Table 1.", { range: "A11:N67"})
Inputs.table(table1, { layout: "fixed"})
advancedDate = {
let rawMonth = marts_current.sheet("Table 1.", { range: "E8:E8"})[0].E.slice(0,3)
let rawYear = marts_current.sheet("Table 1.", { range: "F7:F7"})[0].F
return tp(`${rawMonth}. ${rawYear}`)
}
advanced = {
// create an array with entries
// Retail & food services
let entries = table1.filter(d => !isNaN(+d.E))
.flatMap((d,i) => {
if(i < 5) {
// handle the total cases
let business = d.B.replace(/…/g,"").replace(/\./g,"").trim()
if(business.indexOf("gasoline") == 0) business = "Total (excl. motor vehicle & parts & gasoline stations)"
business = "Retail & food services, " + business
let naics = advancedTotalNamesToNaics.get(business)
business = historicalTotalNaicsToNames.get(naics)
let pnot = {
naics,
business,
sales: d.E,
date: advancedDate,
adjusted: false
}
let padjusted = {
naics,
business,
sales: d.J,
date: advancedDate,
adjusted: true
}
return [pnot, padjusted]
}
let business = d.B.replace(/…/g,"").replace(/\./g,"").trim()
// because the spreadsheet has some business names split across 2 lines we'll manually get their naics code
let naics = String(d.A).replace(/\s/,"")
if(business == "supplies dealers") naics = "444"
if(business == "stores") naics = "448"
if(business == "instrument, & book stores") naics = "451"
business = naicsMap.get(naics)
let pnot = {
naics,
business,
sales: d.E,
date: advancedDate,
adjusted: false
}
let padjusted = {
naics,
business,
sales: d.J,
date: advancedDate,
adjusted: true
}
return [pnot, padjusted]
})
return entries
}
naicsMap.get("4411,4412")
Inputs.table(advanced)
table2 = marts_current.sheet("Table 2.", { range: "A14:C54"})
advancedDifference = {
// create an array with entries
// Retail & food services
let entries = table2.filter(d => d.C !== undefined && d.C != " ")
.map((d,i) => {
if(i < 5) {
// handle the total cases
let business = d.B.replace(/…/g,"").replace(/\./g,"").trim()
if(business.indexOf("gasoline") == 0) business = "Total (excl. motor vehicle & parts & gasoline stations)"
business = "Retail & food services, " + business
let naics = advancedTotalNamesToNaics.get(business)
business = historicalTotalNaicsToNames.get(naics)
let p = {
naics,
business,
difference: d.C,
date: advancedDate
}
return p
}
let bus = d.B.replace(/…/g,"").replace(/\./g,"").trim()
// because the spreadsheet has some business names split across 2 lines we'll manually get their naics code
let naics = String(d.A).replace(/\s/,"")
if(bus == "supplies dealers") naics = "444"
if(bus == "stores") naics = "448"
if(bus == "instrument, & book stores") naics = "451"
bus = naicsMap.get(naics)
let p = {
naics,
business: bus,
difference: d.C,
date: advancedDate,
}
return p
})
return entries
}
Inputs.table(advancedDifference)
historicalTotalNames = [
["Retail and food services sales, total", "1*"],
["Retail sales and food services excl motor vehicle and parts", "2*"],
["Retail sales and food services excl gasoline stations", "3*"],
["Retail sales and food services excl motor vehicle and parts and gasoline stations", "4*"],
["Retail sales, total", "5*"],
["Retail sales, total (excl. motor vehicle and parts dealers)", "6*"],
["GAFO(1)", "7*"],
]
historicalTotalNamesToNaics = new Map(historicalTotalNames)
historicalTotalNaicsToNames = new Map(historicalTotalNames.map(d => d.map(e => e).reverse()))
advancedTotalNames = [
["Retail & food services, total", "1*"],
["Retail & food services, Total (excl motor vehicle & parts)", "2*"],
["Retail & food services, Total (excl gasoline stations)", "3*"],
["Retail & food services, Total (excl. motor vehicle & parts & gasoline stations)","4*"],
["Retail & food services, Retail", "5*"]
]
advancedTotalNamesToNaics = new Map(advancedTotalNames)
import { button as downloadButton } from "@jeremiak/download-data-button"
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.
