published_url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vTKaJhOOz3s68GLrFVY0bVmszwSvVsgIZ_EB0QkZN4s37FQyJAJ9FEi4heoha6Hz8pppAJ9KL-iEx9e/pubhtml?gid=618962527&single=true"
<iframe src="https://docs.google.com/spreadsheets/d/e/2PACX-1vTKaJhOOz3s68GLrFVY0bVmszwSvVsgIZ_EB0QkZN4s37FQyJAJ9FEi4heoha6Hz8pppAJ9KL-iEx9e/pubhtml?gid=618962527&single=true" width=800 height=250></iframe>
tsvData = d3.tsv(tsv_url)
iso3166_url = "https://raw.githubusercontent.com/lukes/ISO-3166-Countries-with-Regional-Codes/master/all/all.csv"
iso3166_data = d3.csv(iso3166_url)
iso3166_map = {
const map = new Map(iso3166_data.map((x) => [x.name, x["alpha-3"]]));
// variablity in how country names are recorded from various sources means we need multiple aliases for some countries
map.set("Islamic Republic of Afghanistan", "AFG");
map.set("ndorra", "AND");
map.set("Ascension", "SHN");
map.set("Tristan da Cunha", "SHN");
map.set("Bahamas, The", "BHS");
map.set("Bolivia", "BOL");
map.set("Bosnia Herzegovina", "BIH");
map.set("Bulgarian", "BGR");
map.set("Burma", "MMR");
map.set("Plurinational State of Bolivia", "BOL");
map.set("British Virgin Islands", "VGB");
map.set("Brunei", "BRN");
map.set("Cape Verde", "CPV");
map.set("Central Africa", "CAF");
map.set("People's Republic of China", "CHN");
map.set("Congo Republic", "COG");
map.set("Republic of the Congo", "COG");
map.set("Congo, Republic of", "COG");
map.set("Congo, Republic of the", "COG");
map.set("Côte d’Ivoire", "CIV");
map.set("Cote d'Ivoire", "CIV");
map.set("Ivory Coast", "CIV");
map.set("Ivory Coast Côte d'Ivoire", "CIV");
map.set("Curacao", "CUW");
map.set("Czech Republic", "CZE");
map.set("Danmark", "DNK");
map.set("DominicanRepublic", "DOM");
map.set("DR Congo", "COD");
map.set("Democratic Republic of the Congo", "COD");
map.set("Democratic Republic of Congo", "COD");
map.set("East Timor", "TLS");
map.set("England", "GBR");
map.set("eSwatini", "SWZ");
map.set("Falkland Islands", "FLK");
map.set("Faroes", "FRO");
map.set("Gambia, The", "GMB");
map.set("Republic of The Gambia", "GMB");
map.set("Republic of Guinea", "GIN");
map.set("Guyan", "GUY");
map.set("Iran", "IRN");
map.set("Islamic Republic of Iran", "IRN");
map.set("Laos", "LAO");
map.set("Lao PDR", "LAO");
map.set("Lao", "LAO");
map.set("Lao People’s Democratic Republic", "LAO");
map.set("Macau", "MAC");
map.set("Micronesia", "FSM");
map.set("Federated States of Micronesia", "FSM");
map.set("Federated States of Micronesia", "FSM");
map.set("Moldova", "MDA");
map.set("Republic of Moldova", "MDA");
map.set("North MacEdonia", "MKD");
map.set("North Korea", "PRK");
map.set("Democratic People's Republic of Korea", "PRK");
map.set("Korea, Democratic People's Republic of", "PRK");
map.set("Northern Marianna Islands", "MNP");
map.set("Palestine", "PSE");
map.set("State of Palestine", "PSE");
map.set("State of Palestine", "PSE");
map.set("Pitcairn Islands", "PCN");
map.set("Reunion", "REU");
map.set("Russia", "RUS");
map.set("Salomon Islands", "SLB");
map.set("São Tomé and Príncipe", "STP");
map.set("Sahrawi Republic", "ESH");
map.set("Sint Maarten", "SXM");
map.set("Slovak Republic", "SVK");
map.set("South African", "ZAF");
map.set("South Korea", "KOR");
map.set("Republic of Korea", "KOR");
map.set("Korea, South", "KOR");
map.set("Syria", "SYR");
map.set("Taiwan", "TWN");
map.set("Chinese Taipei", "TWN");
map.set("Tanzania", "TZA");
map.set("United Republic of Tanzania", "TZA");
map.set("Türkiye", "TUR");
map.set("United Kingdom", "GBR");
map.set("United States", "USA");
map.set("United States Virgin Islands", "VIR");
map.set("and Uruguay", "URY");
map.set("Vatican", "VAT");
map.set("Vatican City", "VAT");
map.set("Venezuela", "VEN");
map.set("Bolivarian Republic of Venezuela", "VEN");
map.set("Vietnam", "VNM");
map.set("Western Samoa", "WSM");
map.set("Yemen Arab Republic", "YEM");
return map;
}
CleanName = (name) => {
let newName = name;
if (newName.includes("(")) {
newName = newName.substring(0, newName.indexOf("("));
}
if (newName.includes("[")) {
newName = newName.substring(0, newName.indexOf("["));
}
if (newName.includes("1")) {
newName = newName.substring(0, newName.indexOf("1"));
}
if (newName.includes(";")) {
newName = newName.substring(0, newName.indexOf(";"));
}
if (newName.includes("St.")) {
newName = newName.replace("St.", "Saint");
}
if (newName.includes(".")) {
newName = newName.substring(0, newName.indexOf("."));
}
if (newName.toUpperCase().startsWith("THE")) {
newName = newName.slice(4);
}
newName = newName.trim();
return newName;
}
ProcessData = (sourceData) => {
const outputData = { organizations: [] };
const columns = Object.entries(sourceData[0]);
// note, we are ignoring the IGO Name column, index offset is deliberate
for (let i = 1; i < columns.length; i++) {
const organization = {};
const entry = columns[i];
organization.name = entry[0];
organization.url = entry[1];
organization.members = [];
outputData.organizations.push(organization);
}
for (let j = 1; j < sourceData.length; j++) {
const row = sourceData[j];
const members = Object.entries(row);
// note, we are ignoring the IGO Name column, index offset is deliberate
for (let k = 1; k < members.length; k++) {
const entry = members[k];
if (entry[1]) {
let country = {};
let name = CleanName(entry[1]);
country.name = name;
country.iso3 = iso3166_map.has(name) ? iso3166_map.get(name) : "";
outputData.organizations[k - 1].members.push(country);
}
}
}
return outputData;
}
myJson = ProcessData(tsvData)
import { serialize } from "@palewire/saving-json"
DOM.download(serialize(myJson), "i18n-organizations", "Download JSON")
FindFailedMatches = () => {
const list = [];
for (const org of myJson.organizations) {
for (const country of org.members) {
if (!country.iso3) list.push(country.name);
}
}
return list;
}
failedMatches = FindFailedMatches()
uniqueFailures = new Set(failedMatches).size
import { SummaryTable } from "@observablehq/summary-table"
// SummaryTable(tsvData)
Type JavaScript, then Shift-Enter. Ctrl-space for more options. Arrow ↑/↓ to switch modes.
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.
