2c:null 2d:["$","title",null,{"children":"Swiss Electricity tariff | Echo | Observable"}] 32:Ta35,```sql SELECT DISTINCT ?d_canton_id ?d_total ?d_category_name ?d_municipality_id ?d_municipality_name ?d_operator_name WHERE { ?observationSet0 . ?observationSet0 ?source0 . ?source0 ?d_category . ?source0 ?d_municipality . ?source0 ?d_operator . ?source0 ?d_period . ?source0 ?d_total . ?source0 ?d_product . FILTER ( (?d_category = ) ) FILTER ( (?d_product = ) ) FILTER ( (?d_period = "2009"^^) ) OPTIONAL { ?d_category ?d_category_en . FILTER ( LANGMATCHES(LANG(?d_category_en), "en") ) } OPTIONAL { ?d_category ?d_category_x . FILTER ( (LANG(?d_category_x) = "") ) } BIND(COALESCE(?d_category_en, ?d_category_x) AS ?d_category_name) OPTIONAL { ?d_municipality ?d_canton . ?d_canton ?d_canton_id . FILTER ( (?d_canton_id != "CHE") ) } OPTIONAL { ?d_municipality ?d_municipality_id . } OPTIONAL { ?d_municipality ?d_municipality_en . FILTER ( LANGMATCHES(LANG(?d_municipality_en), "en") ) } OPTIONAL { ?d_municipality ?d_municipality_x . FILTER ( (LANG(?d_municipality_x) = "") ) } BIND(COALESCE(?d_municipality_en, ?d_municipality_x) AS ?d_municipality_name) OPTIONAL { ?d_operator ?d_operator_en . FILTER ( LANGMATCHES(LANG(?d_operator_en), "en") ) } OPTIONAL { ?d_operator ?d_operator_x . FILTER ( (LANG(?d_operator_x) = "") ) } BIND(COALESCE(?d_operator_en, ?d_operator_x) AS ?d_operator_name) } GROUP BY ?d_canton_id ?d_category ?d_total ?d_category_name ?d_municipality_id ?d_municipality_name ?d_operator_name ```33:[{"id":"5f7e74b8ef5c8cb4bafb2c63c4ff764cdd811e177e8644df51dcc0eefb194ee5a0ae49b8f3d08268b2c41577b0d25931d8b779b71ec6fedb06139d3ac0eeec54","href":"https://static.observableusercontent.com/files/5f7e74b8ef5c8cb4bafb2c63c4ff764cdd811e177e8644df51dcc0eefb194ee5a0ae49b8f3d08268b2c41577b0d25931d8b779b71ec6fedb06139d3ac0eeec54","name":"ch.topojson.json","type":"application/json","lastModified":1656681781588.093,"size":145010},{"id":"8f49e5cf16cd0d14d739af59feb8e73c98f37b62b55368bcc02bac56e345cb703680e5ee4f97206bf00bdfda43a22ee96ce44b7f4bd955025cc364107301f30f","href":"https://static.observableusercontent.com/files/8f49e5cf16cd0d14d739af59feb8e73c98f37b62b55368bcc02bac56e345cb703680e5ee4f97206bf00bdfda43a22ee96ce44b7f4bd955025cc364107301f30f","name":"swiss-energy-with-canton.json","type":"application/json","lastModified":1656684739688.314,"size":1619521}] 30:{"id":"9d58961681b09c70","slug":"swiss-electricity-tariff","title":"Swiss Electricity tariff","thumbnail":"7f66a806cb6e0e55a6bdd867effa9a79ef7f021ebf169513ab1c54873af69007","access_level":"public","create_time":"$D2022-07-01T12:52:33.944Z","update_time":"$D2022-07-01T16:52:21.832Z","view_time":null,"first_public_time":"$D2022-07-01T15:21:33.701Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"b82f80e5ba26ec27","login":"echoyangyang","name":"Echo","avatar_url":"https://avatars-next.observableusercontent.com/93316342e348d4052f1f6b7d5dc1cb831229448d6ea556d882ea40ebb96ed1ae","tier":"starter_2024"},"version":234,"publish_version":234,"role":null,"authored":false,"authors":[{"type":"individual","id":"e85d9c247b1d937b","login":"echoyangyang","name":"Echo","avatar_url":"https://avatars-next.observableusercontent.com/93316342e348d4052f1f6b7d5dc1cb831229448d6ea556d882ea40ebb96ed1ae","tier":null}],"members":[{"type":"individual","id":"e85d9c247b1d937b","login":"echoyangyang","name":"Echo","avatar_url":"https://avatars-next.observableusercontent.com/93316342e348d4052f1f6b7d5dc1cb831229448d6ea556d882ea40ebb96ed1ae","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":3,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Swiss Electricity tariff","cells":[{"id":0,"value":"# Swiss Electricity tariff\n\nElectricity tariff per provider and municipality in Switzerland. \n\nData: Electricity data: [Lindas](https://lindas.admin.ch/), Map data: [Swiss Maps Public API](https://swiss-maps.vercel.app/)\n\n\nCredit: [Interactive Things](https://www.interactivethings.com/)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"chart = {\n const svg = d3.create(\"svg\")\n .attr(\"viewBox\", [0, 0, width, height]);\n\n svg.append(\"g\")\n .selectAll(\"path\")\n .data(topojson.feature(mapData, mapData.objects.cantons).features)\n .join(\"path\")\n .attr(\"fill\", d => {\n const c = color(cantonAvg.get(d.properties.id))\n return c\n })\n .attr(\"d\", d => path(d))\n .append(\"title\")\n .text(d => `${d.properties.name} Fr.${formatPrice(cantonAvg.get(d.properties.id))}`);\n\n svg.append(\"path\")\n .datum(topojson.mesh(mapData, mapData.objects.cantons, (a, b) => a !== b))\n .attr(\"fill\", \"none\")\n .attr(\"stroke\", \"#fff\")\n .attr(\"stroke-linejoin\", \"round\")\n .attr(\"d\", path);\n\n return svg.node();\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":65,"value":"projection = d3.geoMercator().center([8.3, 46.8]).scale(10000).translate([width / 2, height / 2]).precision(0.1)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":39,"value":"path = d3.geoPath().projection(projection)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":69,"value":"width = 975","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":71,"value":"height = 610","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":153,"value":"formatPrice = num => (Math.round(num * 100) / 100).toFixed(2);","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":111,"value":"## 🌈 Color","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":119,"value":"colors=[\"#e8e8e8\", \"#b0d5df\",\"#64acbe\", \"#e4acac\", \"#c85a5a\", \"#574249\"]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":169,"value":"swatches(colors)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":117,"value":"n = colors.length","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":113,"value":"x = d3.scaleQuantile(Array.from(cantonAvg.values()), d3.range(n))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":121,"value":"color = {\n return value => {\n if (!value) return \"#fff\";\n return colors[x(value)];\n };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":32,"value":"## 🏁 Lindas Data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":197,"value":"The electricity tariff data is from [LINDAS](https://lindas.admin.ch/), which is provided by Swiss Federal Archives. \n\n### Dateset information\n\n* The dateset used in this notebook is [electricityprice](https://energy.ld.admin.ch/elcom/electricityprice).\n* The query could be found under [Appendix/SPARQL Query](https://observablehq.com/d/9d58961681b09c70#cell-206).\n\nIf you want to explore more, try [Lindas Query](https://lindas.admin.ch/sparql/), or try [Swiss Visualize Admin](https://visualize.admin.ch/), a web application for the visualization of Lindas data sets.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":94,"value":"swissEnergy = FileAttachment(\"swiss-energy-with-canton.json\").json()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":84,"value":"data = swissEnergy.results.bindings","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":35,"value":"municipalityData = new Map(data.map(x=> [x.d_municipality_id.value, x.d_total.value]))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":96,"value":"cantonAvg = d3.rollup(data, v => {\n return d3.mean(v, d=>+d.d_total?.value??0)\n}, d => +d.d_canton_id?.value ?? 0) ","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":82,"value":"cantonEnergyData = d3.group(data, d => d.d_canton_id?.value ?? 0)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":9,"value":"## 🏁 Map Data\n\nHere provided three ways to get map data (topojson).\n\n* Fetch\n* Direct url import\n* File import\n\nYou could use [Swiss Maps Public API](https://swiss-maps.vercel.app/) to export your desired data shape, and the source data is from [Swiss Bundesamt für Statistik](https://www.bfs.admin.ch/bfs/de/home/dienstleistungen/geostat/geodaten-bundesstatistik/administrative-grenzen/generalisierte-gemeindegrenzen.html)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":51,"value":"mapData = ch_url","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":22,"value":"dataUrl = \"https://swiss-maps-git-maintainance-ixt1.vercel.app/api/v0?year=2022&shapes=country,cantons,lakes\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":18,"value":"### Fetch","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"ch_fetch = fetch(dataUrl).then(res => res.json())","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":20,"value":"### URL import","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":16,"value":"ch_url = d3.json(dataUrl)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":221,"value":"### File import","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":50,"value":"ch_file = FileAttachment(\"ch.topojson.json\").json()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":165,"value":"## ⚙️ Utilities","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":167,"value":"function swatches(colors) {\n return html`${colors.map(c => `

`)}`;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":200,"value":"# 🍃 Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":206,"value":"### SPARQL Query","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":203,"value":"$32","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"}],"files":"$W33","stdlib":"1"}} 31:"$30:body"