33  What it does to the table

A pipeline is a plan, and a plan can be looked at. The previous chapters showed two ways to look at one: the sentence it wrote, and the query it becomes. show_steps shows a third thing, which is what the table itself is doing.

sales |>
  add(margin = revenue - cost) |>
  keep(margin > 50) |>
  take(3) |>
  show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number add [margin] as ([revenue] - [cost]) date region product quantity revenue cost +margin :number keep where ([margin] > 50) date region product quantity revenue cost margin take 3 date region product quantity revenue cost margin at most 3 rows 
show_steps(
  sales
  >> add(margin = col.revenue - col.cost)
  >> keep(col.margin > 50)
  >> take(3)
)
sales date :text region :text product :text quantity :number revenue :number cost :number add [margin] as ([revenue] - [cost]) date region product quantity revenue cost +margin :number keep where ([margin] > 50) date region product quantity revenue cost margin take 3 date region product quantity revenue cost margin at most 3 rows

Nothing ran. The grammar reads a whole sentence against the table’s columns before any of it executes, which is the rule the rest of this part is built on, and the drawing is that reading rather than a second one.

33.1 What the marks mean

Read a drawing downward. The first band is the table you start from, and every band after it is one step, showing the table as it stands once that step has run.

Beside each step is what the table holds at that point, one name per column. A column the step makes carries a +. A column the step takes away carries a -, drawn on a line under the names that stayed, against the step that removed it, not later. Everything else was already there and still is.

The width is the point. A step’s columns are drawn as one block, so the block is as wide as the table is. A summarize that takes five columns away draws a third of the width of the one above it. A join draws one more column than the table had. You can see what a step did to the table before reading what it was.

A wide table wraps rather than running off the page, and the columns that left wrap the same way. Nothing is counted or hidden, so a table with forty columns is drawn with forty columns.

The type is written beside a column where you have not been told it yet. On the table you start from it stands beside every column; on a column a step has just made, it arrives with the +. Repeating it everywhere would double the width of the drawing to say nothing new.

33.2 Where a column went

The question this answers most often is where a column has gone. summarize is where they usually go.

sales |>
  summarize(gross = total(revenue), by = region) |>
  show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number summarize [gross] as total([revenue]) by [region] region +gross :number -date -product -quantity -revenue -cost one row per group 
show_steps(sales >> summarize(gross = total(col.revenue), by = col.region))
sales date :text region :text product :text quantity :number revenue :number cost :number summarize [gross] as total([revenue]) by [region] region +gross :number -date -product -quantity -revenue -cost one row per group

One step makes gross, and the same step takes away everything that was not grouped by. Here you can see it happen instead of finding out later.

The note underneath says what became of the rows. It appears only where you could not have guessed: that keep returns fewer rows surprises nobody, so no line says it, but summarize collapsing a table to one row per group gets one.

33.3 A second table

A table that arrives partway down gets a line of its own, indented under the step that reads it, with its columns beside its name.

sales |> join(products, by = product) |> show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number join products by [product] date region =product quantity revenue cost +maker :text products =product maker :text matched on product · maker crosses over every row of this table is kept rows may multiply — one for each time products repeats a key 
show_steps(sales >> join(products, by = col.product))
sales date :text region :text product :text quantity :number revenue :number cost :number join products by [product] date region =product quantity revenue cost +maker :text products =product maker :text matched on product · maker crosses over every row of this table is kept rows may multiply — one for each time products repeats a key

Four things show here. The column both tables matched on carries an =, and it carries it twice, once in each table’s row: it is on both sides and it arrives once, not as two columns with invented names. The column that crossed over carries a +, the same mark any new column gets. One line says every row of this table is kept: a sales row with no match in products survives, with maker missing. And the last line says the rows may multiply, because they may: whether products holds a product name more than once is a fact about the data, and the grammar has read the column names without reading a single row.

33.4 What a second table sends

Not every second table sends columns. matching reads one and brings nothing back.

sales |> keep(matching(products, by = product)) |> show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number keep where matching(products, by [product]) date region =product quantity revenue cost products =product maker :text matched on product · no columns cross, only rows go only the rows that match products — never more than it started with 
show_steps(sales >> keep(matching(products, by = col.product)))
sales date :text region :text product :text quantity :number revenue :number cost :number keep where matching(products, by [product]) date region =product quantity revenue cost products =product maker :text matched on product · no columns cross, only rows go only the rows that match products — never more than it started with

Compare the last line with the join above. A join may multiply the rows; a matching never can, because it only decides which rows to keep. The two sentences that ask for them read almost the same and mean different things, and confusing them is the most common mistake anyone makes with two tables. The drawing says which one you wrote.

add_rows is the third case, and it sends rows rather than columns: the two tables already hold the same columns, so nothing arrives and the table only gets longer.

33.5 A sentence that will not run

A sentence the grammar refuses is still drawn, as far as it checked.

sales |>
  summarize(gross = total(revenue), by = region) |>
  keep(product == "Widget") |>
  show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number summarize [gross] as total([revenue]) by [region] region +gross :number -date -product -quantity -revenue -cost one row per group keep where ([product] is "Widget" there is no column called `product`. The table has: region, gross 
show_steps(
  sales
  >> summarize(gross = total(col.revenue), by = col.region)
  >> keep(col.product == "Widget")
)
sales date :text region :text product :text quantity :number revenue :number cost :number summarize [gross] as total([revenue]) by [region] region +gross :number -date -product -quantity -revenue -cost one row per group keep where ([product] is "Widget" there is no column called `product`. The table has: region, gross

The refusal says product is not a column, and it is right: one step earlier, summarize took it away. The message alone tells you the column is missing. The drawing tells you where it went, which is the part you need in order to fix it.

This is the one place show_steps differs from every other way of looking at a pipeline. It draws rather than raising, so a sentence you are still writing can be looked at while it is wrong.

33.6 The drawing’s two forms

The drawing has two forms and you do not have to choose between them. At a console it prints as text, because that is what the rest of a session looks like. In a notebook, or on a page like this one, it draws itself, because a page can hold a picture.

Ask for one outright when you want to keep it, with format in R and with .text and .svg in Python.

cat(format(show_steps(sales |> take(2)), "text"))
sales     date:text  region:text  product:text  quantity:number  revenue:number  cost:number
└ take 2  date  region  product  quantity  revenue  cost
    at most 2 rows
print(show_steps(sales >> take(2)).text)
sales     date:text  region:text  product:text  quantity:number  revenue:number  cost:number
└ take 2  date  region  product  quantity  revenue  cost
    at most 2 rows

The picture is one file with its own colors inside it, so saving it and sending it to somebody gives them the drawing you saw. format(x, "svg") in R, and .svg in Python, hand you that file as text.

That is the whole drawing: the marks, the notes, and the two forms it takes. The refusal’s own words are the next chapter.