"Everyone Uses the Popularity Ranks Taken First" — The Final Form of the pre_race Spec: A Design Shift to a "First Come, First Served" Shared Cache, and Fixing the N Management

Tags: Horse racing prediction / Python / Data analysis / Debugging / Design

Period covered: Sep 22 – Oct 5, 2026　Author: yuji

Introduction

In the previous article (9/8–9/21), I found "post_race contamination", where post-race odds sneak into the predictions, and settled on a policy of treating pre_race as the one and only source of truth. Two homework items were still open at that point:

1. The countermeasure (snapshot mode) had only been added to one side of the comparison-and-verification setup (V5 vs the darkmatter version). The V5 vs V58 side was untouched.
2. The supplier of pre_race data was hard-wired to V5py alone, so there was no safety net for a day when V5's build was missed.

This article is the record of the 14 days it took to settle those two items. Along the way came a number of by-products: a detour in which "every result I wanted to check came back empty", bugs in the tool that manages N (the sample size), and a problem where re-running the same race erased its date.

Project Summary

Item	Figure
Period	14 days (9/22–10/5)
New	1 shared file (prerace_snapshot.csv), 1 master xlsx (v58_hikaku_master.xlsx), 1 one-off repair py
Modified py files	7 (3 prediction apps, 2 comparison/verification tools, 1 N-management tool, 1 batch tool)
Change-log entries	30+
Main permanent fixes	3 tracks: shared pre_race snapshot (3 apps + 2 verification tools) / N-management fixes (cutoff date, status judgment, where V5's N is read from) / addition of an execution-history column
Retired	Generation of tekichuritu_master.xlsx and its backups
Notes	Mutual sharing confirmed on real runs in 4 patterns. Still unresolved: a live check on "a day when V5py's build was actually missed"

1. The Trigger: V58's N Existed Nowhere (9/22)

It started with a question: "Is the V5 vs V58 comparison xlsx a copy of the darkmatter one?" When I looked, the situation was the reverse. Only the darkmatter side had been moved to the new N-management tool (n_tracker.py), while the V58 side was left behind on the old tool that was supposed to have been retired. V58's hit-rate N had not been tallied anywhere.

"The darkmatter version is already complete, so wouldn't it be easier to understand everything if you made it the same spec?"

Following that direction, I created v58_hikaku_master.xlsx with exactly the same spec as darkmatter_hikaku_master.xlsx. It involved zero new logic; I only reused existing helper functions.

But when I checked a single race, this appeared on the real machine:

V58 calculation failed: yosou_hikaku_data_v58.csv contains
no rows with 実行フェーズ (execution phase) = 'pre_race'.

This was not a bug from the separation work. The hole left over from V7.4 — "the V58 side has no snapshot mode" — had simply surfaced as an error message.

2. "V5 as the Only Supplier" Was Wrong (9/25–9/27)

At first I proposed: "Make V5py the only supplier, and have the verification side just read the saved results." But Yuji's request was broader than that proposal.

"If we don't do all of it, the fixes made to each py so far are meaningless. Here we are going to make the entire result-verification system complete."

He then restated the requirement like this:

"Put simply: run V5py build → get pre_race snapshot data. Run V58py build → get pre_race snapshot data. Run V5_darkmatterpy build → get pre_race snapshot data. But once the pre_race snapshot data has been obtained by any one py, it is not fetched again when the other two are built; it is shared."

In other words, the right design was not "V5 is the only supplier": whichever of the three is built first becomes the supplier, and the others share it. The lesson I took from this: I had interpreted the request in a way that bent it toward my own understanding.

The design was summarized as follows.

• Create a shared file 04_hikaku/prerace_snapshot.csv (only 6 columns: race_id, horse name, horse ID, popularity rank, recorded-at timestamp, source)
• On startup, each app py (V5, V58, darkmatter) looks at the shared file first. If the data is there, it uses it, skips the result.html lookup, and fixes the phase to pre_race
• If not, it looks up as before. If final odds can be retrieved, the run is post_race (nothing is written to the shared file); if they cannot, it is confirmed as pre_race and appended to the shared file

I also re-confirmed the operational positioning. The reference time for popularity ranks is right after the official race card is published the day before the race (10:15–12:00), and the official source is V5py. The ability of V58py and the darkmatter version to fetch data is a safety net for when V5 misses. Odds are always provisional values, so the value taken first is treated as correct as is. In the 10/3 check, the popularity ranks for 23 races saved by V5py on 10/2 (Fri) between 10:15 and 11:53 matched those used in the comparison-and-verification run on the night of 10/3 in every race.

Real-machine check: mutual sharing in 4 patterns

Pattern	Description	Result
① V5py → V58py	Nakayama 4R, built first by V5, then built by V58. No lookup; shared data applied	Pass
② V58py self-reference	Same race run twice on V58. First run fetches and saves; second run only applies	Pass
③ darkmatter self-reference	Same as ②	Pass
④ darkmatter → V5py	Nakayama 7R, built first by the darkmatter version, then applied in V5	Pass

During this check, comparing the first and second automatic fetches showed one horse's popularity rank shifting from 12th to 11th favorite. That supports the premise that odds are always provisional values, and it also confirmed that a shared snapshot fixes the value at what the first run obtained.

3. An Oversight on the Verification-py Side (9/27–9/28)

Just as I finished checking the 4 patterns, Yuji raised an important point.

"What happens if we run a verification py now and the pre_race data it calculates from is not in prerace_snapshot.csv (what's in there is data built for operation checks)?"

When I investigated, load_prerace_snapshot() in darkmatter_hikaku.py did not look at the new shared file at all; it referenced only the old V5-only CSV. Even if V5 missed, the other two apps should have served as a safety net — but the entry point of the verification itself did not benefit from it.

"The root of what we're fixing now is to get properly calculated, clean results in the comparison xlsx. So unless yosou_hikaku_v3.py and darkmatter_hikaku.py, these two, are updated to support it, it's meaningless."

The fix was a two-stage approach: "① refer to the shared file first → ② fall back to the old V5 CSV if it is not there." I kept ② so that the pre_race rows accumulated with the old method between 9/15 and 9/27 would not be wasted. I put the same change into yosou_hikaku_v3.py as a clone, and also added a guard that checks whether pre_race data exists right after the post-race mode starts.

4. Test Data Pollutes N (9/28)

While I was repeating design, implementation, and testing, build data for operation checks got mixed into the CSV. Left like that, N would become a figure that does not reflect reality. So I introduced a cutoff date, N_RESET_CUTOFF (2026-09-28), into 3 files at once, and made the later of it and the previous article's CLEAN_CUTOFF (8/5) the actual tally cutoff date.

• The 8 Nakayama races on 9/27 were not tests but legitimate pre_race data fixed from that morning's popularity ranks. Yuji's judgment: "It's a time difference before the race, not after the race, so it's OK" — they are included in N
• I added a write-target restriction (write_to) to n_tracker.py. The "Nトラッカー_DM" (N tracker_DM) sheet that had slipped into V58's master is now cleaned up automatically every time the file is opened

As a result, comparisons were organized into two — "V5 vs V58" and "V5 vs darkmatter" — and each master xlsx became self-contained.

5. "Every Result I Wanted to Check Came Back Empty" — The 9/30 Detour

On the morning of 9/30, Yuji proposed a real-machine check using the 8 races already in the csv, saying, "If we can verify the two comparison-and-verification pys with the data we have now, we'd use our time very effectively." Here I assumed he would not want today's check runs included in N, and moved the cutoff date to 10/2 without confirming.

The result: V5 went from 7,980 rows to 0, darkmatter from 832 to 0, and V58 from 140 to 0. All three tallied zero records and the N tracker calculation failed. Yet the final line of the log still showed the fixed message "should be included." On top of that, with zero records the loop never ran even once, and a latent bug in yosou_hikaku_v3.py surfaced: the variable ri, referenced right after the loop, was undefined (it only became visible once the cutoff date changed).

Yuji's intent was the opposite. He wanted today's 8 races included in N, to confirm that they are reflected in the master and the N tracker.

"Normally, you'd check that the result of the current work gets reflected, wouldn't you? If I only wanted to reset N, I wouldn't go to this much trouble."

"As for how to proceed, 'what result do you want' — that's the most basic thing of all."

I put the cutoff date back to 9/28 and revised my way of working in three points.

1. Before making a fix, write the result I want in one line
2. After deciding a means such as a setting value or a date, check before running whether that result would actually let me confirm the goal
3. If I am not confident I have read the goal correctly, ask first instead of deciding on a means

The problems found in that day's check were: a crash when the tally is zero, a wrong end-of-run message, a forgotten replacement py (No module named 'n_tracker', so the N tracker was never created), and the status-judgment error in the next section.

6. "Confident Stage" Even at N=0 — Fixing the Target-N Calculation

In the same xlsx, V5's SHOW② had N=0 and V58's SHOW① had N=1, yet both displayed "Target N=0 · ✅ Confident stage." At first I advised, "Don't trust this display while N is small," but Yuji's reply was clear:

"It's not a matter of trusting or not trusting. This is an item that has to be fixed."

The cause: when the measured hit rate p is 0% or 100%, the variance in the target-N formula becomes 0, so the target N becomes 0 and n >= target N is always true. The smaller the N, the more easily p becomes extreme, so those cases were wrongly judged as "achieved." I fixed it by switching to an adjusted p (the Agresti-Coull method) (code in Section 9). I also confirmed, over all patterns for N=0 to 399 (about 80,000 combinations), that the status never contradicts the actual margin of error.

7. Retiring tekichuritu_master.xlsx, and V5's N Being Off by One

Retirement: At the start of that day, my explanation when asked what the backups were for had misread the purpose. Yuji keeps backups "to retain the comparison-and-verification race results from every weekend, so I can compare last week with this week." The backups stay. On the other hand, the V5-only tekichuritu_master.xlsx became unnecessary, because the V5 sheets are also contained, with identical content, in v58_hikaku_master.xlsx, so I stopped generating it and backing it up. I used grep to list the active pys that reference it, and confirmed the scope of impact — 2 that write, 1 that would break on read, and 4 with comments only — before fixing 3 files.

The N discrepancy: After the 10/3 darkmatter comparison (Tokyo 1R), the N tracker's figures were off by one: V5=8, DM=9. The cause was that V5's N was always read from the 「レース別推移_V5」 (per-race trend, V5) sheet in v58_hikaku_master.xlsx, so while V5 vs V58 was not being run, the V5 N on the darkmatter master side lagged behind by design. The "data source: yosou_hikaku_data.csv" in the log was the name of the prediction CSV, not the source from which hits were read (a reconfirmation of "base conclusions only on what I have actually seen").

The fix was to count V5's N from the sheet of each master that is being written to (n_tracker.py v1.11). Using the 6 backup xlsx files provided on 10/4, I confirmed that in each master the N for V5 and the counterpart match, and that the V5 sheets in the two masters agree on all 54 races.

8. The "Execution History" Column: The Vanishing First-Run Date

When the same race is re-run, the old row disappears and a new row is appended at the bottom, and the date column changes to the latest run date. The date of the first run is not preserved. In response to Yuji's request, "Can't we keep just the dates as history?", I designed it so that the date column itself is left unchanged. The N-tally judgment uses that column, so fixing it to the first date could change which races count toward N. Instead, I added an "Execution history" (実行履歴) column at the right end.

When a race is run several times on the same day, it is displayed with a count, such as "2026/10/5 1, 2026/10/5 2" (only the date is stored in the CSV, so the tally is unaffected). With re-runs of Kyoto 11R on the darkmatter side (10/5 15:16) and the V58 side (15:24), I confirmed on the real machine that both keep running at N=54 with no double counting.

9. Designs and Code I'm Proud Of

① Shared pre_race snapshot — "Whoever takes it first is correct"

A simplified version for explanation:

def resolve_prerace_ninki(race_id, entries):
    shared = load_shared_prerace_snapshot(race_id)   # prerace_snapshot.csv
    if shared:
        # Someone has already taken it → use that. Do not go to result.html
        apply_snapshot_ninki(entries, shared)
        return 'pre_race'
    # I am the first of the three
    if maybe_update_ninki_from_result(entries):      # final odds retrievable = after the race
        return 'post_race'                           # not written to the shared file
    save_shared_prerace_snapshot(race_id, entries, source=MY_NAME)
    return 'pre_race'

The behavior is the same whichever of V5, V58, or the darkmatter version comes first, so no supplier priority order is needed. By moving from a scheme where the caller wires things up to one where each app handles it autonomously, the system became harder to break.

② Target N from an adjusted p — "Doesn't break at 0% or 100%"

import math
Z = 1.96

def target_n(hits, n, err):
    # Adjusted p: add pseudo-trials (Z²/2 hits out of Z² trials) to the measured p (Agresti-Coull method)
    p = (hits + Z**2 / 2) / (n + Z**2)
    return math.ceil(Z**2 * p * (1 - p) / err**2)

The adjusted p never becomes 0 or 1 (it is 50% at N=0), so the target N can never be 0. As N grows, it approaches the measured p. For example, for the whole clean set with N=8 and 3 hits at a ±10% margin, the target N changed from 91 to 94.

③ Cutoff dates only at tally time — "Never touch the source data"

CLEAN_CUTOFF   = '2026-08-05'   # countermeasure for the column-shift bug
N_RESET_CUTOFF = '2026-09-28'   # countermeasure for test-data contamination
cutoff = max(CLEAN_CUTOFF, N_RESET_CUTOFF)

df = df[df['日付'] >= cutoff]   # '日付' = date. Only the df used for tallying is filtered; the CSV is not rewritten

I unified the notation across 3 files (n_tracker, yosou_hikaku_v3, darkmatter_hikaku). It is also a practice of the policy that "within the basic system, use the same spec everywhere."

10. Lessons Learned

• Read handover materials all the way to the end — When handing over at a session limit, I missed the second half of the chat log and nearly mistook an implementation that had already been confirmed on the real machine for unstarted work
• Take a restatement of a design as it is, without bending it toward my own understanding — The "V5 as the only supplier" proposal turned out to be a different design after a single restatement
• Return to the "goal" and check end to end — Even after fixing the 3 apps, the entry point on the user side (the verification py) was not looking at the new shared file
• By-products of design changes and tests can pollute other metrics (N) — Check each time whether the work itself affects existing tallies
• Decide "what result do I want" first — I chose the means (a date) first, and every result I wanted to check came back empty
• Updating the banner and header comment is a basic rule — Always do it without being told, and write a reason only when not updating
• After placing a replacement py, verify it by the number in its banner — A forgotten replacement meant the N tracker was never created
• Don't make users work around a strange display; fix it — "Don't trust it" is not an answer to a bug
• Don't assert what I haven't seen — I was wrong several times: deciding "it isn't working" while the log was still incomplete, and guessing a save destination from what the code specified

Unresolved / To Be Confirmed

• A live check of a day when V5py's build was actually missed (whether V58py and the darkmatter version work as a safety net). At present, "there is no race for which the official race card was not built by V5py," so the only way to check is to build V5 next weekend or later and run the comparison-and-verification after the races finish
• A real-machine check for races where the ◎ pick is scratched, excluded, or cancelled, and a display check on the V5 vs V58 side. A race on 10/5 (Tokyo 5R) in which a horse predicted 6th was excluded went through, but it was not the ◎ pick, so the exclusion from N when the ◎ pick is excluded has not yet been tested

Closing

Last time, the scope was settling on pre_race as the one and only source of truth. These 14 days were the work of finishing that standard into a mechanism where "the value is the same no matter who builds it or when it is run." Even now that N has accumulated to 54, the place-bet hit counts are 26 for V5, 27 for V58, and 26 for the darkmatter version — not yet a stage where I can say whether the corrections are good or bad. That is why I put the yardsticks (N and pre_race) in order first.

The policy of "not erasing failures" is unchanged this time as well. The numbers in the change history have grown again. The next homework is next weekend's V5py build, and a race where the ◎ pick is excluded.