Capstone: a sales report in base R

The table, built by hand

The report below starts from a fixed table of six sales lines: a region, an item, a number of units and a price each. Nothing is read from anywhere, so the output is the same on every Run — which is exactly what you want while you are learning the shape of a report.

The first move is the one from the data frame chapter: compute a column from the columns beside it. One assignment gives every line its own total, and from there the report is arithmetic over a vector. The region column is built with rep(c("north", "south"), 3), which states the alternating pattern instead of listing six values and hoping the pattern survives an edit.

One line per row, and lined up

sprintf is how a report gets straight edges. %-6s prints a string in a field six characters wide and pads it on the right, and %8.2f prints a number with two decimal places in a field eight wide, padded on the left. A loop over 1:nrow(sales) visits every row by position.

The header line is written to match those widths, which is why it is a plain string with the spaces typed in. Getting the header and the rows to agree is the whole craft of a text report. The widths are a contract: %-6s in the header and %-6s in the row mean the same six columns, and a header typed by eye drifts the first time a value is longer than its field.

Totals, groups, and the winner

aggregate gives one total per region, and order(..., decreasing = TRUE) followed by indexing gives the single best line. The share of the total is a two-line calculation: select the rows of one region with a logical test, sum them, and divide by the grand total. which.max is the shorter route when you only need one position, and order is the one to use when the whole table has to be rearranged.

That is the shape of nearly every reporting job you will ever be handed: build a table, compute a column, group it, sort it, format the lines. The tools change with the language; the shape does not.

The pieces of the report

sprintf("%-6s %5s %8.2f", "north", 12, 150)

for (i in 1:nrow(sales)) {
  cat(sprintf("%-6s %8.2f\n", sales$region[i],
              sales$revenue[i]))
}

aggregate(revenue ~ region, data = sales, FUN = sum)

best <- sales[order(sales$revenue, decreasing = TRUE), ]
best$item[1]

north <- sum(sales$revenue[sales$region == "north"])
100 * north / sum(sales$revenue)

Build the report in layers

A report is easier to trust when each layer does one job. First build the table and compute the numeric columns at full precision. Then group and sort. Only at the last moment turn numbers into strings with sprintf, because formatted text cannot be added up again. Keeping raw and printed values apart is what lets you check the total after the lines are printed.

The grand total should be computed once from the data and reused, not recomputed from the formatted lines. If the lines are rounded and the total is not, the two will differ by a few cents; that is normal, and it is why a report should say which total is authoritative. A share column is the same idea: compute the ratio in numbers, then format it with %.1f%%.

Make it survive a change

A report that hard-codes six rows breaks the day a seventh sale arrives. The loop over 1:nrow(sales) already follows the table, and aggregate already discovers its groups. Names are the other kind of hard-coding: refer to columns by name rather than by position, so inserting a column does not silently move the one you meant.

Two guards keep a report honest. Check that the parts add to the whole before printing, and guard a division when the total could be zero. Neither guard is visible in the output when things go well, which is exactly the point: they are there for the day the data changes shape.

Try these next

• Add a seventh sale in a new region and check that the loop, the totals and the share all follow it.
• Add a tax line at 20 percent of the grand total, formatted to two decimals.
• Print the regions as a share table with a total row, and check that the shares sum to 100.