Why Transformations Need a Trace
A prepared pharmacometric dataset often contains values that were not directly recorded in a source file: elapsed time from a dose, a body-size measure calculated from height and weight, or a concentration expressed in a common unit. These fields can be essential for analysis, but their usefulness depends on being able to explain exactly how each value was obtained.
A derived value is not fully documented by its column name or by a note saying “calculated.” A reviewer needs to know which source records and fields contributed to it, what rule was applied, which units and parameters were used, and how missing or ambiguous inputs were handled. Traceability means retaining enough of this information to reproduce the prepared value and understand its lineage, while keeping the original source values intact.
Specify the Transformation Before Applying It
Treat each derived field as an explicit function of named inputs. A general representation is \(y=f(x_1,\ldots,x_k;\theta)\), where \(y\) is the prepared value, the \(x_i\) are source values, and \(\theta\) denotes any specified constants or parameters. For example, a unit conversion has a conversion factor in \(\theta\); an elapsed-time calculation has a defined reference event and time unit.
For the rule to reproduce a value, the source inputs must be identified, the rule must be unambiguous, and the assumptions required by that rule must hold. The rule also needs a declared policy for missing or unusable inputs. A useful transformation record therefore captures:
- Lineage: the source file or dataset version, record identifiers, and input field names.
- Rule: the formula or operation, including the meaning of its inputs and any reference event.
- Measurement details: units, time zone or time convention where relevant, and any conversion factors.
- Implementation: the code or documented procedure, its version, and any parameters that can change the result.
- Output policy: treatment of missing inputs, precision or rounding, and the name and units of the derived field.
The details required depend on the transformation. A fixed conversion between units may need only a factor and unit definitions. A time derived from two event records also needs a rule for selecting those records and a common time basis. Documentation should be specific enough that a different analyst can apply the same rule without guessing.
Record which source records and fields feed the derivation. Keep their recorded values and units available without overwriting them.
State the formula, reference event, conversion factor, and conditions under which the inputs are interpretable.
Generate the derived field using a documented procedure, and preserve the relevant code or procedure version and run parameters.
Recalculate the field from the retained inputs and recorded rule; investigate any difference rather than silently replacing it.
Worked Transformations
Worked Example: Deriving Time Since Dose
Suppose a hypothetical participant has a recorded dose at 08:00 and a concentration sample at 10:30 on the same date. The prepared field is elapsed time from that dose to that sample, in hours. Assume the dose and sample timestamps are actual event times, refer to the same participant and date, and have already been expressed in the same time zone. The dose record is the selected reference event for this sample.
Let \(t_{\text{sample}}\) and \(t_{\text{dose}}\) be the event timestamps. The documented rule is:
Converting the timestamps to seconds after midnight gives \(t_{\text{sample}}=10(3600)+30(60)=37{,}800\ \text{s}\) and \(t_{\text{dose}}=8(3600)=28{,}800\ \text{s}\). Therefore:
A trace for this value would retain the dose and sample record identifiers, both original timestamps, their source fields, the common time-zone convention, and the rule that selects the dose record. If any of these details changed, the result might change too. For example, using a planned time instead of the actual sample time is a different derivation and should be named and documented separately. If a required timestamp is missing, the transformation should follow a recorded missing-input policy; it should not quietly substitute a planned time.
Worked Example: Calculating and Retaining BMI
Consider hypothetical source values for one participant: weight \(68.4\ \text{kg}\) and height \(1.72\ \text{m}\). Assume the recorded units are correct, both measurements refer to the intended assessment, and the documented derivation uses weight in kilograms and height in metres. The derived field is body mass index (BMI), in \(\text{kg/m}^2\).
The formula is:
Substituting the source values and calculating the squared height:
The units follow from dividing kilograms by square metres. A traceable prepared dataset retains the original weight and height fields, their units, assessment identifiers, and the formula; it stores the BMI in a separate field. If the field is rounded to four significant figures for a particular output, that policy should be stated. Retaining the unrounded calculation internally, when feasible, avoids making display rounding an undocumented part of the derivation.
Worked Example: Converting a Concentration Unit
In a hypothetical assay record, a quantified concentration is \(850\ \text{ng/mL}\). A preparation rule requires a second field in \(\text{mg/L}\). Assume the value is a numeric concentration, its recorded unit is confirmed to be \(\text{ng/mL}\), and this conversion is applied to quantified values without changing their assay qualifiers.
Using \(1\ \text{mg}=10^6\ \text{ng}\) and \(1\ \text{L}=10^3\ \text{mL}\), the conversion is:
The units cancel to \(\text{mg/L}\), and the numeric factor is \(10^{-3}\), so the same calculation can be written \(850\times 10^{-3}=0.850\ \text{mg/L}\). The output should remain a separate field linked to the source assay record. Its trace identifies the source value and unit, conversion rule, target unit, and implementation version. If the source value or unit changes, the converted field can be regenerated rather than manually patched.
Why Reproduction Works
The core justification is simple: a deterministic transformation gives the same output when its inputs, rule, parameters, and relevant implementation conditions are the same. For \(y=f(x_1,\ldots,x_k;\theta)\), retaining the \(x_i\) values and \(\theta\), and specifying \(f\), makes the calculation repeatable. If the rule includes a choice—such as which dose is the reference for a sample—that choice is part of the function and must also be recorded.
This explains why keeping only a derived value is insufficient. If the original height is overwritten by BMI, the original input needed to check or recalculate BMI is gone. If a conversion factor is not recorded, a later analyst cannot distinguish a deliberate conversion from a mistaken scale. If a time-zone convention is omitted, identical-looking timestamps can represent different elapsed times. Traceability is therefore not merely a narrative note; it preserves the information required to reconstruct the computation.
The reproducibility claim has limits. It assumes the source values have been identified correctly, the formula is appropriate for the intended field, and any software behavior that affects the output is sufficiently specified. A documented calculation cannot make a wrong source selection correct. Nor can a record reproduce a result if an essential input, parameter, or rule was never retained.
Diagnostics, Alternatives, and Limitations
A practical diagnostic is to select a sample of derived records and recompute them independently from their recorded source inputs. Compare the regenerated value with the prepared field using a tolerance appropriate to the units and stated precision. For exact unit factors and time differences in a fixed time basis, exact agreement may be expected before rounding. If comparison occurs after rounding, the tolerance should reflect that rounding rule rather than be chosen after seeing discrepancies.
Investigate mismatches by tracing the inputs and the operation in order. Common causes include an incorrect source record, a mislabeled unit, an omitted conversion factor, a change in reference event, or rounding at an intermediate step. Also check that the recorded procedure version and parameters correspond to the run that generated the prepared data. These are transformation-lineage checks; they do not replace separate checks of dataset structure or broader data quality.
| Approach | Strength | Traceability risk or limitation |
|---|---|---|
| Keep source fields and generate documented derived fields | Supports recalculation and review while preserving recorded values | Requires maintaining clear links between inputs, rules, and outputs |
| Overwrite source fields with transformed values | Can make a working table look simpler | Destroys the original value or unit and can make the derivation impossible to verify |
| Apply manual edits without a recorded rule | May be quick for an isolated correction | Does not reliably reproduce the value or reveal which records were changed |
A script is often more reproducible than repeated manual calculations, but using code alone does not guarantee traceability. The relevant code version, input data version, parameters, and selection rules still need to be identifiable. Conversely, a carefully controlled manual derivation can be traceable if the inputs, arithmetic, and decisions are recorded, although repeating it across many records increases the opportunity for inconsistent application.
Not every operation is reversible. Rounding, categorizing, or replacing values can discard information. Preserve the untransformed source values and document any intentional loss, such as an output rounded to a specified precision. Missing-value handling also needs explicit treatment: if a required input is absent, state whether the derived field is missing and how that status is recorded. Do not invent a value or default merely to complete a column.
Common Mistakes
- Overwriting an input. Replacing recorded weight with BMI loses an input needed to reproduce BMI. Keep the source and derived fields distinct.
- Documenting only the formula. A formula without source record selection, units, or parameters can still leave multiple plausible results. Record the complete rule.
- Treating a value as self-explanatory. A number such as 2.5 cannot be interpreted without the field meaning and unit. Store units and definitions with the prepared field.
- Using an undocumented default. Substituting a planned time when an actual time is missing changes the derivation. Record a separate rule if that substitution is intended.
- Rounding too early. Intermediate rounding can change the final result. State the precision policy and retain sufficient precision for reproducibility.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions below.
- A source record gives height as \(1.65\ \text{m}\) and weight as \(60.0\ \text{kg}\). What information, besides the BMI formula, should be retained to make the derived BMI traceable?
- A dose is recorded at 09:15 and a sample at 11:45 on the same date and time basis. Calculate elapsed time in hours and state one assumption needed for the calculation.
- Convert \(420\ \text{ng/mL}\) to \(\text{mg/L}\), showing the conversion factor.
- An analyst overwrites the source concentration in \(\text{ng/mL}\) with a converted concentration in \(\text{mg/L}\). Explain the traceability problem and give a better practice.
Solutions. (1) Retain the source weight and height values and units, the relevant record identifiers and assessment context, the formula, the output unit, and the implementation or precision policy. The source measurements must not be replaced by BMI. (2) The difference is \(11{:}45-09{:}15=2\ \text{h}\ 30\ \text{min}=2.5\ \text{h}\). This assumes both timestamps refer to the same time basis and the selected dose is the reference event for the sample. (3) Since \(1\ \text{ng/mL}=0.001\ \text{mg/L}\), \(420\times0.001=0.420\ \text{mg/L}\). (4) Overwriting removes the original value and unit, so a reviewer cannot directly verify or regenerate the conversion from the prepared field alone. Retain the source concentration and unit, create a separate converted field, and document the factor and lineage.