Why the Number of Rows Is a Modeling Decision
A data table may contain one row for each policy, customer, or piece of equipment, or it may contain several rows for each entity, such as one row per month. Neither structure is automatically correct. The choice determines what one prediction refers to, what outcome is being predicted, and which population a summary describes.
The key question is not simply, “How many rows should the table have?” It is, “What decision will use each prediction, and when will that prediction be made?” A prediction about whether a policyholder will make any claim next year is not the same as a prediction about whether a claim will occur next month. These questions can use records about the same people while requiring different data designs.
The target population also needs a precise description. It is the set of entities or entity-periods to which the prediction question applies, under the chosen eligibility rules and time window. Changing the record structure can change that target even when the source database is unchanged.
Match the Record to the Prediction Question
A one-record-per-entity design is natural when the intended prediction is made once per entity for a defined horizon. For example, an insurer might want a score at renewal estimating whether a policy will have any claim during the following 12 months. The row represents a policy at renewal, predictors must be available at that time, and the outcome is defined over the next 12 months.
A repeated-record design is natural when predictions are made at multiple times for an entity. A monthly claims-monitoring process might make a new prediction at each month-end for whether a claim will occur in the following month. Each row must identify the entity and its prediction date; predictors and outcome windows are interpreted relative to that date.
The difference is not merely that one table is longer. The first design asks about the entity over a 12-month horizon; the second asks about an eligible entity-month over a one-month horizon. A user might act once per renewal in the first setting and monthly in the second. The prediction unit and outcome should reflect that operational distinction.
| Design | What one row represents | Example prediction question |
|---|---|---|
| One record per entity | One eligible entity at a specified index date | Will this policy have any claim in the next 12 months? |
| Repeated records | One eligible entity at one prediction date or period | Will this policy have a claim in the next month? |
| Aggregated entity record | One entity with a summary over a stated period | Did this machine fail at least once during the year? |
Aggregation can be useful, but it changes which information and outcomes remain visible. Replacing monthly rows with one annual row can remove the timing of events and variation in predictors across months. Expanding an annual row into monthly rows does not create actual monthly predictor histories or valid monthly outcomes if those were never observed.
Worked Example: Annual Policy Prediction or Monthly Monitoring?
A hypothetical insurer has 1,000 policies eligible at a renewal date. During the next 12 months, 80 policies have at least one claim. The insurer is considering two tasks: (A) assigning one renewal-time score for any claim in the next 12 months, and (B) refreshing a score at each month-end for any claim in the following month. For task B, suppose all 1,000 policies are eligible for all 12 months, giving 12,000 policy-month records, with 105 claim-positive policy-months. Choose the record structure and interpret each rate.
Method. Match the row and outcome to the timing of the decision. For task A, the decision is made once at renewal, so use one row per policy at that index date. The outcome is whether the policy has at least one claim during the 12-month horizon.
The observed proportion for task A is \(80/1{,}000=0.08\), or 8.00%. It describes policies with at least one claim in the stated year, among these 1,000 renewal-eligible policies.
For task B, a new prediction is made monthly, so use one row per eligible policy-month. There are \(1{,}000\times12=12{,}000\) rows. The policy-month claim proportion is \(105/12{,}000=0.00875\), or 0.875%. It describes claim-positive months among the eligible policy-months in this hypothetical data.
These rates are not contradictory: the first concerns whether a policy has any claim over 12 months, while the second concerns a claim in a particular one-month period. Nor should the 105 claim-positive months be treated as 105 distinct policies; one policy could contribute more than one positive month. Common mistake avoided: using one annual outcome to answer a monthly prediction question, or treating rows as distinct policies when they are policy-months.
Repeated Records and Implicit Weighting
When entities contribute different numbers of rows, a calculation that gives each row equal weight gives entities different total influence. This follows directly from the arithmetic. Let entity \(j\) contribute \(n_j\) eligible records, and let \(r_j\) be the fraction of those records with a positive outcome. Across all records, the positive-outcome fraction is:
To see why, entity \(j\) has \(n_jr_j\) positive rows by the definition of its row-level fraction. Adding those counts across entities gives the total number of positive rows; adding \(n_j\) gives the total number of rows. Dividing the two totals produces the displayed fraction. Thus, an entity with more eligible records contributes more to the row-level summary.
A different summary first calculates each entity’s own fraction and then gives each entity equal weight:
where \(J\) is the number of entities. These quantities answer different questions. The first is the positive fraction for the observed set of records; the second is the average of the entity-specific fractions, with each entity counted once. Neither is universally preferable. The appropriate one depends on whether the intended target is an eligible record or an entity.
Worked Example: Unequal Numbers of Monthly Records
A hypothetical service program follows three contracts. Contract A has two eligible months with outcomes 1 and 0; contract B has four eligible months with outcomes 1, 1, 0, and 0; contract C has one eligible month with outcome 1. Here, 1 means that a service call occurred in that month. Compare the fraction of positive months with the average contract-specific fraction.
Method. For the row-level fraction, count positive rows and divide by all rows. A has 1 positive month out of 2, B has 2 out of 4, and C has 1 out of 1. There are \(1+2+1=4\) positive rows and \(2+4+1=7\) rows in total, so the row-level fraction is \(4/7=0.5714\), or 57.14% to two decimal places.
For the equal-contract calculation, first find each contract’s positive-month fraction: A has \(1/2=0.5\), B has \(2/4=0.5\), and C has \(1/1=1\). Their average is \((0.5+0.5+1)/3=2/3=0.6667\), or 66.67% to two decimal places.
The row-level result gives more total weight to contracts with more observed months; the equal-contract result gives each contract the same weight. In this example they differ because contract C has the largest positive fraction but the fewest rows. Common mistake avoided: describing \(4/7\) as the average contract’s fraction without checking whether contracts should receive equal weight.
Aggregation Changes What Can Be Predicted
A repeated-record data set can sometimes be summarized to one row per entity, but the summary must preserve the question of interest. For example, “any event during the year” can be formed from monthly binary outcomes by assigning an entity an annual value of 1 if at least one eligible month had an event. That annual label is not the same as the monthly event label. It also does not retain the event month or the number of event months.
The reverse operation is not generally possible. An annual indicator of “at least one event” does not reveal how many months had an event, when the first event occurred, or which monthly predictors were available before it. Creating repeated rows from that annual indicator would not recover those missing details.
Worked Example: Machine-Level Annual Risk Versus Machine-Month Risk
A hypothetical maintenance team tracks 150 machines for a full year, with one eligible record for every machine in each of 12 months. There are 1,800 machine-month records. A failure occurs in 24 machine-months and affects 20 distinct machines. The team wants both a monthly alert and a yearly maintenance plan. Calculate the relevant observed rates and state what each represents.
Method. For a monthly alert, the row is a machine-month and the outcome is failure during that month. The positive fraction is \(24/1{,}800=0.013333\ldots\), which is 0.0133 to four decimal places, or 1.33% to two decimal places.
For a yearly plan based on whether a machine failed at least once, the row is a machine and the outcome is any failure during the year. The positive fraction is \(20/150=0.133333\ldots\), which is 0.1333 to four decimal places, or 13.33% to two decimal places.
The monthly fraction is about the probability of a positive machine-month in this observed set; the annual fraction is the proportion of machines with at least one failure over the year. They have different denominators and outcome periods. The 24 positive months do not imply 24 distinct failed machines; the stated data identify only 20. Common mistake avoided: interpreting a monthly rate as the annual proportion of machines affected, or assuming event-month counts equal entity counts.
A Practical Design Check
Before building the table, state the intended prediction in a sentence that names the unit, index time, outcome, and horizon. For example: “At each month-end, among machines still eligible for monitoring, predict whether that machine will fail during the next month.” This sentence implies repeated machine-month records and makes the intended target explicit.
State whether a prediction is used for an entity once, or for that entity repeatedly at specified times.
For every row, identify the entity and, when relevant, the prediction date or period.
Define what counts as a positive outcome and the time horizon measured from the row’s prediction time.
Say whether it consists of eligible entities or eligible entity-periods, and note any rules that determine eligibility.
Determine whether the analysis gives equal weight to records or to entities, especially when entities contribute different numbers of rows.
A row count is not automatically a count of unique entities. Likewise, a row-level percentage should not be reported as an entity-level percentage unless its denominator and interpretation support that claim. The data design must also match what would actually be known at each prediction time, consistent with the earlier material on prediction timing and predictor availability.
Repeated records from the same entity may be related to one another. The choice between one row and many rows does not, by itself, resolve how that feature should be handled in modeling or evaluation. The essential decision here is to make the prediction unit and target population explicit before interpreting the rows.
Check Your Understanding
For each problem, identify the appropriate row unit and interpret the requested rate or summary.
- A hypothetical portfolio contains 500 policies eligible at renewal; 35 have at least one claim during the next six months. The business makes one decision at renewal. What should one row represent, and what is the observed positive fraction? (Answer: One policy at renewal; \(35/500=0.0700\), or 7.00% with a six-month outcome horizon.)
- A monitoring file has 240 eligible devices in each of five months, with 18 failure-positive device-months. The decision is refreshed monthly. How many rows are there, and what positive fraction do they contain? (Answer: \(240\times5=1{,}200\) device-month rows; \(18/1{,}200=0.0150\), or 1.50%.)
- Two eligible accounts have observed monthly positive fractions of \(1/2\) and \(2/5\), respectively. What is the equal-account average of these fractions? (Answer: \((0.5+0.4)/2=0.45\), or 45.00%.)
- Account A has one positive outcome among two monthly rows, and account B has three positive outcomes among six monthly rows. What is the pooled row fraction, and what is the equal-account average? (Answer: Pooled fraction \(=(1+3)/(2+6)=4/8=0.50\); equal-account average \(=(1/2+3/6)/2=0.50\).)
- A hypothetical study has 100 machines with one annual indicator each; 15 machines failed at least once. The proposed decision is a new alert at each month-end, but no monthly outcome history is available. Can the annual rows be expanded into valid machine-month outcomes, and what is the observed annual fraction? (Answer: No; the annual indicator does not reveal monthly outcomes. The observed annual fraction is \(15/100=0.1500\), or 15.00%.)