Why the Record Layout Matters
A longitudinal data set records measurements from the same subjects at multiple occasions. Before any statistical model can be specified, each value must be connected to the correct subject and the correct time. A biomarker value without those identifiers is difficult to interpret; a value attached to the wrong subject or occasion can distort every later analysis.
Long format stores one record per subject and measurement occasion (for one biomarker). The subject identifier and occasion information appear on every row, alongside the measured value. This layout makes repeated measurements explicit while allowing subjects to have different numbers and timings of visits. It does not, by itself, imply that measurements were taken on a common schedule or that visits are equally spaced.
What a Long-Format Record Contains
Use \(i\) to label a subject and \(j\) to label a recorded occasion for that subject. Let \(t_{ij}\) be the measurement time and \(y_{ij}\) the biomarker value. The subscript \(j\) identifies a record within subject \(i\); it does not mean that every subject has the same number of records, or that their records occur at the same times. A data table will often also include subject-level information, such as treatment group, repeated on each of that subject’s rows.
Here \(m_i\) is the number of recorded measurement occasions for subject \(i\). The total number of rows is \(\sum_i m_i\), provided each row corresponds to one measurement. The value \(t_{ij}\) should represent the time relevant to the analysis, such as days since enrollment. A visit label like “visit 3” can be useful too, but it should not silently replace actual elapsed time when visit timing varies across subjects.
A table can be physically sorted by subject and time to make it easier to inspect. Sorting does not change which measurement belongs to which subject or time. Nor does sorting establish that the times are equally spaced: records at days 0, 18, and 61 remain irregularly timed even when displayed in increasing order.
Worked Example: Reading Long-Format Records
Suppose a hypothetical monitoring protocol records biomarker concentration in units per milliliter. The table below contains measurements for three subjects.
| Subject | Visit | Time (days) | Biomarker (units/mL) |
|---|---|---|---|
| A01 | Baseline | 0 | 12.4 |
| A01 | Follow-up 1 | 30 | 11.8 |
| A01 | Follow-up 2 | 90 | 10.9 |
| A02 | Baseline | 0 | 9.7 |
| A02 | Follow-up 1 | 30 | 10.1 |
| A03 | Baseline | 0 | 15.2 |
| A03 | Follow-up 2 | 60 | 14.6 |
Each row is one subject-occasion measurement. For example, 10.1 units/mL belongs to subject A02 at day 30; it is not a separate subject or a value at day 90. The occasion count is \(m_{\mathrm{A01}}=3\), \(m_{\mathrm{A02}}=2\), and \(m_{\mathrm{A03}}=2\). Their sum is \(3+2+2=7\), matching the seven table rows. The times are not identical across subjects: A03 has no row at day 30 and has a measurement at day 60. That pattern should be represented as observed, not silently altered to match A01’s schedule.
Ordering, Keys, and Duplicate Checks
A key is the set of variables that should uniquely identify a record. For one biomarker with at most one measurement per subject at each exact time, a natural key is subject identifier plus measurement time. If visits are protocol-defined and their labels uniquely identify occasions, the subject identifier plus visit label may also be a useful check. When both scheduled visit and actual time matter, retain both rather than using one as a substitute for the other.
A duplicate key means that two or more rows have the same values for the variables chosen as the key. It is a signal to investigate, not proof of an error. The repeated rows may be accidental copies, corrected versions that were not reconciled, or legitimate replicate measurements. The correct key depends on how the data were collected. If replicate assays are valid, a replicate identifier or specimen identifier may be required to distinguish them.
Worked Example: Investigating a Duplicate Subject-Time Key
Consider these hypothetical records from an assay in which the protocol ordinarily allows one reported value per subject at a visit.
| Subject | Time (days) | Biomarker (units/mL) | Specimen |
|---|---|---|---|
| B12 | 0 | 7.2 | S1 |
| B12 | 28 | 6.8 | S2 |
| B12 | 28 | 7.0 | S3 |
| B12 | 56 | 6.1 | S4 |
The proposed subject-time key has four rows but only three distinct combinations: (B12, 0), (B12, 28), and (B12, 56). Thus the two day-28 records violate uniqueness for that proposed key. The difference between their values is \(7.0-6.8=0.2\) units/mL, but that arithmetic does not establish which value is correct or whether either is erroneous.
The specimen identifiers show that the rows refer to distinct specimens. The next check is the collection and assay protocol: were two specimens intentionally collected at day 28, or was one record entered incorrectly? If both are legitimate, keep both and include specimen or replicate in the record key. If only one measurement should exist, resolve the discrepancy using source records and documented data-cleaning rules. Averaging the two values would produce \( (6.8+7.0)/2=6.9\) units/mL, but this is justified only if the scientific and assay protocol calls for combining those replicates.
Ordering checks complement key checks. A common display order is subject identifier first and measurement time second, both increasing. Within each subject, check that time is nondecreasing; check whether equal times are permitted; and inspect unexpected gaps or reversals. A time reversal can signal a data-entry or sorting problem, but it can also occur if rows were sorted by visit label while actual times differed. The data should preserve both variables so that this distinction can be examined.
Converting Between Wide and Long Layouts
In wide format, a subject often occupies one row and repeated measurements occupy separate columns, such as biomarker_day0, biomarker_day30, and biomarker_day60. In long format, those columns become rows, while subject-level variables are carried along. Conversion is a bookkeeping operation: each nonmissing cell must retain its subject, its occasion, and its value. A missing cell must not become a measured biomarker value.
Worked Example: Reshaping Wide Records and Preserving Missingness
Suppose a hypothetical data extract contains the following measurements in units/mL.
| Subject | Day 0 | Day 30 | Day 60 |
|---|---|---|---|
| C01 | 5.0 | 4.7 | 4.5 |
| C02 | 8.2 | 7.9 | Missing |
| C03 | 6.4 | Missing | 6.0 |
There are three subjects and three scheduled occasions, so the rectangular wide layout contains \(3\times3=9\) subject-occasion cells. Two cells are marked missing, leaving seven observed biomarker values. To make a scheduled-occasion long table, create one row per cell and use a missing-value marker for the two absent measurements. For instance, the rows for C02 are (C02, day 0, 8.2), (C02, day 30, 7.9), and (C02, day 60, missing). The rows for C03 include a missing value at day 30 and 6.0 at day 60.
If instead the table is intended to contain only observed measurements, the two missing cells are omitted and the result has seven rows. Those two conventions answer different data-management questions. A scheduled-occasion table records that a measurement was expected but unavailable; an observed-record table lists only measurements that exist. Whichever convention is used, missingness should be explicit and documented. It must not be coded as zero, because zero is a possible measured concentration and has a different meaning.
A Practical Validation Sequence
The following checks establish whether the table’s structure agrees with the measurement records. They do not determine whether the visit process is informative; that issue concerns how visits arise and is distinct from whether each row is correctly represented.
State what one row represents and identify subject, occasion or time, biomarker, units, and any replicate identifiers. Confirm that the chosen key matches the collection protocol.
Look for missing subject identifiers, invalid time values, inconsistent units, and biomarker values that violate known data-entry rules. Do not treat an unusual value as an error without checking its source.
Count repeated key combinations, inspect any duplicates, and sort by subject and actual time for review. Within-subject time reversals or unexpected ties should be reconciled with the visit and specimen records.
Compare row counts with expected subject-occasion records, distinguish absent visits from present visits with missing assays, and verify that reshaping has neither dropped nor duplicated valid measurements.
A useful structural check is to count records by subject and compare those counts with the expected schedule, allowing for documented exceptions. For example, if 20 subjects were scheduled for four occasions, the planned number of cells is \(20\times4=80\), but the number of observed biomarker records may be smaller because some visits or assays are missing. The difference alone does not say why records are absent; visit logs and data definitions are needed to classify the reason.
Why Long Format Is Useful—and What It Does Not Do
Long format keeps the subject-occasion relationship explicit and accommodates unequal numbers of measurements without creating one new column for every possible visit. It also makes it straightforward to summarize the number of observations per subject and to join each record to subject-level or visit-level information using stated identifiers. Wide format can be convenient for data entry or a fixed small schedule, but it becomes awkward when visits are irregular or the number of visits varies.
| Feature | Long format | Wide format |
|---|---|---|
| Basic layout | One row per recorded subject-occasion measurement | Often one row per subject, with occasions in separate columns |
| Unequal numbers of visits | Represented by different row counts per subject | Usually requires empty or missing occasion columns |
| Time information | Stored as a variable on each row | Often encoded in column names; irregular actual times need extra care |
| Main structural risk | Wrong identifiers, duplicate keys, or misordered records | Values can be assigned to the wrong occasion during reshaping |
Long format is not a remedy for informative observation schedules. Correctly representing that a subject was measured at day 18 rather than day 30 preserves the timing information; it does not make the timing mechanism ignorable or remove bias that may arise from outcome-related visits. The earlier material on informative observation schedules addresses that separate statistical issue. Likewise, storing repeated values in long format does not by itself specify a statistical model or guarantee that the records are independent.
Common Mistakes and Limitations
- Using row number as a visit identifier. Row order can change during sorting or merging. Use an explicit subject identifier and time or visit variable.
- Assuming the visit label is the actual time. “Visit 2” can occur on different days for different subjects. Retain elapsed time if timing matters.
- Deleting every duplicate automatically. Duplicate keys may represent replicate specimens or assays. Investigate the collection design before removing or combining records.
- Replacing a missing biomarker with zero. A zero is a value; a missing marker says the value is unavailable. Conflating them changes the data.
- Assuming sorted records are equally spaced. Sorting only changes display order. It does not change the observed time gaps.
These checks establish a trustworthy representation, not a complete assessment of data quality. The data extract may omit subjects, contain incorrect identifiers, or have unrecorded visits. Resolving those issues requires comparison with source records and knowledge of the measurement protocol.
Check Your Understanding
Answer each question, then compare with the brief solutions.
- One subject has measurements at days 0, 21, and 55. How many long-format rows are needed if all three values were observed, and what does each row represent?
- A table has two rows for subject D04 at day 14, with separate specimen identifiers. Is the subject-time key unique? What should be checked before combining or removing a row?
- A planned day-30 measurement was not obtained. Why is coding its biomarker as zero inappropriate?
- There are 12 subjects, each scheduled for three occasions, and two measurements are missing. How many subject-occasion cells are planned, and how many observed measurements remain?
- Why should actual measurement time be retained when records are also labelled “baseline,” “visit 1,” and “visit 2”?
Solutions. (1) Three rows: one for each subject-time measurement. (2) No; the same subject-time combination occurs twice. Check whether the specimens are legitimate replicates or whether a record is erroneous, using the protocol and source data. (3) Zero is a measured value, whereas the measurement was unavailable; coding it as zero falsely asserts an observed concentration. (4) The planned count is \(12\times3=36\) cells, and \(36-2=34\) measurements are observed. (5) Visit labels may occur at different elapsed times across subjects, so actual time preserves the timing needed to describe the records correctly.