Clean and Sort Data

Handle null values

❯make a new table that takes the contacts table and drops the rows that have a null in any column

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — drop_nulls() with no arguments looks at every column, so one null anywhere takes the row.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["contacts"],
  output_tables=["contacts_complete"],
)
def drop_incomplete(contacts: TableFrameSpec) -> TableFrameSpec:
  return contacts.drop_nulls()

⏺

Execution committed, 0 failures.

⏺

subscriptions/contacts_complete is live — the row with a null email went, 2 of 3 rows kept:

┌──────┬───────────┐
│ name │   email   │
├──────┼───────────┤
│ Ada  │ ada@x.com │
├──────┼───────────┤
│ Cy   │ cy@x.com  │
└──────┴───────────┘

Transformed tables

contacts

name	email
Ada	ada@x.com
Bo	null
Cy	cy@x.com

3 rows

contacts_complete

name	email
Ada	ada@x.com
Cy	cy@x.com

2 rows

Handle NaN values

❯make a new table that takes the readings table and drops the rows that have a NaN in any column

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — drop_nans() rather than drop_nulls(). NaN is a float value, not a missing one, so the null check would leave it in place.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["readings"],
  output_tables=["readings_valid"],
)
def drop_nan_rows(readings: TableFrameSpec) -> TableFrameSpec:
  return readings.drop_nans()

⏺

Execution committed, 0 failures.

⏺

subscriptions/readings_valid is live — the NaN row went:

┌────┬───────┬────────────┐
│ id │ ratio │ percentage │
├────┼───────┼────────────┤
│ 1  │ 0.5   │ 90         │
└────┴───────┴────────────┘

Transformed tables

readings

id	ratio	percentage
1	0.5	90
2	NaN	80
3	0.9	NaN

3 rows

readings_valid

id	ratio	percentage
1	0.5	90

1 row

Sort rows

❯make a new table that takes the events table and sorts the rows by the date column, earliest first

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — sort("date"), which is ascending by default.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["events"],
  output_tables=["events_sorted"],
)
def sort_by_date(events: TableFrameSpec) -> TableFrameSpec:
  return events.sort("date")

⏺

Execution committed, 0 failures.

⏺

subscriptions/events_sorted is live — same rows, earliest date first:

┌──────────┬────────────┬───────┐
│ category │    date    │ value │
├──────────┼────────────┼───────┤
│ a        │ 2024-01-15 │ 9     │
├──────────┼────────────┼───────┤
│ b        │ 2024-03-01 │ 2     │
├──────────┼────────────┼───────┤
│ a        │ 2024-05-20 │ 5     │
└──────────┴────────────┴───────┘

Transformed tables

events

category	date	value
b	2024-03-01	2
a	2024-01-15	9
a	2024-05-20	5

3 rows

events_sorted

category	date	value
a	2024-01-15	9
b	2024-03-01	2
a	2024-05-20	5

3 rows

Change a column type

❯make a new table that takes the records table and casts the id column from text to an integer

⏺

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

⏺

Generating — cast(Int64) on the column through with_columns. The values read the same, the type does not.

Called tabsdata 3 times (ctrl+o to expand)

⏺

The transformer, registered in subscriptions:

@transformer(
  input_tables=["records"],
  output_tables=["records_typed"],
)
def cast_id(records: TableFrameSpec) -> TableFrameSpec:
  expr = col("id").cast(Int64)
  return records.with_columns(expr)

⏺

Execution committed, 0 failures.

⏺

subscriptions/records_typed is live — id is Int64 now:

┌────┐
│ id │
├────┤
│ 1  │
├────┤
│ 2  │
└────┘

Transformed tables

records

idString
1
2

2 rows

records_typed

idInt64
1
2

2 rows