Why Repeated Measures Need a Clear Row Structure
A repeated-measures table can store the same information in more than one layout. In a wide table, different measurement occasions occupy different columns. In a long table, each row records one subject’s outcome at one measurement occasion. For clustered and longitudinal analyses, this long layout makes the subject identifier and measurement time explicit for each outcome record.
Reshaping changes the arrangement of the data, not the measurements themselves. It does not make repeated observations independent, repair incorrect identifiers, or decide which observations belong in an analysis. The main task is to create a reliable mapping from each wide-table value to the subject and occasion that produced it.
A subject-time pair should identify at most one outcome record when the design allows only one measurement of that outcome per subject at that occasion. If there are multiple legitimate measurements within an occasion, the key must include another identifier, such as replicate number or collection date. Reshaping cannot resolve an ambiguous measurement key; that ambiguity must be understood from the study design and records.
Mapping Wide Columns to Subject and Time
Before changing the layout, identify which columns are identifiers, which contain outcomes, and what occasion each outcome column represents. For example, columns named “week 0,” “week 4,” and “week 12” encode time in their names. A long-format time field should represent those same occasions consistently, such as numeric weeks 0, 4, and 12. If the occasion is a category without meaningful spacing, such as fall and spring terms, a categorical time field may be more faithful than assigning arbitrary numeric values.
The reshape is straightforward only when the source columns have an unambiguous correspondence to occasions and the subject identifier is correct. Identifier fields and other subject-level variables—such as clinic or baseline age—are repeated on each resulting row for that subject. They must not be mistaken for outcome columns.
Choose the subject identifier and define what distinguishes occasions. If an occasion can contain multiple records, identify the additional key needed.
List the wide columns that contain measurements of the same outcome. Record the occasion associated with each column, rather than relying on a vague naming pattern.
For each subject and occasion, place the subject-level fields, the occasion value, and the corresponding outcome together on one row.
Check keys, counts, missingness, and selected values against the source table before using the reshaped data.
Worked Examples
Worked Example: Patient Visits with a Missed Measurement
Suppose a hypothetical clinic table records pain scores for three patients at baseline, week 4, and week 12. The scores range from 0 to 10. A blank means the visit was planned but no score was recorded.
| Patient | Clinic | Week 0 | Week 4 | Week 12 |
|---|---|---|---|---|
| P01 | A | 8 | 6 | 4 |
| P02 | B | 5 | 4 | 3 |
| P03 | A | 7 | 6 | blank |
The subject identifier is Patient. Clinic is a subject-level field, and the three week columns are the outcome columns. Reshape them into fields named, for example, Patient, Clinic, Week, and Pain score. P01’s week 4 value becomes one row with Patient P01, Clinic A, Week 4, and Pain score 6. P03’s planned week 12 occasion becomes a row with Patient P03, Clinic A, Week 12, and a missing Pain score.
There are \(3 \times 3 = 9\) planned subject-occasion combinations, so retaining the missed occasion gives 9 rows. Eight scores are observed, because the source table has eight nonblank outcome values. The reshape must therefore produce 9 rows but only 8 observed scores. Dropping the missing row would instead produce 8 rows and would no longer represent all planned occasions. Whether to retain a row for a missed visit depends on the analysis data convention, but the distinction between “not measured” and “measured as zero” must be preserved.
Worked Example: Student Outcomes by Term
Consider hypothetical reading scores for two students across three terms. Each student has one score in each term.
| Student | Fall | Winter | Spring |
|---|---|---|---|
| S10 | 72 | 78 | 81 |
| S11 | 68 | 70 | 76 |
The wide table has two subject rows and three outcome columns. The long table has \(2 \times 3 = 6\) records. Each record contains a Student value, a Term value, and the score from the matching term column. For example, S11’s spring record has Term = Spring and score 76; it must not be paired with the winter score of 70.
Term should be stored in the intended order—Fall, Winter, Spring—if ordering matters for displays or summaries. It can remain a categorical occasion label. Replacing terms with 1, 2, and 3 is convenient for ordering, but does not establish that the gaps between terms are equal in elapsed time. The data definition, not the shape of the table, determines what the time values mean.
Worked Example: Checking the Subject-Time Key
A hypothetical follow-up table has four records after reshaping: subject A at weeks 0 and 4, and subject B at weeks 0 and 4. The recorded outcomes are 12, 10, 9, and 8, respectively. The intended design allows one outcome per subject per week.
The key consists of Subject and Week. Its four pairs are (A, 0), (A, 4), (B, 0), and (B, 4); all are distinct. Thus the table has four records and four unique subject-week pairs, as expected from \(2 \times 2 = 4\) planned combinations. The observed values should also match the corresponding source cells: A at week 4 remains 10, and B at week 0 remains 9.
Now suppose the table instead contains two rows for (A, 4), with outcomes 10 and 11. This is not a valid one-record-per-subject-week table. It might signal a duplicated row, or it might reflect two legitimate measurements that need a replicate or measurement-date field. Averaging the two values merely to force a unique key would change the data and conceal the unresolved design question.
Why the Basic Checks Work
For a complete wide table with \(n\) subjects and \(m\) outcome columns, there are \(m\) occasion values for each subject. A one-row-per-subject-occasion reshape must therefore have \(n m\) rows. This count is a useful check, but it is not enough by itself: a table can have the right number of rows while assigning values to the wrong occasions.
If each subject-occasion combination is intended to occur once, the number of unique pairs must also be \(n m\). In a complete table, row count equal to \(n m\), together with \(n m\) unique keys, means that no pair is duplicated and none is absent. For incomplete data, the expected count must instead reflect the occasions actually retained; a planned-but-missing outcome may still have a row.
A value-preservation check provides a separate safeguard. Each nonmissing source cell should appear exactly once in the long outcome field, attached to the same subject and occasion. This follows directly from the reshape mapping: the cell at subject \(i\) and occasion \(j\) is assigned to the long record with that same subject \(i\) and occasion \(j\). If values are sorted or displayed differently afterward, compare by keys rather than by row position.
Multiple Outcomes, Alternatives, and Limitations
The one-row-per-occasion definition assumes one outcome is being reshaped. If the wide table contains both systolic and diastolic blood pressure at each visit, each subject-visit pair has two measurements. One option is to maintain separate long outcome fields, such as systolic and diastolic values, so that each row represents a visit with both outcomes. Another is to create a stacked outcome field and an outcome-type variable; then the key for a measurement includes subject, visit, and outcome type. The choice depends on the intended record structure. Do not stack different outcomes into one column without retaining which outcome each value represents.
A wide table can be convenient for data entry or for viewing a small number of fixed occasions. Long format is useful when occasions vary across subjects, when the subject and time need to be explicit on every outcome record, or when downstream software expects one record per observation. A third option is to summarize across occasions, but that discards the occasion-specific outcomes and is not a reshape. A summary table cannot substitute for repeated-measure records when the analysis needs the pattern over time.
Reshaping also has limits. It does not determine whether a visit is truly missing, correct errors in a subject ID, establish the right time scale, or specify a statistical model. If one subject has several visits recorded under the same nominal week, first determine whether those are duplicates or distinct measurements. Retain enough identifiers to distinguish valid records rather than silently deleting or combining them.
Diagnostics and Common Mistakes
After reshaping, check the number of subjects, the occasion labels, and the number of rows per subject. For a design with the same planned occasions for everyone, compare each subject’s occasion set with the planned set. Inspect the minimum and maximum time values, and look for misspelled labels such as “Wk4” and “Week 4” that may be treated as different occasions. Verify a few source cells manually, including a first, last, and missing measurement.
Common mistake: “There are three visit columns, so I can make three rows per patient, and a blank score should become zero.” The row-count logic may be right, but a blank is not a zero. A zero is an observed outcome value; a blank represents an unobserved or unavailable value under the stated data convention. Replacing the blank changes the information and can distort later summaries or models. Keep the missing value distinct, and check whether the corresponding visit was planned.
- Dropping identifiers: Without a subject field on every long record, repeated outcomes cannot be linked to the correct subject.
- Misreading time labels: A column order is not necessarily an elapsed-time scale. Encode occasions according to the protocol.
- Ignoring duplicate keys: Repeated subject-time pairs require investigation or a more detailed key; they should not be averaged automatically.
- Assuming long means independent: Reshaping does not remove within-subject dependence. The records remain repeated measurements on the same subjects.
Check Your Understanding
Answer each question, then compare with the solutions below.
- A wide table has 12 students and four planned term scores per student. How many long-format records are expected if every planned occasion is retained? If two scores are missing, how many outcome values should be observed?
- A patient has a planned week 8 visit but no recorded outcome. Explain why a missing outcome and a score of zero are not interchangeable.
- Two rows have the same subject and visit, but the study records two measurements at that visit. What should be checked before reshaping to one row per subject-visit?
- A table records both systolic and diastolic blood pressure at each visit. What extra information is needed if the two outcomes are stacked into one outcome column?
- Why is the correct total row count not sufficient evidence that a reshape is correct?
Solutions. (1) The expected count is \(12 \times 4 = 48\) records. Two missing scores still leave 48 records if the planned occasions are retained, with \(48 - 2 = 46\) observed outcome values. (2) Zero is an observed numerical score; missing means no score was recorded. Substituting zero would alter the data. (3) Determine whether the rows are duplicates or valid distinct measurements. If distinct, add a key such as replicate number or measurement date; do not combine them without justification. (4) Include an outcome-type field identifying systolic versus diastolic, so the measurement key includes subject, visit, and outcome type. (5) Values can be attached to the wrong subject or occasion while the total row count remains correct. Check unique keys and compare source values to long records by subject and occasion.