The end-to-end solution for building and hosting better data apps, dashboards, and reports.
Pizza ParadiseUSAFacts Higher Education DataStack Overflow Developer Survey DataCurated datasetsStreetEasy Real Estate DataFAA Laser Reports DataVisualization Publication DatasetDarkCideS: Bats in Caves DataThe MoMA Collection DataU.S. Geographic DataPlot Test DataNOAA Hourly Weather DataEIA Electricity DataBandcamp Sales DataGoogle Merchandise Sales Data
Monthly Retail Trade Report Data
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"
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.