Past paper topic analyzer

Past paper topic-frequency analysis

Build a clean, structured Excel deliverable that shows how often each topic recurs across a window of past exam papers — informing what's worth prioritising in revision.

The skill exists because this task has many traps that waste effort if approached naively. Follow the workflow. Don't shortcut it.

---

Phase 1 — Clarify scope BEFORE downloading anything

Always start with AskUserQuestion to pin down scope. Past-paper sets are large; downloading the wrong subset wastes time. Cover, in one batched question call:

1. Exam board + subject code (e.g. CAIE 9708, Edexcel 9EC0, AQA 7136, IB Economics HL).
2. Level (e.g. AS only / A2 only / both; Standard vs Higher).
3. Which paper(s) and section(s) — MCQ only? Essays only? Data response? Specific section letter? Be precise. Section letters can mean different things under different syllabus versions.
4. Year window — typically last 5 years.
5. Sessions / variants — e.g. for CAIE: May/June + Oct/Nov + March, variants 1/2/3.
6. Tagging philosophy — mechanical mapping to syllabus codes vs. inductive clustering (read all questions, let categories emerge, reconcile to syllabus afterwards). When the user says "I want you to understand the questions", choose inductive.
7. Deliverable shape — Excel? Markdown report? Charts? Default to clean Excel.

Do NOT ask the user to "approve a plan" before downloading. Just proceed once scope is confirmed.

---

Phase 2 — Locate a public mirror, then PROBE before sweeping

Public mirrors that work well for various boards:

• CAIE / CAIE-9708 etc. → pastpapers.papacambridge.com/directories/CAIE/CAIE-pastpapers/upload/<filename>.pdf
• Edexcel / Pearson → papers.xtremepape.rs or pmt.physicsandmathstutor.com
• AQA / OCR → exam board own sites (filestore.aqa.org.uk, ocr.org.uk/qualifications/)
• IB → no fully open mirror; ask the user for PDFs
• AP → CollegeBoard publishes free-response PDFs; MCQs are usually copyrighted

Critical: probe ONE URL before sweeping. This avoids burning a 30-iteration loop on a wrong pattern.

curl -sL -o /tmp/probe.pdf "<best-guess-url>" && \
  file /tmp/probe.pdf && \
  head -c 4 /tmp/probe.pdf | xxd  # should be %PDF

If the probe 404s or returns HTML, try alternate mirrors before assuming the year/format is unavailable. Common patterns vary: some sites use 9708_s24_qp_22.pdf, others 9708-s24-qp-22.pdf, others wrap the file in /year/session/ directories.

Sweep template: see scripts/download_papers.sh. It loops over (year, session, variant), skips already-downloaded files, validates the PDF magic bytes (%PDF), and logs successes/failures into _download_log.txt. Some combinations don't exist (e.g. CAIE March session is variant-2 only) — accept these as expected misses.

---

Phase 3 — Extract text, then strip irrelevant sections IMMEDIATELY

pdftotext -layout input.pdf output.txt

Then trim the text down to ONLY the section you care about before reading further:

awk '/^[[:space:]]*Section B[[:space:]]*$/{p=1} p{print}' input.txt \
  | awk '/^Permission to reproduce/{exit} {print}' \
  > slim.txt

This typically cuts files by 60–80%. Slimmer text = much cheaper to read across many papers.

Why it matters: if you skip this and read full 4-page papers in batches, your context fills with data-response prose, copyright footers, blank pages — none of which contributes to topic tagging.

---

Phase 4 — Read end-to-end before tagging

Read every question in chronological order (not in random order, not in parallel batches that obscure the timeline). The whole point of "inductive clustering" is to notice patterns as they emerge across years — that requires sequential reading.

As you read, mentally accumulate clusters: "this is the third PPC + opportunity-cost question" / "elasticity recurs every session". Don't write tags yet; just absorb.

Do this with Read (or Bash cat) on the slim text files. Group your reads by year — 7 papers per batch is a good size.

---

Phase 5 — Build a structured parser, NOT regex inside the build script

Write a separate parse_essays.py (or analogous) that produces a clean JSON of every question. The build script consumes the JSON. This separation lets you iterate on parser bugs without rebuilding the whole deliverable.

Parser pitfalls — verified hard-learned

These all came up in practice and bit during a CAIE 9708 build. Bake them in from the start:

1. Question-header lines may have (a) inline. Don't assume (a) is always on its own indented line. 2 (a) Use production possibility curve diagrams... is one line. Detect both shapes.

2. Mark markers [N] may be on a CONTINUATION line, not the same line as (a) / (b). Use a findall of all [N] matches and take the last; don't anchor to end-of-line on the part-introducing line.

3. Bare page numbers (4, 7) leak into part text when the PDF spans pages. Filter with re.fullmatch(r"\d{1,2}", line) during cleanup.

4. © UCLES 2024, 9708/22/M/J/24, [Turn over, BLANK PAGE are noise. Strip them in the cleanup step.

5. Verify by assertion. After parsing, assert every question has the expected sub-part shape (e.g. exactly two parts with marks 8 and 12 for CAIE essays). Print a count of violations. Don't trust a clean parse that wasn't verified.

6. Handle syllabus revisions. Old papers and new papers within the same 5-year window may have different section structures (CAIE 9708 went from 40-mark "Section A + B" to 60-mark "Section A + B + C" in 2023). Don't assume one structure works for all years.

See scripts/parse_essays.py for a worked example. Adapt the regex constants to match the exam's question numbering / section headers.

---

Phase 6 — Develop a topic taxonomy, then tag

After reading, settle on 15–25 topic buckets. Too few (< 10) loses signal; too many (> 30) makes the frequency table noisy.

The buckets should:

• Be inductive — emerged from the questions, not imposed top-down.
• Be reconciled with the syllabus vocabulary — use the wording the exam board uses (so the friend revising can map to their textbook).
• Optionally be grouped (e.g. Microeconomics / Macroeconomics) so the spreadsheet reads coherently.

Then write tags as a flat dict keyed by (paper_id, q_num) → (primary_topic, secondary_topic_or_None).

Frequency unit = question, not sub-part. A question with parts (a) + (b) on the same theme is ONE essay's worth of topic exposure. Counting both double-counts.

Secondary topic is recorded but NOT counted in the headline frequency table, to avoid overlapping percentages. Mention this in the Coverage sheet.

---

Phase 7 — Build a CLEAN Excel deliverable

The user is going to share this with someone else. Default to terse, structured, visually polished.

Sheets to include

1. Coverage — title, scope, year window, paper count, syllabus version note, source URL. One screen tall.
2. Questions — one row per sub-part, grouped by primary topic (sort by topic order, then year/session), with light row-banding flipping when topic changes. Columns: Primary topic, Secondary topic, Year, Session, Paper code, Section, Q#, Part, Marks, Question text. Wrap question-text column. Freeze header row. Autofilter on.
3. Frequency by Year — topic × year matrix with Total column and % of all. Add Micro / Macro group divider rows. Apply a ColorScaleRule heatmap to the year columns and Total column so hot topics light up.

Styling defaults that look professional

• Hide gridlines (ws.sheet_view.showGridLines = False).
• Header row: dark-blue fill (#1F4E78), white bold text, height 30–34.
• Group bands: light blue tint for Micro topics, light green tint for Macro.
• Total row: same dark-blue as header, white bold.
• Borders: thin light-gray (#D9D9D9) between cells.
• number_format = "@" on any code-like text (paper code "21" / "22") to stop Excel auto-numerifying.
• Percentage column: 0.0%.

What NOT to include in the deliverable

The following are useful as scratch notes during analysis but should not appear in the final spreadsheet unless the user explicitly asks:

• "Tricky" / "synthesis" flags
• Per-question characterizations / one-line summaries
• Command-word columns (Discuss / Assess / Explain)
• Long-form pattern-observation prose

These are analytical scaffolding. The user can read the question text and form their own view. Drop them.

See scripts/build_xlsx.py for a worked example.

---

Common mistakes to avoid (summary)

Mistake	Cost	Fix
Guessing URL pattern, sweeping immediately	30 failed downloads, debugging in the dark	Probe ONE URL first
Reading full PDFs (with data-response, copyright, blanks)	Token budget burns 3–4×	Strip to relevant section before reading
Assuming syllabus structure is uniform across the year window	Parser breaks on old papers	Spot-check both ends of the window
Parser anchored to end-of-line for [N] markers	Page numbers leak in, marks parse as None	Use findall and take the last
Filling the deliverable with analysis notes	User asks you to strip them out	Default to clean. Present commentary in chat, not in the file
Counting sub-parts as the frequency unit	Topic counts inflated 2×	Count each question once
Skipping verification	Silent miscounts ship to the user	Assert counts tie out (per year, per session, total) before saving

---

Worked example: CAIE 9708 AS Paper 2 Section B+C

The end-to-end run for one configuration:

Phase	Output	Volume
Scope clarified	CAIE 9708, AS, P2 essays, 2021–2025, all variants	—
Papers downloaded	35 PDFs from papacambridge	30 MB
Slim text	Section B + C extracts	~1,500 lines
End-to-end read	All essays in chronological order	7 batches of ~7
Parser → JSON	essays.json	126 essays / 252 sub-parts
Topic taxonomy	20 buckets, micro/macro grouped	—
Tagging	(paper_id, q_num) → (primary, secondary)	126 entries
Excel build	Coverage / Questions / Frequency by Year	33 KB, 3 sheets

Top topics that emerged: Elasticity (13.5%), PPC & opportunity cost (11.1%), AD/AS / inflation (11.1%), Exchange rates & terms of trade (10.3%) — together ~46% of all essays.

---

File index

• scripts/download_papers.sh — probe + sweep template
• scripts/parse_essays.py — robust parser with the documented pitfall fixes
• scripts/build_xlsx.py — clean Excel builder with heatmap + topic banding
• examples/caie_9708_tags.py — example tag-dict structure for reference