Selection: Expand & Fill

This page details the usage of the .expand() and .fill() selection methods.

These methods are used to dynamically create selections for the purposes of extracting data sources that are routinely updated.

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

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

Expand

The expand method is roughly analogous to the act of selecting a cell in a speadsheet and dragging to increase that selection in a specific direction.

For example:

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 3a")

# Select cell "B3" then preview
selection = table.excel_ref("B5")
preview(selection, bounded="A4:H10")

# Then "expand" that selection to the right and preview again
selection = selection.expand(right)
preview(selection, bounded="A4:H10")

Unnamed Selection: 0

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

Unnamed Selection: 0

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

This works the same with multiple starting cells, example follows:

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 3a")

# Select cells "A6" and "A8" then preview
selection = table.excel_ref("A6") | table.excel_ref("A8")
preview(selection, bounded="A4:H10")

# Then "expand" that selection to the right and preview again
selection = selection.expand(right)
preview(selection, bounded="A4:H10")

Unnamed Selection: 0

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

Unnamed Selection: 0

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

Fill

The .fill() method works in a very similar direction to .expand(). The only difference is the originally selected cells are not inlcuded in the final selection.

Example follows:

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 3a")

# Select cells "A6" and "A8" then preview
selection = table.excel_ref("A6") | table.excel_ref("A8")
preview(selection, bounded="A4:H10")

# Then "fill" that selection to the right and preview again
selection = selection.fill(right)
preview(selection, bounded="A4:H10")

Unnamed Selection: 0

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

Unnamed Selection: 0

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12

All directions

The supported directions are up, down, left, right, above, below with above and below being aliases of up and down respectively (in some scenarios they are a more natural fit).

The following is a quick example of each of the four principle directions in use with the .fill() and expand() operators.

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

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

# Fill right
fill_right = table.excel_ref("F6").fill(right).label_as("F6 fill right")

# Expand left
expand_left = table.excel_ref("D5").expand(left).label_as("D5 expand left")

# Expand down
expand_down = table.excel_ref("D6").expand(down).label_as("D6 expand down")

# Fill up
fill_up = table.excel_ref("F9").fill(up).label_as("F9 fill up")

preview(fill_right, expand_left, expand_down, fill_up, bounded="A4:H10")

F6 fill right

D5 expand left

D6 expand down

F9 fill up

Table 3a

	A	B	C	D	E	F	G	H
4	Percentage change period on period
5	Time period	Public new housing	Private new housing	Total new housing	Infrastructure new work	Public other new work	Private industrial new work	Private commercial new work
6	Annual dataset identifier code	MV5H	MV5I	MVM3	MV5J	MV5K	MV5L	MV5M
7	Quarterly dataset identifier code	MV54	MV55	MVM7	MV56	MV57	MV58	MV59
8	Monthly dataset identifier code	MV4O	MV4P	MVM2	MV4Q	MV4R	MV4S	MV4T
9	1998	-19	1	-1.2	-2.8	5.4	1.9	8.4
10	1999	-13.3	-10	-10.3	-2.8	12.6	3.2	12