# Finding other Uruguay data **Answer.** Against the 2011 census (the latest by department in the catalogue's file), Uruguay has **3.73 cattle per person**. In **Durazno** it is 16.7; in Montevideo, 0.0021. The dataset was found by meaning in the catalogue graph, fetched from the link its DCAT record gives, and joined on the department codes. Notebook: https://cow-tools.sophia-labs.com/notebooks/finding-other-data/finding-other-data.ipynb · Page: https://cow-tools.sophia-labs.com/notebooks/finding-other-data/index.html · Data: Uruguay MGAP/SNIG DICOSE 2024–2025 (graph `uruguay-cows` at https://cow-tools.sophia-labs.com/mcp). ## The notebook cow-tools started with cows, but the point is the catalogue. Uruguay publishes its open data on `catalogodatos.gub.uy`, a CKAN site. The demo harvests that catalogue into the graph `uruguay-catalogue` as DCAT: every dataset, its publisher, its files and their download links. An agent can find a dataset **by meaning** (semantic search) and then read exactly where to fetch it (SPARQL over DCAT). The question for this notebook: *how many cattle are there per person, in each department?* We have the cattle. We need people, by department, and we will let the catalogue find them. ```python # Setup. import hashlib import io import unicodedata import urllib.request import altair as alt import pandas as pd import cowtools_client as cw import cowtools_charts as cc size = cw.sparql(""" SELECT (COUNT(DISTINCT ?d) AS ?datasets) (COUNT(DISTINCT ?p) AS ?publishers) WHERE { GRAPH ?g { ?d a dcat:Dataset . OPTIONAL { ?d dct:publisher ?p } } } """, graph=cw.CATALOGUE) print(f"catalogue graph {cw.CATALOGUE}: {size.iat[0, 0]:,} datasets from {size.iat[0, 1]:,} publishers") ``` ```text catalogue graph uruguay-catalogue: 2,718 datasets from 70 publishers ``` ## 1. Ask the catalogue, by meaning A plain English question. `semantic_search` ranks the catalogue's *cards* (one paragraph per dataset, grouped by publisher, each Spanish title with an English gloss) by meaning, not by keyword; each paragraph ends with the dataset's IRI in the DCAT graph, so a hit leads straight to the record. ```python import re hits = cw.tool("semantic_search", graphId=cw.CATALOGUE, query="how many people live in each department, from the population censuses", limit=10) rows = (hits.get("hits") or hits.get("results") or []) if isinstance(hits, dict) else hits def strings(v): if isinstance(v, str): yield v elif isinstance(v, dict): for x in v.values(): yield from strings(x) elif isinstance(v, list): for x in v: yield from strings(x) IRI = re.compile(r"https://catalogodatos\.gub\.uy/dataset/[0-9a-f-]{36}") ranked = [] for r in rows: for iri in IRI.findall(" ".join(strings(r))): if iri not in ranked: ranked.append(iri) print(f"{len(rows)} hits, {len(ranked)} distinct datasets named in them") ``` ```text 10 hits, 10 distinct datasets named in them ``` ## 2. Read the datasets' metadata (DCAT) For the datasets the search surfaced, the DCAT record says what they are, who published them, under what licence, and where their files are. ```python # The query: title, publisher and CSV files of the datasets the search named. (VALUES goes inside the # GRAPH block: outside it, the planner tries every dataset in every graph first and runs out of time.) values = " ".join(f"<{i}>" for i in ranked[:10]) dcat = cw.sparql(f""" SELECT ?dataset ?name ?title ?publisher ?license ?url WHERE {{ GRAPH ?g {{ VALUES ?dataset {{ {values} }} ?dataset a dcat:Dataset ; dct:title ?title . OPTIONAL {{ ?dataset ?name }} OPTIONAL {{ ?dataset dct:publisher ?p . ?p ?publisher }} OPTIONAL {{ ?dataset dct:license ?license }} OPTIONAL {{ ?dataset dcat:distribution ?dist . ?dist dcat:downloadURL ?url }} }} }} """, graph=cw.CATALOGUE) dcat["rank"] = dcat["dataset"].map(ranked.index) dcat = dcat.sort_values("rank") cc.show_table(dcat.drop_duplicates("dataset")[["rank", "title", "publisher"]], name="search-hits") ``` | rank | title | publisher | |-------:|:-------------------------------------------------------------------------------------------------------------------|:------------| | 0 | Cantidad de personas según departamento por sexo. Censos 1963-2011 | MIDES | | 1 | Cantidad de personas que residen en asentamientos irregulares según departamento. Total país | MIDES | | 2 | Cantidad de personas según departamento. Censos 1963 a 2011 | MIDES | | 3 | Cantidad de personas según departamento por tramos de edad. Censo 2011 | MIDES | | 4 | Cantidad de personas según tramos de edad por departamento. Total país | MIDES | | 5 | Cantidad de personas según sexo y departamento por tramos de edad . Censo 2011 | MIDES | | 6 | Cantidad de personas mayores según departamento. Total país | MIDES | | 7 | Cantidad de personas mayores según sexo por departamento. Total país | MIDES | | 8 | Distribución porcentual de personas según departamento por tramos de edad. Censo 2011 | MIDES | | 9 | Distribución porcentual de personas según departamento de residencia habitual por lugar de nacimiento. Censos 2011 | MIDES | ```python csvs = dcat[dcat["url"].fillna("").str.lower().str.endswith(".csv")] # Several hits are the same census cut finer (by sex, by age). We want the plain count of people per # department, the one titled exactly so. pick = csvs[csvs["title"].str.match(r"Cantidad de personas según departamento\. Censos")] cw.check("semantic search finds the people-by-department census dataset, with a CSV, in its top ten", len(pick) >= 1, True, source="semantic_search over uruguay-catalogue, then its DCAT records") chosen = pick.iloc[0] cw.check("…and ranks it in the top five", int(chosen["rank"]) < 5, True, source=f"rank {int(chosen['rank']) + 1}") print("dataset: ", chosen["title"], f"({chosen['name']})") print("publisher:", chosen["publisher"]) print("licence: ", chosen["license"]) print("file: ", chosen["url"]) ``` ```text PASS semantic search finds the people-by-department census dataset, with a CSV, in its top ten: True vs True (exactly) — semantic_search over uruguay-catalogue, then its DCAT records PASS …and ranks it in the top five: True vs True (exactly) — rank 3 dataset: Cantidad de personas según departamento. Censos 1963 a 2011 (mides-indicador-10461) publisher: MIDES licence: https://www.gub.uy/agencia-gobierno-electronico-sociedad-informacion-conocimiento/sites/agencia-gobierno-electronico-sociedad-informacion-conocimiento/files/documentos/publicaciones/licencia_de_datos_abiertos_0.pdf file: https://catalogodatos.gub.uy/dataset/d26193c8-0639-40bf-8482-f05cb483cd31/resource/1a8e5fe2-c2e4-477f-ac07-e5ca38667448/download/10461_cantidad_de_personas_segun_departamento-_censos_1963_a_2011.csv ``` ## 3. Fetch it, and keep its fingerprint The file comes from the URL the catalogue recorded, nothing else. We print its SHA-256 so anyone can tell whether they are looking at the same bytes. ```python with urllib.request.urlopen(chosen["url"], timeout=120) as r: raw = r.read() sha = hashlib.sha256(raw).hexdigest() people = pd.read_csv(io.BytesIO(raw), encoding="latin-1") people.columns = ["departamento", "year", "people"] print(f"{len(raw):,} bytes, sha256 {sha}") people.tail(3) ``` ```text 2,700 bytes, sha256 7c184f2d790ed4b9d9e65d861870527e3fcc638de37ebf34ad37f6840637073e ``` ```text departamento year people 111 Soriano 2011 82594 112 Tacuarembó 2011 90051 113 Treinta y Tres 2011 48134 ``` ## 4. Join on the department spine The census file names departments in plain Spanish ("Paysandu", "San José"); DICOSE codes them 1–19. Folding accents and case joins the two, and the join must match all 19 or the notebook stops. ```python def fold(s: str) -> str: return "".join(c for c in unicodedata.normalize("NFKD", str(s)) if not unicodedata.combining(c)).upper().strip() census = people[people["year"] == 2011].assign(key=lambda d: d["departamento"].map(fold)) depts = cw.departments().assign(key=lambda d: d["label"].map(fold)) joined = depts.merge(census, on="key", how="left") cw.check("2011 census rows join all 19 departments", int(joined["people"].notna().sum()), 19, source="DICOSE codelist ⨝ census file, by folded name") ``` ```text PASS 2011 census rows join all 19 departments: 19 vs 19 (exactly) — DICOSE codelist ⨝ census file, by folded name ``` ### Cross-check: a second dataset from the same search The search's other hits include the same census cut by sex (`mides-indicador-10769`). Its men and women, added up, must give the same people in every department in 2011. ```python by_sex_row = dcat[(dcat["name"] == "mides-indicador-10769") & dcat["url"].fillna("").str.endswith(".csv")] cw.check("the by-sex census table is among the search hits, with a CSV", len(by_sex_row) >= 1, True, source="semantic_search + DCAT") with urllib.request.urlopen(by_sex_row.iloc[0]["url"], timeout=120) as r: sex_raw = r.read() sex = pd.read_csv(io.BytesIO(sex_raw), encoding="latin-1") sex.columns = ["sex", "departamento", "year", "people"] sex11 = sex[sex["year"] == 2011].assign(key=lambda d: d["departamento"].map(fold)).groupby("key")["people"].sum() mismatch = int((census.set_index("key")["people"] - sex11.reindex(census["key"]).values).abs().sum()) cw.check("2011 people per department: by-department table = by-sex table (total difference)", mismatch, 0, source=f"mides-indicador-10461 vs mides-indicador-10769, sha256 {hashlib.sha256(sex_raw).hexdigest()[:12]}…") total_2011 = int(census["people"].sum()) INE_2011 = 3_286_314 # INE's published total for the 2011 census print(f"The department tables sum to {total_2011:,} people in 2011; INE's published national total is {INE_2011:,}, " f"{INE_2011 - total_2011:,} more. We use the department tables, which is what a join on departments needs, " "and do not know where the difference sits.") ``` ```text PASS the by-sex census table is among the search hits, with a CSV: True vs True (exactly) — semantic_search + DCAT ``` ```text PASS 2011 people per department: by-department table = by-sex table (total difference): 0 vs 0 (exactly) — mides-indicador-10461 vs mides-indicador-10769, sha256 d096b027440f… The department tables sum to 3,285,877 people in 2011; INE's published national total is 3,286,314, 437 more. We use the department tables, which is what a join on departments needs, and do not know where the difference sits. ``` ```python cattle = cw.sparql(""" SELECT ?department (SUM(?n) AS ?cattle) WHERE { GRAPH ?g { ?obs a cow:AnimalFlowObservation ; cow:year "2025"^^xsd:gYear ; cow:department ?department ; cow:species ?s ; cow:animalsCount ?n . VALUES ?s { } } } GROUP BY ?department """) cattle["code"] = cattle["department"].map(cw.dept_code) t = joined.merge(cattle[["code", "cattle"]], on="code") t["cattle_per_person"] = t["cattle"] / t["people"] t = t.sort_values("cattle_per_person", ascending=False) national = t["cattle"].sum() / t["people"].sum() cw.check("2025 cattle total after the join", int(t["cattle"].sum()), 12_265_269, source="notebook national-herd") cc.show_table(t[["name", "cattle", "people", "cattle_per_person"]], floatfmt=",.2f", name="cattle-per-person") ``` ```text PASS 2025 cattle total after the join: 12,265,269 vs 12,265,269 (exactly) — notebook national-herd ``` | name | cattle | people | cattle_per_person | |:---------------|:----------|:----------|--------------------:| | Durazno | 952,171 | 57,084 | 16.68 | | Florida | 1,117,097 | 67,047 | 16.66 | | Flores | 409,429 | 25,050 | 16.34 | | Treinta y Tres | 584,734 | 48,134 | 12.15 | | Tacuarembó | 1,061,136 | 90,051 | 11.78 | | Cerro Largo | 950,412 | 84,698 | 11.22 | | Lavalleja | 654,451 | 58,815 | 11.13 | | Artigas | 742,198 | 73,377 | 10.11 | | Río Negro | 549,369 | 54,765 | 10.03 | | Rocha | 682,644 | 68,088 | 10.03 | | Salto | 898,899 | 124,861 | 7.2 | | Paysandú | 801,512 | 113,107 | 7.09 | | Soriano | 567,452 | 82,594 | 6.87 | | Rivera | 639,917 | 103,473 | 6.18 | | San José | 541,254 | 108,304 | 5 | | Colonia | 537,066 | 123,203 | 4.36 | | Maldonado | 234,191 | 164,298 | 1.43 | | Canelones | 338,601 | 520,173 | 0.65 | | Montevideo | 2,736 | 1,318,755 | 0 | ```python cw.finding("headline", f"Against the 2011 census (the latest by department in the catalogue's file), Uruguay has " f"**{national:.2f} cattle per person**. In **{t.iloc[0]['name']}** it is {t.iloc[0]['cattle_per_person']:.1f}; " f"in Montevideo, {t.set_index('name').loc['Montevideo', 'cattle_per_person']:.4f}. The dataset was found by " f"meaning in the catalogue graph, fetched from the link its DCAT record gives, and joined on the department codes.") ``` > **Finding.** Against the 2011 census (the latest by department in the catalogue's file), Uruguay has **3.73 cattle per person**. In **Durazno** it is 16.7; in Montevideo, 0.0021. The dataset was found by meaning in the catalogue graph, fetched from the link its DCAT record gives, and joined on the department codes. ```python cc.show(cc.choropleth(t, "cattle_per_person", title="Cattle per person, by department", subtitle="cattle 30 June 2025 (DICOSE) ÷ people at the 2011 census (INE, via the catalogue)", legend="cattle per person", fmt=".1f"), "cattle-per-person-map", thumbnail=True, alt_text="Map: cattle per person is highest in Durazno, Florida and Flores (about 16), near zero in Montevideo.") print(cc.map_credit()) ``` ![Map: cattle per person is highest in Durazno, Florida and Flores (about 16), near zero in Montevideo.](charts/cattle-per-person-map.svg) Chart data and encoding: [charts/cattle-per-person-map.vl.json](charts/cattle-per-person-map.vl.json) (Vega-Lite). ```text Map: Natural Earth 1:10m admin-1 (public domain, v5.1.2), joined on the DICOSE department code. Made with Natural Earth. ``` **A caveat, stated rather than hidden.** The herd is from 2025, the people from 2011: Uruguay's 2023 census counted more people (about 3.44 million), so these ratios run a little high. The catalogue file is the one by department that the catalogue holds; when a newer one is harvested, this notebook's search will find it. ## Where every number came from ```python cc.show_table(cw.checks()[["check", "got", "expected", "rule", "source"]], name="checks") cw.provenance() ``` | check | got | expected | rule | source | |:------------------------------------------------------------------------------------------|---------:|-----------:|:--------|:---------------------------------------------------------------------| | semantic search finds the people-by-department census dataset, with a CSV, in its top ten | True | True | exactly | semantic_search over uruguay-catalogue, then its DCAT records | | …and ranks it in the top five | True | True | exactly | rank 3 | | 2011 census rows join all 19 departments | 19 | 19 | exactly | DICOSE codelist ⨝ census file, by folded name | | the by-sex census table is among the search hits, with a CSV | True | True | exactly | semantic_search + DCAT | | 2011 people per department: by-department table = by-sex table (total difference) | 0 | 0 | exactly | mides-indicador-10461 vs mides-indicador-10769, sha256 d096b027440f… | | 2025 cattle total after the join | 12265269 | 12265269 | exactly | notebook national-herd | ```text kind target rows \ 0 sparql uruguay-catalogue 1.0 1 sparql uruguay-catalogue 40.0 2 sparql uruguay-cows 19.0 3 tool semantic_search NaN first line 0 SELECT (COUNT(DISTINCT ?d) AS ?datasets) (COUN... 1 SELECT ?dataset ?name ?title ?publisher ?licen... 2 SELECT ?department (SUM(?n) AS ?cattle) 3 {"graphId": "uruguay-catalogue", "query": "how... ``` ```python cw.save() ```