From Raw Files to an Analysis Dataset
Concentration measurements, administered doses, and covariates often arrive in separate files because they are collected for different purposes. The analysis dataset must bring the relevant information together while preserving what each row represents and where each value came from. A careless merge can multiply observations, attach a measurement to the wrong time, or make an unresolved discrepancy look settled.
The goal is not simply to combine files. It is to produce a longitudinal dataset whose records can be interpreted, traced to their sources, and checked against explicit rules. Earlier material on dataset schemas, cross-field consistency, source reconciliation, and independent dataset quality control provides useful checks; here the emphasis is on the construction process and its consequential choices.
Specify the Row Before Combining Sources
Begin by stating the row grain: what one row means. For a common event-level structure, a row represents one recorded event, such as a concentration observation or a dose administration. A concentration row contains the observed concentration and its sampling time; a dose row contains the administered amount and administration time. A row-type or event-type field distinguishes them. Depending on the analysis, additional event types may be needed.
A compact design might include a subject identifier, elapsed analysis time, event type, concentration value and unit, dose amount and unit, and relevant covariates. Not every field applies to every row. Missingness should therefore be interpretable: a blank concentration on a dose row means “not a concentration record,” not a failed assay. The data specification should state which fields are required for each event type, which are optional, and how missing values are represented.
Before calculating time, specify the time origin and the timestamp rules. For example, a study may define time zero as each subject’s first actual dose. Timestamps must be interpreted in a stated time zone and converted consistently before subtraction. If the source provides only a date or an imprecise time, do not silently manufacture precision; retain the available precision and document its implications.
Construct the Dataset in a Reproducible Sequence
A controlled sequence makes decisions visible and makes errors easier to localize. Keep raw files unchanged; create reviewed, analysis-ready tables through documented transformations. Assign or confirm subject identifiers consistently, normalize timestamps and units under explicit rules, and preserve source record identifiers so every constructed row can be traced back.
Record the source files, identifiers, time fields, units, record grain, and expected relationships. Define the target row types and required fields before merging.
Check identifiers, duplicates, timestamps, units, and event meaning within each source. Preserve raw values and record any conversion or exclusion rule.
Apply the declared origin and time-zone convention. Keep both the source timestamp and derived elapsed time, or retain a traceable mapping between them.
Stack records of different event types into the declared structure. Attach covariates using their stated applicability rule, not an undocumented nearest-time match.
Check row counts, joins, timing, required fields, and event ordering. Log discrepancies and unresolved decisions, then retain the resulting dataset and its derivation record.
Worked Time and Event Construction
Worked Example: Converting Source Timestamps to Elapsed Time
Hypothetical data. Subject P017 has an actual first dose at 2026-04-01 08:00 UTC. Two concentration samples are recorded at 2026-04-01 09:30 UTC and 2026-04-01 12:00 UTC. Assume the timestamps have been verified as UTC and the protocol defines the first actual dose as time zero.
Elapsed time in hours is the timestamp minus the origin, expressed in hours:
For the 09:30 sample, the difference is 1 hour 30 minutes, or 5,400 seconds:
For the 12:00 sample, the difference is 4 hours, or 14,400 seconds:
The resulting concentration records have elapsed times 1.5 h and 4.0 h. The calculations are valid because the origin and sample timestamps share a verified time zone and use the same clock convention. If daylight-saving changes or inconsistent local time zones were possible, subtracting unnormalized clock times could give incorrect elapsed times.
Join by Record Meaning, Not Convenience
A common error is to merge a concentration table and a dose table using subject ID alone. This is not a valid way to pair samples with doses when each subject has multiple records. If one subject has \(m\) concentration records and \(d\) dose records, a subject-only many-to-many join can create \(m d\) combinations. The intended event-level dataset instead has \(m+d\) event rows, before any additional records are included.
Worked Example: Building Event Rows Without a Many-to-Many Join
Hypothetical data. Subject P017 has concentration records of 4.8 mg/L at 1.5 h and 2.1 mg/L at 4.0 h. The subject received 100 mg at 0 h and 100 mg at 12 h. A verified baseline weight of 70.0 kg applies to all records in this example.
There are \(m=2\) concentration records and \(d=2\) dose records. A join on subject alone would produce:
Those four pairs would repeat concentration and dose information in combinations that do not represent four observed events. Instead, construct two concentration rows and two dose rows, for \(m+d=2+2=4\) event rows:
| Subject | Time (h) | Event type | Concentration (mg/L) | Dose (mg) | Weight (kg) |
|---|---|---|---|---|---|
| P017 | 0 | Dose | — | 100 | 70.0 |
| P017 | 1.5 | Concentration | 4.8 | — | 70.0 |
| P017 | 4.0 | Concentration | 2.1 | — | 70.0 |
| P017 | 12 | Dose | — | 100 | 70.0 |
The dashes indicate fields that do not apply to that event type. The weight is attached because, by the example’s stated rule, the verified baseline measurement applies throughout these records. In a real dataset, that applicability rule must be specified and justified; it cannot be inferred merely because the weight file contains one row.
Covariates Need an Applicability Rule
A subject-level covariate, such as sex recorded once, may apply to every event for that subject if its meaning and source support that interpretation. A time-varying covariate requires more care. For each value, establish whether its timestamp is a measurement time, an effective-from time, or a visit date, and state the interval during which it applies. “Use the latest value” is incomplete unless the rule also explains whether values are carried forward, whether there is a maximum carry-forward period, and how ties or missing intervals are handled.
Do not interpolate between covariate measurements unless the analysis specification explicitly requires it. Interpolation creates values not recorded in the source. Carrying a value forward also creates an analysis convention, not a new measurement. Preserve the original measurement date and the derived assignment so reviewers can distinguish observed from assigned values.
Worked Example: Assigning a Time-Varying Weight Under a Stated Rule
Hypothetical data. For subject P024, weight is measured as 70.0 kg at 0 h and 72.0 kg at 48 h. Concentration samples occur at 24 h and 72 h. The analysis specification says to assign the most recent weight measured at or before the event time, and to carry it forward only until the next recorded measurement; no value is assigned before the first measurement.
At 24 h, the measurements available at or before the sample time are at 0 h only, because \(0\leq24<48\). Therefore the assigned weight is 70.0 kg, with source measurement time 0 h. At 72 h, the available measurements are at 0 h and 48 h, and the latest qualifying measurement is at 48 h because \(48\leq72\). The assigned weight is 72.0 kg, with source measurement time 48 h.
This is a rule-based assignment, not evidence that the subject’s true weight remained exactly 70.0 kg from 0 to 48 h or exactly 72.0 kg thereafter. The assigned value and its source time should be retained, and the carry-forward rule should be reviewed for clinical and analysis relevance.
Order Events at the Same Time
Two records can have equal elapsed times but different meanings. A sample collected immediately before a dose and that dose may share the same recorded clock time because of limited timestamp precision. Sorting by time alone does not establish which event came first. Use source details or a prespecified protocol rule to determine ordering; if the available data cannot resolve it, flag the ambiguity rather than choosing an order silently.
An ordering field can preserve a documented sequence among records with the same subject and time. It should not be used to invent temporal precision. Keep the original timestamp and the basis for the order, such as a recorded pre-dose designation or a documented administration sequence. For an infusion, retain the relevant start and end times or duration as specified; treating the entire infusion as an instantaneous dose can misrepresent the event.
Worked Example: A Sample and Dose Recorded at the Same Time
Hypothetical data. A sample for P031 is recorded at 08:00, and a 50 mg dose is also recorded at 08:00. The collection record explicitly marks the sample “pre-dose.” The protocol defines this designation as collection before administration.
Both records receive the same elapsed time if their timestamps match the declared origin calculation. The sample row is ordered before the dose row because the source designation and protocol establish that sequence. A suitable record order is therefore sample, then dose, with a documented tie-order value or equivalent field. The conclusion does not follow from the timestamps alone: if the pre-dose designation were absent, the ordering would remain unresolved and should be logged as such.
Validation, Diagnostics, and Failure Modes
Validation should test both the input records and the assembled output. Compare source and output counts by subject and event type; account for every excluded or transformed record. Check that each output event has a valid source identifier, that subject identifiers match the approved subject list, and that derived times agree with independently recalculated times. Examine duplicate records, unexpected missingness, implausible chronology, and event-type-specific required fields.
Join diagnostics should report unmatched records on each side and the number of matches per key. Unexpected increases in row count, repeated source record identifiers, or a concentration appearing on multiple output rows are warning signs of an incorrect join. A matching count is not sufficient by itself: a plausible total can still conceal wrong subject or time assignments.
A discrepancy log should state the source records involved, the issue, the decision or reason no decision was made, who reviewed it, and what dataset version was affected. Keep unresolved questions visible. Examples include an administration with uncertain actual time, a sample with no reliable subject match, or a covariate whose effective date is unclear. Do not resolve these by silently substituting planned times, nearest measurements, or assumed values.
- Incorrect time origin: subjects may have different origins. Verify the origin separately for each subject and retain the source timestamp used.
- Unintended many-to-many join: row counts and source identifiers repeat. Rebuild event rows at the intended grain and define an explicit key for every attachment.
- Ambiguous event ordering: equal recorded times do not establish sequence. Use documented evidence or retain an unresolved flag.
- Unjustified covariate carry-forward: a value may be assigned beyond a defensible interval. Apply the declared validity rule and keep the source measurement time.
- Loss of provenance: converted values cannot be traced to their inputs. Preserve source identifiers and record conversion rules and outputs.
Choosing a Dataset Structure and Recognizing Its Limits
An event-level long dataset is useful when subjects contribute repeated samples and dose events at different times. It keeps event rows explicit and permits variable numbers of records per subject. A wide, visit-oriented table can be convenient for a fixed schedule, but repeated visits often require many columns and can obscure irregular or unscheduled events. Keeping source tables separate preserves their original grains, but downstream analyses then need clear, repeatable rules for combining them. These structures are alternatives for organizing records; none removes the need to define keys, time, event meaning, and provenance.
A prepared dataset cannot repair missing source information. If actual administration time is unknown, a precisely calculated elapsed time is not thereby known. If the covariate’s applicability period is unspecified, a software join cannot supply the scientific rule. The dataset is also not a substitute for the earlier material on independent dataset quality control: construction checks by the person preparing the data should be supplemented by an independent review against the specification and source records.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions.
- A subject has three concentration records and four dose records. How many event rows should a stacked event-level dataset contain, and how many pairs might a subject-only many-to-many join create?
- A sample is 2 hours 15 minutes after the declared time origin. What is its elapsed time in hours?
- A covariate is measured at 10 h and 30 h. Under a rule assigning the latest measurement at or before the event, which value is assigned to an event at 25 h?
- A sample and a dose have the same recorded time, with no pre-dose label or other ordering evidence. What should the dataset do?
- Name two diagnostics that can reveal an incorrect join or loss of source traceability.
Solutions. (1) The stacked dataset has \(3+4=7\) event rows. A subject-only many-to-many join may create \(3\times4=12\) matched pairs, which do not represent 12 observed events. (2) \(2\) hours \(15\) minutes is \(2+15/60=2.25\) hours. (3) The 10 h measurement is the latest one at or before 25 h, so assign its value and retain 10 h as its source measurement time, provided the stated carry-forward rule applies. (4) Do not infer an order from equal timestamps. Preserve the common time and flag the sequence as unresolved unless other documented evidence establishes it. (5) Examples include checking row counts against source counts by event type, checking match multiplicities and unmatched records, and verifying that each output row retains a valid source record identifier.