Skip to content
HPLC QC LabMAST30034 · revival

01 · Mandatory task · Lab results preprocessing

Tame the spreadsheet

Lab results arrive as spreadsheets typed by many people. The batch a sample came from is buried in two free-text columns, Expt. No. and Lot No., in whatever format the analyst preferred that day, and sometimes in the wrong column.

The capstone's first job was to turn those sheets into tidy, joinable records. This page generates a synthetic sheet with the same kind of chaos and parses it in your browser.

Generate a sheet

Rows

Messiness50%

Share of rows typed in a non-standard way.

Unparseable rows

Rows in two workbooks

1,200

660 + 540 rows

Set aside

4

Dept, Expt. or Lot No. blank or typed as n/a

Parsed

99.8%

1,194 of 1,196 rows that entered the pipeline

Routed to other

2

1 unmatched · 1 unknown dept

How the Parsed figure arises: every generated row has a small, independent chance of being genuinely unparseable, set by the “Unparseable rows” control. “Some” is tuned so the share lands near the team's reported 99.7%, and it moves from seed to seed. Messiness only changes which case catches a row, because every messy variant has a case.

The pipeline

Four steps, in the order the 2021 notebook ran them.
  1. 1

    Merge the workbooks

    Stack both files, keep each row's workbook and row number, and set aside 4 rows missing a key field. This uses merge_result's default (remove_nan=True); the 2021 notebook switched it off and those rows fell out later, in the stream filters and merges.

  2. 2

    Split the department

    A Dept No. such as 310 Downstream becomes DepartmentID 310 and Stream Downstream. Anything not starting with digits is kept as DepartmentAlternative.

  3. 3

    Run each stream's cases

    Ordered pattern cases per stream; the first match writes BatchID1, BatchID2 and Process Step. Sample Name (Expt. No. + space + Lot No.) and Compound are added to every row.

  4. 4

    Route the rest

    Whatever no case recognises goes to other (2 rows here) so a person can add a case or fix the entry.

Cases per stream

Bars show how many rows each case handled in the current sheet. Raise the messiness and watch the work shift from the canonical case to the fallbacks.

310 Upstream · Cell-culture runs. The run ID (CC + four digits) should lead Expt. No.; everything else, minus compound tags, becomes BatchID2.

  1. U1Run ID leads Expt. No.

    265 rows

    Tests
    Expt. No. against /^CC ?\d{4}/i
    Then
    Normalise the run ID (upper case, no space) into BatchID1. Join Expt. No. and Lot No., drop the run ID and any Compound-n tag, and keep the rest as BatchID2.
    Example
    cc 1043 Compound-2 | D07

  2. U2Columns swapped

    38 rows

    Tests
    Lot No. against /^CC ?\d{4}/i
    Then
    Same as U1, but the run ID was typed into Lot No.
    Example
    Harvest | CC1187
  3. No case matched → the row goes to other for a person to look at. It is never silently dropped.

Before and after

Raw cells on the left, extracted fields on the right. The highlighted span is the text the winning case matched.

1,196 rows

Raw lab-sheet cells next to the fields extracted by the rule cascade
File·RowDept No.Expt. No.Lot No.BatchID1BatchID2Process StepCompoundCase
1·0310 UpstreamCC1829D07CC1829D07··U1
1·1310 UpstreamCC1899D14CC1899D14··U1
1·2310 UpstreamCC 1170D07CC1170D07··U1
1·3310 UpstreamCC1547HarvestCC1547Harvest··U1
1·4420 Concentratecpn 062232FC19 pre_filtrationCPN062232FC19pre_filtration·C1
1·5420 ConcentrateCPN102276HF17 post_filtrationCPN102276HF17post_filtration·C1
1·6420 ConcentrateCPN052288DF03 post_filtrationCPN052288DF03post_filtration·C1
1·7420 ConcentrateCPN102206da13 final_concCPN102206DA13final_conc·C1
1·8420 Concentrate2265FF11 bulk_holdCPN04CPN042265FF11bulk_hold·C2
1·9420 ConcentrateCPN072267AF07 pre_filtrationCPN072267AF07pre_filtration·C1
1·10420 ConcentrateCPN102213CE08 post_filtrationCPN102213CE08post_filtration·C1
1·11420 ConcentrateCPN072238EG06 post_filtrationCPN072238EG06post_filtration·C1
1·12420 ConcentrateCPN072242CA11 final_concCPN072242CA11final_conc·C1
1·13420 ConcentrateCPN092275DG04 final_concCPN092275DG04final_conc·C1
1·14420 ConcentrateCPN022283DB02 post_filtrationCPN022283DB02post_filtration·C1
1·15420 ConcentrateCPN052227CC15 bulk_holdCPN052227CC15bulk_hold·C1
1·16420 ConcentrateCPN022228CG19 final_concCPN022228CG19final_conc·C1
1·17420 Concentrate2234BD09 post_filtrationCPN08CPN082234BD09post_filtration·C2
1·18420 Concentrate2226GF20 pre_filtrationCPN04CPN042226GF20pre_filtration·C2
1·19420 ConcentrateCPN082209EC03 bulk_holdCPN082209EC03bulk_hold·C1
Page 1 of 60

Try your own row

Type a row the way a busy analyst might, and see which case catches it.

Try swapping the two columns, adding a stray space, or typing Compound-2.

  1. F1Underscore suffix
  2. F2Spaced hyphen
  3. F3Space suffix
  4. F4Hyphen suffix
  5. F5Suffix glued on
  6. F6Batch only
  7. F7Columns swapped
DepartmentID
530
Stream
Formulation
BatchID1
F239117
BatchID2
A12
Process Step
Stability
Compound
·
Sample Name
“Stability F239117 - A12”