The Size Column Said 'F6' and 'About A4' — I Let Gemini Read It, and Let a Table Do the Math

# size_schema.py — the shape of one reading. No conversion lives here.
from enum import Enum
from typing import Optional
from pydantic import BaseModel, Field
 
 
class Kind(str, Enum):
    canvas = "canvas"    # Japanese canvas sizes such as F6, P10
    paper = "paper"      # paper sizes such as A4, B5
    cm = "cm"            # centimeters written out, e.g. 41×31.8cm
    unknown = "unknown"  # unreadable or undecidable
 
 
class SizeRead(BaseModel):
    row_id: int = Field(description="Return the input row number unchanged")
    kind: Kind
    series: Optional[str] = Field(
        default=None, description="F/P/M/S or A/B. Only when kind is canvas/paper"
    )
    number: Optional[int] = Field(
        default=None, description="The canvas or paper number. Only when kind is canvas/paper"
    )
    first_cm: Optional[float] = Field(
        default=None, description="When kind is cm: the first number, in the order written"
    )
    second_cm: Optional[float] = Field(
        default=None, description="When kind is cm: the second number. For '30cm square' repeat the first"
    )
    approximate: bool = Field(description="true if the cell says 'about', 'approx.', or similar")
    note: str = Field(description="Anything you were unsure about. Empty string if nothing")

# read_sizes.py — one call for every row, then reconcile the row IDs
import os
from google import genai
from google.genai import types
from pydantic import TypeAdapter
from size_schema import SizeRead
 
client = genai.Client(api_key=os.environ["GEMINI_API_KEY"])
MODEL = "gemini-3.8-flash"
 
INSTRUCTION = """You read the size column of an artwork ledger.
For each row, decide which kind of notation it uses and transcribe the numbers as written.
Do NOT convert to centimeters. For canvas and paper sizes, split into series and number only.
If the cell says 'about', 'approx.', or similar, set approximate to true.
Return every input row, and keep row_id exactly as given."""
 
 
def read_sizes(rows: list[tuple[int, str]]) -> list[SizeRead]:
    body = "\n".join(f"{row_id}\t{raw}" for row_id, raw in rows)
    res = client.models.generate_content(
        model=MODEL,
        contents=f"{INSTRUCTION}\n\nrow_id\tsize\n{body}",
        config=types.GenerateContentConfig(
            response_mime_type="application/json",
            response_schema=list[SizeRead],
            temperature=0,
        ),
    )
    parsed = TypeAdapter(list[SizeRead]).validate_json(res.text)
 
    # Reconcile sent vs. returned rows. Skip this and a missing row sails through.
    sent = {row_id for row_id, _ in rows}
    got = [p.row_id for p in parsed]
    if sorted(got) != sorted(sent):
        missing = sent - set(got)
        dup = {r for r in got if got.count(r) > 1}
        raise RuntimeError(f"Row alignment broke: missing={missing} dup={dup}")
    return sorted(parsed, key=lambda p: p.row_id)

# convert.py — the table converts, the image decides orientation
from pathlib import Path
from typing import Optional
from PIL import Image
from size_schema import SizeRead, Kind
 
# Canvas table: (series, number) -> (long side cm, short side cm)
CANVAS = {
    ("F", 0): (18.0, 14.0), ("F", 3): (27.3, 22.0), ("F", 4): (33.3, 24.2),
    ("F", 6): (41.0, 31.8), ("F", 8): (45.5, 38.0), ("F", 10): (53.0, 45.5),
    ("F", 12): (60.6, 50.0), ("F", 15): (65.2, 53.0), ("F", 20): (72.7, 60.6),
    ("F", 30): (91.0, 72.7),
    ("P", 6): (41.0, 27.3), ("P", 10): (53.0, 41.0),
    ("M", 6): (41.0, 24.2), ("M", 10): (53.0, 33.3),
    ("S", 6): (41.0, 41.0), ("S", 10): (53.0, 53.0),
}
PAPER = {
    ("A", 3): (42.0, 29.7), ("A", 4): (29.7, 21.0), ("A", 5): (21.0, 14.8),
    ("B", 4): (36.4, 25.7), ("B", 5): (25.7, 18.2),
}
 
 
def pair_cm(r: SizeRead) -> Optional[tuple[float, float]]:
    """Return (long side, short side) from a reading, or None if it can't be resolved."""
    if r.kind == Kind.cm and r.first_cm and r.second_cm:
        a, b = float(r.first_cm), float(r.second_cm)
        return (max(a, b), min(a, b))
    if r.kind == Kind.canvas and r.series and r.number is not None:
        return CANVAS.get((r.series.upper(), r.number))
    if r.kind == Kind.paper and r.series and r.number is not None:
        return PAPER.get((r.series.upper(), r.number))
    return None
 
 
def with_orientation(pair: tuple[float, float], image: Path) -> tuple[float, float]:
    """Reorder to (width, height) using the artwork image's aspect ratio."""
    long_cm, short_cm = pair
    with Image.open(image) as im:
        w, h = im.size
    return (long_cm, short_cm) if w >= h else (short_cm, long_cm)

# build.py — ledger -> readings -> conversion -> JSON
import json
from pathlib import Path
from openpyxl import load_workbook
from read_sizes import read_sizes
from convert import pair_cm, with_orientation
 
LEDGER = Path("artworks.xlsx")
IMAGES = Path("images")        # one {artwork_id}.jpg per piece
OUT = Path("artworks.json")
 
wb = load_workbook(LEDGER, data_only=True)
ws = wb["Works"]
 
rows, meta = [], {}
for row_id, row in enumerate(ws.iter_rows(min_row=3, values_only=True), start=1):
    artwork_id, title, size_raw = row[0], row[1], row[3]
    if not artwork_id:
        continue
    rows.append((row_id, str(size_raw or "").strip()))
    meta[row_id] = {"id": str(artwork_id), "title": str(title)}
 
reads = read_sizes(rows)
raw_by_id = dict(rows)
 
out, review = [], []
for r in reads:
    m = meta[r.row_id]
    pair = pair_cm(r)
    image = IMAGES / f"{m['id']}.jpg"
    if pair is None or not image.exists():
        review.append({**m, "raw": raw_by_id[r.row_id], "note": r.note or "not in table / no image"})
        continue
    w_cm, h_cm = with_orientation(pair, image)
    out.append({
        **m,
        "width_cm": w_cm,
        "height_cm": h_cm,
        "approximate": r.approximate,    # the site prefixes "approx." when true
        "size_raw": raw_by_id[r.row_id],  # keep the original string
    })
 
OUT.write_text(json.dumps(out, ensure_ascii=False, indent=2), encoding="utf-8")
print(f"resolved {len(out)} rows / needs review {len(review)} rows")
for item in review:
    print(" -", item["id"], item["title"], "|", item["raw"], "|", item["note"])