Filter Rows

Filter rows by a comparison

❯make a new table that takes the persons table and keeps only the rows where the age column is greater than 18

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — filter() takes a boolean expression, so this is col("age") > 18.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["persons"],
  output_tables=["adults"],
)
def filter_adults(persons: TableFrameSpec) -> TableFrameSpec:
  return persons.filter(col("age") > 18)

⏺

Execution committed, 0 failures.

⏺

subscriptions/adults is live — Bo is 17, so 2 of 3 rows kept:

┌──────┬─────┐
│ name │ age │
├──────┼─────┤
│ Ada  │ 25  │
├──────┼─────┤
│ Cy   │ 30  │
└──────┴─────┘

Transformed tables

persons

name	age
Ada	25
Bo	17
Cy	30

3 rows

adults

name	age
Ada	25
Cy	30

2 rows

Combine conditions

❯make a new table that takes the persons table and keeps only the rows where the age column is greater than 18 and less than 65

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — & joins the two conditions, and each side needs its own parentheses because & binds tighter than >. (col("age") > 18).and_(...) is the method form if you would rather chain.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["persons"],
  output_tables=["working_age"],
)
def filter_working_age(persons: TableFrameSpec) -> TableFrameSpec:
  return persons.filter((col("age") > 18) & (col("age") < 65))

⏺

Execution committed, 0 failures.

⏺

subscriptions/working_age is live — Bo is 17 and Cy is 70, so 1 of 3 rows kept:

┌──────┬─────┐
│ name │ age │
├──────┼─────┤
│ Ada  │ 25  │
└──────┴─────┘

Transformed tables

persons

name	age
Ada	25
Bo	17
Cy	70

3 rows

working_age

name	age
Ada	25

1 row

Filter by range or membership

❯make a new table that takes the items table and keeps only the rows where the price column is between 10 and 100

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — is_between() rather than two comparisons joined by &, and it includes both ends of the range.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["items"],
  output_tables=["mid_price"],
)
def filter_mid_price(items: TableFrameSpec) -> TableFrameSpec:
  return items.filter(col("price").is_between(10.0, 100.0))

⏺

Execution committed, 0 failures.

⏺

subscriptions/mid_price is live — widget is 9.5 and anvil is 250, so 1 of 3 rows kept:

┌────────┬───────┐
│  item  │ price │
├────────┼───────┤
│ gadget │ 42    │
└────────┴───────┘

Transformed tables

items

item	price
widget	9.5
gadget	42
anvil	250

3 rows

mid_price

item	price
gadget	42

1 row

Keep unique rows

❯make a new table that takes the visits table and removes the rows that are duplicated across every column

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — unique() with no arguments compares every column, so a row collapses only when it matches another one completely.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["visits"],
  output_tables=["visits_deduped"],
)
def dedupe_visits(visits: TableFrameSpec) -> TableFrameSpec:
  return visits.unique()

⏺

Execution committed, 0 failures.

⏺

subscriptions/visits_deduped is live — ada/mon appeared twice, so 3 of 4 rows kept:

┌──────┬─────┐
│ user │ day │
├──────┼─────┤
│ ada  │ mon │
├──────┼─────┤
│ bo   │ mon │
├──────┼─────┤
│ cy   │ tue │
└──────┴─────┘

Transformed tables

visits

user	day
ada	mon
bo	mon
ada	mon
cy	tue

4 rows

visits_deduped

user	day
ada	mon
bo	mon
cy	tue

3 rows