Cricket Analytics Challenge

The organisers supplied three Excel workbooks of ball-by-ball cricket data and had deliberately broken them. Wicket counts arrived as English words. Matches appeared more than once with different ingestion timestamps. Innings ball counts sat outside anything physically possible. The win margin column was simply missing on a chunk of rows. Round one was about getting from that to something you could compute on without quietly inventing numbers.

The scale is what makes that awkward. The batsman scorecard is 24,925 rows across 24 columns, the bowler scorecard 18,911 rows across 20, and the match-level sheet 1,738 rows across 33 — small enough that a person could in principle look at every row, large enough that nobody will. A fourth workbook is the data dictionary, and it is the only statement of what "correct" means: match ids between 8,000,000 and 10,000,000, innings 1 or 2, runs 0 to 200, balls faced 0 to 100, first over faced 0 to 20.9, innings ball counts 10 to 120, win amounts 1 to 150, ground ids 50 to 26,000. The tempting move with corrupt data at this size is to drop the bad rows and report a clean number. That is also the move that loses the competition, because the corruptions were placed on rows that carry signal, and the whole point of the round was whether you could recover a field rather than delete it.

Cleaning without guessing

Duplicates were resolved by sorting on ingestion timestamp and keeping the last record per match ID, which is the only ordering the data itself justifies. The missing win margin was recovered by reasoning from the rest of the row: if the toss winner also won the match and had chosen to field, they won chasing and the margin is in wickets; if they chose to bat, it is in runs; if the toss winner lost, both branches invert. That reconstructs a field that would otherwise have to be dropped or imputed from the mean.

Out-of-range innings ball counts were not clipped. They were recomputed by summing balls_bowled from the bowler table for that match and innings, with a team-level mean as the fallback only where the join produced nothing. Word-form wicket counts went through word2number. Season strings like 2022/23 were normalised to full years, roster ID lists were stripped of the .0 suffixes that Excel had left behind, and team IDs moved to pandas' nullable Int64 so that missing meant missing rather than zero.

The same principle drove the rest of the match-level pass. An out-of-band win amount was replaced with the mean margin for that same winning team in matches it won by runs, rather than a global mean, so a side that wins narrowly does not inherit a blowout's number. Match dates were forced through a MM/DD/YYYY round trip to normalise the mixed formats. The one place the approach gives up is wicket counts, which are hard-set to 10 when out of range on the grounds that an innings cannot end any other way.

Validation ran against a pandera schema with per-column range checks (match IDs between 8,000,000 and 10,000,000, strike rate 0-300, innings 1-2, and so on), backed by row-level cross-field checks that a schema alone cannot express:

• Strike rate had to equal runs divided by balls faced times 100, and was recomputed where it did not.
• Runs had to be at least four times the fours plus six times the sixes.
• Boundaries could not exceed balls faced.
• Runs were reconstructed from strike rate and balls faced where the value itself was unrecoverable.
• Negative over numbers were treated as sign errors and mirrored rather than discarded.
• Maidens could not exceed dot balls divided by six, and were reduced to that ceiling where they did.
• Word-form or negative extras went through word2number, falling back to the mean of the non-negative values in the column.

Cleaned frames then went to seaborn scatter and box plots to check the distributions had not moved.

What actually enforced the rules

Worth being straight about the split between those two paragraphs. The three pandera schemas are written out in full, about seventy column definitions with dtypes and range checks, and they are the clearest statement in the repository of what each table is supposed to contain. They are also not what does the work. In two of the three cleaners the schema object is passed into the validation function and never referenced inside it, and in the third the validate(df, lazy=True) calls are commented out. Everything that actually fires is the hand-written iterrows rule engine above. Pandera is documentation-as-code here, and the row engine is the implementation.

That engine has a shape worth naming too: it breaks out of the loop the first time it hits a row it cannot repair. It is a run, inspect, patch, rerun workflow rather than a batch job, which is why the cleaning happened over several passes with intermediate workbooks saved between them, and why the cleaned outputs are written back over their own inputs. Batsman rows went 24,925 to 24,875, bowler rows 18,911 to 18,861, match rows 1,738 to 1,688.

Round two: picking an XI

Selection was built as a two-stage score per role. A rule-based band score came first (a bowler with an economy at or under 3 earns 50 points, under 15 balls per wicket earns 30, and so on), with eligibility gates of at least 100 career runs for batsmen and more than 10 wickets for bowlers. Career economy was aggregated as a balls-bowled-weighted average rather than a mean of per-match figures, which stops a single tidy over from outweighing a long spell.

The bands are all additive and all explicit. A batsman earns 50, 40 or 30 for an average strike rate above 150, 100 or 80; 30, 20, 10 or 5 as his runs-per-innings crosses 50, 40 and 30; 30, 20 or 10 for three, two or one century; and 20, 10 or 5 for five, three or one fifty, capping at 130. A bowler earns 30, 20 or 10 for a strike rate at or under 15, 19 or 24 balls per wicket; 50, 40 or 30 for an economy at or under 3, 5 or 7; 30, 20 or 10 for a bowling average at or under 20, 30 or 40; and 30, 20 or 10 for four, two or one four-wicket haul, capping at 140.

The final ranking blends that band score at 0.5, a consistency term of 1/(1 + standard deviation) at 0.25, and recency-weighted per-match metrics at 0.25, where each match is weighted by 1/(years ago + 1). Bowler extras enter with a negative weight. All-rounders come from the inner join of the two tables filtered to players with both wickets and runs, scored on the sum of their batting and bowling scores; the wicketkeeper is chosen by intersecting the keeper list with the batting ranking.

Assembling the eleven

The eleven came out as three specialist batsmen, four bowlers, a keeper and three all-rounders, taken off the top of four separately ranked sheets. The interesting part was the collision handling. Each pick was checked against the all-rounder and keeper tables before being confirmed, and the fourth bowler in the ranking was skipped because he already sat in the top all-rounders and would be picked up there. The organisers also supplied a reference "near-best" eleven and capped how much of it you could copy; the submitted side shares exactly one player with it.

The entry reached round three.

What I would fix

Three things, and none of them are the modelling. The blend adds the recency-weighted economy term instead of subtracting it, so within a band a worse economy nudges a bowler's final score up rather than down; the extras term is correctly negated and economy was missed. The recency weight is computed against datetime.now().year, which means the saved rankings cannot be reproduced in a later year from the same inputs. And the submission CSV was assembled by hand from the four ranked sheets rather than emitted by a script, which is visible in the file itself, where one player's bowling columns are zeroed and another carries his all-rounder total against a bowler role. The presentation says as much in its own scalability slide: cleaning and scoring are automated, and the selection step still needs a human running scripts and pasting rows.