Why Sorting Needs an Explicit Rule
Longitudinal records often arrive in an order that reflects data entry, file assembly, or a software export rather than the intended within-subject chronology. A model-ready data set needs a reproducible order, but sorting is more than rearranging rows: the rule must say what happens when a subject has multiple records at the same recorded time. If that rule is left to a software default or the current input order, two runs can place tied records differently.
The goal is to arrange records consistently while preserving the recorded event times. A sequence number is an index in the arranged records; it is not a new time measurement. In particular, tied timestamps do not justify changing either timestamp to force a strict order.
The record grain and record keys established in the earlier material on Record Keys for Longitudinal Data determine what each row represents and how records are identified. Sorting does not replace that identification. A sequence number is generally derived during preparation and should not be treated as a substitute for a record key: it can change if records are added, removed, or reordered under a revised rule.
Specify the Sort Key Before Applying It
A reproducible sort should be described as an ordered list of fields. For example, sort by subject identifier, then recorded event time in ascending order, then a stable source-record identifier in ascending order. This is a lexicographic rule: compare the first field; if it ties, compare the second; continue until a field distinguishes the records.
The final tie-breaker should be unique for the records being sorted and stable across the data-preparation runs that are meant to be comparable. A source-system record identifier may work. A row number can work only when it refers to a fixed source-file snapshot and is retained consistently; a row number assigned after an unstable import is not a reliable cross-run tie-breaker. If no meaningful event ordering exists for tied records, a stable identifier can provide a reproducible technical order without claiming that one event occurred before another.
Before sorting, check that the subject identifier and ordering fields are present and comparable. A time field must have a consistent representation and unit within the data being sorted; otherwise, a computer may compare character strings or incompatible values instead of chronological values. Do not silently treat a missing time as zero, or place it at the beginning or end without a stated policy. Records with missing or invalid ordering values need an explicit disposition, such as review or a separately documented placement rule.
The tie-breaker also needs a declared role. If it represents a scientifically justified ordering, state that rationale. If it is used only to make output reproducible, describe it as technical ordering. Do not infer a clinical event order from an identifier merely because it yields a convenient order.
Assign and Validate Sequence Numbers
Once the sort order is final, number records within each subject consecutively. If subject \(i\) has \(m_i\) records, its sequence values should be \(1,2,\ldots,m_i\). They restart at 1 for the next subject. For records sorted by time, the recorded times should be nondecreasing within a subject: each time is less than or equal to the next. Equality is allowed because multiple records can share a recorded time.
This assignment is an index operation, not a measurement operation. It does not imply that the time difference between sequence 2 and sequence 3 is the same as the difference between sequence 1 and sequence 2. Nor does a larger sequence number imply a later recorded time unless the data have first been sorted according to the stated time rule.
Identify the subject field, recorded ordering field, and stable tie-breaker. Check missingness, data type, and units before comparing values.
Sort by subject, recorded time, and the tie-breaker in the specified directions. Do not modify recorded times to remove ties.
Within each subject, assign 1 through the number of records after sorting. Preserve any source sequence field separately if it is needed for audit.
Verify that sequence values are unique and consecutive within each subject, times are nondecreasing in sequence order, and the sorted output contains the same recorded times as the input.
Worked Examples
Worked Example: Sorting Tied Times Deterministically
Suppose a hypothetical subject has four records. The recorded event times are in hours as supplied in the input. The source-record identifier is stable and unique. Use ascending recorded time, then ascending source-record identifier as the tie-breaker.
| Subject | Source record | Recorded time (h) |
|---|---|---|
| S01 | R18 | 2 |
| S01 | R11 | 0 |
| S01 | R24 | 2 |
| S01 | R15 | 1 |
First compare the recorded times: \(0<1<2\). The two records at 2 h tie on time, so compare their identifiers: R18 precedes R24 because R18 sorts before R24. The resulting sequence is therefore R11, R15, R18, R24.
| Sequence | Source record | Recorded time (h) |
|---|---|---|
| 1 | R11 | 0 |
| 2 | R15 | 1 |
| 3 | R18 | 2 |
| 4 | R24 | 2 |
The time values remain \(0,1,2,2\) h: sorting changed row positions, not event times. Sequence 3 preceding sequence 4 is a deterministic technical ordering of the tied records. The data do not establish that R18 occurred earlier than R24.
Worked Example: Finding Sequence and Chronology Errors
A hypothetical subject has these records after export. The supplied sequence field is intended to represent the row order, and the stated sort rule is time ascending, then source-record identifier ascending.
| Source record | Recorded time (h) | Supplied sequence |
|---|---|---|
| Q07 | 0 | 1 |
| Q12 | 1 | 3 |
| Q09 | 1 | 2 |
The supplied sequence values are the set \(\{1,2,3\}\), but the rows are not in the stated order: at 1 h, Q09 must precede Q12 because Q09 sorts first by identifier. The sequence values in the current row order also decrease from 3 to 2. This is not repaired by simply checking that the values are consecutive.
Apply the rule: Q07 at 0 h comes first; at 1 h, Q09 precedes Q12. Assign sequence 1 to Q07, 2 to Q09, and 3 to Q12. The corrected order has nondecreasing times \(0\leq1\leq1\), and the sequence values are \(1,2,3\). Retain the supplied sequence in a separate audit field if its provenance matters; do not use it as the tie-breaker unless the data specification says it is a valid, stable ordering field.
Worked Example: Detecting an Unstable Tie-Breaker
Two hypothetical records for subject T03 both have recorded time 5 h. They have no meaningful event ordering recorded, but their source IDs are C14 and C08. A preparation rule that sorts tied records by their current input row position may place C14 before C08 in one export and C08 before C14 in another. The time field cannot resolve the tie.
If the source IDs are stable and unique, add them as a technical tie-breaker. In ascending identifier order, C08 receives the earlier sequence and C14 the next sequence. If the source IDs are not stable, first identify an appropriate stable record identifier or preserve a fixed source snapshot and its row positions. If neither is possible, the output order of these tied records cannot be guaranteed to reproduce across changed input arrangements; this limitation should be recorded rather than hidden.
In either case, both records remain at 5 h. Adding a small increment to one time to force the sort would alter the data and falsely assert a time difference that was not recorded.
Why the Checks Work, and What They Cannot Prove
The justification for checking nondecreasing times is direct. If records are sorted by time ascending, then for every adjacent pair in a subject, the earlier row's time must be less than or equal to the later row's time. If any adjacent pair has the earlier value greater than the later value, the rows are not in ascending time order. Checking adjacent pairs is sufficient: if \(t_1\leq t_2\), \(t_2\leq t_3\), and so on, transitivity of the ordering gives \(t_j\leq t_k\) for every \(j<k\).
Likewise, if a subject has \(m_i\) rows and the generated sequence is unique, starts at 1, ends at \(m_i\), and increases by 1 at each row, then it contains exactly the integers \(1,\ldots,m_i\). These checks establish the internal structure of the sorted output. They do not prove that the recorded times are correct, that the tie-breaker reflects real event chronology, or that no source records were lost before sorting. Those questions require comparison with source data and the record specification.
A useful preservation check compares the input and output time values for each record identifier, not just the overall time range. The minimum and maximum could remain unchanged even if one record's time was accidentally altered. For each stable record identifier, verify that the output time equals its input time; also check record counts and identifiers before and after sorting.
Diagnostics, Alternatives, and Limitations
Common warning signs include decreasing times within a subject, sequence values that repeat or skip, a sequence that fails to restart for a new subject, and results that change when the input rows are shuffled. The last symptom often indicates reliance on an unstable sort or an undocumented input-order tie-break. Investigate rather than automatically renumbering: sequence errors may point to a wrong sort key, duplicate identifiers, missing ordering values, or records assembled from incompatible sources.
A stable sort preserves the original order among tied values. This can be useful when the input order is itself documented and stable. It is not, on its own, a fully reproducible tie rule if upstream file order can change. A deterministic sort with an explicit stable tie-breaker is usually preferable for repeatable preparation. Alternatively, a data set may retain tied records without asserting a within-tie order if downstream use does not require one; however, any software output still has some row order, so the handling of ties should be stated. Neither approach can recover an event order absent from the recorded data.
Sorting also cannot correct an incorrect time value or make inconsistent time representations comparable. Do not convert a tie into a strict order by adding arbitrary fractions, rounding selectively, or changing the recorded time. If the available precision produces ties, retain them and report the ordering rule. The later material on Observation Time Versus Calendar Time addresses the distinction between kinds of time values; here, the task is only to order the recorded field as specified, not to redefine it.
Common Mistakes
- Assuming sequence is time. Sequence 4 follows sequence 3 in the sorted rows, but the elapsed time can be zero or any other interval.
- Breaking a tie by changing a time. Adding a small amount to one timestamp creates an unsupported difference. Keep both recorded times unchanged and use a documented tie-breaker for row order.
- Using current input order without documenting it. A stable sort can preserve ties, but input order may change between exports. Use a stable identifier or state the limitation.
- Checking only that sequence values are present. Also check that they are unique, consecutive within subject, correctly restarted, and aligned with the declared sort order.
- Assuming sorting proves data correctness. Sorting can arrange values consistently; it cannot validate the source timestamps or establish the true order of simultaneous or tied events.
Check Your Understanding
Answer each question using the stated sort and sequence rules. Short solutions follow.
- A subject has records at 3 h, 1 h, and 3 h. What must the time order be, and what additional field is needed to order the tied records reproducibly?
- Why is changing one of two tied recorded times from 3 h to 3.001 h not an acceptable way to create a sequence?
- A subject has four sorted records with sequence values 1, 2, 4, and 5. Which sequence checks fail, and what should be done before assigning corrected sequence values?
- After sorting, a subject's times in sequence order are 0 h, 2 h, 2 h, and 7 h. Is the ordering valid? What does the tie imply about the actual order of the two records at 2 h?
Solutions. (1) The times must be ordered \(1,3,3\) h. A stable, unique tie-breaker, such as a documented source-record identifier, is needed to order the two records at 3 h reproducibly; it need not imply a real event order. (2) The change invents a time difference not present in the recorded data. Keep both values at 3 h and resolve row order with the specified tie-breaker. (3) The values are not consecutive from 1 through 4, and the sequence does not end at the number of records. First verify that the records, subject assignment, and declared sort key are correct; then regenerate the sequence after sorting, yielding 1, 2, 3, 4. (4) Yes. Times are nondecreasing, so the time order is valid. The tie means the recorded times do not establish which of the two records occurred first; a tie-breaker can provide reproducible row order without proving chronology.