Selection: Shift

Selection: Shift#

The shift() method is move selections of cells in cardinal directions.

This page will show examples of how to do this, along with some simple examples of how you’d use this functionality.’

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 10th tab named “Table 3c”.

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 3c")
preview(table, bounded="A4:H10")

Table 3c

ABCDEFGH
4Percentage change 3 months on previous 3 months
5Time periodPublic new housingPrivate new housingTotal new housingInfrastructure new workPublic other new workPrivate industrial new workPrivate commercial new work
6Dataset identifier codeMVO6MVO7MVO8MVO9MVP2MVP3MVP4
7Jun 20105.69.88.834.33.71.9
8Jul 201025.64.80.2-0.29.73.5
9Aug 20105.54.54.7-2.9-2.924.45.9
10Sep 201011.77.58.5-6.8-3.316.15.3

Shifting Single Directions#

In the following example, we’re going to create one selection by just shifting another selection down.

from tidychef import acquire, preview
from tidychef.selection import XlsxSelectable
from tidychef.direction import right, down

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

# Make the original selection
original_selection = table.excel_ref("B6").expand(right).label_as("Original Selection")

# Create a second selection by shifting the first selection down once
shifted_selection = original_selection.shift(down).label_as("Shifted Selection")

preview(shifted_selection, original_selection, bounded="A4:H10")
Shifted Selection
Original Selection

Table 3c

ABCDEFGH
4Percentage change 3 months on previous 3 months
5Time periodPublic new housingPrivate new housingTotal new housingInfrastructure new workPublic other new workPrivate industrial new workPrivate commercial new work
6Dataset identifier codeMVO6MVO7MVO8MVO9MVP2MVP3MVP4
7Jun 20105.69.88.834.33.71.9
8Jul 201025.64.80.2-0.29.73.5
9Aug 20105.54.54.7-2.9-2.924.45.9
10Sep 201011.77.58.5-6.8-3.316.15.3

Shifting Across More Than One Cell#

In this case we’re going to shift across multiple cells by passing integers to the direction.

In particular, take note of the use of right(3).

from tidychef import acquire, preview
from tidychef.selection import XlsxSelectable
from tidychef.direction import right, down

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

# Make the original selection
original_selection = table.excel_ref("B7").expand(down).label_as("Original Selection")

# Create a second selection by shifting the first selection right three columns
shifted_selection = original_selection.shift(right(3)).label_as("Shifted Selection")

preview(shifted_selection, original_selection, bounded="A4:H10")
Shifted Selection
Original Selection

Table 3c

ABCDEFGH
4Percentage change 3 months on previous 3 months
5Time periodPublic new housingPrivate new housingTotal new housingInfrastructure new workPublic other new workPrivate industrial new workPrivate commercial new work
6Dataset identifier codeMVO6MVO7MVO8MVO9MVP2MVP3MVP4
7Jun 20105.69.88.834.33.71.9
8Jul 201025.64.80.2-0.29.73.5
9Aug 20105.54.54.7-2.9-2.924.45.9
10Sep 201011.77.58.5-6.8-3.316.15.3

An example of using shifts#

In keeping with tidychefs dot notation approach you can chain multiple shift commands to enable movement in a diagonal or otherwise more nuanced direction.

Note - we’re using an anchor here. An Anchor is a handy convention from selecting a cell you don’t want to extract that also holds a useful position in the data structure relative to cells you do want to extract.

We’ll see the anchor pattern crop up more going forward as it’s a key pattern in creating robust repeatable scripts.

 
from tidychef import acquire, preview
from tidychef.selection import XlsxSelectable
from tidychef.direction import right, down, up

table: XlsxSelectable = acquire.xlsx.http("https://github.com/mikeAdamss/tidychef/raw/main/tests/fixtures/xlsx/ons-oic.xlsx", tables="Table 3c")
anchor = table.excel_ref("A6").label_as("This is our anchor, you'd not always preview and never extract this")

# shift right then down, the expand right then down to get observations
observations = anchor.shift(right).shift(down).expand(right).expand(down).label_as("Value")

# shift both up and right to get housing
housing = anchor.shift(right).shift(up).expand(right).label_as("Housing")

# shift right then expand to get the dataset identifier codes
dataset_identifier_codes = anchor.shift(right).expand(right).label_as("Dataset Identifier Code")

# shift down then expand down to get the year
period = anchor.shift(down).expand(down).label_as("Period")

preview(anchor, observations, housing, dataset_identifier_codes, period, bounded="A4:H10")
This is our anchor, you'd not always preview and never extract this
Value
Housing
Dataset Identifier Code
Period

Table 3c

ABCDEFGH
4Percentage change 3 months on previous 3 months
5Time periodPublic new housingPrivate new housingTotal new housingInfrastructure new workPublic other new workPrivate industrial new workPrivate commercial new work
6Dataset identifier codeMVO6MVO7MVO8MVO9MVP2MVP3MVP4
7Jun 20105.69.88.834.33.71.9
8Jul 201025.64.80.2-0.29.73.5
9Aug 20105.54.54.7-2.9-2.924.45.9
10Sep 201011.77.58.5-6.8-3.316.15.3

Going out of bounds#

A typical error when learning to use tidychef is to go “out of bounds”, this means shifting your selection outside the bounds of the data your’e working with.

i.f if your rightmost populated cell is column ‘F’, if you try and shift a selection into column ‘G’ you’re out of bounds.

First, let’s preview the rightmost populated part of this sheet (column O) and select a cell.

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

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

selection = table.excel_ref('O5')

preview(selection, bounded="K4:O10")
Unnamed Selection: 0

Table 3c

KLMNO
4
5Private housing R&MTotal housing R&MNon housing R&MAll R&MAll work
6N3P2N3P3N3P4N3P5N3P6
76.65.26.85.94.9
86.86.12.94.63.4
96.55.423.83.3
106.53.2-2.20.61.8

Now watch what happens if we try and shift that selection right.

selection.shift(right)

---------------------------------------------------------------------------
OutOfBoundsError                          Traceback (most recent call last)
Cell In[6], line 1
----> 1 selection.shift(right)

File ~/.pyenv/versions/3.12.11/lib/python3.12/site-packages/tidychef/utils/decorators/dontmutate.py:24, in dontmutate.<locals>.wrapper(self, *args, **kwargs)
     21 @wraps(method)
     22 def wrapper(self, *args, **kwargs):
     23     self = copy.deepcopy(self)
---> 24     return method(self, *args, **kwargs)

File ~/.pyenv/versions/3.12.11/lib/python3.12/site-packages/tidychef/selection/selectable.py:299, in Selectable.shift(self, direction_or_x, possibly_y)
    296 found_cells = dfc.matching_xy_cells(self.pcells, wanted_cells)
    298 if len(found_cells) == 0 and len(wanted_cells) > 0:
--> 299     raise OutOfBoundsError(
    300         "You are attempting to shift your selection "
    301         "entirely outside of the boundary of the table."
    302     )
    304 self.cells = found_cells
    305 return self

OutOfBoundsError: You are attempting to shift your selection entirely outside of the boundary of the table.
Contents