Programmer specification: ATR sectional extraction

Build a script that reads horse names from sechorse.csv and creates an output file called sectional.csv containing ATR sectional times for each horse.

1. Input file

The input file is sechorse.csv.

Based on the sample provided, it should be treated as a simple horse list, one horse per row, for example:

Horse
PRINCE ALI
RIVER WHARFE
KING OF SPEED
COLOANE
LESSAY

If there is no header row in the real file, the script should still accept it and treat each line as a horse name.

2. Output file

The output file must be called sectional.csv.

It must contain this exact header:

Datec,Course,Time,Horse,f1_finish,f2_f1,f3_f2,f4_f3,f5_f4,f6_f5,f7_f6,f8_f7,f9_f8,f10_f9,f11_f10,f12_f11,f13_f12,f14_f13,f15_f14,f16_f15,f17_f16,f18_f17,f19_f18,f20_f19,f21_f20,f22_f21,Total
3. What the script must do

For each horse in sechorse.csv:

Search ATR for the horse.
Find all races for that horse where ATR sectional times are available.
For each qualifying race, extract:
race date
course
off time
horse name
the horse’s sectional splits
the horse’s total race time
Write one row per horse per race into sectional.csv.

So if one horse has 5 ATR races with sectional data, there should be 5 rows in the output.

4. ATR logic

The important point is that the user may only supply the horse name, not the race date or time.

The script therefore needs to work backwards:

locate the correct ATR horse profile or form page
inspect the horse’s runs
identify which runs have sectional data available
open the corresponding ATR sectional page for that run
extract the sectional times for that horse
5. Mapping the sectionals to the output columns

ATR displays sectionals in running order from the start of the race to the finish.

The output file must store them in reverse order, so that:

f1_finish = final sectional, last split into the finish
f2_f1 = the split immediately before that
f3_f2 = the split before that
and so on

Example:

If ATR shows 12 sectionals in this order:

18.41, 13.20, 13.42, 13.29, 13.40, 13.79, 13.77, 13.22, 12.82, 12.48, 12.05, 12.29

then the output row must store them as:

f1_finish = 12.29
f2_f1 = 12.05
f3_f2 = 12.48
f4_f3 = 12.82
f5_f4 = 13.22
f6_f5 = 13.77
f7_f6 = 13.79
f8_f7 = 13.40
f9_f8 = 13.29
f10_f9 = 13.42
f11_f10 = 13.20
f12_f11 = 18.41

Unused fields from f13_f12 onwards must be left blank.

6. Total time format

ATR total time may appear in a format like:

2m 42.15s

This must be converted into seconds as a decimal for the Total field:

162.15

Examples:

1m 12.34s → 72.34
2m 42.15s → 162.15
7. Output row example

Example output row:

16/03/2026,Wolverhampton,17:30,MAGICIAN OF RIGA,12.29,12.05,12.48,12.82,13.22,13.77,13.79,13.40,13.29,13.42,13.20,18.41,,,,,,,,,,,162.15
8. Matching and naming rules

The script should:

preserve the horse name as found in the input, or standardise consistently to ATR naming
cope with apostrophes and spaces in names
cope with multiple races for the same horse
avoid duplicate rows for the same horse/race
only output races where sectional data is actually available
9. Missing data rules

If a horse has no ATR sectional races:

do not write a blank row to sectional.csv
optionally log the horse name to a separate log file such as no_sectionals.txt

If a race exists but the sectional times cannot be extracted cleanly:

skip that row
log the failure with horse name, course, date, and reason
10. Recommended workflow

A sensible implementation would be:

Read all horse names from sechorse.csv
For each horse:
search ATR horse profile
collect all race links with sectional availability
extract sectional values for that horse
reverse-map them into f1_finish to f22_f21
convert total time into decimal seconds
Append rows to sectional.csv
11. Validation rules

Before writing each row, validate that:

Datec is populated
Course is populated
Time is populated
Horse is populated
at least one sectional value exists
Total is numeric in seconds
12. Final requirement

The finished deliverable is:

Input: sechorse.csv
Output: sectional.csv

The script must automatically derive the race date and race time from ATR and must not require the user to provide them.