Source code for morphogen.io.tables

"""Tables of results as raw data files: comma-separated values (``.csv``), text in
tab-separated columns under a ``#`` header (``.txt``, ``.tsv``: ``numpy.loadtxt`` reads
them) and Excel workbooks (``.xlsx``), without dependency: a workbook is a zip of a few
XML parts (Office Open XML), its strings inline, its header row bold and frozen.

A table is a list of records (dicts), a mapping of columns (name -> values) or a 2D array
(with ``header``); several tables (a mapping name -> table) become the sheets of a
workbook, or one text file each.
"""

from __future__ import annotations

import csv
import math
import zipfile
from pathlib import Path
from xml.etree import ElementTree
from xml.sax.saxutils import escape

import numpy as np

FORMATS = {".csv": "csv", ".txt": "txt", ".tsv": "txt", ".dat": "txt", ".xlsx": "xlsx"}


[docs] def as_columns(table, header=None) -> tuple[list[str], list[list]]: """The header and the columns (lists) of a table (records, columns or an array).""" if isinstance(table, dict): names = [str(k) for k in table] columns = [ list(np.ravel(np.asarray(v, dtype=object))) if np.ndim(v) else [v] for v in table.values() ] elif isinstance(table, (list, tuple)) and table and isinstance(table[0], dict): names = [] for row in table: names.extend(k for k in row if k not in names) columns = [[row.get(k, "") for row in table] for k in names] else: data = np.atleast_2d(np.asarray(table, dtype=object)) names = list(header) if header is not None else [f"c{j + 1}" for j in range(data.shape[1])] columns = [list(data[:, j]) for j in range(data.shape[1])] if header is not None and len(header) == len(names): names = [str(h) for h in header] n = max((len(c) for c in columns), default=0) columns = [c + [""] * (n - len(c)) for c in columns] return names, columns
def _plain(value): """A cell value as a Python number, bool or string.""" if isinstance(value, (bool, np.bool_)): return bool(value) if isinstance(value, (int, np.integer)): return int(value) if isinstance(value, (float, np.floating)): return float(value) return "" if value is None else str(value) def _text(value, precision: int | None) -> str: value = _plain(value) if isinstance(value, float): return repr(value) if precision is None else f"{value:.{precision}g}" return str(value) def _column_name(j: int) -> str: out = "" j += 1 while j: j, r = divmod(j - 1, 26) out = chr(65 + r) + out return out def _sheet_xml(names, columns) -> str: rows = ['<row r="1">'] + [ f'<c r="{_column_name(j)}1" t="inlineStr" s="1"><is><t>{escape(n)}</t></is></c>' for j, n in enumerate(names) ] rows.append("</row>") for i in range(len(columns[0]) if columns else 0): r = i + 2 cells = [f'<row r="{r}">'] for j, column in enumerate(columns): value, ref = _plain(column[i]), f"{_column_name(j)}{r}" if isinstance(value, bool): cells.append(f'<c r="{ref}" t="b"><v>{int(value)}</v></c>') elif isinstance(value, (int, float)): if isinstance(value, float) and not math.isfinite(value): cells.append(f'<c r="{ref}" t="inlineStr"><is><t>{value}</t></is></c>') else: cells.append(f'<c r="{ref}"><v>{value!r}</v></c>') elif value != "": cells.append( f'<c r="{ref}" t="inlineStr"><is>' f'<t xml:space="preserve">{escape(value)}</t></is></c>' ) cells.append("</row>") rows.extend(cells) return ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' '<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" ' 'activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>' "<sheetData>" + "".join(rows) + "</sheetData></worksheet>" ) def _write_xlsx(path: Path, sheets: dict) -> None: names = list(sheets) main = "application/vnd.openxmlformats-officedocument.spreadsheetml" rel = "http://schemas.openxmlformats.org/officeDocument/2006/relationships" overrides = "".join( f'<Override PartName="/xl/worksheets/sheet{k + 1}.xml" ContentType="{main}.worksheet+xml"/>' for k in range(len(names)) ) content = ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">' '<Default Extension="rels" ' 'ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' '<Default Extension="xml" ContentType="application/xml"/>' f'<Override PartName="/xl/workbook.xml" ContentType="{main}.sheet.main+xml"/>' f'<Override PartName="/xl/styles.xml" ContentType="{main}.styles+xml"/>' + overrides + "</Types>" ) root_rels = ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' f'<Relationship Id="rId1" Type="{rel}/officeDocument" ' 'Target="xl/workbook.xml"/></Relationships>' ) sheets_xml = "".join( f'<sheet name="{escape(str(n)[:31])}" sheetId="{k + 1}" r:id="rId{k + 1}"/>' for k, n in enumerate(names) ) workbook = ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ' f'xmlns:r="{rel}"><sheets>{sheets_xml}</sheets></workbook>' ) links = "".join( f'<Relationship Id="rId{k + 1}" Type="{rel}/worksheet" ' f'Target="worksheets/sheet{k + 1}.xml"/>' for k in range(len(names)) ) workbook_rels = ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' + links + f'<Relationship Id="rId{len(names) + 1}" Type="{rel}/styles" ' 'Target="styles.xml"/></Relationships>' ) styles = ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' '<fonts count="2"><font><sz val="11"/><name val="Calibri"/></font>' '<font><b/><sz val="11"/><name val="Calibri"/></font></fonts>' '<fills count="2"><fill><patternFill patternType="none"/></fill>' '<fill><patternFill patternType="gray125"/></fill></fills>' '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>' '<cellStyleXfs count="1">' '<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>' '<cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>' '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/></cellXfs>' "</styleSheet>" ) with zipfile.ZipFile(path, "w", compression=zipfile.ZIP_DEFLATED) as z: z.writestr("[Content_Types].xml", content) z.writestr("_rels/.rels", root_rels) z.writestr("xl/workbook.xml", workbook) z.writestr("xl/_rels/workbook.xml.rels", workbook_rels) z.writestr("xl/styles.xml", styles) for k, n in enumerate(names): z.writestr(f"xl/worksheets/sheet{k + 1}.xml", _sheet_xml(*sheets[n]))
[docs] def write_table( path, table, *, header=None, sheet: str = "data", precision: int | None = None ) -> Path: """Write a table to ``.csv``, ``.txt``/``.tsv``/``.dat`` or ``.xlsx`` (by its suffix); ``precision`` significant digits for the text formats (all by default).""" path = Path(path) kind = FORMATS.get(path.suffix.lower()) if kind is None: raise ValueError(f"write_table: a suffix among {sorted(FORMATS)}") names, columns = as_columns(table, header) path.parent.mkdir(parents=True, exist_ok=True) if kind == "xlsx": _write_xlsx(path, {sheet: (names, columns)}) elif kind == "csv": with path.open("w", newline="", encoding="utf-8") as f: w = csv.writer(f) w.writerow(names) for row in zip(*columns, strict=True): w.writerow([_text(v, precision) for v in row]) else: with path.open("w", encoding="utf-8") as f: f.write("# " + "\t".join(names) + "\n") for row in zip(*columns, strict=True): f.write("\t".join(_text(v, precision) for v in row) + "\n") return path
[docs] def write_tables(path, tables: dict, *, precision: int | None = None) -> list[Path]: """Several tables: the sheets of one ``.xlsx`` workbook, or one text file each (``<stem>_<name><suffix>``).""" path = Path(path) if path.suffix.lower() == ".xlsx": path.parent.mkdir(parents=True, exist_ok=True) _write_xlsx(path, {name: as_columns(t) for name, t in tables.items()}) return [path] return [ write_table(path.with_name(f"{path.stem}_{name}{path.suffix}"), t, precision=precision) for name, t in tables.items() ]
def _number(text: str): try: value = float(text) except ValueError: return text return ( int(value) if value.is_integer() and "." not in text and "e" not in text.lower() else value )
[docs] def read_table(path, sheet: int = 0) -> dict[str, list]: """The columns (name -> values) of a table written by :func:`write_table` (or any simple ``.csv``, text or ``.xlsx`` table with a header row).""" path = Path(path) kind = FORMATS.get(path.suffix.lower()) if kind == "csv": with path.open(newline="", encoding="utf-8") as f: rows = list(csv.reader(f)) names, body = rows[0], rows[1:] return {n: [_number(r[j]) for r in body] for j, n in enumerate(names)} if kind == "txt": lines = path.read_text(encoding="utf-8").splitlines() names = lines[0].lstrip("#").strip().split("\t") body = [line.split("\t") for line in lines[1:] if line.strip()] return {n: [_number(r[j]) for r in body] for j, n in enumerate(names)} if kind != "xlsx": raise ValueError(f"read_table: a suffix among {sorted(FORMATS)}") ns = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"} with zipfile.ZipFile(path) as z: shared = [] if "xl/sharedStrings.xml" in z.namelist(): root = ElementTree.fromstring(z.read("xl/sharedStrings.xml")) shared = [ "".join(t.text or "" for t in si.iter(f"{{{ns['m']}}}t")) for si in root.findall("m:si", ns) ] root = ElementTree.fromstring(z.read(f"xl/worksheets/sheet{sheet + 1}.xml")) grid = [] for row in root.iter(f"{{{ns['m']}}}row"): values = {} for c in row.findall("m:c", ns): ref, kind_c = c.get("r"), c.get("t") col = 0 for ch in "".join(x for x in ref if x.isalpha()): col = 26 * col + ord(ch) - 64 if kind_c == "inlineStr": value = "".join(t.text or "" for t in c.iter(f"{{{ns['m']}}}t")) elif kind_c == "s": value = shared[int(c.find("m:v", ns).text)] elif kind_c == "b": value = c.find("m:v", ns).text == "1" else: v = c.find("m:v", ns) value = _number(v.text) if v is not None else "" values[col - 1] = value grid.append(values) names = [str(grid[0][j]) for j in sorted(grid[0])] return {n: [r.get(j, "") for r in grid[1:]] for j, n in enumerate(names)}