Plates are managed tables specialised for microplates. You collect samples in a
flat plate table, project them onto a plate layout (a
rows × columns well grid that mirrors the bench), then round-trip the
grid back to a tidy DataFrame. Three functions tie it together:
create_plate_table, create_plate_layout, and
plate_layout_to_dataframe — with formats from
get_plate_format_presets.
create_plate_table writes a data-entry sheet (default
"plate sheet"): a merged title band, an orange
header row of Sample No. / Sample ID /
Plate Position plus any extra_headers, and a bordered data
area of sample_rows rows (defaulting to the format's well count). Each
header cell gets a sheet-scoped named range (sample_id,
plate_position, concentration, …) and the whole row is
named plate_table.
Plate Position is a validated dropdown. Every cell in the
Plate Position column carries an in-cell Data Validation list
(xlValidateList, xlValidAlertStop) of every valid well for
the chosen format (A1 … H12 for 96-well). The options are written to a
separate list sheet (default "lists") via add_dropdown_list,
which keeps positions canonical so the layout can match on them.
Row 1 holds hidden metadata — plate_format_key (A1),
plate_rows_count_key (B1), plate_cols_count_key (C1).
create_plate_layout reads these to detect the format automatically, so
don't delete them. start_col defaults to 2 (column B),
leaving column A free. Writes escape leading = + - @ by default
(sanitize_formulas=True).
Fill the table (type or paste Sample ID, pick Plate Position
from the dropdown, add per-well variables), then call create_plate_layout.
It fills wells by matching Plate Position values — well
A1 gets the row whose plate_position == "A1", not by row
order. The variables in value_named_ranges stack vertically inside each
well in well_value_order.
The format is read automatically from the source sheet's row-1
metadata — you don't pass it again. Each well stacks the chosen variables in
well_value_order (here sample_id then
concentration), and a Variables label column names the lines
on the right. Duplicate positions, or a well with no matching row, raise
ValueError.
Example:
ws = create_plate_layout(
wb,
plate_name="plate_run_001",
plate_title="Run 001 — Batch A",
value_named_ranges=["sample_id", "concentration"],
well_value_order=["sample_id", "concentration"],
source_sheet_name="plate sheet",
target_sheet_name="plate layouts",
)
get_plate_format_presets() returns the built-in formats keyed by well
count. The layout grows in the same shape — just bigger.
"6" · 6-well · 2 × 3 · A–B
"12" · 12-well · 3 × 4 · A–C
"24" · 24-well · 4 × 6 · A–D
"48" · 48-well · 6 × 8 · A–F
"96" · 96-well · 8 × 12 · A–H default
"384" · 384-well · 16 × 24 · A–P
"1536" · 1536-well · 32 × 48 · A–AF
Custom dimensions. When no preset fits, pass both
plate_rows_count and plate_cols_count (only one raises
ValueError); the shape stores as "custom". Row labels extend
past 26 the same way Excel columns do — A–Z, then AA–ZZ, then AAA–ZZZ (up to
18,278 rows), so a 1536-well plate's rows spill into AA … AF and tall custom
plates round-trip correctly.
plate_layout_to_dataframe reads a drawn plate grid back into a tidy
DataFrame using the named ranges create_plate_layout wrote — the
format is inferred automatically, no format parameter. Every frame
includes plate_name, plate_row, plate_col, and
plate_position; that plate_position column round-trips
exactly into a new plate table's Plate Position column.
Filter with variables (by display name or token), rows
(["A","B"]), and columns ([1,2,3]), or switch to
return_format="long" (one row per well × variable, with
variable_token + variable_display). Row labels are sorted by
physical Excel row position, so a 40-row custom plate (A … AN)
returns in true plate order, not alphabetically.
Re-call create_plate_layout with the same
plate_name to refresh a plate in place. It clears the old
footprint and redraws — including a format change. Growing taller (96 → 384)
inserts rows so plates stacked below are pushed down, not overwritten.
Shrinking (384 → 96) removes the excess rows with a full-row
delete so plates below shift up and stay compact.
Danger zone — full-row delete on shrink. Shrinking a plate deletes entire worksheet rows across the shrink band. This deliberately protects plates stacked directly below (their cell content is preserved; merged formatting is rebuilt). But any loose, side-by-side content in other columns on those same rows — notes, a chart, an unmanaged table beside the plate — sits in the delete path and will be removed. Keep the columns beside a plate clear of anything you can't lose, or stack content vertically.
Before any insert/delete the stacked-only invariant is enforced: if
another managed table's projected row range would overlap this plate's footprint, you
get SideBySideNotSupportedError and nothing is written. A wide format at a
high start_col that runs past Excel's last column (XFD = 16,384) is
rejected before writing with ExcelColumnOverflowError.