Public
Edited
Nov 22, 2022
9 stars
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
data
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
adjusted = true // by default we will look at the adjusted for inflation numbers in everything
Insert cell
d3.utcMonth.floor(d3.utcMonth.offset(new Date(), -1))
Insert cell
allYears = mrtssales92Present.sheetNames.flatMap(sheet => parseYear(sheet, adjusted))
.sort((a,b) => d3.ascending(a.date, b.date))
.concat(advanced.filter(d => d.adjusted == adjusted))
Insert cell
industryDateMap = d3.rollup(allYears, v => new d3.InternMap(v.map(d => [d.date, d])), d => d.naics)
Insert cell
parsedYear = parseYear("2021", adjusted)
Insert cell
naicsMap = d3.rollup(parsedYear, v => v[0].business, d => d.naics)
Insert cell
percent = d3.format("0.2%")
Insert cell
Insert cell
// 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@1.xlsx").xlsx()
Insert cell
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))
}
Insert cell
tp = d3.utcParse("%b. %Y")
Insert cell
Insert cell
Insert cell
marts_current = FileAttachment("marts_current@2.xlsx").xlsx()
Insert cell
Insert cell
table1 = marts_current.sheet("Table 1.", { range: "A11:N67"})
Insert cell
Inputs.table(table1, { layout: "fixed"})
Insert cell
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}`)
}
Insert cell
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
}
Insert cell
naicsMap.get("4411,4412")
Insert cell
Inputs.table(advanced)
Insert cell
Insert cell
table2 = marts_current.sheet("Table 2.", { range: "A14:C54"})
Insert cell
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
}
Insert cell
Inputs.table(advancedDifference)
Insert cell
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*"],
]
Insert cell
historicalTotalNamesToNaics = new Map(historicalTotalNames)
Insert cell
historicalTotalNaicsToNames = new Map(historicalTotalNames.map(d => d.map(e => e).reverse()))
Insert cell
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*"]
]
Insert cell
advancedTotalNamesToNaics = new Map(advancedTotalNames)
Insert cell
import { button as downloadButton } from "@jeremiak/download-data-button"
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