The avatar for @yaleenvironment
Yale School of the Environment
Unlisted
Edited
Oct 17, 2024
8
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
onemeasureline = Plot.plot({
title: measurefullname,
subtitle: "Score by Country",
height: 160,
x: {axis: null},
y: {axis: "right", grid: true, nice: true, label: "Score"},
marks: [
Plot.barY(countries_one_measure, {x: "spot", y: "current", fill: "#3ea1da", sort: {x: "y"}}),
Plot.ruleX(countries_one_measure, Plot.pointerX({x: "spot", py: "current", stroke: "red"})),
Plot.dot(countries_one_measure, Plot.pointerX({x: "spot", y: "current", stroke: "red"})),
Plot.text(countries_one_measure, Plot.pointerX({px: "spot", py: "current", dx: -20, dy: -17, frameAnchor: "top-right", fontVariant: "tabular-nums", fontSize: 16, text: (d) => [d.country, d.current].join(" ")}))
]
})
Insert cell
epimeasureline = Plot.plot({
title: 'Environmental Performance Index',
subtitle: "Score by Country",
height: 160,
x: {axis: null},
y: {axis: "right", grid: true, nice: true, label: "Score"},
marks: [
Plot.barY(countries_epi_measure, {x: "spot", y: "current", fill: (d) => (d.iso == countryrow.iso ? "green":"#3ea1da"), sort: {x: "y"}}),
Plot.text(onecountry_epi_measure, {px: "spot", py: "current", dy: -17, frameAnchor: "top-left", fontVariant: "tabular-nums", fontSize: 16, text: (d) => [d.country, d.current].join(" ")}),
Plot.ruleY(onecountry_epi_measure, {y: "current", strokeOpacity: 0.5, stroke: "green"}),
Plot.text(onecountry_epi_measure, {x: "spot", y: "current", dy: -8, text: (d) => d.iso}),
Plot.ruleX(countries_epi_measure, Plot.pointerX({x: "spot", py: "current", stroke: "red"})),
Plot.dot(countries_epi_measure, Plot.pointerX({x: "spot", y: "current", stroke: "red"})),
Plot.text(countries_epi_measure, Plot.pointerX({px: "spot", py: "current", dx: -20, dy: -17, frameAnchor: "top-right", fontVariant: "tabular-nums", fontSize: 16, text: (d) => [d.country, d.current].join(" ")}))
]
})
Insert cell
Insert cell
measurescatter = Plot.plot((() => {
const n = 1;
const keys = Array.from(d3.union(epi_plusone_measure.map((d) => d.region)));
const index = new Map(keys.map((key, i) => [key, i]));
const fx = (key) => index.get(key) % n;
const fy = (key) => Math.floor(index.get(key) / n);
return {
title: "Relationship by Region",
subtitle: "EPI Score and " + (mcode_in == 'EPI' ? 'Climate Change' : measurefullname),
grid: true,
x: {nice: true}, y: {nice: true},
fy: {padding: 0.3, axis: null},
fx: {padding: 0.09, axis: null},
marks:[
Plot.frame(),
Plot.dot(epi_plusone_measure, {
x: "epi",
y: "measure",
fill: "#808080",
r: 1.5,
channels: {country: "country", region: "region"},
tip: false
}),
Plot.dot(epi_plusone_measure, {
x: "epi",
y: "measure",
stroke: "region",
r: 4,
fx: (d) => fx(d.region),
fy: (d) => fy(d.region),
channels: {country: "country", region: "region"},
tip: {
format: {
country: true,
region: false,
stroke: false,
x: true,
y: true,
fx: false,
fy: false
}
}
}),
Plot.axisX({anchor: "bottom", facetAnchor: null, nice: true, label: "EPI Score" }),
Plot.axisY({label: mcode_in == 'EPI' ? 'Climate Change' : measurefullname}),
Plot.text(keys, {fx, fy, frameAnchor: "top-right", dy: -16, fontSize: 16}),
]
};
})())
Insert cell
threescatter = Plot.plot((() => {
const n = 1;
const keys = Array.from(d3.union(epi_plus_three.map((d) => d.measurement)));
const index = new Map(keys.map((key, i) => [key, i]));
const fx = (key) => index.get(key) % n;
const fy = (key) => Math.floor(index.get(key) / n);
return {
title: "Relationship by Region",
subtitle: "EPI and Policy Objective Scores",
grid: true,
height: 1200,
x: {nice: true}, y: {nice: true},
fy: {padding: 0.2, axis: null},
fx: {padding: 0.09, axis: null},
marks:[
Plot.frame(),
Plot.dot(epi_plus_three, {
x: "epi",
y: "measure",
fill: "#808080",
r: 1.5,
fx: (d) => fx(d.measurement),
fy: (d) => fy(d.measurement),
channels: {country: "country", region: "region", measurement:"measurement"},
tip: false
}),
Plot.dot(epi_plus_three_iso_region, {
x: "epi",
y: "measure",
stroke: "measurement",
r: 5,
fx: (d) => fx(d.measurement),
fy: (d) => fy(d.measurement),
channels: {country: "country", region: "region", measurement:"measurement"},
tip: {
format: {
country: true,
region: true,
stroke: false,
measurement: false,
x: true,
y: true,
fx: false,
fy: false
}
}
}),,
Plot.dot(epi_plus_three_iso, {
x: "epi",
y: "measure",
fill: "green",
stroke: "green",
fx: (d) => fx(d.measurement),
fy: (d) => fy(d.measurement),
//r: (d) => d.measure,
r: 6,
symbol: "star",
legend: true,
channels: {country: "country", region: "region", measurement:"measurement"},
tip: false
}),
Plot.axisX({anchor: "bottom", facetAnchor: null, nice: true, label: "EPI Score" }),
Plot.axisY({label: "Policy Objective Score"}),
Plot.text(keys, {fx, fy, frameAnchor: "top-right", dy: -16, fontSize: 16}),
]
};
})())
Insert cell
Insert cell
epi_db = DuckDBClient.of({
epi_measures_keys: { file: FileAttachment("epi_measures_meta.csv"), header: true },
epi_grouping_keys: { file: FileAttachment("epi_grouping_keys.csv"), header: true },
epi_measures_rows: { file: FileAttachment("epi_measures_rows@2.csv"), header: true },
epi_grouping_rows: { file: FileAttachment("epi_grouping_rows.csv"), header: true },
epi_display_rows: { file: FileAttachment("epi_display_rows@1.csv"), header: true }
})
Insert cell
Insert cell
iso_in = params.get("iso")
Insert cell
mcode_in = params.get("mcode")
Insert cell
Insert cell
epi_db
CREATE OR REPLACE VIEW rankedrows AS
SELECT
dense_rank() OVER (PARTITION BY mr.mcode ORDER BY CASE WHEN isnan(y2024) THEN -1 ELSE y2024 END DESC) AS rank,
dense_rank() OVER (PARTITION BY mr.mcode ORDER BY CASE WHEN isnan(y2014) THEN -1 ELSE y2014 END DESC) AS past,
row_number() OVER (PARTITION BY mr.mcode ORDER BY CASE WHEN isnan(y2024) THEN -1 ELSE y2024 END DESC, country ASC) AS spot,
mr.mcode, mr.iso, gk.country, gk.region, mr.y2024 AS "current", mr.y2014 AS "former", mk.slot, mk.variable AS measure, mk.type,
'2024' AS yyyy
FROM epi_measures_rows mr
LEFT JOIN epi_grouping_keys gk ON (mr.iso = gk.iso)
LEFT JOIN epi_measures_keys mk ON (mr.mcode = mk.mcode)
WHERE gk.include = TRUE
Insert cell
epi_localdb = sql_view_rankedrows, epi_db
Insert cell
epi_db
SELECT
dense_rank() OVER (PARTITION BY mr.mcode ORDER BY CASE WHEN isnan(y2024) THEN -1 ELSE y2024 END DESC) AS rank,
dense_rank() OVER (PARTITION BY mr.mcode ORDER BY CASE WHEN isnan(y2014) THEN -1 ELSE y2014 END DESC) AS past,
row_number() OVER (PARTITION BY mr.mcode ORDER BY CASE WHEN isnan(y2024) THEN -1 ELSE y2024 END DESC, country ASC) AS spot,
mr.mcode, mr.iso, gk.country, gk.region,
mr.y2024 AS "current",
mr.y2014 AS "former",
mk.slot, mk.variable AS measure, mk.type, '2024' AS yyyy
FROM epi_measures_rows mr
LEFT JOIN epi_grouping_keys gk ON (mr.iso = gk.iso)
LEFT JOIN epi_measures_keys mk ON (mr.mcode = mk.mcode)
WHERE gk.include = TRUE
Insert cell
// stashing
// CASE WHEN isnan(mr.y2024) THEN NULL ELSE mr.y2024 END AS current,
// CASE WHEN isnan(mr.y2014) THEN NULL ELSE mr.y2014 END AS former,
Insert cell
epi_localdb
SELECT * FROM rankedrows WHERE isnan(current) = TRUE ORDER BY mcode, spot LIMIT 2
Insert cell
Insert cell
Insert cell
epi_rankedrows.filter((d) => (d.region === 'Former Soviet States' && d.mcode === 'FSH'))
Insert cell
Insert cell
epi_localdb
SELECT iso, country, region FROM "epi_grouping_keys" where include = TRUE
Insert cell
epi_db
SELECT DISTINCT region FROM "epi_grouping_keys" WHERE include = TRUE ORDER BY region
Insert cell
iso_country_region_row = iso_country_region_list.filter((d) => ( iso_in !== null ? iso_in == d.iso : false))
Insert cell
countryrow = iso_country_region_row[0] ? iso_country_region_row[0] : countrychoice
Insert cell
countryiso = countryrow !== null ? countryrow.iso : null
Insert cell
Insert cell
epi_localdb
SELECT slot, mcode, variable AS measure, type, pol, ica, weight, description FROM "epi_measures_keys"
Insert cell
measurementchoice = indicators ?? issue_categories ?? policy_objectives ?? measurements_list.filter((d) => d.type === 'EPI')[0]
Insert cell
measurement_mcode_row = measurements_list.filter((d) => ( mcode_in !== null ? mcode_in == d.mcode : false))
Insert cell
measurement = measurement_mcode_row[0] ? measurement_mcode_row[0] : measurementchoice
Insert cell
measurefullname = measurement.measure
Insert cell
measurelabel = measurefullname + " score"
Insert cell
measure = measurement.mcode
Insert cell
Insert cell
epi_localdb
SELECT m1.iso,m1.country,m1.region,m1.current as epi, m2.current as measure,
FROM rankedrows m1
LEFT JOIN rankedrows m2 on (m1.iso = m2.iso AND m2.mcode = ${onemeasure})
WHERE m1.mcode = 'EPI'
Insert cell
onemeasure = mcode_in == 'EPI' ? 'PCC' : measure
Insert cell
epi_plusone_measure_iso = epi_plusone_measure.filter((d) => d.iso === countryrow.iso)
Insert cell
epi_plusone_measure_iso_region = epi_plusone_measure.filter((d) => d.region === countryrow.region)
Insert cell
epi_plusone_measure_iso_region_ex = epi_plusone_measure.filter((d) => d.region === countryrow.region && d.iso !== countryrow.iso)
Insert cell
epi_localdb
SELECT m2.mcode, m2.measure as measurement, m1.iso,m1.country,m1.region,m1.current as epi, m2.current as measure,
FROM rankedrows m1
LEFT JOIN rankedrows m2 on (m1.iso = m2.iso AND m2.mcode IN ('ECO','HLT','PCC'))
WHERE m1.mcode = 'EPI'
Insert cell
epi_plus_three_iso = epi_plus_three.filter((d) => d.iso === countryrow.iso)
Insert cell
epi_plus_three_iso_region = epi_plus_three.filter((d) => d.region === countryrow.region)
Insert cell
epi_plus_three_iso_region_ex = epi_plus_three.filter((d) => d.region === countryrow.region && d.iso !== countryrow.iso)
Insert cell
Insert cell
epi_localdb
SELECT iso, country, current, rank, spot
FROM rankedrows
WHERE mcode = ${measure}
ORDER BY current
Insert cell
epi_localdb
SELECT iso, country, current, rank, spot,
concat_ws('/', 'https://epi.yale.edu/country/', '2024', iso) as isohref
FROM rankedrows
WHERE mcode = 'EPI'
ORDER BY current
Insert cell
epi_localdb
SELECT iso, country, current, rank, spot,
concat_ws('/', 'https://epi.yale.edu/country/', '2024', iso) as isohref
FROM rankedrows
WHERE mcode = 'EPI' and iso = ${countryrow.iso}
ORDER BY current
Insert cell
filtered_country_list = iso_country_region_list.filter((d) => ( regionfilter !== null ? regionfilter.region == d.region : true)) // filter returns the collection is no filter is chosen
Insert cell
epistaticline = Plot.plot({
title: 'Environmental Performance Index',
subtitle: "Score by Country",
height: 160,
x: {axis: null},
y: {axis: "right", grid: true, nice: true, label: "Score"},
marks: [
Plot.barY(countries_epi_measure, {x: "spot", y: "current", fill: (d) => (d.iso == countryrow.iso ? "green":"#3ea1da"), sort: {x: "y"}}),
Plot.ruleY(onecountry_epi_measure, {y: "current", strokeOpacity: 0.5, stroke: "green"}),
Plot.text(onecountry_epi_measure, {x: "spot", y: "current", dy: -17, text: (d) => [d.iso, d.current].join("\n")}),

Plot.ruleX(countries_epi_measure, Plot.pointerX({x: "spot", py: "current", stroke: "red"})),
Plot.dot(countries_epi_measure, Plot.pointerX({x: "spot", y: "current", stroke: "red"})),
Plot.text(countries_epi_measure, Plot.pointerX({px: "spot", py: "current", dy: -17, frameAnchor: "top-left", fontVariant: "tabular-nums", fontSize: 16, text: (d) => [d.country, d.current].join(" ")}))
]
})
Insert cell
singlescatter = Plot.plot((() => {
const n = 1;
const keys = Array.from(d3.union(epi_plusone_measure.map((d) => d.region)));
const index = new Map(keys.map((key, i) => [key, i]));
const fx = (key) => index.get(key) % n;
const fy = (key) => Math.floor(index.get(key) / n);
const fa = (n) => n > 50 ? "end" : "start";
return {
title: "Relationship by Region",
subtitle: "EPI Score and " + measurefullname,
grid: true,
height: 400,
width: 400,
//x: {domain: [0,100]}, y: {domain: [0,100]},
x : {nice: true}, y: {nice: true},
marks:[
Plot.frame(),
Plot.dot(epi_plusone_measure, {
x: "epi",
y: "measure",
fill: "grey",
r: 2,
channels: {country: "country", region: "region"},
tip: false
}),
Plot.dot(epi_plusone_measure_iso_region, {
x: "epi",
y: "measure",
stroke: "region",
//r: (d) => d.measure,
r: 4,
channels: {
country: "country",
region: "region",
x: { value: "x", label: "EPI" },
y: { value: "y", label: (d) => d.mcode }
},
tip: {
format: {
country: true,
region: true,
x: true,
y: true
}
}
}),
Plot.dot(epi_plusone_measure_iso, {
x: "epi",
y: "measure",
fill: "orange",
stroke: "orange",
//r: (d) => d.measure,
r: 5,
symbol: "star",
legend: true,
channels: {country: "country", region: "region"},
tip: false
}),
Plot.axisX({anchor: "bottom", facetAnchor: null, nice: true, label: "EPI Score" }),
Plot.axisY({label: measurefullname}),
//Plot.text(keys, {fx, fy, frameAnchor: "top-right", dy: -16, fontSize: 16}),
]
};
})())
Insert cell
comparison = mcode_in == 'EPI' || measure == 'EPI' ? 'PCC' : measure
Insert cell
Insert cell
params = new URLSearchParams(location.search)
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more