0.2 Where this week sits

Week	Topic
1–4	Variable types. Descriptive statistics. Histograms, boxplots, scatter plots, correlation.
5–6	Group presentations. Trend lines. Time series.
7	Visualisation for multivariate data.
8	Missing values: imputation and interpolation.
9	Aggregates and joining tables.
10–11	Data visualisation with ggplot.
12	Individual presentations.

Picking up from last week Week 8 finished on the observation that a join is where missing values are created rather than found. A key present in one table and absent from the other produces a row of NAs that appeared in neither source. Section 3 today shows exactly that happening, and counts what it costs.

0.3

0.3 What you should be able to do by the end

Describe any aggregate as split, apply, combine, and name what is being split on.
Produce grouped summaries in base R with tapply, aggregate, table and ave, and read the dplyr equivalents.
Explain why a summary that collapses rows and a summary that keeps them answer different questions.
Say why a table gets split into several, and identify a primary key and a foreign key.
Predict the row count of an inner, left, right and full join before running it.
Diagnose duplicate keys and unmatched keys, and state what each one does to your result.

The one that matters most Outcome 5. A join runs without complaint whether it returns the rows you expected or ten times as many. The count is the only thing standing between you and a wrong total.

0.4

0.4 Three datasets for this lesson

warpbreaks, for aggregates

54 rows. Number of warp breaks on a loom, under two wool types and three tension levels, nine observations in every combination.

wb <- warpbreaks
str(wb)
#> 'data.frame': 54 obs. of 3 variables:
#>  $ breaks : num  26 30 54 25 70 52 51 26 67 18 ...
#>  $ wool   : Factor w/ 2 levels "A","B"
#>  $ tension: Factor w/ 3 levels "L","M","H"

table(wb$wool, wb$tension)
#>     L M H
#>   A 9 9 9
#>   B 9 9 9

A balanced design, which makes every marginal mean a clean average of the cells beneath it.

Three small tables, for joins

orders, customers and products, built by hand so that every row is printable and every join result can be checked by eye.

USArrests and the state vectors, for the real thing

50 states of crime data, joined to state.name, state.region, state.division and state.area, all of which ship with R.

Nothing to install Every example runs in a fresh R session with no packages. The dplyr version of each command appears in a second tab wherever the two differ.

Section 1

Aggregates

One pattern with three steps. Split the rows into groups, apply a function to each group, combine the answers. Every command in this section is that pattern with different arguments.

1.1

1.1 Split, apply, combine

Splitting warpbreaks on tension gives three groups of 18. Applying mean to each gives three numbers. Combining them gives a table with three rows.

1.2

1.2 Two kinds of answer

The same split can produce a table with one row per group, or the original table with one extra column. Which you want depends on the question.

1.3

1.3 The toolkit

One grouping variable

tapply(wb$breaks, wb$tension, mean)
#>        L        M        H
#> 36.38889 26.38889 21.66667

aggregate(breaks ~ tension, data = wb, FUN = mean)
#>   tension   breaks
#> 1       L 36.38889
#> 2       M 26.38889
#> 3       H 21.66667

# tapply returns a named vector
# aggregate returns a data frame

Two grouping variables

aggregate(breaks ~ wool + tension, wb, mean)

tapply(wb$breaks, list(wb$wool, wb$tension), mean)
#>          L       M        H
#> A 44.55556 24.0000 24.55556
#> B 28.22222 28.7778 18.77778

# counts, no aggregation function needed
table(wb$wool, wb$tension)

# sums laid out as a two-way table
xtabs(breaks ~ wool + tension, data = wb)

Which to reach for aggregate when you want a data frame you will join or plot next. tapply when you want a quick number at the console. table when you only need counts.

The same two results

library(dplyr)

wb %>%
  group_by(tension) %>%
  summarize(mean_breaks = mean(breaks))

wb %>%
  group_by(wool, tension) %>%
  summarize(mean_breaks = mean(breaks),
            .groups = "drop")

Differences worth knowing

summarize names the output column; aggregate reuses the input name.
Grouping by two variables leaves the result grouped by the first unless you set .groups = "drop". Forgetting it changes what the next verb in the chain does.
n() counts rows inside summarize. In base R that is length() or table().
Both drop rows where the grouping variable is NA by default.

For this unit Use base R unless you have been told otherwise. The dplyr forms are here because you will meet them in every workplace and in the Week 10 material.

1.4

1.4 Grouping by two variables

Figure 1.4. Mean breaks in each of the six wool by tension cells, with the standard deviation and the count.

UnitOne tile is one cell of the design: a wool type crossed with a tension level. Nine observations sit behind each tile.
EncodingThe printed number is the cell mean. Shading repeats it, so the tile with the largest mean is darkest.
ScaleMeans run from 18.78 to 44.56 breaks. The shading is relative to the largest cell and carries no separate information.
StructureBreaks fall as tension rises, in both wool types.
Groups and exceptionsThe A/L cell at 44.56 sits far above every other cell. It also carries the largest spread, sd 18.10, against 4.89 in the B/H cell.
Claim and limitLow tension produces more warp breaks, and the effect is much larger for wool A than for wool B. Nine observations per cell is a small sample, and the sd column shows the cells differ widely in spread.

# every cell, with three statistics at once
cell <- aggregate(breaks ~ wool + tension, wb,
                  function(v) c(n    = length(v),
                                mean = mean(v),
                                sd   = sd(v)))

# aggregate returns a matrix column, so flatten it
cell <- do.call(data.frame, cell)
names(cell) <- c("wool", "tension", "n", "mean", "sd")
cell
#>   wool tension n     mean        sd
#> 1    A       L 9 44.55556 18.097644
#> 2    B       L 9 28.22222  9.858724
#> 3    A       M 9 24.00000  8.660254
#> 4    B       M 9 28.77778  9.431036
#> 5    A       H 9 24.55556 10.272671
#> 6    B       H 9 18.77778  4.893305

# the same means as a two-way layout
round(tapply(wb$breaks,
             list(wb$wool, wb$tension), mean), 2)

# Report the count and the spread alongside every
# mean. A mean on its own hides both.

1.5

1.5 The reversal that marginal means hide

Figure 1.5. Cell means joined by wool type. The two lines cross between low and medium tension.

UnitOne point is a cell mean over nine observations. One line joins the three cells for one wool type.
EncodingHorizontal position gives tension, vertical position gives the cell mean, colour gives wool.
ScaleMean breaks from 14 to 50. The vertical axis does not start at zero, so read differences rather than ratios.
StructureBoth wool types break less as tension rises. Wool A falls steeply from low to medium tension; wool B falls gently and then sharply.
Groups and exceptionsAt low tension A averages 44.56 against B's 28.22. At medium tension the order reverses: A 24.00 against B 28.78. The marginal means, A 31.04 and B 25.26, describe an ordering that holds in only two of the three cells.
Claim and limitWhich wool breaks more depends on the tension it is run at. A single grouped mean by wool would report the opposite of what happens at medium tension. The chart cannot say whether the crossing is real or a product of nine observations per cell.

# the marginal means, one variable at a time
tapply(wb$breaks, wb$wool, mean)
#>        A        B
#> 31.03704 25.25926

# the cell means, both variables at once
round(tapply(wb$breaks,
             list(wb$wool, wb$tension), mean), 2)
#>       L     M     H
#> A 44.56 24.00 24.56
#> B 28.22 28.78 18.78

# the picture that shows it
means <- tapply(wb$breaks,
                list(wb$wool, wb$tension), mean)
matplot(t(means), type = "b", pch = 19, lty = 1,
        lwd = 2, col = c("#0072B2", "#D55E00"),
        xaxt = "n", xlab = "tension",
        ylab = "mean breaks")
axis(1, 1:3, colnames(means))
legend("topright", c("wool A", "wool B"), bty = "n",
       col = c("#0072B2", "#D55E00"), lty = 1, lwd = 2)

# Before you report a grouped mean, group by the
# other variables too and check the ordering holds.

1.6

1.6 The function menu

Function	Returns
mean, median	Centre. Median when the group is skewed.
sd, var, IQR	Spread. Report one of these next to every mean.
min, max, range	Extremes.
quantile	Any percentile. Returns several numbers at once.
length	Rows in the group. The dplyr name is `n()`.
function(v) length(unique(v))	Distinct values. The dplyr name is `n_distinct()`.
sum	Totals, and counts of a logical column.

# several statistics in one call
aggregate(breaks ~ tension, wb,
          function(v) c(n = length(v),
                        mean = mean(v),
                        sd = sd(v),
                        iqr = IQR(v)))

# quantiles
aggregate(breaks ~ tension, wb,
          function(v) quantile(v, c(.25, .5, .75)))

# distinct values, base R
length(unique(wb$tension))     #> 3

# counting a condition
aggregate(cbind(high = breaks > 30) ~ tension,
          wb, sum)

One habit Always return the count alongside whatever else you compute. A mean of 44.56 over nine rows and a mean of 44.56 over nine hundred are different findings, and the table alone will not tell them apart.

1.7

1.7 What aggregates do with missing values

Week 8's rules apply inside every group, and the two main functions behave differently.

aq <- airquality

# tapply passes the NAs straight to mean()
tapply(aq$Ozone, aq$Month, mean)
#>  5  6  7  8  9
#> NA NA NA NA NA

tapply(aq$Ozone, aq$Month, mean, na.rm = TRUE)
#>        5        6        7        8        9
#> 23.61538 29.44444 59.11538 59.96154 31.44828

# aggregate with a formula DROPS incomplete rows
# before it groups, silently
aggregate(Ozone ~ Month, aq, mean)
aggregate(Ozone ~ Month, aq, length)
#>   Month Ozone
#> 1     5    26
#> 2     6     9
#> 3     7    26
#> 4     8    26
#> 5     9    29

The trap in those counts

Every month has 30 or 31 days. The counts above are 26, 9, 26, 26 and 29. June contributed nine days to its mean, because 21 of its Ozone readings are missing.

Nothing in the output says so. The June mean of 29.44 looks like the other four, and it rests on a third as much data.

The fix, every time Return the count with the statistic. One extra call turns an invisible problem into a visible one:

aggregate(cbind(mean = Ozone) ~ Month, aq, mean)
aggregate(cbind(n = Ozone) ~ Month, aq, length)

The formula interface uses na.action = na.omit by default. Set na.action = na.pass and supply na.rm = TRUE yourself if you want control over which rows are dropped.

1.8

1.8 Filtering on a group statistic

Figure 1.8. Mean breaks by tension against a threshold of 25. Two groups pass and one does not.

UnitOne bar is one tension level, computed over 18 rows.
EncodingBar height is the group mean. Red marks a group that passes the test, grey one that does not.
ScaleMean breaks from 0 to 42. The dashed rule is the threshold, chosen for this example.
StructureGroup means fall as tension rises, so the test partitions the groups in tension order.
Groups and exceptionsL at 36.39 and M at 26.39 pass. H at 21.67 does not, so all 18 of its rows leave the dataset, including the row with 70 breaks.
Claim and limitA group filter keeps or discards whole groups, never individual rows. A row is judged by the company it keeps, so an extreme value in a low-mean group is discarded and an ordinary value in a high-mean group survives.

# the test is computed once per group
tm <- tapply(wb$breaks, wb$tension, mean)
tm
#>        L        M        H
#> 36.38889 26.38889 21.66667

keep <- names(tm)[tm > 25]
keep                       #> "L" "M"

sub <- wb[wb$tension %in% keep, ]
nrow(sub)                  #> 36
nrow(wb)                   #> 54

# the dplyr form does it in one chain
# wb %>% group_by(tension) %>%
#        filter(mean(breaks) > 25)

# Contrast with a ROW filter, which tests each
# row on its own value:
nrow(wb[wb$breaks > 25, ])     #> 30
# Different rows, different count, different
# question. Say which one you meant.

1.9

1.9 Adding a group statistic to every row

Figure 1.9. All 54 observations, with each cell mean drawn as a rule and each deviation from it as a segment.

UnitOne point is one observation. One black rule is one cell mean over nine points.
EncodingVertical position is the number of breaks. The segment length is the deviation that ave() lets you compute.
ScaleBreaks from 5 to 75, shared across all six cells so the cells are comparable.
StructureEvery cell has points above and below its own mean, and within each cell the deviations sum to exactly zero.
Groups and exceptionsThe A/L cell has both the highest mean and the longest segments. The largest single deviation is +25.44, from an observation of 70 against a cell mean of 44.56.
Claim and limitCentring within groups removes the group effect and leaves the variation inside each group. The deviations are relative to a mean estimated from nine values, so they inherit the uncertainty of that estimate.

# ave() computes a group statistic and returns it
# aligned to the original rows: same length as the
# input, one value per row
wb$cellmean <- ave(wb$breaks, wb$wool, wb$tension)
wb$dev      <- wb$breaks - wb$cellmean

nrow(wb)                          #> 54
round(max(wb$dev), 2)             #> 25.44

# within every cell the deviations sum to zero
tapply(wb$dev, list(wb$wool, wb$tension), sum)
#>   L M H
#> A 0 0 0
#> B 0 0 0

# ave() takes any function, not just the mean
wb$rank <- ave(wb$breaks, wb$tension,
               FUN = function(v) rank(-v))

# the dplyr form
# wb %>% group_by(wool, tension) %>%
#        mutate(dev = breaks - mean(breaks))

1.Q

1.Q Knowledge check: Section 1

Q1. aggregate(Ozone ~ Month, airquality, length) returns 26, 9, 26, 26 and 29 for the five months, each of which has 30 or 31 days. Why?

The default na.action = na.omit removes incomplete rows silently. June contributes 9 days to its mean because 21 of its Ozone readings are missing, and the printed mean gives no hint of it.

Q2. The mean breaks are 31.04 for wool A and 25.26 for wool B. In the medium tension cells they are 24.00 for A and 28.78 for B. What does that tell you?

All four numbers are correct. The wool ordering flips between tension levels, so a summary grouped on wool alone reports something no cell in the data supports. This is the Week 7 reversal reaching you through aggregation.

Q3. What is the difference between summarise and mutate applied to the same grouping?

54 rows in. summarise on tension gives 3 rows out. mutate on tension gives 54 rows out with the group statistic broadcast back to each. Choose by what the next step needs.

Section 2

Joining tables

Data arrives split across tables so that each fact is stored once. A join is how you put the pieces back together for one question, and the choice of join is the choice of which unmatched rows to keep.

2.1

2.1 Why one table becomes three

Storing a customer's name once per order means storing it thousands of times, and changing it in thousands of places. Splitting the table fixes that, and creates the need to join.

2.2

2.2 Keys

Figure 2.2. The orders and customers tables, with every foreign key drawn to the row it points at.

UnitOne row of orders is one order line. One row of customers is one customer.
EncodingShaded columns are keys. A curve joins a foreign key to the primary key it matches; a dashed stub marks a key that matches nothing.
Scale7 order rows and 5 customer rows, the complete tables.
StructureEvery order carries a customer_id, and most of them find a home.
Groups and exceptionsC1 appears in 2 orders and C3 in 3, so the key repeats on the orders side. C9 appears in orders and in no customer record. C4 and C5 appear in customers and in no order.
Claim and limitA primary key identifies a row uniquely; a foreign key points at another table's primary key and can repeat, be absent, or match nothing. Those three possibilities are what make four different joins necessary.

customers <- data.frame(
  customer_id = c("C1","C2","C3","C4","C5"),
  name        = c("Ng","Patel","Okafor","Silva","Tran"),
  city        = c("Brisbane","Perth","Sydney",
                  "Cairns","Hobart"),
  stringsAsFactors = FALSE)

orders <- data.frame(
  order_id    = c("O1","O2","O3","O4","O5","O6","O7"),
  customer_id = c("C1","C1","C2","C3","C3","C3","C9"),
  product_id  = c("P1","P2","P2","P3","P4","P1","P2"),
  qty         = c(1, 2, 1, 5, 1, 3, 2),
  stringsAsFactors = FALSE)

# is the key unique on each side?
anyDuplicated(customers$customer_id)   #> 0
anyDuplicated(orders$customer_id)      #> 2

# which keys sit on only one side?
setdiff(orders$customer_id,
        customers$customer_id)         #> "C9"
setdiff(customers$customer_id,
        orders$customer_id)            #> "C4" "C5"

# Run these three lines before every join.

2.3

2.3 The four joins

Figure 2.3. Which key values survive each join, and the resulting row count.

UnitOne row of the figure is a set of key values with the same fate. One column is one join type.
EncodingA coloured tile means those rows appear in the result; a grey tile means they are dropped. The red bar is the total row count.
ScaleThe same two tables throughout: 7 orders, 5 customers.
StructureEvery join keeps the 6 rows whose keys appear on both sides. The joins differ only in what they do with unmatched keys.
Groups and exceptionsInner keeps 6, left adds C9's row for 7, right adds C4 and C5 for 8, full keeps everything for 9.
Claim and limitThe four joins differ only in which unmatched keys they keep. No join invents or discards a matched row, so the six rows in the middle band are identical across all four results.

inner <- merge(orders, customers, by = "customer_id")
left  <- merge(orders, customers, by = "customer_id",
               all.x = TRUE)
right <- merge(orders, customers, by = "customer_id",
               all.y = TRUE)
full  <- merge(orders, customers, by = "customer_id",
               all   = TRUE)

c(inner = nrow(inner), left = nrow(left),
  right = nrow(right), full = nrow(full))
#> inner  left right  full
#>     6     7     8     9

# all.x keeps every row of the FIRST argument
# all.y keeps every row of the SECOND
# all    keeps every row of both

# Which one is right depends on the question:
#   "orders with their customer details" -> left
#   "orders that we can attribute"        -> inner
#   "every customer, ordering or not"     -> right
#   "a complete audit of both tables"     -> full

2.4

2.4 The arguments

The ones you will use

merge(x, y,
      by     = "customer_id",  # shared key name
      all.x  = FALSE,          # keep unmatched x?
      all.y  = FALSE,          # keep unmatched y?
      suffixes = c(".x", ".y"))

# keys with different names
merge(orders, cust2,
      by.x = "customer_id", by.y = "id")

# more than one key column
merge(a, b, by = c("year", "state"))

# no 'by' at all: merge uses EVERY shared column
# name, which is rarely what you meant

Behaviour worth knowing

The key column appears once, at the front of the result, whatever position it held in the inputs.
Rows come back sorted by the key unless you pass sort = FALSE. The row order of your inputs is not preserved.
Any non-key column name present in both tables gets a suffix.
With no by argument, merge joins on every shared column name at once. Name the key explicitly, every time.

One function per join type

library(dplyr)

inner_join(orders, customers, by = "customer_id")
left_join (orders, customers, by = "customer_id")
right_join(orders, customers, by = "customer_id")
full_join (orders, customers, by = "customer_id")

# keys with different names
orders %>%
  inner_join(cust2, by = c("customer_id" = "id"))

# suffixes
orders %>%
  inner_join(customers, by = "customer_id",
             suffix = c("_order", "_customer"))

# the two that return no new columns
semi_join(orders, customers, by = "customer_id")
anti_join(orders, customers, by = "customer_id")

Differences from merge()

Row order of the left table is preserved.
The key column stays where it was.
Omitting by prints a message naming the columns it chose, rather than proceeding in silence.
semi_join and anti_join have no base R function of their own. In base R they are %in% on a single column.

2.5

2.5 Count the rows before and after

Figure 2.5. Result size and missing-value count for each of the four joins on the same two tables.

UnitOne bar is one join applied to orders and customers.
EncodingBar height is the number of rows returned. Blue marks a result no larger than the orders table; red marks one that is larger.
ScaleRows from 0 to 10. The dashed rule marks the 7 rows of the orders table.
StructureResult size rises from inner through to full, because each join in turn keeps more unmatched keys.
Groups and exceptionsInner returns fewer rows than either input. Full returns more than either input. Missing cells rise from 0 to 8 across the four.
Claim and limitA join can shrink your data, leave it the same size, or grow it. None of the four warns you which happened. On these tables the differences are one or two rows; on real tables with duplicate keys the growth is multiplicative, which Section 3 covers.

for (nm in c("inner", "left", "right", "full")) {
  z <- get(nm)
  cat(sprintf("%-6s rows %d  NA cells %d\n",
              nm, nrow(z), sum(is.na(z))))
}
#> inner  rows 6  NA cells 0
#> left   rows 7  NA cells 2
#> right  rows 8  NA cells 6
#> full   rows 9  NA cells 8

# the habit worth forming
before <- nrow(orders)
after  <- nrow(left)
stopifnot(after == before)   # a left join on a
                             # unique right key
                             # cannot change the
                             # row count

# stopifnot() turns an assumption into an error
# instead of a wrong number further downstream.

2.6

2.6 A join creates missing values

Figure 2.6. The complete full-join result. Red cells were in neither source table.

UnitOne row is one key value paired with whatever each side could supply.
EncodingRed marks a cell the join created. Everything else came from one of the two inputs.
Scale9 rows, 6 columns, 54 cells, of which 8 are new.
StructureThe first six rows are complete, because their keys matched on both sides.
Groups and exceptionsRows 7 and 8 are customers who never ordered, so their order columns are empty. Row 9 is an order whose customer does not exist, so its customer columns are empty.
Claim and limitThese NAs mean ‘no matching row’, which is a different statement from ‘not recorded’. Every technique from Week 8 still applies to them, and imputing them would be a mistake: the right response is to fix the key or to choose a different join.

full <- merge(orders, customers,
              by = "customer_id", all = TRUE)
full[!complete.cases(full), ]
#>   customer_id order_id product_id qty  name   city
#> 7          C4     <NA>       <NA>  NA Silva Cairns
#> 8          C5     <NA>       <NA>  NA  Tran Hobart
#> 9          C9       O7         P2   2  <NA>   <NA>

sum(is.na(full))              #> 8
sum(!complete.cases(full))    #> 3

# Which side failed to supply the row?
full$missing_customer <- is.na(full$name)
full$missing_order    <- is.na(full$order_id)

# Two questions, two different answers:
#   "how many orders can we attribute?"   -> 6
#   "how many customers have we served?"  -> 3
# Neither is nrow(full).

2.7

2.7 Keys with different names, and columns that collide

Different names, same meaning

# customers stores it as 'id', orders as
# 'customer_id'
cust2 <- customers
names(cust2)[1] <- "id"

# option 1: rename first
names(cust2)[1] <- "customer_id"
merge(orders, cust2, by = "customer_id")

# option 2: name both sides in the call
merge(orders, cust2,
      by.x = "customer_id", by.y = "id")
#> 6 rows, key column named customer_id

# dplyr
# inner_join(orders, cust2,
#            by = c("customer_id" = "id"))

Same name, different meaning

a <- data.frame(k = 1:3, v = c(10, 20, 30))
b <- data.frame(k = 2:4, v = c(200, 300, 400))

merge(a, b, by = "k")
#>   k v.x v.y
#> 1 2  20 200
#> 2 3  30 300

merge(a, b, by = "k",
      suffixes = c("_left", "_right"))
#>   k v_left v_right
#> 1 2     20     200
#> 2 3     30     300

Why this matters more than it looks The default suffixes are .x and .y, which say nothing about where each column came from. Three joins later you will be reading v.x.y and guessing. Name the suffixes after the tables.

2.8

2.8 Chaining three tables

# join twice, on two different keys
oc  <- merge(orders, customers, by = "customer_id")
ocp <- merge(oc,     products,  by = "product_id")

nrow(orders)   #> 7
nrow(oc)       #> 6   O7 lost: customer C9 unknown
nrow(ocp)      #> 6   every product_id matched

ocp$revenue <- ocp$qty * ocp$price
ocp[order(ocp$order_id),
    c("order_id","name","item","qty","price","revenue")]
#>  order_id   name     item qty price revenue
#>        O1     Ng Keyboard   1    89      89
#>        O2     Ng  Monitor   2   340     680
#>        O3  Patel  Monitor   1   340     340
#>        O4 Okafor    Cable   5    15      75
#>        O5 Okafor     Dock   1   210     210
#>        O6 Okafor Keyboard   3    89     267

aggregate(revenue ~ name, ocp, sum)
#>     name revenue
#>       Ng     769
#>   Okafor     552
#>    Patel     340

Read the row count at each step

7 orders became 6 at the first join and stayed 6 at the second. That single lost row is order O7.

The order of the joins is a decision Joining orders to customers first, then to products, keeps the orders table as the spine. Starting from customers instead would make customers the spine and change which rows survive. Decide which table is the one you cannot afford to lose rows from, and start there with left joins.

Pipe form

ocp <- orders |>
  merge(customers, by = "customer_id") |>
  merge(products,  by = "product_id")

2.Q

2.Q Knowledge check: Section 2

Q1. You left join a 500-row orders table to a customers table whose key is unique. How many rows can the result have?

A left join keeps every left row once, and a unique right key can match each left row at most once. The count is fixed at 500. If it comes back as anything else, the right key had repeated values, and that is worth an error rather than a shrug.

Q2. merge(a, b) is called with no by argument. What does R join on?

It uses the intersection of the two sets of column names. If both tables happen to have a column called date as well as the key you intended, the join silently requires both to match, and your result quietly shrinks.

Q3. A full join of orders and customers returns 8 NA cells. What do those NAs mean?

They mean absence of a match, which is a structural fact about the key, not a measurement failure. Imputing them would invent a customer or an order that does not exist. Fix the key or change the join.

Section 3

What a join does to your rows

Joins fail quietly. They return a data frame either way, and the number of rows in it is the only warning you get.

3.1

3.1 Duplicate keys multiply rows

Figure 3.1. Joining orders to a visits table. Both tables repeat the key, so every pairing appears in the result.

UnitOne row of the result is one pairing of an order with a visit for the same customer.
EncodingThe right panel counts, for each key value, how many rows each table supplies and how many the join returns.
Scale7 order rows joined to 5 visit rows.
StructureFor every key, the result contributes the product of the two counts, not the sum.
Groups and exceptionsC1 has 2 orders and 2 visits, giving 4 rows. C3 has 3 and 3, giving 9. C2 and C9 have no visits, so an inner join drops them. The result is 13 rows.
Claim and limitJoining 7 rows to 5 rows returned 13. Any sum, mean or count taken on the result is now wrong, and nothing in the output says so. This is the single most common way a join produces a confident, incorrect number.

The row count, in general For each key value $k$, the join contributes $n_x(k) \times n_y(k)$ rows, so the result has $$N = \sum_{k} n_x(k)\, n_y(k)$$ rows. When one side has a unique key every $n(k)$ is 0 or 1, the products collapse, and the count cannot grow.

visits <- data.frame(
  customer_id = c("C1","C1","C3","C3","C3"),
  visit = c("V1","V2","V3","V4","V5"),
  stringsAsFactors = FALSE)

fan <- merge(orders, visits, by = "customer_id")
nrow(orders)   #> 7
nrow(visits)   #> 5
nrow(fan)      #> 13

table(fan$customer_id)
#> C1 C3
#>  4  9

# 2 x 2 = 4 and 3 x 3 = 9

# Detect it before it happens
anyDuplicated(orders$customer_id) > 0   #> TRUE
anyDuplicated(visits$customer_id) > 0   #> TRUE
# Duplicates on BOTH sides is the dangerous case.

# If you only need one row per customer, aggregate
# first and join the summary
vc <- aggregate(visit ~ customer_id, visits, length)
nrow(merge(orders, vc, by = "customer_id"))   #> 6

3.2

3.2 The revenue that disappeared

Figure 3.2. Order revenue after inner joining orders to customers and products. The final row is the one the join removed.

UnitOne row is one order line. Revenue is quantity times unit price.
EncodingThe shaded row is present in the orders table and absent from the join result. The two bars compare the totals.
ScaleDollars, on a common axis running to 2,400.
StructureSix of the seven orders survived the join and produced $1,661 of revenue.
Groups and exceptionsOrder O7 is for 2 monitors at $340, which is $680. It belongs to customer C9, who has no record in the customers table, so the inner join dropped it.
Claim and limitThe reported total is $1,661 against $2,341 actually recorded in the orders table, so 29 per cent of the revenue vanished. The result printed cleanly, the arithmetic on it is correct, and the number is wrong. Only a row count would have caught it.

ocp <- merge(merge(orders, customers,
                   by = "customer_id"),
             products, by = "product_id")
ocp$revenue <- ocp$qty * ocp$price
sum(ocp$revenue)      #> 1661

# what the orders table actually contains
p <- products$price[match(orders$product_id,
                          products$product_id)]
sum(orders$qty * p)   #> 2341

# the gap
1 - 1661 / 2341       #> 0.2904

# the check that would have caught it
nrow(orders)          #> 7
nrow(ocp)             #> 6
stopifnot(nrow(ocp) == nrow(orders))
#> Error: nrow(ocp) == nrow(orders) is not TRUE

# A left join keeps the row and makes the problem
# visible as an NA instead of hiding it
ocl <- merge(orders, customers,
             by = "customer_id", all.x = TRUE)
sum(is.na(ocl$name))  #> 1

3.3

3.3 Three counts before you commit

Figure 3.3. The three questions that decide which join you need, answered on the orders and customers tables.

UnitEach panel counts rows of one table under one condition on the key.
EncodingThe large figure is the count. The line beneath names what that count means for your choice of join.
ScaleOut of 7 orders in the first two panels, and 5 customers in the third.
StructureThe first two panels partition the orders table: 6 matched plus 1 unmatched makes 7.
Groups and exceptionsThe single unmatched order is the row an inner join removes. The two customers who never ordered are the rows a right join adds.
Claim and limitThese three counts fully describe what each join will do. They do not tell you which join is correct; that depends on the question you are answering, and you have to state it.

# semi: order rows that have a customer
sum(orders$customer_id %in%
    customers$customer_id)                    #> 6

# anti: order rows that do not
sum(!orders$customer_id %in%
    customers$customer_id)                    #> 1
orders[!orders$customer_id %in%
       customers$customer_id, ]
#>   order_id customer_id product_id qty
#> 7       O7          C9         P2   2

# anti, the other way: customers who never ordered
customers[!customers$customer_id %in%
          orders$customer_id, ]
#>   customer_id  name   city
#> 4          C4 Silva Cairns
#> 5          C5  Tran Hobart

# the same three with set functions
length(intersect(orders$customer_id,
                 customers$customer_id))      #> 3 keys
setdiff(orders$customer_id,
        customers$customer_id)                #> "C9"
setdiff(customers$customer_id,
        orders$customer_id)                   #> "C4" "C5"

3.4

3.4 A check you can reuse

check_join <- function(x, y, by) {
  cat("left rows        :", nrow(x), "\n")
  cat("right rows       :", nrow(y), "\n")
  cat("dup key in left  :",
      anyDuplicated(x[[by]]) > 0, "\n")
  cat("dup key in right :",
      anyDuplicated(y[[by]]) > 0, "\n")
  cat("keys in left only:",
      length(setdiff(x[[by]], y[[by]])), "\n")
  cat("keys in right only:",
      length(setdiff(y[[by]], x[[by]])), "\n")
  invisible(NULL)
}

check_join(orders, customers, "customer_id")
#> left rows        : 7
#> right rows       : 5
#> dup key in left  : TRUE
#> dup key in right : FALSE
#> keys in left only: 1
#> keys in right only: 2

How to read the six lines

dup in right FALSE	A left join cannot change the row count. Assert it with `stopifnot`.
dup in both TRUE	Fan-out is coming. Aggregate one side first, or accept that the result is a list of pairings and not a list of orders.
keys in left only	Rows an inner join will delete. Here, 1.
keys in right only	Rows a right or full join will add, filled with NA. Here, 2.

Then assert what you expect

before <- nrow(orders)
res <- merge(orders, customers,
             by = "customer_id", all.x = TRUE)
stopifnot(nrow(res) == before)

An assertion that fails is a good afternoon. A wrong total that never fails is a bad quarter.

3.5

3.5 The other kind of combining

Stacking rows

A join matches on a key. rbind stacks tables that already have the same columns, which is what you want when the tables are two months of the same thing rather than two facts about the same entity.

may  <- airquality[airquality$Month == 5, ]
june <- airquality[airquality$Month == 6, ]

both <- rbind(may, june)
nrow(may); nrow(june); nrow(both)
#> 31
#> 30
#> 61

# rbind demands identical column names in the same
# order, and errors if they differ. That strictness
# is the feature.

# many tables at once
parts <- split(airquality, airquality$Month)
nrow(do.call(rbind, parts))   #> 153

Adding columns by position

cbind(a, b)   # glues columns side by side
              # and matches NOTHING

Why cbind is the dangerous one cbind pairs row 1 with row 1 and row 2 with row 2, whatever those rows contain. If either table has been sorted, filtered or aggregated since you last looked, the pairing is wrong and the result still prints. Use a join whenever a key exists.

Choosing between the three

merge	Same entities, different facts, linked by a key.
rbind	Different entities, same facts, same columns.
cbind	Only when the rows are already aligned and you can prove it.

3.Q

3.Q Knowledge check: Section 3

Q1. Table A has 7 rows and table B has 5. Their join returns 13 rows. What happened?

Unmatched keys can add at most one row each, so they cannot take 7 to 13. One key with 2 rows on each side gives 4, another with 3 on each side gives 9, and 4 + 9 = 13. Check anyDuplicated() on both key columns first.

Q2. An inner join of orders to customers drops one order, and the revenue total falls from $2,341 to $1,661. What is the strongest defence against this?

A left join keeps the order and makes the problem visible; the row count assertion turns it into an error rather than a silent 29 per cent shortfall. Imputing a customer would invent an entity, and never joining at all abandons the analysis.

Q3. When is cbind a safe way to combine two data frames?

Equal row counts prove nothing about alignment. cbind pairs by position, so a sort or a filter anywhere upstream silently mismatches every row. If a key exists, join on it.

Section 4

A real pipeline

Join, then group, then summarise, then draw. Four steps, four row counts, and one figure at the end of it.

4.1

4.1 The shape of the whole thing

USArrests holds crime rates per state and no geography. R's state.* vectors hold geography and no crime rates. Neither answers a question about regions until they are joined.

4.2

4.2 Step one: build the key, then join

# USArrests keeps the state in the row names, not
# in a column. A key has to be a column.
arrests <- data.frame(state = rownames(USArrests),
                      USArrests,
                      row.names = NULL,
                      stringsAsFactors = FALSE)
head(arrests, 3)
#>       state Murder Assault UrbanPop Rape
#> 1   Alabama   13.2     236       58 21.2
#> 2    Alaska   10.0     263       48 44.5
#> 3   Arizona    8.1     294       80 31.0

# the four state vectors are parallel, so a data
# frame is one call
meta <- data.frame(
  state    = state.name,
  region   = as.character(state.region),
  division = as.character(state.division),
  area     = state.area,
  stringsAsFactors = FALSE)

check_join(arrests, meta, "state")
#> left rows        : 50
#> right rows       : 50
#> dup key in left  : FALSE
#> dup key in right : FALSE
#> keys in left only: 0
#> keys in right only: 0

sj <- merge(arrests, meta, by = "state")
nrow(sj)   #> 50

Why this join is the easy case

Both keys are unique, so no fan-out is possible.
No key sits on only one side, so all four joins return the same 50 rows.
The check confirmed all of that in six lines, before anything was joined.

The step people skip Moving the row names into a column. USArrests and mtcars both store their identifier there, and a row name is not something you can join on. Every analysis that starts from one of these datasets starts with that line.

Spelling is the whole game This join worked because both sources spell the fifty states identically. Real keys arrive as "NSW" against "New South Wales", or with trailing spaces. Compare setdiff() in both directions before you blame the join.

4.3

4.3 Step two: aggregate what you joined

Figure 4.3. Mean crime rates by census region, after joining 50 states to their region labels.

UnitOne bar is one census region. The n label gives how many states it contains.
EncodingBar length is the regional mean of the state rates. Colour separates regions and carries nothing else.
ScaleRates per 100,000 residents. Each panel has its own scale, so bar lengths are comparable within a panel and not between them.
StructureThe two panels rank the regions almost identically: South highest, Northeast lowest for murder.
Groups and exceptionsThe South averages 11.71 murders per 100,000 against the Northeast's 4.70, a ratio of 2.5 to 1. The Northeast overtakes North Central on assault, 126.67 against 120.33, reversing the murder ordering between those two.
Claim and limitViolent crime rates in 1973 were highest in the South and lowest in the Northeast. These are unweighted means of state rates, so Wyoming counts as much as California. A population-weighted mean would answer a different question and give different numbers.

ag <- aggregate(cbind(Murder, Assault,
                      UrbanPop, Rape) ~ region,
                sj, mean)
ag[, 2:5] <- round(ag[, 2:5], 3)
ag$n <- as.vector(table(sj$region)[ag$region])
ag
#>          region Murder Assault UrbanPop   Rape  n
#>   North Central  5.700 120.333   64.417 18.442 12
#>       Northeast  4.700 126.667   70.556 13.778  9
#>           South 11.706 220.000   59.438 21.163 16
#>            West  7.031 187.231   70.615 29.054 13

sum(ag$n)      #> 50   every state accounted for

barplot(setNames(ag$Murder, ag$region),
        col = "#CC0000", las = 1,
        ylab = "Mean murder rate per 100,000")

# cbind(...) ~ region aggregates four columns in
# one call. Without it you would write four
# separate aggregate() calls and bind the results.

4.4

4.4 When the join loses rows, and when that matters

# a region table covering only the lower 48
cont <- meta[!meta$state %in%
             c("Alaska", "Hawaii"),
             c("state", "region")]
nrow(cont)   #> 48

inn <- merge(arrests, cont, by = "state")
lef <- merge(arrests, cont, by = "state",
             all.x = TRUE)

nrow(inn)                #> 48
nrow(lef)                #> 50
sum(is.na(lef$region))   #> 2
lef$state[is.na(lef$region)]
#> "Alaska" "Hawaii"

# what the two lost states did to the answer
mean(inn$Murder)         #> 7.7938
mean(arrests$Murder)     #> 7.7880

Reading that honestly

Dropping Alaska and Hawaii moved the overall mean murder rate by 0.006, which is nothing. Their rates, 10.0 and 5.3, sit either side of the average and cancel.

The general point Whether a lost row matters depends on the row, not on how many were lost. Week 8 showed 21 missing June days distorting a seasonal analysis. Here two missing states out of fifty changed almost nothing.

The reporting rule either way Say how many rows the join dropped and name them if there are few enough. A reader who knows Alaska and Hawaii were excluded can decide whether that matters for their question. A reader who is told only the final mean cannot.

A left join makes the loss visible as two NAs. An inner join makes it invisible. Prefer the version that leaves evidence.

4.Q

4.Q Knowledge check: Section 4

Q1. Why does an analysis of USArrests by region have to start with data.frame(state = rownames(USArrests), USArrests)?

USArrests stores its identifier as row names, which merge cannot use as a key. mtcars has the same shape. Moving the identifier into a column is the first line of almost every analysis that starts from one of them.

Q2. check_join reports no duplicate keys and no keys on only one side. What follows?

With unique keys on both sides and complete overlap, there are no unmatched rows to keep or drop and no duplicates to multiply. The four joins differ only in their treatment of unmatched keys, and here there are none.

Q3. The regional means are unweighted averages of state rates. What does that mean for the claim?

The means correctly answer 'what is the average state rate in this region'. They do not answer 'what rate does the average resident of this region face', which would need population weights. Say which question you answered.

Section 5

Choosing and reporting

Both halves of this lesson end in the same place. State what you grouped on, state which rows you kept, and give the count.

5.1

5.1 Choosing

What you want	Use	Because
One row per group, for a table or a chart	aggregate, or group_by + summarize	The original rows are no longer needed once you have the group statistic.
Every original row, plus its group's statistic	ave, or group_by + mutate	Centring, ranking within group, and share-of-group all need the original rows.
Every order, with customer details attached	left join, orders on the left	Orders is the table you cannot afford to lose rows from.
Only the orders you can fully attribute	inner join, with the dropped count reported	The choice is defensible; hiding how many rows it cost is not.
Every customer, including those who never ordered	right join, or left join with customers first	Customers with no orders are the finding, not an inconvenience.
A full audit of what matches and what does not	full join, then complete.cases	Both kinds of unmatched row appear in one result.
Two months of the same table, stacked	rbind	There is no key to match on; the rows are new entities, not new facts.

5.2

5.2 Four errors to avoid

The error	Why it happens	What to do instead
Reporting a group mean with no count	The output of `aggregate` looks complete, and the June column of `airquality` rests on 9 days rather than 30 without saying so.	Return `length` alongside every statistic, in the same table.
Grouping on one variable when two matter	The marginal mean is the natural first thing to compute, and it can report an ordering that reverses inside the cells.	Group by the second variable too and check the ordering survives. Draw the interaction plot.
Joining without checking the key	A join returns a data frame whether it lost 30 per cent of your rows or multiplied them by four.	Run the six lines of `check_join`, then assert the expected row count with `stopifnot`.
Using cbind where a join belongs	Two frames have the same number of rows, so gluing them looks safe.	Join on the key. If there is no key, establish why the rows are aligned before you rely on it.

The common thread Each of these produces output that looks finished. The count is what separates a result you can defend from one that merely printed.

5.3

5.3 Summary

Aggregates

Split, apply, combine. Every command in Section 1 is that pattern.
aggregate returns a data frame, tapply a vector, table counts, ave broadcasts a group statistic back to every row.
Collapsing to one row per group and keeping every row answer different questions.
Group means computed one variable at a time can reverse the ordering that holds inside the cells.
The formula interface drops incomplete rows before grouping, and does not say so. Report the count.

Joins

Tables get split so each fact is stored once. A join reassembles them for one question.
A primary key is unique; a foreign key points at one and can repeat, be absent, or match nothing.
The four joins differ only in which unmatched keys they keep: 6, 7, 8 and 9 rows from the same two tables.
Duplicate keys on both sides multiply rows. 7 joined to 5 returned 13.
An inner join dropped one order and 29 per cent of the revenue, silently.
Check the key, then assert the row count. Both take one line.

5.4

5.4 Before next week

Practise

Build the six-cell table from slide 1.4 for ChickWeight, grouping on Diet and a banded Time. Report n, mean and sd for every cell, and check whether any ordering reverses.
Run check_join on arrests against a version of meta from which you have deleted five states at random. Predict all four row counts before you run the joins, then run them.
Reproduce the fan-out on slide 3.1 with your own two tables, and write the one line that would have caught it.
Take the regional means from slide 4.3 and recompute them weighted by state.area. Say which of the two answers your claim needs.

Week 10: Data visualisation with ggplot

Every chart in the next two weeks takes a data frame in the shape this lesson produces: one row per observation, one column per variable, with the grouping variable present as a column rather than implied by the layout.

The link forward ggplot maps columns to visual channels, which is the Week 7 idea with a different syntax. It cannot map something that is not a column, so a variable stored in row names or spread across column headers has to be joined or reshaped in first.

Bring Your ChickWeight cell table, and one sentence on whether the diet ordering held at every time band.

Aggregates and joining tables

0.1 Copyright notice

0.2 Where this week sits

0.3 What you should be able to do by the end

0.4 Three datasets for this lesson

warpbreaks, for aggregates

Three small tables, for joins

USArrests and the state vectors, for the real thing

Aggregates

1.1 Split, apply, combine

1.2 Two kinds of answer

1.3 The toolkit

One grouping variable

Two grouping variables

The same two results

Differences worth knowing

1.4 Grouping by two variables

1.5 The reversal that marginal means hide

1.6 The function menu

1.7 What aggregates do with missing values

The trap in those counts

1.8 Filtering on a group statistic

1.9 Adding a group statistic to every row

1.Q Knowledge check: Section 1

Joining tables

2.1 Why one table becomes three

2.2 Keys

2.3 The four joins

2.4 The arguments

The ones you will use

Behaviour worth knowing

One function per join type

Differences from merge()

2.5 Count the rows before and after

2.6 A join creates missing values

2.7 Keys with different names, and columns that collide

Different names, same meaning

Same name, different meaning

2.8 Chaining three tables

Read the row count at each step

2.Q Knowledge check: Section 2

What a join does to your rows

3.1 Duplicate keys multiply rows

3.2 The revenue that disappeared

3.3 Three counts before you commit

3.4 A check you can reuse

How to read the six lines

3.5 The other kind of combining

Stacking rows

Adding columns by position

Choosing between the three

3.Q Knowledge check: Section 3

A real pipeline

4.1 The shape of the whole thing

4.2 Step one: build the key, then join

Why this join is the easy case

4.3 Step two: aggregate what you joined

4.4 When the join loses rows, and when that matters

Reading that honestly

4.Q Knowledge check: Section 4

Choosing and reporting

5.1 Choosing

5.2 Four errors to avoid

5.3 Summary

Aggregates

Joins

5.4 Before next week

Practise

Week 10: Data visualisation with ggplot

Contents