31:T4f9,[Zegama Aizkorri](www.zegama-aizkorri.com) is a trail running race that has been taking place since 2002, except during covid years (2020, 2021). Due to the increasing number of runners aiming to participate since 2006 there is a lottery draw for all those that enrolled in a pre-registration, hoping to get dorsal for the race. Such dorsals are scarce so year after year chances to participare in this iconic race are more unlikely. As the organization states: _"Prescribed people who participated in the last edition and don’t get a number in times or participate for the first time in the draw will be assigned just one number for the draw. Those who participated in the previous draws and didn’t get any number will be assigned more than one number for the next edition, in the way that they will have as many numbers as times they have participated in the previous draws. In the case a person was assigned a number in any previous edition, he will be entered into account from the following year."_ The lottery draw is public and so they are the results, which contain all the historical data from previous years as well. That makes this data really interesting to explore, in order to get a sense of how this race has been growing in popularity over the years!32:T6df,{ // allocate year summaries let summaryByYear = _.fromPairs(years.map((year) => [year, { D: 0, X: 0 }])); years.forEach((year) => { summaryByYear[year].D = _.sumBy(data, (o) => (o[year] === "D" ? 1 : 0)); summaryByYear[year].X = _.sumBy(data, (o) => (o[year] === "X" ? 1 : 0)); summaryByYear[year].total = _.sumBy(data, (o) => ["X", "D"].includes(o[year]) ? 1 : 0 ); summaryByYear[year].drawnPercentage = summaryByYear[year].D / summaryByYear[year].total; }); let myData = _.values( _.mapValues(summaryByYear, (value, key) => _.set(value, "year", +key)) ); return Plot.plot({ marginLeft: 50, insetTop: 25, insetRight: 25, insetLeft: 10, y: { grid: true, type: "linear" }, marks: [ Plot.axisX({ anchor: "bottom", label: "", tickFormat: (d) => d === 2006 ? d.toString() : d.toString().substring(2, 4) }), Plot.axisY({ anchor: "left", label: null, tickSize: 0, tickFormat: (d) => d === 0 ? 0 : _.replace(d3.format(".2s")(d), ",0", "") }), Plot.axisY({ label: "Preinscritos en " + currentEdition + ": Número de preinscripciones, por año", ticks: [], fontSize: 16, fontWeight: "bold" }), Plot.barY(myData, { x: "year", y: "total", fill: zegamaColor }), Plot.text([..._.take(myData, 1), ..._.takeRight(myData, 5)], { x: "year", dx: -1, dy: -7, y: "total", textAnchor: "center", text: (d) => d.total === 0 ? "" : _.replace(d.total.toString(), ",", ".") }), Plot.ruleY([0]) ] }); }33:T61e,{ let myData = aq.from(data).groupby("win").count().objects(); return Plot.plot({ width: 700, height: 350, marginLeft: 50, marginBottom: 40, insetTop: 35, insetLeft: 5, y: { grid: true, type: "sqrt" }, marks: [ Plot.axisX({ anchor: "bottom", label: "Dorsales obtenidos" }), /*Plot.axisY({ label: "Preinscritos en 2023: Nº de preinscripciones agraciadas para un mismo participante", ticks: [], fontSize: 16, fontWeight: "bold" }),*/ Plot.axisX({ label: "Preinscritos en " + currentEdition + ": Nº de preinscripciones agraciadas para un mismo participante", labelOffset: 30, labelAnchor: "left", anchor: "top", ticks: [], fontSize: 16, fontWeight: "bold" }), Plot.axisX({ label: "2006-2023", labelOffset: 5, labelAnchor: "left", anchor: "top", ticks: [], fontSize: 13, color: "#666", fontWeight: "bold" }), Plot.axisY({ anchor: "left", label: null, tickSize: 0, tickFormat: (d) => d === 0 ? 0 : _.replace(d3.format(".2s")(d), ",0", "") }), Plot.barY(myData, { x: "win", y: "count", fill: zegamaColor }), Plot.textY(myData, { x: "win", y: "count", text: (d) => _.replace(d.count.toString(), ",", ""), dy: -8 }), Plot.ruleY([0]) ] }); }34:T5ff,{ let myData = aq.from(data).groupby("attempts").count().objects(); return Plot.plot({ marginLeft: 50, marginBottom: 40, height: 350, insetTop: 35, insetLeft: 5, y: { grid: true, type: "sqrt" }, marks: [ /*Plot.axisY({ label: "Distribución del número de intentos realizados por un mismo participante", ticks: [], fontSize: 16, fontWeight: "bold" }),*/ Plot.axisX({ label: "Preinscritos en " + currentEdition + ": Distribución de intentos por participante", labelOffset: 30, labelAnchor: "left", anchor: "top", ticks: [], fontSize: 16, fontWeight: "bold" }), Plot.axisX({ label: "2006-2023", labelOffset: 5, labelAnchor: "left", anchor: "top", ticks: [], fontSize: 13, color: "#666", fontWeight: "bold" }), Plot.axisX({ anchor: "bottom", label: "Preinscripciones" }), Plot.axisY({ anchor: "left", label: null, tickSize: 0, tickFormat: (d) => d === 0 ? 0 : _.replace(d3.format(".2s")(d), ",0", "") }), Plot.barY(myData, { x: "attempts", y: "count", fill: zegamaColor }), Plot.textY(myData, { x: "attempts", y: "count", text: (d) => _.replace(d.count.toString(), ",", ""), dy: -8 }), Plot.ruleY([0]) ] }); }35:T931,{ // % of winning pre-registration over the total, by year // do not take into account 2023 let myYears = _.dropRight(years); // allocate year summaries let summaryByYear = _.fromPairs( myYears.map((year) => [year, { D: 0, X: 0 }]) ); myYears.forEach((year) => { summaryByYear[year].D = _.sumBy(data, (o) => (o[year] === "D" ? 1 : 0)); summaryByYear[year].X = _.sumBy(data, (o) => (o[year] === "X" ? 1 : 0)); summaryByYear[year].total = _.sumBy(data, (o) => ["X", "D"].includes(o[year]) ? 1 : 0 ); summaryByYear[year].drawnPercentage = summaryByYear[year].D / summaryByYear[year].total; }); let myData = _.values( _.mapValues(summaryByYear, (value, key) => _.set(value, "year", +key)) ); return Plot.plot({ width: 700, height: 300, marginLeft: 50, insetTop: 25, insetLeft: 25, y: { percent: true, grid: true, type: "linear" }, marks: [ Plot.axisY({ label: "Preinscritos en " + currentEdition + ": % de dorsales asignados sobre total de preinscripciones, por año", ticks: [], fontSize: 16, fontWeight: "bold" }), Plot.axisX({ anchor: "bottom", label: "", ticks: myYears, tickFormat: (d) => d === 2006 ? d.toString() : d.toString().substring(2, 4) }), Plot.axisY({ anchor: "left", label: null, ticks: [5, 10, 20, 30, 40, 50], tickSize: 0, tickFormat: (d) => d === 0 ? 0 : _.replace(d3.format(".2s")(d), ",0", "") }), Plot.line(myData, { x: "year", y: "drawnPercentage", strokeWidth: 3, stroke: zegamaColor }), Plot.dot(myData, { x: "year", y: "drawnPercentage", stroke: "white", strokeWidth: 2, fill: zegamaColor, r: 4 }), Plot.text([_.first(myData), _.last(myData)], { x: "year", dx: 0, dy: -10, fill: "black", stroke: "white", strokeWidth: 3, y: "drawnPercentage", textAnchor: "center", text: (d) => d.drawnPercentage === 0 ? "" : _.replace(d3.format(".0%")(d.drawnPercentage), ",0", "") }), Plot.ruleY([0]) ] }); }36:T1016,{ let myData = aq .from(dataWithout2023) .groupby("win", "attempts") // count how many times a combination of occurs .count() // get percentage of winning relative to the nº of attempts (To calculate the percentage rate of getting a dorsal given a specific nº // of enrollments, divide it by the sum of all values in the same group (nº of attempts), resulting in the proportion (or observed //probability) of the value relative to the whole. .groupby("attempts") .derive({ percentage: (d) => d.count / op.sum(d.count) || 0 }) // include `|| 0` in case sum is zero .objects(); myData.forEach((d) => (d.count = d.win === 0 ? d.count * -1 : d.count)); let failScale = d3 .scaleSqrt() .domain( showWinRate === "rate" ? [1, 0] : [_.minBy(myData, "count").count, 0] ) .range(failColoRange); let successScale = d3 .scaleSqrt() .domain( showWinRate === "rate" ? [0, _.maxBy(_.reject(myData, ["win", 0]), "percentage").percentage] : [0, _.maxBy(myData, "count").count] ) .range(successColoRange); let yDomain = _.range(_.maxBy(myData, "win").win, -1); return Plot.plot({ height: 500, width: 880, padding: 0.015, grid: true, marginTop: 40, insetTop: 35, insetRight: 50, insetBottom: 0, marginBottom: 40, //caption: md` .`, x: { axis: "bottom", label: "Nº de preinscripciones realizadas", tickSize: 0, padding: 0.15 }, y: { label: "Dorsales obtenidos", domain: yDomain, type: "band", tickSize: 0 }, marks: [ Plot.cell(myData, { x: "attempts", y: "win", fill: (d) => d.count < 0 ? failScale(showWinRate === "rate" ? d.percentage : d.count) : successScale(showWinRate === "rate" ? d.percentage : d.count) }), /*Plot.axisX({ anchor: "top", labelAnchor: "left", label: showWinRate === "rate" ? "Preinscritos 2023: % de conseguir (o no) 1 o más dorsales según nº de preinscripciones realizadas, 2016-2022" : "Preinscritos 2023: Ocasiones en las que se obtiene (o no) 1 o más dorsales según nº de preinscripciones realizadas, 2016-2022", ticks: [], fontSize: 16, fontWeight: "bold" }),*/ Plot.axisX({ label: showWinRate === "rate" ? "Preinscritos " + currentEdition + ": % de conseguir (o no) 1 o más dorsales según nº de preinscripciones realizadas" : "Preinscritos " + currentEdition + ": Ocasiones en las que se obtiene (o no) 1 o más dorsales según nº de preinscripciones realizadas", labelOffset: 30, labelAnchor: "left", anchor: "top", ticks: [], fontSize: 16, fontWeight: "bold" }), Plot.axisX({ label: "2006-" + (currentEdition - 1), labelOffset: 5, labelAnchor: "left", anchor: "top", ticks: [], fontSize: 13, color: "#666", fontWeight: "bold" }), Plot.text(myData, { x: (d) => 15, y: (d) => 0, fill: "#444", stroke: "white", strokeWidth: 5, fontWeight: "regular", text: (d) => "← Preinscripciones sin dorsal", title: "count" }), Plot.text(myData, { x: "attempts", y: "win", fill: (d) => { if (showWinRate === "rate") { return d.attempts < 8 && d.win === 0 ? "white" : "black"; } else { return d.attempts < 3 && d.win === 0 ? "white" : "black"; } }, text: (d) => showWinRate === "rate" ? _.replace( _.replace(d3.format(".1%")(d.percentage), ",0", ""), "%", "" ) : Math.abs(d.count).toString(), title: "count" }), Plot.ruleY([0], { dy: -17, strokeWidth: 3 }) ] }); }37:T56d,{ let myData = _(accumSummary) .filter(["year", accumulatedPointsForYear]) .map((o) => _.set(o, "points", +o.points)) .sortBy(["year", "points"]) .value(); return Plot.plot({ height: 350, width: 675, marginLeft: 50, marginBottom: 40, insetTop: 25, insetLeft: 5, y: { grid: true, type: "sqrt" }, marks: [ Plot.axisX({ anchor: "bottom", label: "Números acumulados" }), Plot.axisY({ label: "Preinscritos " + currentEdition + ": Opciones acumuladas por los participantes para el sorteo, por año", ticks: [], fontSize: 16, fontWeight: "bold" }), Plot.axisY({ anchor: "left", label: null, tickSize: 0 //tickFormat: (d) => // d === 0 ? 0 : _.replace(d3.format(".2s")(d), ".0", "") }), Plot.barY(myData, { x: "points", y: "count", fill: zegamaColor }), Plot.text([accumulatedPointsForYear], { fontSize: 40, fill: "#d0d0d0", fontWeight: "bold", frameAnchor: "bottom-right", dy: -40 }), Plot.textY(myData, { x: "points", y: "count", text: (d) => d.count === 0 ? "" : _.replace(d.count.toString(), ",", ""), dy: -8 }), Plot.ruleY([0]) ] }); }38:T70d,{ let myData = _.reject(longDataExtentRegistrations, ["name", "undefined"]); let labelY = (d) => { let wins = _.countBy(_.filter(myData, ["name", d]), (o) => o.value)["D"]; let fails = _.countBy(_.filter(myData, ["name", d]), (o) => o.value)["X"]; wins = _.isNaN(wins) || _.isUndefined(wins) ? 0 : wins; let total = wins + fails; return ""; /* if (_.isNaN(total)) return ""; // show special cases else if( total === 16) return wins + "/" + total; else if( wins === 0) return wins + "/" + total; else wins + "/" + total; */ }; return Plot.plot({ height: 550, width: 550, padding: 0, marginLeft: 0, insetTop: 0, insetRight: -40, insetBottom: -10, grid: false, marks: [ Plot.cell(myData, { inset: 0.35, x: "year", y: "name", fill: (d) => d["value"] === "D" ? _.last(successColoRange) : d["value"] === "X" ? failColor : naColor }), Plot.text(myData, { x: "attempts", y: "win", text: (d) => d.value, title: "count" }), Plot.axisY({ anchor: "right", label: "", tickSize: 0, tickFormat: (d) => labelY(d) }), Plot.axisX({ anchor: "bottom", label: "", ticks: years, tickSize: 0, fontSize: 12, tickFormat: (d) => [2006, 2023].includes(d) ? d.toString() : d.toString().substring(2, 4) }), Plot.axisX({ anchor: "top", label: "", ticks: years, tickSize: 0, fontSize: 12, tickFormat: (d) => [2006, 2023].includes(d) ? d.toString() : d.toString().substring(2, 4) }) ] }); }39:T7ac,{ allInGridCanvas; var canvas = document.getElementById(allInGridCanvas.id).getContext("2d"); canvas.clearRect(0, 0, canvasWidth, canvasHeight); const cellWidth = 50, cellHeight = 0.15, cellHeightSuccess = cellHeight * 3, offsetX = 1, paddingTop = 30; let myYears = _.without(years, ...covidYears); let sortByAttempts = _.reverse(_.sortBy(data, "attempts")), cols = myYears.length, rows = sortByAttempts.length; console.log(sortByAttempts); let attempts = -1; canvas.font = "13px Arial"; canvas.textAlign = "left"; console.log(sortByAttempts.length); for (var y = 0; y < rows; y++) { for (var x = 0; x < cols; x++) { if (sortByAttempts[y] === undefined) { console.log(x, y, sortByAttempts[y]); } let value = sortByAttempts[y][myYears[x]]; canvas.fillStyle = value === "X" ? failColor2 : value === "D" ? _.last(successColoRange) : "#F8F8F8"; canvas.fillRect( x * cellWidth + x * offsetX, paddingTop + y * cellHeight, cellWidth, value === "D" ? cellHeightSuccess : cellHeight ); if (attempts !== sortByAttempts[y].attempts) { attempts = sortByAttempts[y].attempts; let xPos = cols * cellWidth + cellWidth / 2, yPos = paddingTop + y * cellHeight; if (![15, 14, 13, 12, 11].includes(attempts)) { canvas.fillStyle = "#333333"; canvas.fillText(attempts + " intentos ↓", xPos, yPos); } canvas.beginPath(); canvas.moveTo(xPos, yPos); canvas.lineTo(xPos - 8, yPos); canvas.lineWidth = cellHeight; canvas.stroke(); } } } canvas.font = "13px Arial"; canvas.fillStyle = "#333333"; canvas.textAlign = "center"; myYears.forEach((year, x) => { canvas.fillText(year, x * cellWidth + x * offsetX + cellWidth / 2, 20); }); return "all the registration table"; }3a:T573,data = { let yearKeys = years.map((year) => year.toString()); return ( _.chain(zegama2024) // keep source data .cloneDeep() // add current year .map((o) => _.set(o, currentEdition, "X")) // add covid years, race not taking place //.map((o) => _.set(o, "2020", "")) //.map((o) => _.set(o, "2021", "")) // avoid empty strings on year fields .map((o) => _.mapValues(o, (value, key) => years.includes(+key) ? (_.isEqual(value, "") ? 0 : value) : value ) ) // get some data // number of times a person wins lottery .each((o) => { _.set( o, "win", _.reduce( years, (result, value, key) => { return result + (o[value.toString()] === "D" ? 1 : 0); }, 0 ) ); }) // count attempts (Ds + Xs) .each((o) => _.set( o, "attempts", _.values(o).filter((value) => ["X", "D"].includes(value)).length ) ) // get % of winning (Ds vs attempts) .each((o) => _.set(o, "winShare", o.win / o.attempts)) // which year has more people opting? Xs + Ds // which year has more Ds // which people has more gaps (opts agains after x years of abscense) // draw heatmap .value() ); }3b:T679,accumSummary = { let myYears = _.without(years, _.first(years), ...covidYears); // all possible accumulated pre-registrations let accum = _.range(1, myYears.length + 1); // create empty counter, to be populated for each year let counterTemplate = {}; accum.forEach((acc) => _.set(counterTemplate, acc, 0)); // array of counters let counters = {}; myYears.forEach((year, i) => { let counter = _.cloneDeep(counterTemplate); // create array of years from first to current let yearSpan = _.without(_.range(_.first(years), year + 1), ...covidYears); data.forEach((row) => { let values = yearSpan.map((y) => row[y]); // was the person participating the observed year? if (["X", "D"].includes(row[year])) { // check last time value is a 0 or D let lastX = _.findLastIndex( _.dropRight(values), // do not consider the current year (v) => v != "X" ); if (lastX === values.length - 1) { // never applied before or won this year counter[1] = counter[1] + 1; } else { // if no index found, we have all Xs before the current year! if (lastX === -1) lastX = -1; let diff = values.length - 1 - lastX; counter[diff] = counter[diff] + 1; } } }); counters[year] = counter; }); // return counters; // convert to tidy data let result = []; _.keys(counters).forEach((year) => { _.keys(counters[year]).forEach((accum) => { result.push({ year, points: accum, count: counters[year][accum] }); }); }); return result; }3c:T569,dataWithout2023 = { let yearKeys = years.map((year) => year.toString()); return ( _.chain(zegama2024) // keep source data .cloneDeep() // add current year //.map((o) => _.set(o, currentEdition, "X")) // add covid years, race not taking place //.map((o) => _.set(o, "2020", "")) //.map((o) => _.set(o, "2021", "")) // avoid empty strings on year fields .map((o) => _.mapValues(o, (value, key) => years.includes(+key) ? (_.isEqual(value, "") ? 0 : value) : value ) ) // get some data // number of times a person wins lottery .each((o) => { _.set( o, "win", _.reduce( years, (result, value, key) => { return result + (o[value.toString()] === "D" ? 1 : 0); }, 0 ) ); }) // count attempts (Ds + Xs) .each((o) => _.set( o, "attempts", _.values(o).filter((value) => ["X", "D"].includes(value)).length ) ) // get % of winning (Ds vs attempts) .each((o) => _.set(o, "winShare", o.win / o.attempts)) // since we are not taking into account the year of the current edition here, filter all those that never registered before .reject(["attempts", 0]) .value() ); }3d:T88f,// Copyright 2021, Observable Inc. // Released under the ISC license. // https://observablehq.com/@d3/color-legend function Swatches( color, { columns = null, format, unknown: formatUnknown, swatchSize = 15, swatchWidth = swatchSize, swatchHeight = swatchSize, marginLeft = 0 } = {} ) { const id = `-swatches-${Math.random().toString(16).slice(2)}`; const unknown = formatUnknown == null ? undefined : color.unknown(); const unknowns = unknown == null || unknown === d3.scaleImplicit ? [] : [unknown]; const domain = color.domain().concat(unknowns); if (format === undefined) format = (x) => (x === unknown ? formatUnknown : x); function entity(character) { return `&#${character.charCodeAt(0).toString()};`; } if (columns !== null) return htl.html`

${domain.map((value) => { const label = `${format(value)}`; return htl.html`

${label}

`; })}

`; return htl.html`

${domain.map( (value) => htl.html`${format( value )}` )}

`; }3e:T4dc,{ // allocate year summaries let summaryByYear = _.fromPairs(years.map((year) => [year, { D: 0, X: 0 }])); years.forEach((year) => { summaryByYear[year].D = _.sumBy(data, (o) => (o[year] === "D" ? 1 : 0)); summaryByYear[year].X = _.sumBy(data, (o) => (o[year] === "X" ? 1 : 0)); summaryByYear[year].total = _.sumBy(data, (o) => ["X", "D"].includes(o[year]) ? 1 : 0 ); summaryByYear[year].drawnPercentage = summaryByYear[year].D / summaryByYear[year].total; }); let myData = _.values( _.mapValues(summaryByYear, (value, key) => _.set(value, "year", +key)) ); return Plot.plot({ width: 800, grid: true, insetLeft: 50, y: { percent: true, grid: true, type: "linear", domain: [0, 55] }, x: { type: "sqrt" }, marks: [ /*Plot.line(myData, { x: "total", y: "drawnPercentage", stroke: "#ccc", curve: "catmull-rom" }),*/ Plot.dot(myData, { x: "total", y: "drawnPercentage", fill: "black" }), Plot.text(myData, { x: "total", dx: 5, dy: -7, y: "drawnPercentage", textAnchor: "start", text: (d) => d.year.toString() }) ] }); }3f:[{"id":"145e23838cbc85d78f42f36a86b2add2e91de7fc23e5f8ff5689e1b4b7bc16089049122515766405ffddc24bc3093d706c9b9e59091006b86d774ffe3d08c353","href":"https://static.observableusercontent.com/files/145e23838cbc85d78f42f36a86b2add2e91de7fc23e5f8ff5689e1b4b7bc16089049122515766405ffddc24bc3093d706c9b9e59091006b86d774ffe3d08c353","name":"Zegama-2023.csv","type":"text/csv","lastModified":1707899758944.311,"size":615342},{"id":"49d9295eace970223e8c98a4fcebebd811f9681e57e025e288746ee18b9a6ad545ed859cef07830f670999db8e25dd38f103d4b3bbdb670a45fb0b623e7479da","href":"https://static.observableusercontent.com/files/49d9295eace970223e8c98a4fcebebd811f9681e57e025e288746ee18b9a6ad545ed859cef07830f670999db8e25dd38f103d4b3bbdb670a45fb0b623e7479da","name":"Zegama-2024.csv","type":"text/csv","lastModified":1707899816139.171,"size":501416}] 2f:{"title":"Zegama Aizkorri 2024 pre-registrations analysis","cells":[{"id":0,"value":"# Zegama Aizkorri 2024 pre-registrations analysis","pinned":false,"hidden":false,"mode":"md"},{"id":1012,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":1872,"value":"

\n Methodological notes:\n

\n To take into account that the dataset with pre-registered participants on a given year just contains a 'snapshot' of their past attempts for these participants, not all participants that have pre-registered at any previous edition. All summaries for previous years (i.e., sum of pre-registered participants in 2006, or 2007, etc. ) are based on the latest pre-registered list, so participants that pre-registered in past editions are not present.\n

\n Ideally we should look for all pre-registered lists of previous editions and group them all in single historical dataset.\n

","pinned":false,"hidden":false,"mode":"html"},{"id":1888,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":339,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":481,"value":"How many people trying it for first time?","pinned":false,"hidden":false,"mode":"md"},{"id":485,"value":"// new participtant are those with 1 chance (AUKERAK = 1). 1 chance can be either because it´s the first time enrolling OR the previous year got a dorsal (no chances accumulated) and pre-registered again. The latest case is not considered as new participant\nnewLotteryParticipants = _(data)\n .filter([\"AUKERAK\", 1])\n .reject([2022, \"D\"])\n .value().length","pinned":false,"hidden":false,"mode":"ojs"},{"id":492,"value":"That means ${d3.format('.1%')(newLotteryParticipants / data.length)} of ${currentEdition} pre-registrations.","pinned":false,"hidden":false,"mode":"md"},{"id":164,"value":"Over the period 2006-${currentEdition-1}, most of the pre-registrations \n(${\n_.maxBy(\n aq.from(data).groupby(\"win\").count().objects(), \n 'count'\n).count}) haven´t been drawn and a large number of participants do not win a dorsal, but since previous unsuccessful pre-registrations are taken into account on each new attempt, there is a considerable number of people (more on that later on) that get the chance to participate at least once. \n\nEvent at the other end there are up to \n${\n_.maxBy(\n aq.from(data).groupby(\"win\").count().objects(), \n 'win'\n).count} cases which had \n${\n_.maxBy(\n aq.from(data).groupby(\"win\").count().objects(), \n 'win'\n).win} winning pre-registrations!","pinned":false,"hidden":false,"mode":"md"},{"id":69,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":172,"value":"How often people try? At least \n${_.minBy(\n aq.from(data).groupby(\"attempts\").count().objects(), \n \"attempts\")\n .count\n} have tried at least once, which is almost half of the total pre-registrations\n${\nd3.format(\".1%\")(_.minBy(\n aq.from(data).groupby(\"attempts\").count().objects(), \n \"attempts\")\n .count / data.length)\n} done over all the years.\n\nThe other half of participants have tried more then once, ending up with a small reduced number of persistent individuals ${_.maxBy(\n aq.from(data).groupby(\"attempts\").count().objects(), \n \"attempts\")\n .count\n} that have pre-registered every year.","pinned":false,"hidden":false,"mode":"md"},{"id":174,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":1174,"value":"Odds of winning the pre-registration lottery? Quite a few nowadays. After the first year of the lottery, chances have declined sharply and it was only 4% in the last lottery draw.","pinned":false,"hidden":false,"mode":"md"},{"id":385,"value":"$35","pinned":false,"hidden":false,"mode":"ojs"},{"id":1200,"value":"So, which are your odds of winning if you pre-register for the first time? How likely is it to win the lottery draw if you have been participating several times? The graph below summarises all that showing how odds have been over the previous years by looking at all the combinations of getting a dorsal N times vs for each number of attempts to win the lottery.","pinned":false,"hidden":false,"mode":"md"},{"id":969,"value":"viewof showWinRate = Inputs.radio([\"counted observations\", \"rate\"], {\n label: \"For each enrollment attempt bucket, show lotery winner events as:\",\n value: \"counted observations\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":183,"value":"$36","pinned":false,"hidden":false,"mode":"ojs"},{"id":1592,"value":"$37","pinned":false,"hidden":false,"mode":"ojs"},{"id":742,"value":"As a point of interest, nowadays there is still people pre-registered this year, who also applied in the first edition 2006!\n\nSome curious cases here:\n- One participant pre-registered in 2006, never went back and this year has applied again.\n- All those who have applied all years won the lottery draw at least once.","pinned":false,"hidden":false,"mode":"md"},{"id":1362,"value":"Swatches(\n d3.scaleOrdinal(\n [\"Pregistro con dorsal\", \"Pregistro sin dorsal\", \"Sin preregistro\"],\n [_.last(successColoRange), failColor, naColor]\n )\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":705,"value":"$38","pinned":false,"hidden":false,"mode":"ojs"},{"id":1375,"value":"Over all these years ${data.length} people have pre-registered in the lottery draw for Zegama Aizkorri. While there are a thousand of different stories for each of the participants and it´s impossible to view them all in detail, it´s worth to capture all these ${data.length} runners over these 15 years of lottery draws and the explosion of interest the last years.\n\nSo here is a high-level view of all the data, sorted top-down by nº of attempts per participant:","pinned":false,"hidden":false,"mode":"md"},{"id":605,"value":"$39","pinned":false,"hidden":false,"mode":"ojs"},{"id":845,"value":"canvasHeight = 3000","pinned":false,"hidden":false,"mode":"ojs"},{"id":1382,"value":"Swatches(\n d3.scaleOrdinal(\n [\"Pregistro con dorsal\", \"Pregistro sin dorsal\", \"Sin preregistro\"],\n [_.last(successColoRange), failColor2, naColor]\n )\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":596,"value":"allInGridCanvas = html`

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1534,"value":"---\n## Data and config","pinned":false,"hidden":false,"mode":"md"},{"id":1846,"value":"currentEdition = 2024","pinned":true,"hidden":false,"mode":"ojs"},{"id":1856,"value":"zegama2024 = await FileAttachment(\"Zegama-2024.csv\").csv({typed: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":273,"value":"zegama2023 = await FileAttachment(\"Zegama-2023.csv\").csv({typed: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs"},{"id":1541,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs"},{"id":740,"value":"Get data without taking into account 2023. Graph attempts vs wins uses this dataset. As of 2023/01/15 there are no results for the lottery, so attempts in 2023 are not considered.","pinned":false,"hidden":false,"mode":"md"},{"id":737,"value":"$3c","pinned":false,"hidden":false,"mode":"ojs"},{"id":1678,"value":"dataWithout2023","pinned":true,"hidden":false,"mode":"ojs"},{"id":579,"value":"longData = {\n let myData = [],\n myYears = _.without(years, ...covidYears);\n // for each person, transform the row into long data (years from wide to long)\n data.forEach((row) => {\n myData.push(\n myYears.map((year) => ({\n year,\n value: row[year],\n name: row[\"ABIZENAK IZENA\"]\n }))\n );\n });\n return _.flatten(myData);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":700,"value":"longDataExtentRegistrations = {\n let myData = [],\n myYears = _.without(years, ...covidYears);\n // for each person, transform the row into long data (years from wide to long)\n data.forEach((row) => {\n // if the participant applied in the first and the last edition, select it\n if (\n [\"D\", \"X\"].includes(row[_.first(years)]) &&\n [\"D\", \"X\"].includes(row[_.last(years)])\n )\n myData.push(\n myYears.map((year) => ({\n year,\n value: row[year],\n name: row[\"ABIZENAK IZENA\"]\n }))\n );\n });\n return _.flatten(myData);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":35,"value":"years = _.range(firstYear, currentEdition + 1)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1651,"value":"accumulatedPointsForYear = {\n let myYears = _.without(_.drop(years, 1), ...covidYears);\n let i = 0;\n let accumulatedPointsForYear = myYears[i % myYears.length].toString();\n\n yield accumulatedPointsForYear;\n while (true) {\n yield Promises.delay(1000, myYears[++i % myYears.length].toString());\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":21,"value":"firstYear = _.chain(zegama2023)\n .sample()\n .keys()\n .filter((key) => !_.isNaN(+key))\n .map((key) => +key)\n .first()\n .value()","pinned":false,"hidden":false,"mode":"ojs"},{"id":545,"value":"covidYears = [2020, 2021]","pinned":false,"hidden":false,"mode":"ojs"},{"id":379,"value":"// get colors from diverging color scheme d3['schemePiYG'][9];\nsuccessColoRange = [\"#f7f7f7\", /*\"#7fbc41\"*/ zegamaColor]","pinned":false,"hidden":false,"mode":"ojs"},{"id":376,"value":"// get colors from diverging color scheme d3['schemePiYG'][9];\n//failColoRange = [\"#c51b7d\", \"#f7f7f7\"]\n//failColoRange = [\"#e34a33\", \"#fee8c8\"]\nfailColoRange = [failColor3, \"#f0f0f0\"]","pinned":false,"hidden":false,"mode":"ojs"},{"id":1349,"value":"winColor = \"#49558F\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1352,"value":"failColor = \"#dadaeb\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1469,"value":"failColor2 = \"#bcbddc\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1526,"value":"failColor3 = \"#807dba\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1355,"value":"naColor = \"#f8f8f8\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1512,"value":"zegamaColor = \"#6f9058\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":599,"value":"canvasWidth = 1400","pinned":false,"hidden":false,"mode":"ojs"},{"id":75,"value":"import { aq, op } from \"@uwdata/arquero\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1364,"value":"$3d","pinned":false,"hidden":false,"mode":"ojs"},{"id":1488,"value":"\n\n---\n\n## Work in Progress / Deprecated ","pinned":false,"hidden":false,"mode":"md"},{"id":412,"value":"$3e","pinned":false,"hidden":false,"mode":"ojs"},{"id":241,"value":"Combination of events combining nº of preregistrations vs nª dorsal obtained:","pinned":false,"hidden":false,"mode":"md"},{"id":945,"value":"data1 = {\n let data1 = aq\n .from(dataWithout2023)\n .groupby(\"win\", \"attempts\")\n .count()\n .objects();\n\n return aq\n .from(dataWithout2023)\n .groupby(\"win\", \"attempts\")\n .count()\n .groupby(\"attempts\")\n .derive({\n percentage: (d) => d.count / op.sum(d.count) || 0\n }) // include `|| 0` in case sum is zero\n .objects();\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":562,"value":"Count by registration gap (by nº of years between registration):","pinned":false,"hidden":false,"mode":"md"},{"id":569,"value":"{\n // difference between consecutive nums\n let diff = function (A) {\n return A.slice(1).map(function (n, i) {\n return n - A[i];\n });\n };\n\n // transpose row data and keep a series of {year, value}\n // exclude covid years, we want to know how many editions have passed by between\n // to registrations (no race took place during covid years)\n let rowsByYear = data.map((row) =>\n _.without(years, ...covidYears).map((year) => ({ year, value: row[year] }))\n );\n let yearDiffs = rowsByYear.map((row) => {\n // get indexes for Xs or Ds\n let indexes = _.sortBy(\n [\n ..._.keys(_.pickBy(row, { value: \"X\" })),\n ..._.keys(_.pickBy(row, { value: \"D\" }))\n ].map((k) => +k)\n );\n return diff(indexes);\n });\n\n let registrationGaps = yearDiffs;\n\n return _.countBy(_.flattenDeep(registrationGaps));\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":759,"value":"For each year, % of new participants","pinned":false,"hidden":false,"mode":"md"},{"id":1853,"value":"","pinned":true,"hidden":false,"mode":"sql","output":"","database":"Zegama-2023.csv"}],"files":"$W3f","stdlib":"1","titling":"auto"}