tables

Read tabular data from a file.

extension_delimiter_map module-attribute

extension_delimiter_map: dict[str, str] = {'.csv': ','}

The delimiter to use to separate columns in a table based on the file extension.

If the delimiter keyword is not specified when calling the read_table function then this extension-delimiter map is used to determine the value of the delimiter to use to separate the columns in a text-based file format. If the file extension is not in the map, then columns are split by any whitespace.

See the Overview for an example.

read_table

read_table(file: PathLike | ReadLike, **kwargs: Any) -> Dataset

Read data in a table format from a file.

A table has the following properties:

1. The first row is a header
2. All rows have the same number of columns
3. All data values in a column have the same data type

See the Overview for examples.

Parameters:

Name	Type	Description	Default
file	PathLike | ReadLike	The file to read. If file is a Google Sheets spreadsheet then file must end with .gsheet even if the ID of the spreadsheet is specified.	required
kwargs	Any	If the file is an Excel spreadsheet then the keyword arguments are passed to read_table_excel. If a Google Sheets spreadsheet then the keyword arguments are passed to read_table_gsheets. If an OpenDocument Spreadsheet then the keyword arguments are passed to read_table_ods. Otherwise, all keyword arguments are passed to read_table_text.	{}

Returns:

Type	Description
Dataset	The table as a Dataset. The header is included in the Metadata.

Source code in src/msl/io/tables.py

def read_table(file: PathLike | ReadLike, **kwargs: Any) -> Dataset:
    """Read data in a table format from a file.

    A *table* has the following properties:

    1. The first row is a header
    2. All rows have the same number of columns
    3. All data values in a column have the same data type

    !!! example "See the [Overview][read-a-table] for examples."

    Args:
        file: The file to read. If `file` is a Google Sheets spreadsheet then `file` must end
            with `.gsheet` even if the ID of the spreadsheet is specified.
        kwargs: If the file is an Excel spreadsheet then the keyword arguments are passed to
            [read_table_excel][msl.io.tables.read_table_excel]. If a Google Sheets spreadsheet then
            the keyword arguments are passed to [read_table_gsheets][msl.io.tables.read_table_gsheets].
            If an OpenDocument Spreadsheet then the keyword arguments are passed to
            [read_table_ods][msl.io.tables.read_table_ods]. Otherwise, all keyword arguments are
            passed to [read_table_text][msl.io.tables.read_table_text].

    Returns:
        The table as a [Dataset][msl.io.node.Dataset]. The header is included in the
            [Metadata][msl.io.metadata.Metadata].
    """
    ext = get_extension(file).lower()
    if ext in {".xls", ".xlsx"}:
        return read_table_excel(file, **kwargs)

    if ext in {".ods", ".fods"}:
        return read_table_ods(file, **kwargs)

    if ext == ".gsheet":
        file = os.fsdecode(file) if isinstance(file, (bytes, str, os.PathLike)) else str(file.name)
        return read_table_gsheets(file.removesuffix(".gsheet"), **kwargs)

    return read_table_text(file, **kwargs)

read_table_excel

read_table_excel(
    file: PathLike | ReadLike,
    *,
    cells: str | None = None,
    sheet: str | None = None,
    as_datetime: bool = True,
    dtype: DTypeLike | None = None,
    invalid_date: str | date | datetime | None = None,
    skip_rows: Iterable[int] | None = None,
    **kwargs: Any,
) -> Dataset

Read a data table from an Excel spreadsheet.

The generic way to read any table is with the read_table function.

Parameters:

Name	Type	Description	Default
file	PathLike | ReadLike	The file to read.	required
cells	str | None	The cells to read. For example, C9 (i.e, specifying only the top-left cell of the table) will start at cell C9 and include all columns to the right and all rows below C9, A:C includes all rows in columns A, B and C, C9:G20 includes only the specified cells, and A:C,E includes all values in columns A, B, C and E. If not specified, assumes that the table starts at cell A1 and returns all cells from the specified sheet.	None
sheet	str | None	The name of the sheet to read the data from. If there is only one sheet in the workbook then you do not need to specify the name of the sheet.	None
as_datetime	bool	Whether dates should be returned as datetime or date objects. If False, dates are returned as an ISO 8601 string.	True
dtype	DTypeLike | None	The data type(s) to use for the table.	None
invalid_date	str | date | datetime | None	If None, an error is raised if a cell contains a value that is an invalid date. If not None, all cells that contain an invalid date are replaced with the specified value.	None
skip_rows	Iterable[int] | None	Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in Excel and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.	None
kwargs	Any	All additional keyword arguments are passed to xlrd.open_workbook. Can use an encoding keyword argument as an alias for encoding_override.	{}

Returns:

Type	Description
Dataset	The table as a Dataset. The header is included in the Metadata.

Source code in src/msl/io/tables.py

def read_table_excel(  # noqa: PLR0913
    file: PathLike | ReadLike,
    *,
    cells: str | None = None,
    sheet: str | None = None,
    as_datetime: bool = True,
    dtype: DTypeLike | None = None,
    invalid_date: str | date | datetime | None = None,
    skip_rows: Iterable[int] | None = None,
    **kwargs: Any,
) -> Dataset:
    """Read a data table from an Excel spreadsheet.

    The generic way to read any table is with the [read_table][msl.io.tables.read_table] function.

    Args:
        file: The file to read.
        cells: The cells to read. For example, `C9` (i.e, specifying only the top-left cell
            of the table) will start at cell C9 and include all columns to the right and
            all rows below C9, `A:C` includes all rows in columns A, B and C, `C9:G20`
            includes only the specified cells, and `A:C,E` includes all values in columns
            A, B, C and E. If not specified, assumes that the table starts at cell `A1`
            and returns all cells from the specified `sheet`.
        sheet: The name of the sheet to read the data from. If there is only one sheet
            in the workbook then you do not need to specify the name of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime] or
            [date][datetime.date] objects. If `False`, dates are returned as an
            ISO 8601 string.
        dtype: The data type(s) to use for the table.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If not `None`, all cells that contain an invalid date are
            replaced with the specified value.
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet is
            viewed in Excel and are not based on the row number in the data that would be
            returned. For example, if `cells="A3:D10"` and you want to skip the data in rows
            `A5:D5` and `A7:D7` then `skip_rows=[5, 7]` achieves the desired result.
        kwargs: All additional keyword arguments are passed to [xlrd.open_workbook][].
            Can use an `encoding` keyword argument as an alias for `encoding_override`.

    Returns:
        The table as a [Dataset][msl.io.node.Dataset]. The header is included in the
            [Metadata][msl.io.metadata.Metadata].
    """
    file = os.fsdecode(file) if isinstance(file, (bytes, str, os.PathLike)) else str(file.name)

    with ExcelReader(file, **kwargs) as excel:
        if cells is not None and _spreadsheet_top_left_regex.match(cells):
            name = sheet or excel.sheet_names()[0]
            num_rows, num_cols = excel.dimensions(name)
            letters = excel.to_letters(num_cols - 1)
            cells += f":{letters}{num_rows}"
        table = excel.read(cells, sheet=sheet, as_datetime=as_datetime, invalid_date=invalid_date, skip_rows=skip_rows)

    return _spreadsheet_to_dataset(table, file, dtype)

read_table_gsheets

read_table_gsheets(
    file: PathLike | ReadLike,
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    dtype: DTypeLike | None = None,
    invalid_date: datetime | None = None,
    skip_rows: Iterable[int] | None = None,
    **kwargs: Any,
) -> Dataset

Read a data table from a Google Sheets spreadsheet.

Note

You must have already performed the instructions specified in GDrive and in GSheets to be able to use this function.

The generic way to read any table is with the read_table function.

Parameters:

Name	Type	Description	Default
file	PathLike | ReadLike	The file to read. Can be the ID of a Google Sheets spreadsheet.	required
cells	str | None	The cells to read. For example, C9 (i.e, specifying only the top-left cell of the table) will start at cell C9 and include all columns to the right and all rows below C9, A:C includes all rows in columns A, B and C, and, C9:G20 includes only the specified cells. If not specified, assumes that the table starts at cell A1 and returns all cells from the specified sheet.	None
sheet	str | None	The name of the sheet to read the data from. If there is only one sheet in the spreadsheet then you do not need to specify the name of the sheet.	None
as_datetime	bool	Whether dates should be returned as datetime or date objects. If False, dates are returned as a string in the display format of the spreadsheet cell.	True
dtype	DTypeLike | None	The data type(s) to use for the table.	None
invalid_date	datetime | None	If None, an error is raised if a cell contains a value that is an invalid date. If not None, all cells that contain an invalid date are replaced with the specified value.	None
skip_rows	Iterable[int] | None	Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in Google Sheets and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.	None
kwargs	Any	All additional keyword arguments are passed to GSheetsReader.	{}

Returns:

Type	Description
Dataset	The table as a Dataset. The header is included in the Metadata.

Source code in src/msl/io/tables.py

def read_table_gsheets(  # noqa: PLR0913
    file: PathLike | ReadLike,
    cells: str | None = None,
    sheet: str | None = None,
    *,
    as_datetime: bool = True,
    dtype: DTypeLike | None = None,
    invalid_date: datetime | None = None,
    skip_rows: Iterable[int] | None = None,
    **kwargs: Any,
) -> Dataset:
    """Read a data table from a Google Sheets spreadsheet.

    !!! note
        You must have already performed the instructions specified in
        [GDrive][msl.io.google_api.GDrive] and in [GSheets][msl.io.google_api.GSheets]
        to be able to use this function.

    The generic way to read any table is with the [read_table][msl.io.tables.read_table] function.

    Args:
        file: The file to read. Can be the ID of a Google Sheets spreadsheet.
        cells: The cells to read. For example, `C9` (i.e, specifying only the top-left cell
            of the table) will start at cell C9 and include all columns to the right and
            all rows below C9, `A:C` includes all rows in columns A, B and C, and, `C9:G20`
            includes only the specified cells. If not specified, assumes that the table
            starts at cell `A1` and returns all cells from the specified `sheet`.
        sheet: The name of the sheet to read the data from. If there is only one sheet
            in the spreadsheet then you do not need to specify the name of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime] or
            [date][datetime.date] objects. If `False`, dates are returned as a string in
            the display format of the spreadsheet cell.
        dtype: The data type(s) to use for the table.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If not `None`, all cells that contain an invalid date are
            replaced with the specified value.
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet is
            viewed in Google Sheets and are not based on the row number in the data that would
            be returned. For example, if `cells="A3:D10"` and you want to skip the data in rows
            `A5:D5` and `A7:D7` then `skip_rows=[5, 7]` achieves the desired result.
        kwargs: All additional keyword arguments are passed to [GSheetsReader][msl.io.readers.gsheets.GSheetsReader].

    Returns:
        The table as a [Dataset][msl.io.node.Dataset]. The header is included in the
            [Metadata][msl.io.metadata.Metadata].
    """
    file = os.fsdecode(file) if isinstance(file, (bytes, str, os.PathLike)) else str(file.name)

    with GSheetsReader(file, **kwargs) as sheets:
        if cells is not None and not _spreadsheet_range_regex.match(cells):
            if not _spreadsheet_top_left_regex.match(cells):
                msg = f"Invalid cell {cells!r}"
                raise ValueError(msg)

            r, c = sheets.to_indices(cells)
            data = sheets.read(sheet=sheet, as_datetime=as_datetime, invalid_date=invalid_date, skip_rows=skip_rows)
            table = [row[c:] for row in data[r:]]
        else:
            table = sheets.read(
                cells, sheet=sheet, as_datetime=as_datetime, invalid_date=invalid_date, skip_rows=skip_rows
            )

    return _spreadsheet_to_dataset(table, file, dtype)

read_table_ods

read_table_ods(
    file: PathLike | ReadLike,
    *,
    cells: str | None = None,
    sheet: str | None = None,
    as_datetime: bool = True,
    dtype: DTypeLike | None = None,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
    **kwargs: Any,
) -> Dataset

Read a data table from an OpenDocument Spreadsheet.

The generic way to read any table is with the read_table function.

Parameters:

Name	Type	Description	Default
file	PathLike | ReadLike	The file to read.	required
cells	str | None	The cells to read. For example, C9 (i.e, specifying only the top-left cell of the table) will start at cell C9 and include all columns to the right and all rows below C9, A:C includes all rows in columns A, B and C, C9:G20 includes only the specified cells, and A:C,E includes all values in columns A, B, C and E. If not specified, assumes that the table starts at cell A1 and returns all cells from the specified sheet.	None
sheet	str | None	The name of the sheet to read the data from. If there is only one sheet in the OpenDocument then you do not need to specify the name of the sheet.	None
as_datetime	bool	Whether dates should be returned as datetime or date objects. If False, dates are returned as a string in the display format of the spreadsheet cell.	True
dtype	DTypeLike | None	The data type(s) to use for the table.	None
invalid_date	str | date | datetime | None	If None, an error is raised if a cell contains a value that is an invalid date. If not None, all cells that contain an invalid date are replaced with the specified value.	None
merged	bool	Applies to cells that are merged with other cells. If False, the value of each unmerged cell is returned, otherwise the same value is returned for all merged cells. In an OpenDocument Spreadsheet, the value of a hidden cell that is merged with a visible cell can still be retained (depends on how the merger was performed).	False
skip_rows	Iterable[int] | None	Row numbers to skip. The row numbers are as shown when the spreadsheet is viewed in the OpenDocument application and are not based on the row number in the data that would be returned. For example, if cells="A3:D10" and you want to skip the data in rows A5:D5 and A7:D7 then skip_rows=[5, 7] achieves the desired result.	None
kwargs	Any	All keyword arguments are passed to ODSReader.	{}

Returns:

Type	Description
Dataset	The table as a Dataset. The header is included in the Metadata.

Source code in src/msl/io/tables.py

def read_table_ods(  # noqa: PLR0913
    file: PathLike | ReadLike,
    *,
    cells: str | None = None,
    sheet: str | None = None,
    as_datetime: bool = True,
    dtype: DTypeLike | None = None,
    invalid_date: str | date | datetime | None = None,
    merged: bool = False,
    skip_rows: Iterable[int] | None = None,
    **kwargs: Any,
) -> Dataset:
    """Read a data table from an OpenDocument Spreadsheet.

    The generic way to read any table is with the [read_table][msl.io.tables.read_table] function.

    Args:
        file: The file to read.
        cells: The cells to read. For example, `C9` (i.e, specifying only the top-left cell
            of the table) will start at cell C9 and include all columns to the right and
            all rows below C9, `A:C` includes all rows in columns A, B and C, `C9:G20`
            includes only the specified cells, and `A:C,E` includes all values in columns
            A, B, C and E. If not specified, assumes that the table starts at cell `A1`
            and returns all cells from the specified `sheet`.
        sheet: The name of the sheet to read the data from. If there is only one sheet
            in the OpenDocument then you do not need to specify the name of the sheet.
        as_datetime: Whether dates should be returned as [datetime][datetime.datetime] or
            [date][datetime.date] objects. If `False`, dates are returned as a string in
            the display format of the spreadsheet cell.
        dtype: The data type(s) to use for the table.
        invalid_date: If `None`, an error is raised if a cell contains a value that
            is an invalid date. If not `None`, all cells that contain an invalid date are
            replaced with the specified value.
        merged: Applies to cells that are merged with other cells. If `False`, the
            value of each unmerged cell is returned, otherwise the same value is
            returned for all merged cells. In an OpenDocument Spreadsheet, the value
            of a hidden cell that is merged with a visible cell can still be retained
            (depends on how the merger was performed).
        skip_rows: Row numbers to skip. The row numbers are as shown when the spreadsheet is
            viewed in the OpenDocument application and are not based on the row number in the
            data that would be returned. For example, if `cells="A3:D10"` and you want to skip
            the data in rows `A5:D5` and `A7:D7` then `skip_rows=[5, 7]` achieves the desired
            result.
        kwargs: All keyword arguments are passed to [ODSReader][msl.io.readers.ods.ODSReader].

    Returns:
        The table as a [Dataset][msl.io.node.Dataset]. The header is included in the
            [Metadata][msl.io.metadata.Metadata].
    """
    file = os.fsdecode(file) if isinstance(file, (bytes, str, os.PathLike)) else str(file.name)
    with ODSReader(file, **kwargs) as ods:
        if cells is not None and _spreadsheet_top_left_regex.match(cells):
            name = sheet or ods.sheet_names()[0]
            num_rows, num_cols = ods.dimensions(name)
            letters = ods.to_letters(num_cols - 1)
            cells += f":{letters}{num_rows}"
        table = ods.read(
            cells, sheet=sheet, as_datetime=as_datetime, merged=merged, invalid_date=invalid_date, skip_rows=skip_rows
        )
    return _spreadsheet_to_dataset(table, file, dtype)

read_table_text

read_table_text(file: PathLike | ReadLike, **kwargs: Any) -> Dataset

Read a data table from a text-based file.

The generic way to read any table is with the read_table function.

Parameters:

Name	Type	Description	Default
file	PathLike | ReadLike	The file to read.	required
kwargs	Any	All keyword arguments are passed to numpy loadtxt. If the delimiter is not specified and the file has .csv as the file extension then the delimiter is automatically set to be , (see extension_delimiter_map for more details).	{}

Returns:

Type	Description
Dataset	The table as a Dataset. The header is included in the Metadata.

Source code in src/msl/io/tables.py

def read_table_text(file: PathLike | ReadLike, **kwargs: Any) -> Dataset:
    """Read a data table from a text-based file.

    The generic way to read any table is with the [read_table][msl.io.tables.read_table] function.

    Args:
        file: The file to read.
        kwargs: All keyword arguments are passed to numpy [loadtxt][numpy.loadtxt]. If the
            `delimiter` is not specified and the `file` has `.csv` as the file
            extension then the `delimiter` is automatically set to be `,` (see
            [extension_delimiter_map][msl.io.tables.extension_delimiter_map]
            for more details).

    Returns:
        The table as a [Dataset][msl.io.node.Dataset]. The header is included in the
            [Metadata][msl.io.metadata.Metadata].
    """
    if kwargs.get("unpack", False):
        msg = "Cannot use the 'unpack' option"
        raise ValueError(msg)

    if "delimiter" not in kwargs:
        ext = get_extension(file).lower()
        kwargs["delimiter"] = extension_delimiter_map.get(ext)

    if "skiprows" not in kwargs:
        kwargs["skiprows"] = 0
    kwargs["skiprows"] += 1  # Reader.get_lines is 1-based, np.loadtxt is 0-based

    first_line = [
        h.decode() if isinstance(h, bytes) else h for h in get_lines(file, kwargs["skiprows"], kwargs["skiprows"])
    ]

    if not first_line:
        header, data = [], np.array([])
    else:
        header = first_line[0].split(kwargs["delimiter"])

        use_cols = kwargs.get("usecols")
        if use_cols:
            if isinstance(use_cols, int):
                use_cols = [use_cols]
            header = [header[i] for i in use_cols]

        dtype = kwargs.get("dtype")
        if isinstance(dtype, str) and dtype[:6].lower().startswith("header"):
            kwargs["dtype"] = _header_dtype(dtype, header)

        # Calling np.loadtxt (on Python 3.5, 3.6 and 3.7) on a file
        # on a mapped drive could raise an OSError. This occurred
        # when a local folder was shared and then mapped on the same
        # computer. Opening the file using open() and then passing
        # in the file handle to np.loadtxt is more universal
        if isinstance(file, (bytes, str, os.PathLike)):
            with Path(os.fsdecode(file)).open() as f:
                data = np.loadtxt(f, **kwargs)
        else:
            data = np.loadtxt(file, **kwargs)

    return Dataset(
        name=get_basename(file), parent=None, read_only=True, data=data, header=np.asarray(header, dtype=str)
    )