2d:null 2e:["$","title",null,{"children":"Template for exploring an Excel sheet | ee2dev | Observable"}] 33:T6ed,function processData(_initialData) { /*_initialData.forEach((record, i) => { record.Date = new Date("1/1/22").setDate(new Date("1/1/22").getDate() + i); }); */ // process data here // Step 1: Transform the data into a hierarchical format function transformData(data) { const hierarchy = {}; data.forEach((d) => { if (!hierarchy[d.PRODUKT]) { hierarchy[d.PRODUKT] = {}; } if (!hierarchy[d.PRODUKT][d.VERTRIEBSWEG]) { hierarchy[d.PRODUKT][d.VERTRIEBSWEG] = {}; } hierarchy[d.PRODUKT][d.VERTRIEBSWEG][d.STORNO_VERMEIDBAR] = { ANZ: d.ANZ }; }); const root = { name: "root", children: [] }; Object.keys(hierarchy).forEach((PRODUKT) => { const PRODUKTNode = { name: PRODUKT, children: [] }; Object.keys(hierarchy[PRODUKT]).forEach((VERTRIEBSWEG) => { const VERTRIEBSWEGNode = { name: VERTRIEBSWEG, children: [] }; Object.keys(hierarchy[PRODUKT][VERTRIEBSWEG]).forEach( (STORNO_VERMEIDBAR) => { const STORNO_VERMEIDBARNode = { name: STORNO_VERMEIDBAR, ANZ: hierarchy[PRODUKT][VERTRIEBSWEG][STORNO_VERMEIDBAR].ANZ }; VERTRIEBSWEGNode.children.push(STORNO_VERMEIDBARNode); } ); PRODUKTNode.children.push(VERTRIEBSWEGNode); }); root.children.push(PRODUKTNode); }); return root; } // Step 2: Create the hierarchy const hierarchyData = transformData(_initialData); const root = d3.hierarchy(hierarchyData).sum((d) => d.ANZ); // Assuming you want to sum ANZs for size representation // Now `root` is a D3 hierarchy object that you can use for visualization console.log(root); _initialData = root; return _initialData; }34:[{"id":"133dbde06fa31dad325237b4d2b96d6d9f4e1e929545693dbced5872492e7548f9f925e0b2f6c6899c9507583a3acd6f7fc7d83c6d55ec9bafb3976e56f93bc8","href":"https://static.observableusercontent.com/files/133dbde06fa31dad325237b4d2b96d6d9f4e1e929545693dbced5872492e7548f9f925e0b2f6c6899c9507583a3acd6f7fc7d83c6d55ec9bafb3976e56f93bc8","name":"cpu.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1628807723161.624,"size":54023}] 31:{"id":"64757bd9e5432b6e","slug":"template-for-exploring-an-excel-sheet","title":"Template for exploring an Excel sheet","thumbnail":"bd81ebf12b91971101097d00ac71282b8c211657947c224d3c216a5dc95e73eb","access_level":"public","create_time":"$D2021-08-12T22:06:41.651Z","update_time":"$D2025-11-19T22:41:53.956Z","view_time":null,"first_public_time":"$D2021-08-12T22:52:49.933Z","has_unpublished_edits":null,"fork":{"id":"2d387a445b38251c","slug":"template-for-exploring-a-data-set","title":"Template for exploring a data set","owner":{"id":"e6e1e2a0da500bb6","login":"ee2dev","name":""}},"stars":4,"starred":false,"owner":{"type":"team","id":"e6e1e2a0da500bb6","login":"ee2dev","name":"","avatar_url":"https://avatars-next.observableusercontent.com/c41b514d64cff7415ffc87106c620e16cefe80b6d853fb3a05e19a000c3612c0","tier":"starter_2024"},"version":924,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"9ba5e0b68d3e7972","login":"ee2dev","name":"","avatar_url":"https://avatars-next.observableusercontent.com/c41b514d64cff7415ffc87106c620e16cefe80b6d853fb3a05e19a000c3612c0","tier":null}],"members":[{"type":"individual","id":"9ba5e0b68d3e7972","login":"ee2dev","name":"","avatar_url":"https://avatars-next.observableusercontent.com/c41b514d64cff7415ffc87106c620e16cefe80b6d853fb3a05e19a000c3612c0","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":20,"collection":{"id":"59c2f34838f33517","slug":"visualization-libraries","title":"visualization libraries","owner":{"id":"e6e1e2a0da500bb6","login":"ee2dev","name":""}},"type":"notebook","body":{"title":"Template for exploring an Excel sheet","cells":[{"id":0,"value":"step1 = md`# Template for exploring an Excel sheet\n\nData import based on https://observablehq.com/@observablehq/xlsx\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":451,"value":"heading(\" 👉 Choose an Excel file\", 1, 4)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":105,"value":"viewof file = localFileInput({\n accept: \".xlsx\",\n value: FileAttachment(\"cpu.xlsx\")\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":583,"value":"heading(\" 👉 Choose a sheet\", 2, 4)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":506,"value":"viewof sheet_name = Inputs.select(wb.sheetNames, { label: \"Sheet name\" })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":457,"value":"heading(\" 🤓 Process your data (if applicable)\", 3, 4)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":710,"value":"initialData = wb.sheet(sheet_name, { headers: true })\n// initialData = wb.sheet(sheet_name, { headers: true, range: \"C1:J\" }) // from cell C1 to column J, last row\n// initialData = wb.sheet(sheet_name, { headers: true, range: \"A1:B\" })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":599,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":525,"value":"data = processData(initialData)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":461,"value":"heading(\" 🧐 Check if the data looks right?\", 4, 4)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":783,"value":"data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":308,"value":"heading(\" Visualize and explore 📊📈✨ \", 1, 1, \"blue\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":900,"value":"Plot.plot({\n marks: [\n Plot.rect(leaves, {\n x1: \"x0\",\n x2: \"x1\",\n y1: \"y0\",\n y2: \"y1\",\n fill: (d) => d.parent.data.name,\n title: (d) =>\n `${d.parent.parent.data.name}\\n \n ${\" \" + d.parent.data.name}\\n \n ${\" \" + d.data.name + d.value}`\n }),\n Plot.text(leaves, {\n x: \"x0\",\n y: \"y1\",\n dx: 10,\n dy: 10,\n text: (d) => {\n const v = d.value?.toFixed(1);\n const width = (v.length - 1) * 8 + 5;\n const height = 15;\n return d.x1 - d.x0 > width && d.y1 - d.y0 > height ? v : \"\";\n },\n fill: \"#fff\"\n })\n ],\n x: { axis: null },\n y: { axis: null },\n width: width,\n height: height,\n marginTop: 20\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":904,"value":"data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":895,"value":"leaves = {\n /* const hierarchy = d3.hierarchy(data).sum((d) => d.value);\n d3.treemap().size([width, height]).padding(2)(hierarchy);\n */\n d3.treemap().size([width, height]).padding(2)(data);\n return data.leaves();\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":897,"value":"height = 800","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":883,"value":"pm = PlotMatrix()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":781,"value":"Plot.plot({\n marks: [\n Plot.barY(alphabet, {\n x: \"letter\",\n y: \"frequency\",\n sort: { x: \"y\", reverse: true }\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":436,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":810,"value":"#### Colors","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":846,"value":"viewof myColor = Inputs.table(colorPalette, {\n format: {\n cyan: colorCell,\n orange: colorCell,\n blue: colorCell,\n green: colorCell\n }\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":851,"value":"quantizeColors(\"blue\", \"white\", 5, true)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":821,"value":"import {\n colorPalette,\n colorCell,\n quantizeColors,\n swatches\n} from \"@ee2dev/template-for-exploring-a-german-data-set/2\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":748,"value":"\n#### Arquero References\n- [Introducing Arquero](https://observablehq.com/@uwdata/introducing-arquero)\n- [Arquero API - Verbs](https://uwdata.github.io/arquero/api/verbs)\n- [An Illustrated Guide to Arquero Verbs](https://observablehq.com/@uwdata/an-illustrated-guide-to-arquero-verbs)\n- [Tidy Data in Javascript](https://observablehq.com/@uwdata/tidy-data-in-javascript)\n- [Data Wrangler based on Arquero](https://observablehq.com/@observablehq/data-wrangler)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":577,"value":"\n#### Plot References\n- https://observablehq.com/plot\n- https://github.com/observablehq/plot","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":752,"value":"import { aq, op } from \"@uwdata/arquero\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":755,"value":"import { Wrangler } from \"@observablehq/data-wrangler\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":690,"value":"wb = file.xlsx()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":875,"value":"setFormatLocale = {\n // const ldef = require(`d3-format/locale/${lang}.json`);\n const ldef = {\n // German Locale\n decimal: \",\",\n thousands: \".\",\n grouping: [3],\n currency: [\"\", \"\\u00a0€\"]\n };\n d3.formatDefaultLocale(ldef);\n return ldef;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":569,"value":"function convert(row) {\n // example how use d3.autoType and update one or more properties\n return { ...d3.autoType(row), property: row.property };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":736,"value":"import { localFileInput } from \"@mbostock/localfile\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":448,"value":"import { heading } from \"@ee2dev/formatting-in-observable\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":745,"value":"import { NumberParser } from \"@mbostock/localized-number-parsing\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":880,"value":"import { PlotMatrix } with { data } from \"@observablehq/autoplot-matrix\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W34","stdlib":"1","titling":"auto"}} 32:"$31:body"