15 Tidying text

What do you do with " ann marie "? Real tables arrive untidy, and these are the words for that.

The table is messy, two names with the whitespace still on them:

raw	n
ann marie	7
bob	99

run('
  messy
    then add [name] as trim([raw])
    then add [first] as split_text([name], " ", 1),
             [size] as characters([name]),
             [fixed] as replace_text([name], "a", "A")
')

raw	n	name	first	size	fixed
ann marie	7	ann marie	ann	9	Ann mArie
bob	99	bob	bob	3	bob

messy |>
  add(name = trim(raw)) |>
  add(first = split_text(name, " ", 1),
      size = characters(name),
      fixed = replace_text(name, "a", "A"))

raw	n	name	first	size	fixed
ann marie	7	ann marie	ann	9	Ann mArie
bob	99	bob	bob	3	bob

(messy
 >> add(name = trim(col.raw))
 >> add(first = split_text(col.name, " ", 1),
        size = characters(col.name),
        fixed = replace_text(col.name, "a", "A")))

raw	n	name	first	size	fixed
ann marie	7	ann marie	ann	9	Ann mArie
bob	99	bob	bob	3	bob

messy then add [name] as trim([raw]) then add [first] as split_text([name], " ", 1), [size] as characters([name]), [fixed] as replace_text([name], "a", "A")

split_text says which piece it wants, counting from 1, because every value in the grammar is one value. A value with no separator is all one piece, so asking bob for piece 1 gives back bob. Asking it for piece 2 finds no such piece, and the answer is empty text rather than missing.

replace_text looks for the text itself, not for a pattern, so no character in it has a special meaning. characters counts characters; it is not called length, because R’s length counts the elements of a vector. A word that reads as one thing and does another is the trap this vocabulary is built to avoid.

15.1 Putting text back together

join_text is split_text read the other way. It joins two or more values into one, and a separator is written where it goes, as a value, rather than being set somewhere else.

run('
  messy
    then add [name] as trim([raw])
    then add [greeting] as join_text("hello ", [name])
    then pick [greeting, n]
')

greeting	n
hello ann marie	7
hello bob	99

messy |>
  add(name = trim(raw)) |>
  add(greeting = join_text("hello ", name)) |>
  pick(greeting, n)

greeting	n
hello ann marie	7
hello bob	99

(messy
  >> add(name = trim(col.raw))
  >> add(greeting = join_text("hello ", col.name))
  >> pick(col.greeting, col.n))

greeting	n
hello ann marie	7
hello bob	99

messy then add [name] as trim([raw]) then add [greeting] as join_text("hello ", [name]) then pick [greeting, n]

Numbers are refused rather than converted. join_text([raw], [n]) will not run, and the message names to_text, because how a number should look is a decision you make rather than one the grammar makes for you.

messy |> add(both = join_text(raw, n))

Error:
! 
illegal: `join_text` joins text, and this is a number. Convert it first with `to_text(...)`, which is where you say how it should look
  |
2 |   then add [both] as join_text([raw], [n])
  |                                        ^

try:
    collect(messy >> add(both = join_text(col.raw, col.n)))
except GodError as refusal:
    print(refusal)


illegal: `join_text` joins text, and this is a number. Convert it first with `to_text(...)`, which is where you say how it should look
  |
2 |   then add [both] as join_text([raw], [n])
  |                                        ^

A missing value anywhere makes the whole answer missing. That is the rule that addition already follows. It is the one engines disagree about most quietly: several of them drop the absent part and hand back the rest. So a label built from a name nobody recorded comes back looking finished. If you would rather fill the hole than lose the row, say what to fill it with: join_text([first], " ", first_present([last], "")).

15.2 A whole group, run together

join_text reaches across the columns of one row. join_rows reaches down the rows of a group, and hands back one piece of text for all of them. The names carry the direction, because that is the real difference between them.

It is an aggregate, so it belongs in summarize beside total and row_count:

run('
  sales
    then sort [product]
    then summarize [products] as join_rows([product], ", ") by [region]
')

region	products
East	Doohickey, Gadget, Gadget, Sprocket, Widget
North	Doohickey, Gadget, Widget, Widget
West	Doohickey, Gadget, Sprocket, Widget, Widget, Widget

sales |>
  sort(product) |>
  summarize(products = join_rows(product, ", "), by = region)

region	products
East	Doohickey, Gadget, Gadget, Sprocket, Widget
North	Doohickey, Gadget, Widget, Widget
West	Doohickey, Gadget, Sprocket, Widget, Widget, Widget

(sales
  >> sort(col.product)
  >> summarize(products = join_rows(col.product, ", "), by = col.region))

region	products
East	Doohickey, Gadget, Gadget, Sprocket, Widget
North	Doohickey, Gadget, Widget, Widget
West	Doohickey, Gadget, Sprocket, Widget, Widget, Widget

sales then sort [product] then summarize [products] as join_rows([product], ", ") by [region]

The sort is what makes the answer the same every time. join_rows joins the rows in the order they stand, and without a sort that order is whatever the engine had them in. first and last make the same promise.

A missing value is skipped here, where join_text would have made the whole answer missing. The two words answer differently on purpose. An aggregate reduces the rows it is handed, and total already ignores the absent ones: it does not come back missing because a single row was empty. A scalar builds one value out of parts you named, and quietly dropping one would hand back a label that looks finished.

The separator is written out rather than read from a column. The rows are being collapsed into one answer, so a separator that varied by row would have no row left to come from.

15.3 Case, both ways

lower and upper fold a value’s case. They are ordinary functions on a value, so they go anywhere a value goes.

run('
  messy
    then add [shout] as upper(trim([raw])), [quiet] as lower(trim([raw]))
    then pick [shout, quiet]
')

shout	quiet
ANN MARIE	ann marie
BOB	bob

messy |>
  add(shout = upper(trim(raw)), quiet = lower(trim(raw))) |>
  pick(shout, quiet)

shout	quiet
ANN MARIE	ann marie
BOB	bob

(messy
  >> add(shout = upper(trim(col.raw)), quiet = lower(trim(col.raw)))
  >> pick(col.shout, col.quiet))

shout	quiet
ANN MARIE	ann marie
BOB	bob

The same two words also work on a column’s name rather than on its contents, which is how you match names whose capitalization you cannot rely on. That is shown in What a column holds, and it needs no second spelling of either word.

15.4 A number is not text

The repairs read text, and a number is not text, however much it looks like one in a printout. Ask anyway and the refusal names the conversion, which is the next chapter’s subject:

collect(messy |> add(t = trim(n)))

Error:
! 
illegal: `trim` takes the spaces off text, and this is a number. Convert it first with `to_text(...)`
  |
2 |   then add [t] as trim([n])
  |                         ^

try:
    collect(messy >> add(t = trim(col.n)))
except GodError as refusal:
    print(refusal)


illegal: `trim` takes the spaces off text, and this is a number. Convert it first with `to_text(...)`
  |
2 |   then add [t] as trim([n])
  |                         ^

One kind goes in and the declared kind comes out, with nothing silently coerced in between.