spb/company-atlas
Public
1#!/usr/bin/env python2"""Polite Wikidata harvester for the Company Atlas seed registry (docs/SEEDS.md).34Stages (each resumable; every SPARQL result is cached under data/seed/wikidata/sparql/<sha256>.json):5 candidates company-class × sitelink-band queries + per-country and per-industry boost queries → data/seed/candidates.json6 dissolved P576 check over every candidate → data/seed/dissolved.json (excluded by `select`)7 select normalise websites, drop generic hosts / duplicate domains, diversify (US ≤ 40 %, others ≤ 12 %, country minimums)8 → data/seed/selected.json9 details batched detail queries (labels, legal names, industries, HQ, coordinates, tickers, LEI, CIK, parent, logo, employees)10 → data/seed/details.json11 assemble importance + tiers + industry mapping + parent/domain conflicts → registry/companies/wikidata-<region>.ndjson + README.md12 all the four stages in sequence (default)1314Politeness: User-Agent CompanyAtlasBot/0.1, one query at a time, ≥ 2 s between queries, 60 s server timeout, retries with backoff.15"""16from __future__ import annotations1718import argparse19import csv20import hashlib21import json22import logging23import math24import re25import sys26import time27from collections import Counter, defaultdict28from datetime import UTC, datetime29from pathlib import Path30from typing import Any31from urllib.parse import quote, unquote, urlparse3233import httpx3435ROOT = Path(__file__).resolve().parents[1]36sys.path.insert(0, str(ROOT / "src"))3738from companyatlas.registry.industries import load_industries, map_industry, top_level_of, top_level_slugs39from companyatlas.urls import registrable_domain4041log = logging.getLogger("seed_wikidata")4243SPARQL_URL = "https://query.wikidata.org/sparql"44USER_AGENT = "CompanyAtlasBot/0.1 (contact@spboucher.ai)"45MIN_INTERVAL_S = 2.046TIMEOUT_S = 60.047MAX_TRIES = 648DATA_DIR = ROOT / "data" / "seed"49CACHE_DIR = DATA_DIR / "wikidata" / "sparql"50REGISTRY_DIR = ROOT / "registry"51OUT_DIR = REGISTRY_DIR / "companies"52COUNTRIES_CSV = REGISTRY_DIR / "countries.csv"5354# Wikidata classes queried with wdt:P31 (no P279* — the subclass tree of "business" is too broad to page). Banded classes are large.55CLASSES: dict[str, str] = {56 "Q4830453": "business", "Q6881511": "enterprise", "Q891723": "public company", "Q783794": "company", "Q1589009": "privately held company",57 "Q167037": "corporation", "Q18388277": "technology company", "Q1058914": "software company", "Q22687": "bank", "Q46970": "airline",58 "Q786820": "automobile manufacturer", "Q6500733": "pharmaceutical company", "Q210167": "video game developer",59 "Q1137109": "video game publisher", "Q1762059": "film production company", "Q18127": "record label", "Q2085381": "publisher",60 "Q613142": "law firm", "Q740752": "transport company", "Q2401749": "telecommunications company", "Q131734": "brewery",61 "Q507619": "retail chain", "Q936518": "aerospace manufacturer", "Q206361": "conglomerate", "Q249556": "railway company",62 "Q2005696": "real estate company", "Q708676": "shipyard", "Q1668024": "internet service", "Q1320047": "book publisher",63 "Q19967801": "online service",64}65BANDED_CLASSES = {"Q4830453", "Q6881511", "Q891723", "Q46970"}66# Country boosts run only when the class stage yielded fewer than BOOST_MARGIN × minimum candidates for that country.67BOOST_MARGIN = 2.068SITELINK_BANDS: list[tuple[int, int | None]] = [(80, None), (50, 80), (35, 50), (25, 35), (18, 25), (12, 18), (8, 12), (5, 8)]69MIN_SITELINKS = 570BOOST_MIN_SITELINKS = 271BOOST_LIMIT = 250072INDUSTRY_BOOST_LIMIT = 6007374# Diversification (spec: "seed diversified companies across US, Canada, Europe, UK, Japan, South Korea, India, Australia, LatAm, ME, Africa, SEA")75US_CAP_SHARE = 0.4076OTHER_CAP_SHARE = 0.1277UNKNOWN_COUNTRY_SHARE = 0.0378COUNTRY_MINIMUMS: dict[str, int] = {79 **dict.fromkeys(["CA", "GB", "DE", "FR", "JP", "KR", "IN", "AU"], 150),80 **dict.fromkeys(["BR", "MX", "AE", "SA", "ZA", "NG", "SG", "ID", "NL", "SE", "CH", "ES", "IT", "CN", "TW", "HK"], 60),81}82# Share-of-target caps for narrow classes that Wikidata over-represents among high-sitelink items (airlines, record labels, publishers…).83CLASS_CAPS: dict[str, float] = {"Q46970": 0.05, "Q18127": 0.03, "Q210167": 0.04, "Q1137109": 0.02, "Q2085381": 0.03, "Q1320047": 0.02,84 "Q1762059": 0.03, "Q708676": 0.02, "Q131734": 0.02, "Q249556": 0.03}85INDUSTRY_MINIMUM = 6086INDUSTRY_RESERVE = 150 # extra candidates per top-level industry fetched in the detail stage to satisfy INDUSTRY_MINIMUM87# English labels of Wikidata items commonly used as P452 (industry) values, per top-level industry, for the industry-boost queries.88INDUSTRY_BOOST_LABELS: dict[str, list[str]] = {89 "technology": ["information technology", "electronics industry", "consumer electronics", "computer hardware", "electronics"],90 "financial-services": ["financial services", "insurance", "banking", "asset management", "investment banking", "payment system"],91 "real-estate": ["real estate", "real estate development", "property management", "real estate industry"],92 "construction": ["construction", "construction industry", "civil engineering", "building construction", "engineering"],93 "retail": ["retail", "retailing", "supermarket", "department store", "wholesale", "grocery store"],94 "consumer-goods": ["consumer goods", "cosmetics industry", "toy industry", "furniture industry", "fast-moving consumer goods",95 "household goods", "luxury goods", "personal care", "home appliance"],96 "energy": ["energy industry", "energy", "electric power industry", "nuclear power", "oil industry", "petroleum industry",97 "renewable energy", "solar energy", "wind power"],98 "utilities": ["public utility", "electric utility", "water industry", "waste management", "electricity generation",99 "electric power distribution", "water supply"],100 "mining": ["mining", "mining industry", "steel industry", "metallurgy", "gold mining", "steelmaking", "metal industry"],101 "chemicals": ["chemical industry", "chemicals", "petrochemical industry", "specialty chemicals", "plastics industry", "fertilizer"],102 "materials": ["glass industry", "paper industry", "packaging industry", "pulp and paper industry", "forestry", "building material",103 "cement industry", "wood industry"],104 "manufacturing": ["manufacturing", "mechanical engineering", "industrial machinery", "machine industry", "electrical engineering",105 "machine tool", "industrial engineering", "heavy industry", "shipbuilding"],106 "automotive": ["automotive industry", "automobile", "automotive", "motor vehicle", "auto parts"],107 "aerospace-defense": ["aerospace industry", "arms industry", "defense industry", "aerospace", "aviation industry", "space industry",108 "defence industry"],109 "transportation": ["transport", "rail transport", "public transport", "logistics", "transportation", "freight transport", "shipping",110 "maritime transport", "railway"],111 "telecommunications": ["telecommunications industry", "telecommunications", "telecommunication", "mobile telephony",112 "internet service provider"],113 "media": ["mass media", "publishing", "advertising", "broadcasting", "entertainment industry", "film industry", "video game industry",114 "music industry", "media industry", "newspaper"],115 "healthcare": ["health care industry", "health care", "medical technology", "hospital", "pharmaceutical industry", "biotechnology",116 "medical device", "healthcare industry", "medical equipment"],117 "hospitality": ["hospitality industry", "hotel industry", "hotel", "restaurant", "hospitality", "catering", "restaurant chain"],118 "travel": ["tourism", "travel agency", "travel industry", "travel", "tourism industry", "online travel agency", "tour operator"],119 "education": ["education", "educational technology", "higher education", "e-learning", "education industry", "educational services"],120 "professional-services": ["professional services", "consulting", "management consulting", "accounting", "outsourcing", "staffing",121 "business services", "legal services", "information technology consulting", "human resources"],122 "agriculture": ["agriculture", "agribusiness", "agricultural industry", "forestry", "fishing industry", "food industry", "farming",123 "aquaculture", "agricultural machinery"],124}125126# Hosts that are never a company's own website (social profiles, blogs, stores, code forges, encyclopaedias, site builders …).127GENERIC_DOMAINS = {128 "facebook.com", "fb.com", "linkedin.com", "twitter.com", "x.com", "instagram.com", "youtube.com", "youtu.be", "wikipedia.org",129 "wikimedia.org", "wikidata.org", "blogspot.com", "blogspot.co.uk", "wordpress.com", "tumblr.com", "medium.com", "github.com", "gitlab.com",130 "sourceforge.net", "archive.org", "tiktok.com", "vk.com", "weibo.com", "t.me", "telegram.me", "bit.ly", "wix.com", "wixsite.com",131 "weebly.com", "squarespace.com", "webnode.com", "jimdo.com", "jimdosite.com", "godaddysites.com", "carrd.co", "notion.site", "substack.com",132 "patreon.com", "itch.io", "steampowered.com", "bandcamp.com", "soundcloud.com", "imdb.com", "myspace.com", "flickr.com", "pinterest.com",133 "twitch.tv", "discord.gg", "discord.com", "bilibili.com", "tistory.com", "ameblo.jp", "fc2.com", "livejournal.com", "geocities.com",134 "angelfire.com", "tripod.com", "netlify.app", "vercel.app", "herokuapp.com", "github.io", "pages.dev", "web.app", "firebaseapp.com",135 "glitch.me", "strikingly.com", "yolasite.com", "webs.com", "linktr.ee", "about.me", "crunchbase.com", "bloomberg.com", "sec.gov",136 "yelp.com", "tripadvisor.com", "foursquare.com", "goo.gl", "ow.ly", "tinyurl.com", "wa.me", "whatsapp.com", "line.me", "kakao.com",137 "spotify.com", "deezer.com", "vimeo.com", "dailymotion.com", "behance.net", "dribbble.com", "etsy.com", "ebay.com", "aliexpress.com",138 "taobao.com", "tmall.com", "rakuten.co.jp", "shopee.com", "mercadolibre.com", "google.co.uk", "googleusercontent.com", "gstatic.com",139 "webflow.io", "mystrikingly.com", "site123.me", "simplesite.com", "ucoz.ru", "narod.ru", "hatenablog.com", "note.com", "wixstatic.com",140 "shopify.com", "myshopify.com", "bigcartel.com", "storenvy.com", "yahoo.co.jp", "yahoo.com", "aol.com", "cargo.site", "format.com",141 "blogger.com", "mixi.jp", "naver.me", "cafe24.com", "modoo.at", "over-blog.com", "canalblog.com", "skyrock.com", "free.fr", "orange.fr",142 "wanadoo.fr", "pagesperso-orange.fr", "t-online.de", "web.de", "gmx.de", "chello.at", "bplaced.net", "beepworld.de", "npage.de",143 "altervista.org", "xoom.it", "libero.it", "interfree.it", "terra.com.br", "uol.com.br", "ig.com.br", "sapo.pt", "webcindario.com",144 "iespana.es", "galeon.com", "hpage.com", "wordpress.org", "js.org", "readthedocs.io", "gitbook.io", "gumroad.com", "ko-fi.com",145 "onlyfans.com", "reddit.com", "quora.com", "scribd.com", "issuu.com", "slideshare.net", "docs.google.com", "drive.google.com",146 "sites.google.com", "play.google.com", "apps.apple.com", "itunes.apple.com", "amazon.com", "amazon.co.uk", "amazon.de", "amazon.co.jp",147 "amazon.fr", "amazon.ca", "amazon.in", "amazon.com.br", "amzn.to", "microsoft.com", "apple.com", "google.com", "naver.com", "daum.net",148 "qq.com", "163.com", "sina.com.cn", "baidu.com", "sohu.com", "douyin.com", "kuaishou.com", "zhihu.com", "xiaohongshu.com",149}150# Registrable domains that are themselves seed companies: only the bare/www host counts as that company's site (not sub-brands/store pages).151PLATFORM_ROOTS = {"google.com": "www.google.com", "apple.com": "www.apple.com", "amazon.com": "www.amazon.com", "microsoft.com": "www.microsoft.com",152 "naver.com": "www.naver.com", "yahoo.com": "www.yahoo.com", "qq.com": "www.qq.com", "baidu.com": "www.baidu.com",153 "163.com": "www.163.com", "sohu.com": "www.sohu.com", "sina.com.cn": "www.sina.com.cn", "daum.net": "www.daum.net",154 "kakao.com": "www.kakao.com", "shopify.com": "www.shopify.com", "spotify.com": "www.spotify.com", "reddit.com": "www.reddit.com",155 "ebay.com": "www.ebay.com", "etsy.com": "www.etsy.com", "yelp.com": "www.yelp.com", "tripadvisor.com": "www.tripadvisor.com",156 "linkedin.com": "www.linkedin.com", "facebook.com": "www.facebook.com", "instagram.com": "www.instagram.com",157 "youtube.com": "www.youtube.com", "twitter.com": "twitter.com", "x.com": "x.com", "tiktok.com": "www.tiktok.com",158 "github.com": "github.com", "gitlab.com": "gitlab.com", "medium.com": "medium.com", "substack.com": "substack.com",159 "patreon.com": "www.patreon.com", "twitch.tv": "www.twitch.tv", "pinterest.com": "www.pinterest.com", "vimeo.com": "vimeo.com",160 "soundcloud.com": "soundcloud.com", "bandcamp.com": "bandcamp.com", "imdb.com": "www.imdb.com", "crunchbase.com": "www.crunchbase.com",161 "bloomberg.com": "www.bloomberg.com", "wix.com": "www.wix.com", "squarespace.com": "www.squarespace.com", "weebly.com": "www.weebly.com",162 "godaddy.com": "www.godaddy.com", "wordpress.com": "wordpress.com", "tumblr.com": "www.tumblr.com", "flickr.com": "www.flickr.com",163 "quora.com": "www.quora.com", "scribd.com": "www.scribd.com", "issuu.com": "issuu.com", "discord.com": "discord.com",164 "telegram.org": "telegram.org", "whatsapp.com": "www.whatsapp.com", "line.me": "line.me", "bilibili.com": "www.bilibili.com",165 "weibo.com": "weibo.com", "vk.com": "vk.com", "zhihu.com": "www.zhihu.com", "aliexpress.com": "www.aliexpress.com",166 "taobao.com": "www.taobao.com", "tmall.com": "www.tmall.com", "rakuten.co.jp": "www.rakuten.co.jp", "shopee.com": "shopee.com",167 "mercadolibre.com": "www.mercadolibre.com", "archive.org": "archive.org", "sourceforge.net": "sourceforge.net", "itch.io": "itch.io",168 "steampowered.com": "store.steampowered.com", "deezer.com": "www.deezer.com", "dailymotion.com": "www.dailymotion.com",169 "behance.net": "www.behance.net", "dribbble.com": "dribbble.com", "notion.so": "www.notion.so", "gumroad.com": "gumroad.com",170 "onlyfans.com": "onlyfans.com", "yahoo.co.jp": "www.yahoo.co.jp", "aol.com": "www.aol.com", "free.fr": "www.free.fr",171 "orange.fr": "www.orange.fr", "t-online.de": "www.t-online.de", "web.de": "web.de", "gmx.de": "www.gmx.de", "uol.com.br": "www.uol.com.br",172 "terra.com.br": "www.terra.com.br", "sapo.pt": "www.sapo.pt", "libero.it": "www.libero.it", "douyin.com": "www.douyin.com",173 "kuaishou.com": "www.kuaishou.com", "xiaohongshu.com": "www.xiaohongshu.com", "note.com": "note.com", "cafe24.com": "www.cafe24.com",174 "hatenablog.com": "hatenablog.com", "mixi.jp": "mixi.jp", "myspace.com": "myspace.com", "livejournal.com": "www.livejournal.com",175 "webflow.com": "webflow.com", "carrd.co": "carrd.co", "linktr.ee": "linktr.ee", "about.me": "about.me", "netlify.com": "www.netlify.com",176 "vercel.com": "vercel.com", "heroku.com": "www.heroku.com", "glitch.com": "glitch.com", "readthedocs.org": "readthedocs.org",177 "gitbook.com": "www.gitbook.com", "ko-fi.com": "ko-fi.com", "foursquare.com": "foursquare.com", "slideshare.net": "www.slideshare.net"}178179180# ------------------------------------------------------------------------------------------------------------ SPARQL client181class Sparql:182 def __init__(self, *, refresh: bool = False) -> None:183 self.client = httpx.Client(headers={"User-Agent": USER_AGENT, "Accept": "application/sparql-results+json"}, timeout=TIMEOUT_S + 15,184 follow_redirects=True)185 self.last_call = 0.0186 self.refresh = refresh187 self.queries = 0188 self.cached = 0189 CACHE_DIR.mkdir(parents=True, exist_ok=True)190191 def query(self, sparql: str, *, label: str = "") -> list[dict[str, str]]:192 key = hashlib.sha256(sparql.encode("utf-8")).hexdigest()193 path = CACHE_DIR / f"{key}.json"194 if path.exists() and not self.refresh:195 self.cached += 1196 return json.loads(path.read_text(encoding="utf-8"))["bindings"]197 delay = 5.0198 last_err = ""199 gateway_timeouts = 0200 for attempt in range(1, MAX_TRIES + 1):201 wait = MIN_INTERVAL_S - (time.monotonic() - self.last_call)202 if wait > 0:203 time.sleep(wait)204 t0 = time.monotonic()205 try:206 r = self.client.get(SPARQL_URL, params={"query": sparql, "format": "json"})207 self.last_call = time.monotonic()208 self.queries += 1209 if r.status_code == 200:210 try:211 # strict=False: a handful of Wikidata literals contain raw control characters.212 payload = json.loads(r.text, strict=False)213 except json.JSONDecodeError:214 # The endpoint streams results and, on a server-side timeout, appends a Java stack trace to a *200* body215 # (which the gateway may even cache). Treat a truncated body as a timeout so callers can split or skip.216 if "TimeoutException" in r.text or "SPARQL-QUERY" in r.text:217 raise TimeoutError(f"server timeout (truncated body) {label}") from None218 last_err = "truncated/invalid JSON body"219 gateway_timeouts += 1220 if gateway_timeouts >= 2:221 raise TimeoutError(f"truncated body twice {label}") from None222 continue223 rows = [{k: v["value"] for k, v in b.items()} for b in payload["results"]["bindings"]]224 path.write_text(json.dumps({"label": label, "fetched_at": datetime.now(UTC).isoformat(), "query": sparql, "bindings": rows},225 ensure_ascii=False), encoding="utf-8")226 log.info("sparql ok %s rows=%d %.1fs", label, len(rows), self.last_call - t0)227 return rows228 last_err = f"HTTP {r.status_code}: {r.text[:160]!r}"229 if r.status_code == 504:230 # Gateway timeout = the query ran past the 60 s server limit. One retry (load varies), then let the caller split it.231 gateway_timeouts += 1232 if gateway_timeouts >= 2:233 raise TimeoutError(f"gateway timeout {label}")234 if r.status_code == 429:235 retry_after = r.headers.get("Retry-After")236 delay = max(delay, float(retry_after)) if retry_after and retry_after.isdigit() else max(delay, 30.0)237 if r.status_code == 400:238 raise RuntimeError(f"bad query {label}: {r.text[:500]}")239 if r.status_code == 500 and "TimeoutException" in r.text:240 raise TimeoutError(f"server timeout {label}")241 except (httpx.TimeoutException, httpx.TransportError) as e:242 self.last_call = time.monotonic()243 last_err = f"{type(e).__name__}: {e}"244 if isinstance(e, httpx.RemoteProtocolError):245 # "incomplete chunked read": the server cut the stream at its time limit — same as a timeout, fail fast.246 gateway_timeouts += 1247 if gateway_timeouts >= 2:248 raise TimeoutError(f"stream cut by server {label}") from None249 log.warning("sparql retry %d/%d %s (%s) sleeping %.0fs", attempt, MAX_TRIES, label, last_err, delay)250 time.sleep(delay)251 delay = min(delay * 2, 120.0)252 raise RuntimeError(f"sparql failed {label}: {last_err}")253254255def values_clause(var: str, qids: list[str]) -> str:256 return f"VALUES ?{var} {{ {' '.join('wd:' + q for q in qids)} }}"257258259def class_values() -> str:260 return values_clause("cls", list(CLASSES))261262263# Truthy website (best rank, deprecated excluded). Dissolution is fetched as an OPTIONAL and filtered client-side: `FILTER NOT EXISTS`264# sub-selects over tens of thousands of bindings are what pushed the big class scans past the 60 s server limit.265WEBSITE_BLOCK = """266 ?item wdt:P856 ?web .267 OPTIONAL { ?item wdt:P576 ?dissolved }268 OPTIONAL { ?item wdt:P17 ?c . ?c wdt:P297 ?iso }269"""270271272def q_class_band(cls: str, lo: int, hi: int | None) -> str:273 band = f"?sl >= {lo}" + (f" && ?sl < {hi}" if hi else "")274 return f"""SELECT ?item ?sl ?web ?iso WHERE {{275 ?item wdt:P31 wd:{cls} ; wikibase:sitelinks ?sl .276 FILTER({band})277 {WEBSITE_BLOCK}278}}"""279280281def q_country_qids(isos: list[str]) -> str:282 vals = " ".join(json.dumps(x) for x in isos)283 return f"""SELECT ?iso ?country WHERE {{ VALUES ?iso {{ {vals} }} ?country wdt:P297 ?iso . FILTER NOT EXISTS {{ ?country wdt:P576 [] }} }}"""284285286def q_country_boost(iso: str, country_qid: str) -> str:287 """Country-first scan (P17 → website → class) with the optimizer pinned. Cheap for countries with few items in Wikidata; the288 caller only issues it for countries still short of their minimum after the class stage and tolerates a timeout."""289 return f"""SELECT ?item ?sl ?web ?iso WHERE {{290 hint:Query hint:optimizer "None" .291 ?item wdt:P17 wd:{country_qid} .292 ?item wdt:P856 ?web .293 ?item wdt:P31 ?cls .294 {class_values()}295 ?item wikibase:sitelinks ?sl . FILTER(?sl >= {BOOST_MIN_SITELINKS})296 OPTIONAL {{ ?item wdt:P576 ?dissolved }}297 BIND("{iso}" AS ?iso)298}}"""299300301def q_industry_boost(labels: list[str]) -> str:302 vals = " ".join(json.dumps(x) + "@en" for x in labels)303 return f"""SELECT ?item ?sl ?web ?iso ?indLabel WHERE {{304 hint:Query hint:optimizer "None" .305 VALUES ?indLabel {{ {vals} }}306 ?ind rdfs:label ?indLabel .307 ?item wdt:P452 ?ind .308 ?item wikibase:sitelinks ?sl .309 FILTER(?sl >= {BOOST_MIN_SITELINKS})310 ?item wdt:P31 ?cls .311 {class_values()}312 {WEBSITE_BLOCK}313}} ORDER BY DESC(?sl) LIMIT {INDUSTRY_BOOST_LIMIT}"""314315316def q_dissolved(qids: list[str]) -> str:317 """Only items that have a dissolution / abolition date (P576) come back."""318 return f"""SELECT ?item ?dissolved WHERE {{ {values_clause("item", qids)} ?item wdt:P576 ?dissolved . }}"""319320321def q_labels(qids: list[str]) -> str:322 return f"""SELECT ?item ?itemLabel ?itemDescription ?itemAltLabel WHERE {{323 {values_clause("item", qids)}324 SERVICE wikibase:label {{ bd:serviceParam wikibase:language "en". }}325}}"""326327328# Plain (non-aggregated) detail queries, reduced client-side: a GROUP BY with a dozen SAMPLE() aggregates over nested OPTIONALs makes329# Blazegraph throw StackOverflowError.330def q_misc(qids: list[str]) -> str:331 return f"""SELECT ?item ?inception ?coord ?lei ?cik ?logo WHERE {{332 {values_clause("item", qids)}333 OPTIONAL {{ ?item wdt:P571 ?inception }}334 OPTIONAL {{ ?item wdt:P625 ?coord }}335 OPTIONAL {{ ?item wdt:P1278 ?lei }}336 OPTIONAL {{ ?item wdt:P5531 ?cik }}337 OPTIONAL {{ ?item wdt:P154 ?logo }}338}}"""339340341def q_hq_parent(qids: list[str]) -> str:342 return f"""SELECT ?item ?hq ?hqEnd ?hqLabel ?hqcoord ?hqRegionLabel ?hqiso ?parent ?parentEnd ?parentLabel WHERE {{343 {values_clause("item", qids)}344 OPTIONAL {{345 ?item p:P159 ?hqs . ?hqs ps:P159 ?hq .346 OPTIONAL {{ ?hqs pq:P582 ?hqEnd }}347 OPTIONAL {{ ?hq rdfs:label ?hqLabel FILTER(LANG(?hqLabel) = "en") }}348 OPTIONAL {{ ?hq wdt:P625 ?hqcoord }}349 OPTIONAL {{ ?hq wdt:P131 ?hqRegion . ?hqRegion rdfs:label ?hqRegionLabel FILTER(LANG(?hqRegionLabel) = "en") }}350 OPTIONAL {{ ?hq wdt:P17 ?hqc . ?hqc wdt:P297 ?hqiso }}351 }}352 OPTIONAL {{353 ?item p:P749 ?ps . ?ps ps:P749 ?parent .354 OPTIONAL {{ ?ps pq:P582 ?parentEnd }}355 OPTIONAL {{ ?parent rdfs:label ?parentLabel FILTER(LANG(?parentLabel) = "en") }}356 }}357}}"""358359360def q_names_industries(qids: list[str]) -> str:361 return f"""SELECT ?item ?legal ?indLabel WHERE {{362 {values_clause("item", qids)}363 OPTIONAL {{ ?item wdt:P1448 ?legal }}364 OPTIONAL {{ ?item wdt:P452 ?ind . ?ind rdfs:label ?indLabel FILTER(LANG(?indLabel) = "en") }}365}}"""366367368def q_tickers_employees(qids: list[str]) -> str:369 return f"""SELECT ?item ?ticker ?exchangeLabel ?ticker2 ?employees ?empDate WHERE {{370 {values_clause("item", qids)}371 OPTIONAL {{372 ?item p:P414 ?exs . ?exs ps:P414 ?exchange . FILTER NOT EXISTS {{ ?exs pq:P582 [] }}373 OPTIONAL {{ ?exs pq:P249 ?ticker }}374 OPTIONAL {{ ?exchange rdfs:label ?exchangeLabel FILTER(LANG(?exchangeLabel) = "en") }}375 }}376 OPTIONAL {{ ?item wdt:P249 ?ticker2 }}377 OPTIONAL {{ ?item p:P1128 ?es . ?es ps:P1128 ?employees . OPTIONAL {{ ?es pq:P585 ?empDate }} }}378}}"""379380381# ------------------------------------------------------------------------------------------------------------ helpers382def qid_of(uri: str) -> str:383 return uri.rsplit("/", 1)[-1]384385386def load_countries() -> dict[str, dict[str, str]]:387 with COUNTRIES_CSV.open(encoding="utf-8") as f:388 return {r["code"]: r for r in csv.DictReader(f)}389390391def normalise_website(url: str) -> tuple[str, str] | None:392 """→ (website `https://host`, registrable domain) or None when unusable/generic."""393 url = (url or "").strip()394 if not url:395 return None396 if "://" not in url:397 url = "https://" + url398 try:399 p = urlparse(url)400 except ValueError:401 return None402 host = (p.hostname or "").lower().strip().rstrip(".")403 if not host or "." not in host or re.fullmatch(r"[\d.]+", host) or ":" in host:404 return None405 if p.scheme not in ("http", "https"):406 return None407 try:408 host.encode("idna")409 except UnicodeError:410 return None411 dom = registrable_domain(host)412 if not dom or "." not in dom:413 return None414 path = (p.path or "/").rstrip("/")415 if dom in PLATFORM_ROOTS:416 canonical_host = PLATFORM_ROOTS[dom]417 if host not in (canonical_host, dom, "www." + dom) or path:418 return None419 return f"https://{canonical_host}", dom420 if dom in GENERIC_DOMAINS or host in GENERIC_DOMAINS:421 return None422 if host.endswith((".blogspot.com", ".wordpress.com", ".github.io", ".wixsite.com", ".weebly.com", ".tumblr.com")):423 return None424 return f"https://{host}", dom425426427def parse_point(value: str | None) -> tuple[float, float] | None:428 if not value or not value.startswith("Point("):429 return None430 try:431 lon, lat = value[6:-1].split()432 return round(float(lat), 5), round(float(lon), 5)433 except ValueError:434 return None435436437def parse_year(value: str | None) -> int | None:438 if not value:439 return None440 m = re.match(r"^(-?\d{1,4})-", value)441 if not m:442 return None443 y = int(m.group(1))444 return y if 1000 <= y <= datetime.now(UTC).year else None445446447def parse_int(value: str | None) -> int | None:448 try:449 return int(float(value)) if value not in (None, "") else None450 except ValueError:451 return None452453454def commons_url(filename: str | None) -> str | None:455 """P154 arrives as an already-encoded Special:FilePath URL (or a bare file name): decode, then encode once."""456 if not filename:457 return None458 name = unquote(filename.rsplit("/", 1)[-1]).replace(" ", "_")459 return f"https://commons.wikimedia.org/wiki/Special:FilePath/{quote(name)}"460461462# Home exchanges per country: preferred listing when a company has several tickers (Wikidata order is arbitrary).463HOME_EXCHANGES: dict[str, tuple[str, ...]] = {464 "US": ("NASDAQ", "Nasdaq", "New York Stock Exchange", "NYSE"), "GB": ("London Stock Exchange",), "JP": ("Tokyo Stock Exchange",),465 "DE": ("Frankfurt Stock Exchange", "Xetra", "Börse"), "FR": ("Euronext Paris", "Paris"), "CA": ("Toronto Stock Exchange", "TSX Venture"),466 "KR": ("Korea Exchange", "KOSDAQ"), "IN": ("National Stock Exchange of India", "Bombay Stock Exchange"), "AU": ("Australian Securities Exchange",),467 "CN": ("Shanghai Stock Exchange", "Shenzhen Stock Exchange"), "HK": ("Hong Kong Stock Exchange",), "TW": ("Taiwan Stock Exchange", "Taipei"),468 "CH": ("SIX Swiss Exchange",), "NL": ("Euronext Amsterdam",), "BE": ("Euronext Brussels",), "SE": ("Nasdaq Stockholm", "Stockholm"),469 "FI": ("Nasdaq Helsinki", "Helsinki"), "DK": ("Nasdaq Copenhagen", "Copenhagen"), "NO": ("Oslo",), "IT": ("Borsa Italiana", "Euronext Milan"),470 "ES": ("Bolsa de Madrid", "Madrid"), "PT": ("Euronext Lisbon",), "BR": ("B3", "São Paulo"), "MX": ("Mexican Stock Exchange", "Bolsa Mexicana"),471 "SG": ("Singapore Exchange",), "ID": ("Indonesia Stock Exchange",), "MY": ("Bursa Malaysia",), "TH": ("Stock Exchange of Thailand",),472 "ZA": ("Johannesburg Stock Exchange", "JSE"), "SA": ("Saudi Stock Exchange", "Tadawul"), "AE": ("Dubai Financial Market", "Abu Dhabi"),473 "IL": ("Tel Aviv Stock Exchange",), "RU": ("Moscow Exchange",), "PL": ("Warsaw Stock Exchange",), "AT": ("Vienna Stock Exchange", "Wiener Börse"),474 "IE": ("Euronext Dublin", "Irish Stock Exchange"), "NZ": ("New Zealand Exchange", "NZX"), "AR": ("Buenos Aires",), "CL": ("Santiago",),475 "TR": ("Borsa Istanbul", "Istanbul"), "EG": ("Egyptian Exchange",), "NG": ("Nigerian", "Nigeria"), "PH": ("Philippine Stock Exchange",),476 "VN": ("Ho Chi Minh", "Hanoi"), "PK": ("Pakistan Stock Exchange",), "GR": ("Athens Stock Exchange",), "CZ": ("Prague Stock Exchange",),477 "HU": ("Budapest Stock Exchange",), "LU": ("Luxembourg Stock Exchange",), "KZ": ("Kazakhstan",), "QA": ("Qatar Stock Exchange",),478 "KW": ("Boursa Kuwait", "Kuwait"), "CO": ("Colombia",), "PE": ("Lima",),479}480481482def pick_ticker(tickers: list[list[str | None]], country: str | None) -> tuple[str | None, str | None]:483 """(ticker, exchange): home exchange of the company's country first, then any listing with an alphabetic ticker, then the first."""484 if not tickers:485 return None, None486 home = HOME_EXCHANGES.get(country or "", ())487 for t, ex in tickers:488 if ex and any(h.lower() in ex.lower() for h in home):489 return t, ex490 for t, ex in tickers:491 if t and not t.isdigit():492 return t, ex493 return tickers[0][0], tickers[0][1]494495496DOMAIN_LIKE_RE = re.compile(r"^(https?://)?[\w-]+(\.[\w-]+)+(/.*)?$")497JUNK_LABEL_RE = re.compile(r"classification|category|list of|wikimedia", re.IGNORECASE)498499500def dump_json(path: Path, data: Any) -> None:501 path.parent.mkdir(parents=True, exist_ok=True)502 path.write_text(json.dumps(data, ensure_ascii=False), encoding="utf-8")503504505def load_json(path: Path) -> Any:506 return json.loads(path.read_text(encoding="utf-8"))507508509# ------------------------------------------------------------------------------------------------------------ stage: candidates510def stage_candidates(sp: Sparql, *, refresh: bool = False) -> dict[str, dict[str, Any]]:511 out_path = DATA_DIR / "candidates.json"512 cands: dict[str, dict[str, Any]] = {}513 dissolved: set[str] = set()514515 def add(row: dict[str, str], *, cls: str | None = None, boost_country: str | None = None, boost_industry: str | None = None) -> None:516 if row.get("dissolved"):517 dissolved.add(qid_of(row["item"]))518 return519 qid = qid_of(row["item"])520 c = cands.setdefault(qid, {"sitelinks": 0, "websites": [], "isos": [], "classes": [], "boost_countries": [], "boost_industries": []})521 c["sitelinks"] = max(c["sitelinks"], parse_int(row.get("sl")) or 0)522 if row.get("web") and row["web"] not in c["websites"]:523 c["websites"].append(row["web"])524 iso = (row.get("iso") or "").upper()525 if iso and iso not in c["isos"]:526 c["isos"].append(iso)527 if cls and cls not in c["classes"]:528 c["classes"].append(cls)529 if boost_country and boost_country not in c["boost_countries"]:530 c["boost_countries"].append(boost_country)531 if boost_industry and boost_industry not in c["boost_industries"]:532 c["boost_industries"].append(boost_industry)533534 def run_band(cls: str, lo: int, hi: int | None) -> None:535 label = f"class {cls} {CLASSES[cls]} sl[{lo},{hi or '∞'})"536 try:537 rows = sp.query(q_class_band(cls, lo, hi), label=label)538 except TimeoutError:539 if hi is None:540 hi = 400541 if hi - lo <= 1:542 raise543 mid = (lo + hi) // 2544 log.warning("splitting band %s → [%d,%d) [%d,%d)", label, lo, mid, mid, hi)545 run_band(cls, lo, mid)546 run_band(cls, mid, hi)547 return548 for r in rows:549 add(r, cls=cls)550551 for cls in CLASSES:552 if cls in BANDED_CLASSES:553 for lo, hi in SITELINK_BANDS:554 run_band(cls, lo, hi)555 else:556 run_band(cls, MIN_SITELINKS, None)557 log.info("candidates so far: %d", len(cands))558 iso_to_qid = {r["iso"]: qid_of(r["country"]) for r in sp.query(q_country_qids(list(COUNTRY_MINIMUMS)), label="country qids")}559 per_country: Counter[str] = Counter(c["isos"][0] for c in cands.values() if c["isos"])560 for iso, minimum in COUNTRY_MINIMUMS.items():561 if per_country[iso] >= minimum * BOOST_MARGIN or iso not in iso_to_qid:562 log.info("country %s: %d candidates (min %d) — no boost", iso, per_country[iso], minimum)563 continue564 try:565 rows = sp.query(q_country_boost(iso, iso_to_qid[iso]), label=f"country boost {iso}")566 except (RuntimeError, TimeoutError) as e:567 log.warning("country boost %s failed (%s) — known gap, see README", iso, e)568 continue569 for r in rows:570 add(r, boost_country=iso)571 for slug, labels in INDUSTRY_BOOST_LABELS.items():572 for r in sp.query(q_industry_boost(labels), label=f"industry boost {slug}"):573 add(r, boost_industry=slug)574 for qid in dissolved:575 cands.pop(qid, None)576 dump_json(out_path, cands)577 log.info("candidates: %d (%d dissolved dropped) → %s", len(cands), len(dissolved), out_path)578 return cands579580581# ------------------------------------------------------------------------------------------------------------ stage: dissolved582def stage_dissolved(sp: Sparql, *, batch: int = 300) -> set[str]:583 """P576 (dissolved/abolished) check over every candidate — the candidate queries cannot afford a NOT EXISTS filter."""584 cands = load_json(DATA_DIR / "candidates.json")585 qids = sorted(cands, key=lambda q: int(q[1:]))586 dissolved: dict[str, str] = {}587 for i in range(0, len(qids), batch):588 chunk = qids[i:i + batch]589 for r in sp.query(q_dissolved(chunk), label=f"dissolved {i + len(chunk)}/{len(qids)}"):590 dissolved.setdefault(qid_of(r["item"]), r.get("dissolved") or "")591 dump_json(DATA_DIR / "dissolved.json", dissolved)592 log.info("dissolved: %d of %d candidates → %s", len(dissolved), len(qids), DATA_DIR / "dissolved.json")593 return set(dissolved)594595596# ------------------------------------------------------------------------------------------------------------ stage: select597def prefilter(cands: dict[str, dict[str, Any]], countries: dict[str, dict[str, str]]) -> tuple[list[dict[str, Any]], dict[str, int]]:598 """Normalise websites, pick one country, drop generic hosts and duplicate registrable domains (highest sitelinks wins)."""599 stats: Counter[str] = Counter()600 rows: list[dict[str, Any]] = []601 for qid, c in cands.items():602 picked = None603 for w in c["websites"]:604 picked = normalise_website(w)605 if picked:606 break607 if not picked:608 stats["dropped_generic_or_invalid_website"] += 1609 continue610 website, dom = picked611 isos = [i for i in c["isos"] if i in countries]612 country = isos[0] if isos else None613 rows.append({"wikidata_id": qid, "sitelinks": c["sitelinks"], "website": website, "canonical_domain": dom, "country": country,614 "country_candidates": isos, "classes": c["classes"], "boost_industries": c["boost_industries"]})615 rows.sort(key=lambda r: (-r["sitelinks"], int(r["wikidata_id"][1:])))616 by_domain: dict[str, dict[str, Any]] = {}617 for r in rows:618 keeper = by_domain.get(r["canonical_domain"])619 if keeper is None:620 by_domain[r["canonical_domain"]] = r621 else:622 keeper.setdefault("domain_conflicts", []).append(r["wikidata_id"])623 stats["dropped_duplicate_domain"] += 1624 kept = list(by_domain.values())625 stats["kept"] = len(kept)626 return kept, dict(stats)627628629def select_diversified(rows: list[dict[str, Any]], *, target: int, countries: dict[str, dict[str, str]]) -> list[dict[str, Any]]:630 """Sitelinks-ordered selection with country caps and minimum coverage. Rows must be sorted by sitelinks desc."""631 caps: dict[str | None, int] = defaultdict(lambda: int(target * OTHER_CAP_SHARE))632 caps["US"] = int(target * US_CAP_SHARE)633 caps[None] = int(target * UNKNOWN_COUNTRY_SHARE)634 chosen: dict[str, dict[str, Any]] = {}635 per_country: Counter[str | None] = Counter()636 per_class: Counter[str] = Counter()637638 def take(r: dict[str, Any]) -> None:639 chosen[r["wikidata_id"]] = r640 per_country[r["country"]] += 1641 for c in r.get("classes", []):642 per_class[c] += 1643644 def class_capped(r: dict[str, Any]) -> bool:645 return any(per_class[c] >= int(target * CLASS_CAPS[c]) for c in r.get("classes", []) if c in CLASS_CAPS)646647 by_country: dict[str | None, list[dict[str, Any]]] = defaultdict(list)648 for r in rows:649 by_country[r["country"]].append(r)650 for iso, minimum in COUNTRY_MINIMUMS.items():651 for r in by_country.get(iso, [])[:minimum]:652 take(r)653 for r in rows:654 if len(chosen) >= target:655 break656 if r["wikidata_id"] in chosen or r["sitelinks"] < MIN_SITELINKS:657 continue658 if per_country[r["country"]] >= caps[r["country"]] or class_capped(r):659 continue660 take(r)661 return list(chosen.values())662663664def stage_select(*, target: int) -> list[dict[str, Any]]:665 countries = load_countries()666 cands = load_json(DATA_DIR / "candidates.json")667 dissolved_path = DATA_DIR / "dissolved.json"668 if dissolved_path.exists():669 dissolved = set(load_json(dissolved_path))670 cands = {q: c for q, c in cands.items() if q not in dissolved}671 log.info("select: %d dissolved candidates excluded", len(dissolved))672 else:673 log.warning("select: no dissolved.json — run the `dissolved` stage first to exclude defunct companies")674 kept, stats = prefilter(cands, countries)675 log.info("prefilter: %s", stats)676 selected = select_diversified(kept, target=target, countries=countries)677 # Pool for the industry guarantee: best boost candidates per industry, fetched in the details stage too (≤ INDUSTRY_RESERVE each).678 chosen_ids = {r["wikidata_id"] for r in selected}679 reserve: list[dict[str, Any]] = []680 per_ind: Counter[str] = Counter()681 for r in kept:682 if r["wikidata_id"] in chosen_ids:683 continue684 for slug in r["boost_industries"]:685 if per_ind[slug] < INDUSTRY_RESERVE:686 per_ind[slug] += 1687 reserve.append(r)688 break689 dump_json(DATA_DIR / "selected.json", {"selected": selected, "reserve": reserve, "stats": stats, "target": target})690 log.info("selected %d (+%d reserve for industry minimums) → %s", len(selected), len(reserve), DATA_DIR / "selected.json")691 return selected692693694# ------------------------------------------------------------------------------------------------------------ stage: details695def stage_details(sp: Sparql, *, batch: int = 100) -> dict[str, dict[str, Any]]:696 sel = load_json(DATA_DIR / "selected.json")697 qids = [r["wikidata_id"] for r in sel["selected"]] + [r["wikidata_id"] for r in sel["reserve"]]698 out_path = DATA_DIR / "details.json"699 details: dict[str, dict[str, Any]] = load_json(out_path) if out_path.exists() else {}700 todo = [q for q in qids if q not in details]701 log.info("details: %d to fetch (%d cached)", len(todo), len(qids) - len(todo))702 for i in range(0, len(todo), batch):703 chunk = todo[i:i + batch]704 tag = f"details {i + len(chunk)}/{len(todo)}"705 d: dict[str, dict[str, Any]] = {q: {"legal_names": [], "industry_labels": [], "tickers": [], "employees_obs": []} for q in chunk}706 for r in sp.query(q_labels(chunk), label=f"{tag} labels"):707 q = qid_of(r["item"])708 d[q]["label"] = r.get("itemLabel")709 d[q]["description"] = r.get("itemDescription")710 d[q]["alt_labels"] = [a.strip() for a in (r.get("itemAltLabel") or "").split(",") if a.strip()]711 for r in sp.query(q_misc(chunk), label=f"{tag} misc"):712 m = d[qid_of(r["item"])]713 for key in ("inception", "coord", "lei", "cik", "logo"):714 if r.get(key) and (not m.get(key) or (key == "inception" and r[key] < m[key])):715 m[key] = r[key]716 grouped: dict[str, list[dict[str, str]]] = defaultdict(list)717 for r in sp.query(q_hq_parent(chunk), label=f"{tag} hq/parent"):718 grouped[qid_of(r["item"])].append(r)719 for q, rs in grouped.items():720 hqs = [r for r in rs if r.get("hq")]721 current = [r for r in hqs if not r.get("hqEnd")] or hqs722 if current:723 r = current[0]724 d[q].update({"hq": qid_of(r["hq"]), "hq_label": r.get("hqLabel"), "hq_coord": r.get("hqcoord"),725 "hq_region": r.get("hqRegionLabel"), "hq_iso": r.get("hqiso")})726 parents = [r for r in rs if r.get("parent") and not r.get("parentEnd")]727 if parents:728 d[q].update({"parent": qid_of(parents[0]["parent"]), "parent_label": parents[0].get("parentLabel")})729 for r in sp.query(q_names_industries(chunk), label=f"{tag} names/industries"):730 q = qid_of(r["item"])731 if r.get("legal") and r["legal"] not in d[q]["legal_names"]:732 d[q]["legal_names"].append(r["legal"])733 if r.get("indLabel") and r["indLabel"] not in d[q]["industry_labels"]:734 d[q]["industry_labels"].append(r["indLabel"])735 for r in sp.query(q_tickers_employees(chunk), label=f"{tag} tickers/employees"):736 q = qid_of(r["item"])737 t = r.get("ticker") or r.get("ticker2")738 if t:739 pair = [t, r.get("exchangeLabel")]740 if pair not in d[q]["tickers"]:741 d[q]["tickers"].append(pair)742 if r.get("employees"):743 obs = [r["employees"], r.get("empDate")]744 if obs not in d[q]["employees_obs"]:745 d[q]["employees_obs"].append(obs)746 details.update(d)747 dump_json(out_path, details)748 log.info("details: %d entries → %s", len(details), out_path)749 return details750751752# ------------------------------------------------------------------------------------------------------------ stage: assemble753def importance_score(sitelinks: int, employees: int | None, public: bool, ticker: str | None) -> float:754 s_sl = min(1.0, math.log1p(max(sitelinks, 0)) / math.log1p(300))755 s_emp = min(1.0, math.log10((employees or 0) + 1) / 6) if employees else 0.0756 score = 0.6 * s_sl + 0.2 * s_emp + 0.1 * (1 if public else 0) + 0.1 * (1 if ticker else 0)757 return round(min(1.0, max(0.02, score)), 4)758759760def assign_tiers(rows: list[dict[str, Any]]) -> None:761 rows.sort(key=lambda r: (-r["importance"], -r["sitelinks"], r["wikidata_id"]))762 for i, r in enumerate(rows):763 r["tier"] = 1 if i < 150 else 2 if i < 950 else 3 if i < 3450 else 4764765766def build_row(sel: dict[str, Any], d: dict[str, Any], harvested_at: str) -> dict[str, Any]:767 label = d.get("label") or ""768 if not label or re.fullmatch(r"Q\d+", label):769 label = (d.get("legal_names") or [sel["canonical_domain"]])[0]770 legal = next((x for x in d.get("legal_names", []) if re.search(r"[A-Za-z]", x)), None) or (d.get("legal_names") or [None])[0]771 aliases = [a for a in d.get("alt_labels", []) if a and a not in (label, legal) and len(a) <= 80 and not DOMAIN_LIKE_RE.match(a)][:8]772 emp = None773 obs = d.get("employees_obs") or []774 if obs:775 obs = sorted(obs, key=lambda o: (o[1] or ""), reverse=True)776 emp = parse_int(obs[0][0])777 coord = parse_point(d.get("coord")) or parse_point(d.get("hq_coord"))778 country = sel["country"]779 if d.get("hq_iso") and d["hq_iso"] in (sel.get("country_candidates") or []):780 country = d["hq_iso"]781 ticker, exchange = pick_ticker(d.get("tickers") or [], country)782 public = "Q891723" in sel.get("classes", []) or bool(ticker) or bool(exchange)783 industry_labels = [x for x in (d.get("industry_labels") or []) if not JUNK_LABEL_RE.search(x)]784 slugs = map_industry(industry_labels)785 if not slugs and sel.get("boost_industries"):786 slugs = [sel["boost_industries"][0]]787 if not slugs:788 slugs = map_industry([label, d.get("description") or ""] + [CLASSES.get(c, "") for c in sel.get("classes", [])], limit=2)789 return {790 "wikidata_id": sel["wikidata_id"], "display_name": label.strip(), "legal_name": legal, "aliases": aliases, "website": sel["website"],791 "canonical_domain": sel["canonical_domain"], "country": country, "hq_city": d.get("hq_label"), "hq_region": d.get("hq_region"),792 "lat": coord[0] if coord else None, "lon": coord[1] if coord else None, "industries": slugs, "industry_labels": industry_labels[:8],793 "founded_year": parse_year(d.get("inception")), "employees": emp, "public_company": public, "ticker": ticker, "exchange": exchange,794 "lei": d.get("lei"), "sec_cik": str(d["cik"]).lstrip("0") or None if d.get("cik") else None,795 "parent": {"wikidata_id": d["parent"], "name": d.get("parent_label")} if d.get("parent") else None,796 "logo_url": commons_url(d.get("logo")), "description": (d.get("description") or None), "sitelinks": sel["sitelinks"],797 "importance": importance_score(sel["sitelinks"], emp, public, ticker), "tier": 4, "source": "wikidata", "harvested_at": harvested_at,798 "domain_conflicts": sel.get("domain_conflicts") or [],799 }800801802def stage_assemble() -> list[dict[str, Any]]:803 countries = load_countries()804 sel = load_json(DATA_DIR / "selected.json")805 details = load_json(DATA_DIR / "details.json")806 harvested_at = datetime.now(UTC).replace(microsecond=0).isoformat()807 selected = [r for r in sel["selected"] if r["wikidata_id"] in details]808 reserve = [r for r in sel["reserve"] if r["wikidata_id"] in details]809 rows = [build_row(s, details[s["wikidata_id"]], harvested_at) for s in selected]810 # Industry minimums: top up from the reserve where a top-level industry is below the floor.811 counts: Counter[str] = Counter()812 for r in rows:813 for top in {top_level_of(s) for s in r["industries"]}:814 counts[top] += 1815 chosen = {r["wikidata_id"] for r in rows}816 reserve_rows = [build_row(s, details[s["wikidata_id"]], harvested_at) for s in reserve]817 reserve_rows.sort(key=lambda r: -r["sitelinks"])818 for top in top_level_slugs():819 for rr in reserve_rows:820 if counts[top] >= INDUSTRY_MINIMUM:821 break822 if rr["wikidata_id"] in chosen or top not in {top_level_of(s) for s in rr["industries"]}:823 continue824 rows.append(rr)825 chosen.add(rr["wikidata_id"])826 for t in {top_level_of(s) for s in rr["industries"]}:827 counts[t] += 1828 # Parent/child sharing a domain: parent wins (regardless of sitelinks); duplicate domains (should not happen after prefilter) drop the lower.829 by_domain: dict[str, dict[str, Any]] = {}830 for r in sorted(rows, key=lambda r: -r["sitelinks"]):831 by_domain.setdefault(r["canonical_domain"], r)832 ids = {r["wikidata_id"] for r in by_domain.values()}833 dropped: list[dict[str, Any]] = []834 for r in list(by_domain.values()):835 p = r.get("parent")836 if p and p["wikidata_id"] in ids and p["wikidata_id"] != r["wikidata_id"]:837 parent_row = next((x for x in by_domain.values() if x["wikidata_id"] == p["wikidata_id"]), None)838 if parent_row and parent_row["canonical_domain"] == r["canonical_domain"]:839 dropped.append({"wikidata_id": r["wikidata_id"], "reason": "shares_parent_domain", "parent": p["wikidata_id"]})840 for r in rows:841 if r["wikidata_id"] not in ids:842 dropped.append({"wikidata_id": r["wikidata_id"], "reason": "duplicate_domain", "domain": r["canonical_domain"]})843 drop_ids = {x["wikidata_id"] for x in dropped}844 final = [r for r in by_domain.values() if r["wikidata_id"] not in drop_ids]845 for r in final:846 r["notes"] = [{"related_domain_conflict": q} for q in r.pop("domain_conflicts", [])] or []847 if not r["notes"]:848 r.pop("notes")849 assign_tiers(final)850 # Write per region.851 OUT_DIR.mkdir(parents=True, exist_ok=True)852 for old in OUT_DIR.glob("wikidata-*.ndjson"):853 old.unlink()854 by_region: dict[str, list[dict[str, Any]]] = defaultdict(list)855 for r in final:856 region = (countries.get(r["country"] or "", {}).get("region") or "other").lower().replace(" ", "-")857 by_region[region].append(r)858 for region, items in sorted(by_region.items()):859 items.sort(key=lambda r: (r["tier"], -r["importance"], r["wikidata_id"]))860 with (OUT_DIR / f"wikidata-{region}.ndjson").open("w", encoding="utf-8") as f:861 for r in items:862 f.write(json.dumps(r, ensure_ascii=False) + "\n")863 dump_json(DATA_DIR / "dropped.json", dropped)864 write_readme(final, countries)865 log.info("assembled %d companies into %d region files (%d dropped) → %s", len(final), len(by_region), len(dropped), OUT_DIR)866 return final867868869def write_readme(rows: list[dict[str, Any]], countries: dict[str, dict[str, str]]) -> None:870 names = {i.slug: i.name for i in load_industries()}871 by_country = Counter(r["country"] or "??" for r in rows)872 by_tier = Counter(r["tier"] for r in rows)873 by_top: Counter[str] = Counter()874 by_ind: Counter[str] = Counter()875 for r in rows:876 for s in r["industries"]:877 by_ind[s] += 1878 for t in {top_level_of(s) for s in r["industries"]}:879 by_top[t] += 1880 no_ind = sum(1 for r in rows if not r["industries"])881 public = sum(1 for r in rows if r["public_company"])882 region = Counter((countries.get(r["country"] or "", {}).get("region") or "other") for r in rows)883 generated = rows[0]["harvested_at"] if rows else ""884 intro = (f"Generated by `scripts/seed_wikidata.py` on {generated}. **{len(rows)} companies**, {public} public, {no_ind} without an "885 f"industry mapping, {len(by_country)} countries.")886 files_note = ("Files: one NDJSON per UN region (`wikidata-<region>.ndjson`), one JSON object per line — see `docs/SEEDS.md` for the "887 "schema, the diversification rules and how to add companies.")888 lines = [889 "# Seed registry — Wikidata harvest", "", intro, "", files_note, "",890 "## Tiers", "", "| Tier | Companies |", "|---|---|",891 *[f"| {t} ({ {1: 'global', 2: 'major', 3: 'notable', 4: 'long tail'}[t] }) | {by_tier[t]} |" for t in sorted(by_tier)], "",892 "## Regions", "", "| Region | Companies |", "|---|---|", *[f"| {k} | {v} |" for k, v in region.most_common()], "",893 "## Countries", "", "| Code | Country | Companies | Share |", "|---|---|---|---|",894 *[f"| {c} | {countries.get(c, {}).get('name', 'unknown')} | {n} | {100 * n / len(rows):.1f} % |" for c, n in by_country.most_common()], "",895 "## Top-level industries (a company counts once per top-level sector)", "", "| Industry | Companies |", "|---|---|",896 *[f"| {names.get(k, k)} | {v} |" for k, v in by_top.most_common()], "",897 "## All industries", "", "| Slug | Companies |", "|---|---|", *[f"| {k} | {v} |" for k, v in by_ind.most_common()], "",898 ]899 (OUT_DIR / "README.md").write_text("\n".join(lines) + "\n", encoding="utf-8")900901902# ------------------------------------------------------------------------------------------------------------ main903def main() -> int:904 ap = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)905 ap.add_argument("stage", nargs="?", default="all", choices=["candidates", "dissolved", "select", "details", "assemble", "all"])906 ap.add_argument("--target", type=int, default=8000, help="companies to select before detail fetching (default 8000)")907 ap.add_argument("--batch", type=int, default=100, help="QIDs per detail query")908 ap.add_argument("--refresh", action="store_true", help="ignore the SPARQL cache")909 ap.add_argument("-v", "--verbose", action="store_true")910 args = ap.parse_args()911 logging.basicConfig(level=logging.DEBUG if args.verbose else logging.INFO, format="%(asctime)s %(levelname)s %(message)s")912 logging.getLogger("httpx").setLevel(logging.WARNING)913 DATA_DIR.mkdir(parents=True, exist_ok=True)914 sp = Sparql(refresh=args.refresh)915 t0 = time.monotonic()916 if args.stage in ("candidates", "all"):917 stage_candidates(sp)918 if args.stage in ("dissolved", "all"):919 stage_dissolved(sp)920 if args.stage in ("select", "all"):921 stage_select(target=args.target)922 if args.stage in ("details", "all"):923 stage_details(sp, batch=args.batch)924 if args.stage in ("assemble", "all"):925 stage_assemble()926 log.info("done in %.0fs (%d live queries, %d cached)", time.monotonic() - t0, sp.queries, sp.cached)927 return 0928929930if __name__ == "__main__":931 sys.exit(main())932