"""Writes the Bookshop Trail committee's spreadsheets (data/*.csv) from the course dataset (reference/*.ttl), in the conventions a person keeps a spreadsheet in: short names instead of IRIs, yes/no, UK dates, prices with a pound sign, a list of titles in one cell. The manual's mappings turn these back into RDF; `check.py` compares what they make with the course's own graph. Run from the example's directory: python tools/make-spreadsheets.py """ import csv import sys from pathlib import Path import holosdb HERE = Path(__file__).resolve().parents[1] BT = "https://example.org/bookshop-trail/" PREFIXES = """PREFIX bt: PREFIX bs: PREFIX rdfs: PREFIX dct: PREFIX wgs84: """ def slug(iri: str, kind: str) -> str: """bt:shop-inkwell -> inkwell""" return iri.removeprefix(BT).removeprefix(kind + "-") def yes_no(value: str) -> str: return {"true": "yes", "false": "no"}[value] def uk_date(iso: str) -> str: year, month, day = iso.split("-") return f"{day}/{month}/{year}" ESCAPES = {"t": "\t", "n": "\n", "r": "\r", '"': '"', "'": "'", "\\": "\\", "b": "\b", "f": "\f"} def lexical(term: str | None) -> str: """The IRI or lexical form of a term in N-Triples form, as written in the data.""" if term is None: return "" if term.startswith("<"): return term[1:-1] body, out, i = term[1 : term.rindex('"')], [], 0 while i < len(body): c = body[i] if c != "\\": out.append(c) i += 1 elif body[i + 1] in "uU": n = 4 if body[i + 1] == "u" else 8 out.append(chr(int(body[i + 2 : i + 2 + n], 16))) i += 2 + n else: out.append(ESCAPES[body[i + 1]]) i += 2 return "".join(out) def rows(store, query): return [{v: lexical(s.term(v)) for v in s.variables} for s in store.query(PREFIXES + query)] def write(name, header, records): path = HERE / "data" / name with path.open("w", newline="", encoding="utf-8") as f: w = csv.writer(f, lineterminator="\n") w.writerow(header) w.writerows(records) print(f"{name}: {len(records)} rows") def main() -> None: store = holosdb.Store() for f in sorted((HERE / "reference").glob("0*.ttl")): store.load(str(f)) # Bookshops, with the titles each stocks in one cell shops = rows(store, """ SELECT ?shop ?name ?town ?founded ?area ?staff ?genre ?second ?cafe ?web ?lat ?long (GROUP_CONCAT(STR(?work); SEPARATOR=" ") AS ?stocks) WHERE { ?shop a bs:Bookshop ; rdfs:label ?name ; bs:locatedIn ?town ; bs:founded ?founded ; bs:floorArea ?area ; bs:staffCount ?staff ; bs:specialises ?genre ; bs:sellsSecondHand ?second ; bs:hasCafe ?cafe ; wgs84:lat ?lat ; wgs84:long ?long . OPTIONAL { ?shop bs:website ?web } OPTIONAL { ?shop bs:stocks ?work } } GROUP BY ?shop ?name ?town ?founded ?area ?staff ?genre ?second ?cafe ?web ?lat ?long ORDER BY ?shop""") write( "bookshops.csv", ["shop", "name", "town", "founded", "floor_area", "staff", "specialism", "second_hand", "cafe", "website", "lat", "long", "stocks"], [ [ slug(r["shop"], "shop"), r["name"], slug(r["town"], "place"), r["founded"], r["area"], r["staff"], slug(r["genre"], "genre"), yes_no(r["second"]), yes_no(r["cafe"]), r["web"], r["lat"], r["long"], ";".join(sorted(slug(w, "book") for w in r["stocks"].split())), ] for r in shops ], ) events = rows(store, """ SELECT ?event ?shop ?date ?title ?kind ?author ?attendance ?price WHERE { ?event a bs:Event ; bs:heldAt ?shop ; bs:eventDate ?date ; rdfs:label ?title ; bs:eventKind ?kind ; bs:featuring ?author ; bs:attendance ?attendance ; bs:ticketPrice ?price . } ORDER BY ?event""") write( "events.csv", ["event", "shop", "date", "title", "kind", "featuring", "attendance", "ticket"], [ [slug(r["event"], "event"), slug(r["shop"], "shop"), uk_date(r["date"]), r["title"], r["kind"], slug(r["author"], "author"), r["attendance"], "£" + r["price"]] for r in events ], ) works = rows(store, """ SELECT ?work ?title (LANG(?title) AS ?title_language) ?author ?publisher ?year ?genre ?pages ?rrp ?language ?isbn ?series ?position ?original ?translator WHERE { ?work a bs:Work ; dct:title ?title ; bs:author ?author ; bs:publishedBy ?publisher ; bs:publicationYear ?year ; bs:genre ?genre ; bs:pages ?pages ; bs:rrp ?rrp ; bs:originalLanguage ?language . OPTIONAL { ?work bs:isbn ?isbn } OPTIONAL { ?work bs:inSeries ?series ; bs:seriesPosition ?position } OPTIONAL { ?work bs:translationOf ?original } OPTIONAL { ?work bs:translatedBy ?translator } } ORDER BY ?work""") write( "works.csv", ["book", "title", "title_language", "author", "publisher", "year", "genre", "pages", "rrp", "language", "isbn", "series", "series_position", "translation_of", "translated_by"], [ [slug(r["work"], "book"), r["title"], r["title_language"], slug(r["author"], "author"), slug(r["publisher"], "pub"), r["year"], slug(r["genre"], "genre"), r["pages"], r["rrp"], r["language"], r["isbn"], slug(r["series"], "series") if r["series"] else "", r["position"], slug(r["original"], "book") if r["original"] else "", slug(r["translator"], "author") if r["translator"] else ""] for r in works ], ) stock = rows(store, """ SELECT ?shop ?work ?copies ?price WHERE { ?record a bs:StockRecord ; bs:atShop ?shop ; bs:ofWork ?work ; bs:copies ?copies ; bs:shelfPrice ?price . } ORDER BY ?shop ?work""") write( "stock.csv", ["shop", "book", "copies", "shelf_price"], [[slug(r["shop"], "shop"), slug(r["work"], "book"), r["copies"], r["price"]] for r in stock], ) # A segment's line runs from one shop, through a waypoint, to the other: the spreadsheet # keeps the waypoint (the middle of the course's LINESTRING) segments = rows(store, """ PREFIX geo: SELECT ?from ?to ?km ?waymarked ?wkt WHERE { ?segment a bs:TrailSegment ; bs:segmentFrom ?from ; bs:segmentTo ?to ; bs:distanceKm ?km ; bs:waymarked ?waymarked ; geo:hasGeometry/geo:asWKT ?wkt . } ORDER BY ?from ?to""") def via(wkt: str) -> list[str]: points = wkt[wkt.index("(") + 1 : wkt.rindex(")")].split(",") return points[1].split() write( "segments.csv", ["from", "to", "km", "waymarked", "via_long", "via_lat"], [[slug(r["from"], "shop"), slug(r["to"], "shop"), r["km"], yes_no(r["waymarked"]), *via(r["wkt"])] for r in segments], ) faulty() # The mistakes a spreadsheet collects, one per row, for the validation chapter to find MISTAKES = { "marginalia": ("town", "wigton"), # a typo: Wigtown is the town "borderprint": ("founded", "c.1972"), # "about 1972" is not a year "bookwyrm": ("staff", "0"), # nobody works there? "bookbarrow": ("website", "www.example.org/shops/bookbarrow"), # no https:// "castle-steps": ("second_hand", "Yes"), # Yes, not yes } def faulty() -> None: with (HERE / "data" / "bookshops.csv").open(newline="", encoding="utf-8") as f: table = list(csv.DictReader(f)) for row in table: if row["shop"] in MISTAKES: column, value = MISTAKES[row["shop"]] row[column] = value (HERE / "data" / "faulty").mkdir(exist_ok=True) with (HERE / "data" / "faulty" / "bookshops.csv").open("w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=list(table[0]), lineterminator="\n") w.writeheader() w.writerows(table) print(f"faulty/bookshops.csv: {len(MISTAKES)} mistakes in {len(table)} rows") if __name__ == "__main__": sys.exit(main())