Public
Edited
Mar 20, 2023
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
addTooltips(
Plot.plot({
color: {
...makePartyScale(twoCandidatePreferred2022),
legend: true,
label: "Party"
},
projection: {
type: "mercator",
domain: {
type: "MultiPoint",
coordinates: [
// Brisbane bounding box
[152.965818, -27.511633],
[153.10495, -27.436701]
]
}
},
caption:
"Arrow angle indicates swing. Arrow size is swing mangitude multiplied by the total of people who voted at the booth (a big arrow is a lot of 'converted' voters. Booth voronoi is coloured by the 2CP victory magnitude.",
marks: [
Plot.graticule(),
Plot.sphere(),
// TODO(mjbo) Much nicer tilemap
Plot.geo(divisionGeojson2022, { strokeOpacity: 0.5 }),
Plot.vector(twoCandidatePreferred2022, {
x: "Longitude",
y: "Latitude",
// if Swing is 100 point right, if Swing is 0, point up, if Swing is -100 point left
rotate: (d) => (d.Swing / 100) * 90,
// arrow length should match the magnitude of the swing * the size of the booth
length: (d) => Math.abs(d.Swing) * d.OrdinaryVotes,
stroke: (d) => d.PartyAb,
title: getTooltip
}),
Plot.voronoi(twoCandidatePreferred2022, {
x: "Longitude",
y: "Latitude",
fill: (d) => d.PartyAb,
title: getTooltip,
fillOpacity: (d) => d.OrdinaryVotes
})
// TODO(mjbo) Why aren't these centering themselves in the centroid?
// Plot.text(
// divisionGeojson2022.features,
// Plot.centroid({
// text: (d) => d.properties.electorateName,
// fill: "currentColor",
// stroke: "white"
// })
// )
]
})
)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db = DuckDBClient.of({
HouseDopByPP2022,
GeneralPollingPlaces2022: FileAttachment("GeneralPollingPlaces-2022@1.csv")
})
Insert cell
Insert cell
db
SELECT * FROM HouseDopByPP2022
WHERE
DivisionNm = 'Griffith'
AND PPNm = 'West End'
AND (CalculationType = 'Preference Count' OR CalculationType = 'Transfer Count')
AND Surname = 'BUTLER'
Insert cell
Insert cell
db
SELECT
PPNm, Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) :: INT as votes
FROM HouseDopByPP2022
WHERE
DivisionNm = 'Griffith'
AND PPNm = 'West End'
AND (CalculationType = 'Preference Count' OR CalculationType = 'Transfer Count')
GROUP BY (PPNm, Surname)
Insert cell
Insert cell
db
SELECT
DivisionId, ANY_VALUE(DivisionNm) as DivisionNm,
PPId, ANY_VALUE(PPNm) as PPNm,
CandidateId, ANY_VALUE(Surname) as Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) :: INT as votes
FROM HouseDopByPP2022
WHERE
CalculationType = 'Preference Count'
OR CalculationType = 'Transfer Count'
GROUP BY (DivisionId, PPId, CandidateId)
Insert cell
Insert cell
db
SELECT DivisionId, MAX(CountNum) :: INT as MaxCountNum
FROM HouseDopByPP2022
-- TODO(mjbo) Fix Parquet file as CSV headers have polluted the combined table
WHERE CountNum != 'CountNum'
GROUP BY (DivisionId)
Insert cell
db
-- ???(mjbo) I had to do this because I couldn't write a nested aggregation
-- `FILTER (WHERE CountNum < MAX(CountNum) - 1)` which seems a little silly.
-- Am I holding this wrong?
WITH max_rounds_per_division AS (
SELECT DivisionId, MAX(CountNum) :: INT as MaxCountNum
FROM HouseDopByPP2022
-- TODO(mjbo) Fix Parquet file as CSV headers have polluted the combined table
WHERE CountNum != 'CountNum'
GROUP BY (DivisionId)
)

SELECT
HouseDopByPP2022.DivisionId, ANY_VALUE(DivisionNm) as DivisionNm,
PPId, ANY_VALUE(PPNm) as PPNm,
CandidateId, ANY_VALUE(Surname) as Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) FILTER (WHERE CountNum < MaxCountNum - 1) :: INT as votes
FROM HouseDopByPP2022, max_rounds_per_division
WHERE
HouseDopByPP2022.DivisionId = max_rounds_per_division.DivisionId
AND (CalculationType = 'Preference Count'
OR CalculationType = 'Transfer Count'
)
GROUP BY (HouseDopByPP2022.DivisionId, PPId, CandidateId)
Insert cell
Insert cell

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.
Learn more