Version: 2.0.0

Select and Modify Columns

Projection operations allow you to select, transform, and reshape columns in your data.

Use the Tabsdata MCP Integration with claude code or openai codex to easily create new tables

Select columns by name

make a new table that takes the persons table and selects only the first_name and last_name columns

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — select() takes plain column-name strings.

Called tabsdata 5 times (ctrl+o to expand)

The transformer, registered as fv20260812-2 in subscriptions:

@transformer(
  input_tables=["local_files/persons"],
  output_tables=["person_names"],
)
def select_person_names(persons: TableFrameSpec) -> TableFrameSpec:
  return persons.select("first_name", "last_name")

Execution ep20260812-2 — Committed, 0 failures.

subscriptions/person_names is live — 2 columns, age dropped:

┌────────────┬───────────┐
│ first_name │ last_name │
├────────────┼───────────┤
│ Ada        │ Lovelace  │
├────────────┼───────────┤
│ Grace      │ Hopper    │
└────────────┴───────────┘

Transformed tables

persons

first_name	last_name	age
Ada	Lovelace	36
Grace	Hopper	45

2 rows

person_names

first_name	last_name
Ada	Lovelace
Grace	Hopper

2 rows

Select columns by data type

make a new table that takes the records table and selects only the columns with a numeric type

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — you named a type rather than columns, so this is select() with the cs.numeric() selector, which resolves against whatever columns the table holds at run time.

Called tabsdata 3 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["records"],
  output_tables=["numeric_only"],
)
def select_numeric(records: TableFrameSpec) -> TableFrameSpec:
  return records.select(cs.numeric())

Execution committed, 0 failures.

subscriptions/numeric_only is live — 2 of 4 columns. name is a String and created is a Date, so both dropped out:

┌────┬───────┐
│ id │ price │
├────┼───────┤
│ 1  │ 9.99  │
├────┼───────┤
│ 2  │ 4.50  │
└────┴───────┘

Transformed tables

records

idInt64	priceFloat64	nameString	createdDate
1	9.99	widget	2024-03-15
2	4.50	gadget	2024-11-01

2 rows

numeric_only

idInt64	priceFloat64
1	9.99
2	4.50

2 rows

Select columns by name pattern

make a new table that takes the orders table and selects only the columns whose name starts with "customer"

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — select() with cs.starts_with("customer_"). Matching the prefix rather than listing the two columns survives a third one arriving upstream.

Called tabsdata 3 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["orders"],
  output_tables=["customer_cols"],
)
def select_customer_cols(orders: TableFrameSpec) -> TableFrameSpec:
  return orders.select(cs.starts_with("customer_"))

Execution committed, 0 failures.

subscriptions/customer_cols is live — 2 of 5 columns:

┌─────────────┬───────────────┐
│ customer_id │ customer_name │
├─────────────┼───────────────┤
│ c1          │ Ada           │
├─────────────┼───────────────┤
│ c2          │ Grace         │
└─────────────┴───────────────┘

Transformed tables

orders

customer_id	customer_name	price	amount_1	note_id
c1	Ada	10	5	n1
c2	Grace	20	7	n2

2 rows

customer_cols

customer_id	customer_name
c1	Ada
c2	Grace

2 rows

Select columns by position

make a new table that takes the orders table and selects only the first three columns

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — position rather than name or type, so select() with cs.first(3), counting from the left.

Called tabsdata 3 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["orders"],
  output_tables=["first_three"],
)
def select_first_three(orders: TableFrameSpec) -> TableFrameSpec:
  return orders.select(cs.first(3))

Execution committed, 0 failures.

subscriptions/first_three is live — the leading 3 of 5 columns. A publisher that starts emitting a new column in the middle would change what this picks, without erroring:

┌─────────────┬───────────────┬───────┐
│ customer_id │ customer_name │ price │
├─────────────┼───────────────┼───────┤
│ c1          │ Ada           │ 10    │
├─────────────┼───────────────┼───────┤
│ c2          │ Grace         │ 20    │
└─────────────┴───────────────┴───────┘

Transformed tables

orders

customer_id	customer_name	price	amount_1	note_id
c1	Ada	10	5	n1
c2	Grace	20	7	n2

2 rows

first_three

customer_id	customer_name	price
c1	Ada	10
c2	Grace	20

2 rows

Select everything except some columns

make a new table that takes the records table and selects every column except internal_id and metadata

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — select() with cs.exclude(), which keeps every column you did not name.

Called tabsdata 3 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["records"],
  output_tables=["public_cols"],
)
def select_public_cols(records: TableFrameSpec) -> TableFrameSpec:
  return records.select(cs.exclude("internal_id", "metadata"))

Execution committed, 0 failures.

subscriptions/public_cols is live — id left standing:

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

Transformed tables

records

id	internal_id	metadata
1	x1	seed
2	x2	import

2 rows

public_cols

id
1
2

2 rows

Drop columns

make a new table that takes the records table and drops the col1, col2 and col3 columns

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — drop() names what goes rather than what stays, which is the shorter way round here.

Called tabsdata 3 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["records"],
  output_tables=["records_clean"],
)
def drop_spare_cols(records: TableFrameSpec) -> TableFrameSpec:
  return records.drop("col1", "col2", "col3")

Execution committed, 0 failures.

subscriptions/records_clean is live — id left standing:

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

Transformed tables

records

id	col1	col2	col3
1	a	b	c
2	d	e	f

2 rows

records_clean

id
1
2

2 rows

Add or replace columns

make a new table that takes the orders table and adds a total column, uppercases the name column, and stamps every row with a currency column

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — with_columns() rather than select(), since it keeps every column you do not name and select() would keep only the listed ones.

Called tabsdata 4 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["orders"],
  output_tables=["orders_priced"],
)
def add_totals(orders: TableFrameSpec) -> TableFrameSpec:
  return orders.with_columns(
      (col("price") * col("quantity")).alias("total"),
      col("name").str.to_uppercase().alias("name"),
      lit("USD").alias("currency"),
  )

Execution committed, 0 failures.

subscriptions/orders_priced is live — price and quantity came through untouched, name was replaced in place because the alias reuses its name, and total and currency are new:

┌────────┬───────┬──────────┬───────┬──────────┐
│  name  │ price │ quantity │ total │ currency │
├────────┼───────┼──────────┼───────┼──────────┤
│ WIDGET │ 10    │ 3        │ 30    │ USD      │
├────────┼───────┼──────────┼───────┼──────────┤
│ GADGET │ 4     │ 2        │ 8     │ USD      │
└────────┴───────┴──────────┴───────┴──────────┘

Transformed tables

orders

name	price	quantity
widget	10	3
gadget	4	2

2 rows

orders_priced

name	price	quantity	total	currency
WIDGET	10	3	30	USD
GADGET	4	2	8	USD

2 rows

Add columns with literal values

make a new table that takes the records table and stamps every row with a version, status, is_valid and pi column

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — four constants rather than expressions over existing columns, so with_columns() with lit(), which fills a column with the same value for every row.

Called tabsdata 4 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["records"],
  output_tables=["records_stamped"],
)
def add_literals(records: TableFrameSpec) -> TableFrameSpec:
  return records.with_columns(
      lit(1).alias("version"),
      lit("active").alias("status"),
      lit(True).alias("is_valid"),
      lit(3.14159).alias("pi"),
  )

Execution committed, 0 failures.

subscriptions/records_stamped is live — 4 new columns beside the id that was already there:

┌────┬─────────┬────────┬──────────┬─────────┐
│ id │ version │ status │ is_valid │   pi    │
├────┼─────────┼────────┼──────────┼─────────┤
│ 1  │ 1       │ active │ true     │ 3.14159 │
├────┼─────────┼────────┼──────────┼─────────┤
│ 2  │ 1       │ active │ true     │ 3.14159 │
└────┴─────────┴────────┴──────────┴─────────┘

Transformed tables

records

id
1
2

2 rows

records_stamped

id	version	status	is_valid	pi
1	1	active	true	3.14159
2	1	active	true	3.14159

2 rows

Keep only derived columns

make a new table that takes the orders table and keeps only a full_name column and a price_with_tax column, both derived from other columns

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — "nothing else" makes this select() rather than with_columns(). The expressions read columns that will not survive into the output, which is fine, they are read before the projection happens.

Called tabsdata 4 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["orders"],
  output_tables=["order_summary"],
)
def select_derived(orders: TableFrameSpec) -> TableFrameSpec:
  return orders.select(
      (col("first_name") + " " + col("last_name")).alias("full_name"),
      (col("price") * 1.1).alias("price_with_tax"),
  )

Execution committed, 0 failures.

subscriptions/order_summary is live — first_name, last_name and price are gone, and the columns come out in the order the expressions were listed:

┌──────────────┬────────────────┐
│  full_name   │ price_with_tax │
├──────────────┼────────────────┤
│ Ada Lovelace │ 11             │
├──────────────┼────────────────┤
│ Grace Hopper │ 22             │
└──────────────┴────────────────┘

Transformed tables

orders

first_name	last_name	price
Ada	Lovelace	10
Grace	Hopper	20

2 rows

order_summary

full_name	price_with_tax
Ada Lovelace	11
Grace Hopper	22

2 rows

Rename columns

make a new table that takes the records table and renames the old_name column to new_name and the customer_id column to cust_id

Needs a transformer. Getting its guide.

Called tabsdata 2 times (ctrl+o to expand)

Generating — names only, no values touched, so rename() with a dict of old name to new name.

Called tabsdata 3 times (ctrl+o to expand)

The transformer, registered in subscriptions:

@transformer(
  input_tables=["records"],
  output_tables=["records_renamed"],
)
def rename_cols(records: TableFrameSpec) -> TableFrameSpec:
  return records.rename({
      "old_name": "new_name",
      "customer_id": "cust_id",
  })

Execution committed, 0 failures.

subscriptions/records_renamed is live — same data under new headers. This version carries the new names and every earlier version keeps the old ones, so anything downstream reading old_name breaks on its next run:

┌──────────┬─────────┐
│ new_name │ cust_id │
├──────────┼─────────┤
│ Ada      │ c1      │
├──────────┼─────────┤
│ Grace    │ c2      │
└──────────┴─────────┘

Transformed tables

records

old_name	customer_id
Ada	c1
Grace	c2

2 rows

records_renamed

new_name	cust_id
Ada	c1
Grace	c2

2 rows

Select columns by name​

Select columns by data type​

Select columns by name pattern​

Select columns by position​

Select everything except some columns​

Drop columns​

Add or replace columns​

Add columns with literal values​

Keep only derived columns​

Rename columns​

Select columns by name

Select columns by data type

Select columns by name pattern

Select columns by position

Select everything except some columns

Drop columns

Add or replace columns

Add columns with literal values

Keep only derived columns

Rename columns