Why Group by Two Variables?
A summary by one category can hide important differences among the observations in each group. For example, an insurer reviewing average claim amounts by region might also want to distinguish policy type. Grouping by both region and policy type produces a result for each observed combination, such as North–Basic or South–Premium. These combination-specific results are called cell-level summaries.
This is a way to organize and describe data, not a method for establishing why groups differ. A cell mean describes the observed outcome values in that cell; it does not, by itself, show that either grouping variable caused the outcome.
A two-way summary can report counts, outcome means, medians, or other statistics within cells. Counts are especially important: a mean based on one observation is less stable than a mean based on many observations. Keep the grouping variables distinct from the outcome being summarized. For instance, region and policy type may define cells, while claim amount is the numeric outcome summarized within each cell.
Creating Cell Summaries in R
With the dplyr package, place both categorical variables in group_by(). Then use summarise() to calculate statistics separately for each combination. The function n() counts the rows in the current group.
For a data frame named policies with columns region, plan, and claim_amount, a basic summary is:
policies %>% group_by(region, plan) %>% summarise(n = n(), mean_claim = mean(claim_amount), .groups = "drop")
The result has one row per observed region–plan combination. The .groups = "drop" argument removes the grouping structure from the result, so later operations do not accidentally continue to act on those groups. If the outcome contains missing values, use mean(claim_amount, na.rm = TRUE) to calculate the mean from the non-missing values, and report the number of observed outcomes as well as the number of rows.
A cell count is not the same as a proportion. To report the share of each region's policies belonging to each plan, the denominator is the total number of policies in that region. To report each cell's share of the whole data set, the denominator is the overall row count. Always state which denominator is being used.
Worked Example: Claim Amounts by Region and Plan
A hypothetical portfolio has the following claim amounts, in dollars. Find the count and mean claim amount in each region–plan cell, calculate each plan's share within its region, and check the overall mean.
| Region | Plan | Claim amounts |
|---|---|---|
| North | Basic | 100, 200, 300 |
| North | Plus | 80, 120 |
| South | Basic | 150, 250 |
| South | Plus | 400 |
Method. Group by both categorical variables. For each cell, count the values and divide their sum by that count. There are four observed cells.
North–Basic: the count is \(3\), the sum is \(100+200+300=600\), and the mean is \(600/3=200\) dollars.
North–Plus: the count is \(2\), the sum is \(80+120=200\), and the mean is \(200/2=100\) dollars.
South–Basic: the count is \(2\), the sum is \(150+250=400\), and the mean is \(400/2=200\) dollars.
South–Plus: the count is \(1\), the sum is \(400\), and the mean is \(400/1=400\) dollars.
The region totals are \(3+2=5\) policies in North and \(2+1=3\) in South. Within North, the Basic share is \(3/5=0.60\) and the Plus share is \(2/5=0.40\). Within South, the Basic share is \(2/3\), approximately \(0.6667\), and the Plus share is \(1/3\), approximately \(0.3333\). Each pair of within-region shares sums to \(1\).
To check the overall mean, add the cell sums: \(600+200+400+400=1600\). There are \(3+2+2+1=8\) policies, so the overall mean is \(1600/8=200\) dollars.
In R, the cell summaries can be obtained with policies %>% group_by(region, plan) %>% summarise(n = n(), mean_claim = mean(claim_amount), .groups = "drop"). The common mistake this method avoids is treating each cell as equally large when combining its mean into an overall result.
Why Cell Means Must Be Weighted
The overall mean is not generally the simple average of the cell means. Each cell contributes to the overall mean in proportion to its number of observations. If the cells are indexed by \(a,b\), and all outcome values are observed, then the overall mean is:
where \(\bar{Y}_{ab}\) is the mean outcome in cell \((a,b)\). This formula applies when the cells partition the observations being averaged and the cell counts match the observations used to calculate the cell means. If outcome values are missing, use the number of non-missing outcomes in each cell as the weight, not the total number of rows in that cell.
The justification follows directly from the definition of a mean. In a cell, the sum of its outcome values is \(n_{ab}\bar{Y}_{ab}\). Adding these cell sums gives the total of all outcomes. Dividing by the total number of outcomes gives the overall mean:
This also explains why an unweighted average of cell means can be wrong: it gives a small cell the same influence as a large cell, even though the large cell contains more observations.
Worked Example: Combining Cell Claim Rates
A hypothetical insurer records whether each policy had a claim during the year, using 1 for a claim and 0 for no claim. Calculate the claim rate in each region–vehicle cell, then calculate the overall rate. The table gives the number of policies and the number with a claim.
| Region | Vehicle class | Policies | Policies with a claim |
|---|---|---|---|
| Urban | Compact | 10 | 2 |
| Urban | Utility | 10 | 3 |
| Rural | Compact | 5 | 1 |
| Rural | Utility | 5 | 4 |
Method. In a cell, the mean of a 0/1 outcome equals the proportion of 1s: the sum of the indicators is the claim count, and the mean divides that count by the number of policies.
Urban–Compact: the rate is \(2/10=0.20\). Urban–Utility: the rate is \(3/10=0.30\). Rural–Compact: the rate is \(1/5=0.20\). Rural–Utility: the rate is \(4/5=0.80\).
There are \(2+3+1+4=10\) claims among \(10+10+5+5=30\) policies. Therefore, the overall rate is \(10/30=1/3\), or approximately \(0.3333\). As a weighted check, the cell contributions to the claim count are \(10(0.20)=2\), \(10(0.30)=3\), \(5(0.20)=1\), and \(5(0.80)=4\). They sum to 10; dividing by the total count of 30 again gives \(0.3333\).
The unweighted average of the four rates is \((0.20+0.30+0.20+0.80)/4=0.375\), which is not the portfolio rate. It gives each cell equal influence even though the cells have different counts. In R, summarise a 0/1 outcome with mean(claim_indicator) and also report n = n() for context.
Observed Combinations and Missing Outcomes
A grouped summary normally contains rows for combinations that occur in the data. If no observation has a particular combination of categories, that combination may not appear in the result at all. This is not the same as observing a count of zero, and it is not evidence that an outcome value is zero. When interpreting a table, distinguish an absent combination from an observed cell whose outcome happens to be zero.
Missing outcome values create a separate issue. The row still belongs to its categorical cell, so n() includes it. But a mean calculated with missing values removed does not use that row. Reporting both the row count and the number of observed outcomes makes the denominator visible:
policies %>% group_by(region, plan) %>% summarise(n_rows = n(), n_observed = sum(!is.na(claim_amount)), mean_claim = mean(claim_amount, na.rm = TRUE), .groups = "drop")
If a cell has rows but all its outcomes are missing, there is no observed mean to interpret. In R, mean(x, na.rm = TRUE) returns NaN when no non-missing values remain. Do not report that result as a zero mean; report that the cell has no observed outcomes.
Worked Example: Counts and Means with Missing Values
A hypothetical service team records resolution times in hours by territory and service tier. The recorded values are shown below. Find the row count, observed-outcome count, and mean for each observed cell, and identify any unobserved combination.
| Territory | Tier | Resolution times |
|---|---|---|
| Coastal | Standard | 10, 20, NA |
| Coastal | Priority | 40 |
| Inland | Standard | 30, 50 |
Coastal–Standard: there are \(3\) rows, of which \(2\) outcomes are observed. The observed sum is \(10+20=30\), so the mean is \(30/2=15\) hours. The missing value contributes to the row count but not to the mean denominator.
Coastal–Priority: there is \(1\) row and \(1\) observed outcome. Its mean is \(40/1=40\) hours.
Inland–Standard: there are \(2\) rows and \(2\) observed outcomes. Their sum is \(30+50=80\), so the mean is \(80/2=40\) hours.
Inland–Priority: this combination has no rows in the supplied data. It is unobserved, not a cell with a measured mean of zero. A suitable R summary would use n_rows = n(), n_observed = sum(!is.na(time)), and mean_time = mean(time, na.rm = TRUE) after grouping by territory and tier. The common mistake avoided here is using the row count as the mean denominator when an outcome is missing.
Common Mistakes and a Practical Sequence
Before interpreting a two-variable summary, check what each row represents, which categories define the cells, and which outcome is summarized. Then verify the counts and denominators. A clear sequence reduces errors:
Identify the two categorical grouping variables and the outcome. Confirm that each row represents one observation for the analysis.
Group by both categories, then calculate a cell count and the requested outcome statistic.
For proportions, state whether the denominator is a row total, a column total, or the overall total. For means with missing outcomes, count the observed outcomes.
Confirm that cell counts add to the number of included rows and that weighted cell means reproduce the overall mean when the same observed outcomes are used.
A common exam trap is to call a cell proportion “the proportion of all policies” without checking its denominator. For example, \(3/5\) for North–Basic in the first example is the Basic share within North, not the share of the entire portfolio. The portfolio share is \(3/8\), because there are 3 North–Basic policies among 8 policies overall. The numerator is unchanged; the question determines the denominator.
Two-way summaries are useful because they retain detail that a single overall or one-variable summary can conceal. But a cell result should always be read alongside its count, its definition, and its denominator. This disciplined descriptive work is aligned with the SOA Exam PA (Predictive Analytics) syllabus.
Check Your Understanding
For each problem, work out the requested cell or marginal summaries and check the relevant denominators.
- A hypothetical portfolio has 4 West–Basic policies, 2 West–Plus policies, 3 East–Basic policies, and 1 East–Plus policy. Find the West total and the share of West policies that are Plus. (Answer: West total \(=6\); West Plus share \(=2/6=0.3333\).)
- Two cells have outcome values North–A: \(6, 12, 18\), and South–A: \(20\). Find both cell means and the overall mean. (Answer: cell means \(12\) and \(20\); overall mean \(14\).)
- A hypothetical 0/1 outcome has 3 successes among 12 observations in Cell 1 and 6 successes among 8 observations in Cell 2. Find both cell rates and the overall rate. (Answer: \(0.2500\), \(0.7500\), and \(9/20=0.4500\).)
- A cell has values \(5, 15, \text{NA}\), while another cell has values \(25, 35\). For each, report row count, observed count, and observed mean; then give the overall observed mean. (Answer: first cell \(3,2,10\); second cell \(2,2,30\); overall observed mean \(20\).)
- A summary reports cell counts \(2\) and \(6\), with respective outcome means \(10\) and \(30\). A candidate reports the overall mean as \(20\). Find the correct overall mean and explain the discrepancy. (Answer: \((2(10)+6(30))/8=25\); the value \(20\) is the incorrect unweighted average of cell means.)