Selection: Simple & Useful

Selection: Simple & Useful#

To finish our introduction to cell selection mechanisms we’re going to just list a few simple but useful commands we haven’t touched upon yet.

Source Data#

The data source we’re using for these examples is shown below:

Note - this particular table has some very verbose headers we don’t care about, so we’ll be using bounded= to remove them from the previews as well as to show just the subset of data we’re working with.

The full data source can be downloaded here. We’ll be using th 8th tab named “Table 3a”.

from tidychef import acquire, preview
from tidychef.selection import XlsxSelectable

table: XlsxSelectable = acquire.xlsx.http("https://github.com/mikeAdamss/tidychef/raw/main/tests/fixtures/xlsx/ons-oic.xlsx", tables="Table 3a")
preview(table, bounded="A4:H10")

Table 3a

ABCDEFGH
4Percentage change period on period
5Time periodPublic new housingPrivate new housingTotal new housingInfrastructure new workPublic other new workPrivate industrial new workPrivate commercial new work
6Annual dataset identifier codeMV5HMV5IMVM3MV5JMV5KMV5LMV5M
7Quarterly dataset identifier codeMV54MV55MVM7MV56MV57MV58MV59
8Monthly dataset identifier codeMV4OMV4PMVM2MV4QMV4RMV4SMV4T
91998-191-1.2-2.85.41.98.4
101999-13.3-10-10.3-2.812.63.212

Other simple assertions#

In addition to assert one, you can also use:

  • assert_len(<int>) - assert your selection contains the specified number of cells.

  • assert_single_row() - assert that all currently selected cells are present on a single row.

  • assert_single_column() - assert that all currently selected cells are present in a single column.

Is Blank & Is Not Blank#

Two of the most common methods in tidychef are is_blank() and is_not_blank().

Usage examples follow.

from tidychef import acquire
from tidychef.selection import XlsxSelectable

table: XlsxSelectable = acquire.xlsx.http("https://github.com/mikeAdamss/tidychef/raw/main/tests/fixtures/xlsx/ons-oic.xlsx", tables="Table 3a")

non_blank_cells = table.is_not_blank().label_as("Non Blank Cells")
blank_cells = table.is_blank().label_as("Blank Cells")

preview(non_blank_cells, blank_cells, bounded="A4:H10")
Non Blank Cells
Blank Cells

Table 3a

ABCDEFGH
4Percentage change period on period
5Time periodPublic new housingPrivate new housingTotal new housingInfrastructure new workPublic other new workPrivate industrial new workPrivate commercial new work
6Annual dataset identifier codeMV5HMV5IMVM3MV5JMV5KMV5LMV5M
7Quarterly dataset identifier codeMV54MV55MVM7MV56MV57MV58MV59
8Monthly dataset identifier codeMV4OMV4PMVM2MV4QMV4RMV4SMV4T
91998-191-1.2-2.85.41.98.4
101999-13.3-10-10.3-2.812.63.212

Handling Whitespace In Blanks#

The default behaviour for both is_blank() and is_not_blank() is to treat cells that contain only whitespace as blank.

If you need to change this behaviour (if you want to treat a cell containing only space and/or tabs as not blank) you can pass the keyword disregard_whitespace=False into either method.

i.e

is_blank(disregard_whitespace=False)

and

is_not_blank(disregard_whitespace=False)

Lone Value#

.lone_value() asserts that a selection contains a single cell and if so returns the value contained in that cell.

Assert One#

The assert_one() function is used to quickly confirm that a cell selection contains exactly one cell.

The following will not raise as there is exactly one cell in the selection.

from tidychef import acquire
from tidychef.selection import XlsxSelectable

table: XlsxSelectable = acquire.xlsx.http("https://github.com/mikeAdamss/tidychef/raw/main/tests/fixtures/xlsx/ons-oic.xlsx", tables="Table 3a")
table.excel_ref('A1').assert_one()

<tidychef.selection.xlsx.xlsx.XlsxSelectable at 0x76b172318050>

the following will raise a python AssertionError as there’s more than one cell.

from tidychef import acquire
from tidychef.selection import XlsxSelectable

table: XlsxSelectable = acquire.xlsx.http("https://github.com/mikeAdamss/tidychef/raw/main/tests/fixtures/xlsx/ons-oic.xlsx", tables="Table 3a")
table.excel_ref('A1:A2').assert_one()

---------------------------------------------------------------------------
AssertionError                            Traceback (most recent call last)
Cell In[4], line 5
      2 from tidychef.selection import XlsxSelectable
      4 table: XlsxSelectable = acquire.xlsx.http("https://github.com/mikeAdamss/tidychef/raw/main/tests/fixtures/xlsx/ons-oic.xlsx", tables="Table 3a")
----> 5 table.excel_ref('A1:A2').assert_one()

File ~/.pyenv/versions/3.12.11/lib/python3.12/site-packages/tidychef/selection/selectable.py:74, in Selectable.assert_one(self)
     70 def assert_one(self):
     71     """
     72     Assert that the current selection contains exactly one cell
     73     """
---> 74     return self.assert_len(1)

File ~/.pyenv/versions/3.12.11/lib/python3.12/site-packages/tidychef/selection/selectable.py:66, in Selectable.assert_len(self, number_of_cells)
     60 def assert_len(self, number_of_cells: int):
     61     """
     62     Assert that the current selection contains the number of cells
     63     specified
     64     """
     65     assert (
---> 66         len(self.cells) == number_of_cells
     67     ), f"Selection contains {len(self.cells)} cells, not {number_of_cells}"
     68     return self

AssertionError: Selection contains 2 cells, not 1
Contents