Why Table Preparation Matters
A joint model links a subject’s repeated measurements to that subject’s event record. That link is only meaningful if the two data sources identify the same subjects and preserve the intended measurement history. A duplicated event record can silently duplicate longitudinal rows; a mismatched identifier can attach measurements to the wrong event outcome; and a missing measurement can be confused with a visit that never occurred. These are data-structure errors, not modeling choices, but they can change the analysis as substantially as changing a model.
The goal here is to construct and audit two linked tables before fitting: an event table with one record per subject and a longitudinal table with one record per observed measurement occasion. Software interfaces differ, so the exact column names and required object types must be checked in the chosen package’s documentation. The underlying data relationships, however, are the same.
The event table’s identifier should be unique: each subject appears once. The longitudinal table’s identifier is generally not unique because a subject can have several measurements. Its combination of subject identifier and measurement occasion should identify a measurement row, unless repeated records at the same recorded time are legitimate and explicitly represented. This distinction is central: uniqueness is required for the event-table key, while repeated subject identifiers are expected in the longitudinal table.
Construct the Two Tables Before Joining
Begin by deciding what one row means in each source. A useful working structure is:
- Event table: one row per subject, containing the identifier and the event-related variables in the format required by the selected software.
- Longitudinal table: one row per recorded measurement, containing the identifier, measurement time, outcome, and any measurement-level variables.
Store subject-level covariates in one clearly designated source, even if the software ultimately needs them copied to multiple longitudinal rows. If a subject-level covariate is repeated down the longitudinal table for convenience, verify that it is constant within subject. Do not treat a changing measurement-level variable as though it were a fixed subject attribute.
Before combining the tables, check that identifiers are nonmissing, use the same representation in both sources, and refer to the same subjects. For example, the character identifier “0042” and numeric identifier 42 may look equivalent to a person but will not necessarily match in software. Preserve leading zeroes when they are part of the identifier. Also check whitespace, capitalization, and accidental recoding.
Write down whether each row represents a subject or a subject-measurement occasion. This determines which keys must be unique.
Check missing IDs, duplicate event-table IDs, IDs in only one table, and format mismatches.
Check times, outcome values, repeated-time records, and the distinction between unrecorded measurements and absent occasions.
Use the identifier as the link and confirm that the resulting rows and subject counts agree with the intended structure.
Identifier Joins and Their Consequences
A join matches records according to a key. If each event-table identifier occurs exactly once, every longitudinal row can match at most one event record. If an event-table identifier occurs more than once, each longitudinal row for that subject may be repeated once for every matching event record. This is why checking event-table uniqueness before joining is essential.
Worked Example: Calculating the Expected Join Size
Suppose a hypothetical longitudinal table has 14 rows: subjects A, B, C, and D have 4, 3, 4, and 3 measurement rows, respectively. The event table should have one row for each of these four subjects. Before joining, an audit finds that A, C, and D each occur once in the event table, but B occurs twice.
For A, C, and D, the matched row counts are 4, 4, and 3. Each of B’s 3 longitudinal rows matches both event records, producing 2 × 3 = 6 joined rows for B. Therefore, the joined table has 4 + 4 + 3 + 6 = 17 rows. Equivalently, there are 14 longitudinal rows, of which 3 belong to the duplicated event ID; the join has (14 − 3) + 2 × 3 = 17 rows.
The increase from 14 to 17 rows is evidence of a key violation, not additional measurement information. The appropriate action is to investigate why B has two event records and resolve the source of the duplication before proceeding. Choosing one record arbitrarily would not be a valid repair unless it follows a documented rule supported by the data-generating process.
A useful audit compares the number of unique subjects in each source with the number expected in the analysis. List IDs present in the event table but absent from the longitudinal table, and vice versa. A subject with no observed longitudinal measurements may or may not belong in the analysis, depending on the model and the study’s inclusion rules; do not silently discard that subject just to make the tables match. Record the inclusion decision and its reason.
Worked Example: Finding Unmatched and Duplicated IDs
Consider a hypothetical event table with IDs 101, 102, 103, and 104, each appearing once. The longitudinal table has measurements for IDs 101, 102, 102, 103, and 105. Here ID 102 appropriately appears more than once in the longitudinal table, while ID 105 is not in the event table and ID 104 has no longitudinal row.
The event table has 4 unique subjects and the longitudinal table has 4 unique subjects: 101, 102, 103, and 105. The ID sets differ. ID 105’s measurements cannot be linked to an event record, so investigate a typo, an omitted event record, or an out-of-scope subject. ID 104’s absence from the longitudinal data could mean no measurement was recorded, or that the subject is not eligible under the analysis plan. Those possibilities require different decisions.
The join should not be treated as successful merely because it runs. First resolve whether ID 105 belongs in the analysis and how to handle ID 104. Then verify that each retained event-table ID is unique and that every retained longitudinal row matches exactly one event record.
Measurement Times and Missingness
Measurement times should be numeric or otherwise represented in the form required by the software, consistently use the same units, and have no unintentional formatting differences. Sort records by subject and time for inspection. Check values that are impossible under the data collection plan, unexpected gaps, and repeated times. Do not impose a time origin or assume that the first recorded time must equal zero: the study’s time convention and its encoding are handled separately. The preparation task is to preserve the recorded scale and make its units and meaning explicit.
Repeated records at the same time are not automatically errors. They may reflect technical replicates, multiple readings, or a data-entry duplication. Determine which explanation applies and whether the chosen software can represent the records as intended. Do not average or delete repeated values merely to make the subject-time pair unique; any aggregation needs a substantive and analytical justification.
Missingness also has more than one form. A missing measurement is a record for an intended or observed occasion whose outcome value is unavailable. An absent occasion is a time point for which no measurement row exists. These are not interchangeable. A blank outcome cell may be retained as a missing value if the software supports it, while an absent visit is represented by no row. Document whether a blank means not measured, not recorded, or not applicable. Never replace missing outcomes with zero simply to satisfy a software input requirement.
Worked Example: Distinguishing a Missing Value From an Absent Visit
In a hypothetical study, subject K has scheduled measurement occasions at weeks 0, 4, and 8. The data show a row at week 0 with outcome 12.1, a row at week 4 with a blank outcome, and a row at week 8 with outcome 9.8. Subject L has rows at weeks 0 and 8 only.
For K, the week-4 row documents an occasion with an unavailable outcome; the time and row remain informative about the record structure. For L, there is no week-4 row, so the table shows an absent occasion rather than a row with a missing outcome. Neither case justifies setting the week-4 outcome to zero. The analyst should check the collection records to determine whether K’s outcome was not obtained or was lost in data transfer, and whether L’s week-4 visit was not scheduled, missed, or simply omitted during export.
These distinctions matter because preprocessing can otherwise alter the observed visit pattern. Preserve the source status where possible, and make a documented decision about how the selected software handles each type of missingness.
Long and Wide Formats, and Software Inputs
In long format, each measurement occasion has its own row. In wide format, multiple occasions for a subject are stored in separate columns, such as outcome at week 0, outcome at week 4, and outcome at week 8. Long format makes the subject-measurement relationship explicit and naturally accommodates subjects with different numbers of observed measurements. Wide format can be convenient for some interfaces, but it can encourage accidental assumptions that everyone has the same schedule.
The right input layout is the one required by the chosen software and supported for the intended model. Before converting formats, retain a clean source copy and write down the mapping from source columns to software inputs. Confirm how the interface expects the subject identifier, measurement times, outcome, event information, and covariates to be supplied. Do not infer requirements from an example that uses a different model or software version.
A conversion should preserve the observed values and their subject-time associations. For example, reshaping a wide table to long format should not create rows for unobserved occasions unless the software explicitly requires a schedule table and the distinction is retained. Conversely, reshaping long to wide should not overwrite repeated measurements at the same nominal time without a decision about their meaning.
Worked Example: Reshaping Without Changing the Data
A hypothetical wide table contains one subject, M, with values \(y_{0}=5.2\), \(y_{2}=4.7\), and \(y_{6}=4.1\), where the subscripts denote measurement weeks. Its long-format representation has three rows:
The conversion creates three measurement rows, not three subjects. The identifier is repeated, the time value is recorded in weeks, and each outcome remains attached to its original time. A basic conversion check is that the number of nonmissing wide outcome cells equals the number of corresponding long-format measurement rows: here, 3 = 3. If the week-2 value had been blank, the analyst would need to follow the intended missing-value convention rather than silently omit or recode it.
Validation Checks Before Fitting
A table that looks plausible can still be mislinked or altered during processing. Keep an audit trail of checks and their results. At minimum, confirm:
- Event-table IDs are nonmissing and unique.
- Every retained longitudinal row has an ID that matches exactly one event-table row.
- Subject counts and row counts before and after joins agree with the expected join behavior.
- Measurement times use consistent units, are stored correctly, and are ordered sensibly within subject.
- Repeated subject-time records and missing outcome values have been investigated and documented.
- Subject-level covariates are constant within subject when they are copied across measurement rows.
- Reshaping preserves the values, times, missingness, and subject assignments.
Useful diagnostics include frequency tables of rows per subject, counts of unique IDs, lists of unmatched IDs, and a sample of joined records inspected by hand. Unusually many rows for one subject, an unexpected change in the number of rows after a join, or identical event information duplicated across many rows can reveal a faulty key or an unintended many-to-many match. These checks cannot establish that the model is scientifically appropriate, but they can expose avoidable input errors.
Common Mistakes and Limits
| Mistake | Why it is wrong | Correction |
|---|---|---|
| Joining before checking event-ID uniqueness | Duplicate event rows can multiply longitudinal rows. | Require one event record per subject before joining, or resolve documented exceptions first. |
| Assuming every subject must have the same number of rows | Subjects may have different numbers of recorded measurements. | Inspect counts and explain unusual patterns rather than forcing equal row counts. |
| Treating a blank outcome as a zero | Zero is an observed value, not a generic code for unavailable data. | Preserve missingness distinctly and follow the software’s documented missing-data handling. |
| Removing all duplicate subject-time records | Some duplicates are valid replicates; others are data errors. | Investigate their origin and apply a justified, documented rule. |
| Assuming one universal software input format | Packages and model interfaces differ in accepted structures. | Check the specific interface and validate any conversion against the source tables. |
Preparation does not determine how the time origin, event indicator, censoring, delayed entry, missing-data assumptions, or longitudinal model should be specified. Those are separate analytical decisions. A well-formed table cannot repair biased measurement collection or make an unsupported model available in software. Its role is narrower and essential: to present the recorded information in a transparent structure that the chosen analysis can interpret as intended.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions.
- An event table has 80 rows and 78 unique, nonmissing IDs. What should be checked before linking it to longitudinal records?
- A longitudinal table has 26 rows, including 5 for subject R. R appears twice in the event table; every other longitudinal ID appears once in the event table. How many rows will an ID-based join produce, assuming all 26 rows have matching IDs?
- For one subject, the table has measurements at weeks 0 and 8 but no week-4 row. Is the week-4 outcome a missing value in the current table?
- Why is it unsafe to require every subject to have the same number of longitudinal rows?
- A wide-to-long conversion produces 15 rows from 15 nonmissing outcome cells. Is that alone sufficient to establish that the conversion is correct? What else should be checked?
Solutions. (1) The event table has two duplicate-ID rows beyond its 78 unique IDs. Identify the duplicated IDs, determine why they occur, and resolve the records before joining; otherwise measurements may be multiplied. (2) The 21 rows not belonging to R match once, and R’s 5 rows match twice, so the join produces 21 + 2 × 5 = 31 rows. (3) No. There is no week-4 row, so it is an absent occasion in this table; collection records are needed to determine why. (4) Subjects can have different numbers of recorded visits. Enforcing equal counts may create invented rows or discard legitimate measurements. (5) No. Also check that each value remains attached to the correct subject and time, that units and missingness are preserved, and that no repeated-time records were overwritten or expanded incorrectly.