Why Rows and Columns Need Meaning
A data frame is often the starting point for analysis in R. Its rectangular appearance can make it seem self-explanatory: each row is a record, and each column has a name. But a record is useful only when its meaning is clear. Before interpreting a value or summarizing a column, identify what one row represents and what each column records about that row.
For actuarial work, a data set might have one row per policy, one row per policy-year, or one row per claim. Those choices are not interchangeable. A count of rows is a count of the stored records, not necessarily a count of distinct policies, covered people, or events. The row definition should follow the question being investigated.
An observational unit may be a policy, a person, a vehicle, a claim, or a policy observed during a particular year. A variable records an attribute of that unit, such as region, earned premium, or claim count. An identifier, such as a policy number, helps distinguish units; it is not automatically a useful explanatory variable. A response or outcome column is the quantity the analysis is intended to describe or predict.
The table below has one row for each of four hypothetical policies. The policy number identifies the row’s policy; the remaining columns record attributes or an outcome for that policy.
| Policy ID | Tenure (years) | Region | Annual premium ($) | Had a claim? |
|---|---|---|---|---|
| P101 | 3 | North | 620 | No |
| P102 | 1 | South | 780 | Yes |
| P103 | 5 | North | 910 | No |
| P104 | 2 | East | 700 | Yes |
There are four records and five columns. More importantly, the intended grain is one policy per row. If the question is whether a policy had at least one claim during the stated period, the outcome belongs at the policy level. If the question instead concerns the size of each claim, a policy-level row may not hold enough detail to represent the separate claims.
Establish the Row Grain Before Interpreting Counts
The grain of a data frame is the specific definition of one row. To establish it, state the unit in a complete phrase: “one row represents one policy for the observation period,” for example. If a policy can appear more than once, also state what distinguishes those records, such as different years or separate claims.
A sound row definition lets you answer three questions:
- What entity does one row describe?
- Can the same real-world entity appear in more than one row?
- Does each column describe that row’s entity, or does it refer to another level, such as a household or a claim?
The third question matters because mixed levels can lead to incorrect interpretations. For instance, attaching an annual policy premium to every claim row may cause that premium to appear repeatedly. The repeated value does not mean the policy was charged that amount separately for every claim; it reflects that the data are recorded at the claim level while one column describes the policy.
Worked Example: One Row per Policy
A hypothetical insurer assembles a small data frame for a review of four policies. The rows are P101, P102, P103, and P104, with the values shown in the table above. The review asks what proportion of these policies had at least one claim. Identify the observational unit, the outcome, and the result.
Method. Match the question’s unit, “policies,” to the data frame’s row definition, then count the outcome at that same level.
Step 1: Identify the observational unit. Each of the four rows represents one policy. The row count is 4, and the listed policy IDs are P101, P102, P103, and P104, so the number of distinct policies is also 4.
Step 2: Identify the outcome. “Had a claim?” is the outcome because it records whether each policy had at least one claim. The two “Yes” entries are for P102 and P104.
Step 3: Calculate the requested proportion. There are 2 policies with a “Yes” outcome out of 4 policies:
Check. The two “No” entries and two “Yes” entries account for all four policy rows, and \(50\%+50\%=100\%\). The common mistake avoided here is treating a column as the unit: the outcome column describes policies; it is not itself the set of observational units.
Columns Have Roles, Not Just Names
A column name can suggest a role, but its role depends on the question. In the policy table, “Policy ID” is an identifier, “Region” and “Tenure” describe the policy, and “Had a claim?” is the outcome for the stated review. If the task changed, the outcome could change too. For example, a study of premium amounts might make annual premium the outcome and use other policy attributes as potential predictors.
This distinction also guards against a subtle unit mismatch. A claim count recorded once per policy differs from a claim amount recorded once per claim. Similarly, a policy-level outcome such as “any claim” should not be mistaken for a claim-level outcome such as the cost of an individual claim. State what each column means at the data frame’s grain before assigning it an analytical role.
Repeated Policies and Policy-Year Records
A data frame may contain multiple rows for one policy without being incorrectly structured. The repeated rows are appropriate when each row represents a distinct unit at a finer level, such as one policy-year. In that case, the observational unit is not simply “a policy”; it is “a policy in a particular year.” The year is part of what distinguishes one record from another.
This difference affects denominators. If each row represents a policy-year, the number of rows counts policy-years, while the number of unique policy IDs counts policies. The two counts answer different questions. Likewise, dividing total claims by the number of policies is not the same calculation as dividing by total earned exposure when policies have different exposure amounts.
Worked Example: A Policy Appearing in Two Years
A hypothetical data frame records policy-year observations. It contains three rows: P301 in 2024 has 1.0 earned exposure and 0 claims; P301 in 2025 has 1.0 earned exposure and 2 claims; P302 in 2025 has 0.5 earned exposure and 1 claim. Find the number of policy-year rows, the number of distinct policies, and the aggregate number of claims per unit of earned exposure.
Method. Count rows for policy-years, count distinct IDs for policies, and align the claim total with the exposure total for the aggregate rate.
Step 1: Count policy-year rows. The three records are (P301, 2024), (P301, 2025), and (P302, 2025). Therefore, there are 3 policy-year rows.
Step 2: Count distinct policies. The IDs appearing in the rows are P301 and P302. Therefore, there are 2 distinct policies. P301 appears twice, but it remains one policy.
Step 3: Add the claims. The total is \(0+2+1=3\) claims.
Step 4: Add earned exposure. The total is \(1.0+1.0+0.5=2.5\) exposure units.
Step 5: Divide total claims by total exposure.
Check. The policy-level totals are 2 claims for P301 over 2.0 exposure units, and 1 claim for P302 over 0.5 exposure units. Their combined claims and exposure are still 3 and 2.5, respectively. The common mistake avoided is reporting 3 policy-year rows as 3 distinct policies.
Why an Exposure-Based Aggregate Uses the Row Values
The calculation in the policy-year example is not an arbitrary choice of denominator. When rows have different exposure, the aggregate claims per exposure unit is the total number of claims divided by total exposure. If every row has positive exposure, this is also an exposure-weighted average of the row-specific claim rates.
To see why, let row \(j\) have claim count \(c_j\) and exposure \(e_j>0\). Its rate is \(c_j/e_j\). Weight that rate by its exposure, add the weighted rates, and divide by the total weight:
The equality follows because \(e_j(c_j/e_j)=c_j\) for each row. In the example, the row-specific rates are \(0/1.0=0\), \(2/1.0=2\), and \(1/0.5=2\). Weighting these rates by exposure gives \((1.0\cdot0+1.0\cdot2+0.5\cdot2)/(1.0+1.0+0.5)=3/2.5=1.2\). This agrees with the direct aggregate calculation.
This justification assumes the claim counts and exposure amounts refer to the same row units and observation periods, and that the exposures in the row-rate version are positive. It does not make the rate a policy-level average: the aggregate weights rows according to exposure. If the analysis question asks for a different quantity, such as an unweighted average across policies, the intended unit and calculation must be stated accordingly.
Worked Example: Claim Transactions Are Not Policies
A hypothetical transaction table has four rows: claim C51 for policy P201 has an amount of $240; claim C52 for policy P201 has an amount of $110; claim C53 for policy P202 has an amount of $500; and claim C54 for policy P203 has an amount of $90. Determine the row unit, the number of claim records, the number of distinct policies, and the mean amount per recorded claim.
Method. Interpret the row grain first, then calculate a claim-level average using claim records as the denominator.
Step 1: Identify the row unit. Each row represents one claim transaction, identified by a claim ID. The row unit is a claim, not a policy.
Step 2: Count records and policies separately. There are 4 claim rows: C51, C52, C53, and C54. Their policy IDs are P201, P201, P202, and P203, giving 3 distinct policies.
Step 3: Add the claim amounts. The total is \(240+110+500+90=940\) dollars.
Step 4: Divide by the number of claim records.
Check. The amounts for P201 sum to $350; adding $500 for P202 and $90 for P203 gives $940. The mean is $235 per claim. The common mistake avoided is dividing by 3 distinct policies and calling the result a mean claim amount. That would give \(940/3=\$313.33\) per policy, a different quantity, not the average per claim.
A Practical Row-and-Column Check
Before using a data frame for analysis, write a one-sentence description of its grain and a short interpretation of each important column. Then check that the question and the data frame refer to the same unit. This is a data-design check, not merely a formatting check: an attractive table can still answer the wrong question if its rows represent the wrong entities.
Write exactly what one row represents, including time period or transaction level if relevant.
Identify identifiers, descriptive variables, and the outcome for the particular question.
Ask whether one real-world policy or other entity can appear in multiple rows and why.
Use rows for record counts, distinct entities for entity counts, and a denominator that matches the requested quantity.
For a policy-level question, a policy-level data frame is usually the clearest structure. For a question about individual claims, a claim-level data frame may be appropriate. If the available records use a different grain from the question, make that mismatch explicit rather than silently treating row count as the number of policies.
This way of reading data structures supports the earlier material on defining the analytical problem and evaluation plan: the unit represented in the data should be consistent with the unit for which a prediction or summary is intended. This foundational practice is aligned with the SOA Exam PA (Predictive Analytics) syllabus.
Check Your Understanding
For each problem, identify the row unit carefully before interpreting the counts.
- A hypothetical table has one row for each of 12 policies and includes policy ID, territory, and renewal outcome. What is the row unit, how many rows are there, and what does the number 12 count? (Answer: One policy per row; 12 rows; 12 policy records, representing 12 policies.)
- A hypothetical claim table contains 9 rows. The policy IDs appearing are A17, A17, B04, B04, B04, C21, C21, D10, and D10. How many claim records and distinct policies are represented? (Answer: 9 claim records and 4 distinct policies.)
- A policy-year table has two rows for policy K11, each with exposure 1 and claim count 1, and one row for policy K12 with exposure 0.5 and claim count 1. Find the number of rows, distinct policies, total claims, total exposure, and claims per exposure unit. (Answer: 3 rows, 2 policies, 3 claims, 2.5 exposure units, and 1.2 claims per exposure unit.)
- A hypothetical transaction table records three claims with amounts $80, $120, and $400. The first two claims belong to the same policy, and the third belongs to another policy. What is the mean amount per claim, and why is the number of policies not its denominator? (Answer: $200 per claim; there are 3 claim rows, while 2 policies count entities rather than claims.)
- A data frame has 25 rows, each representing one vehicle observed in a single month. Some policy IDs repeat because a policy may cover multiple vehicles. How many vehicle-month records are present, and can the number of distinct policies be inferred from the row count alone? (Answer: 25 vehicle-month records; no, the distinct policy count cannot be inferred from the row count alone.)