Preparing Medical Data for Analysis

Overview

Medical data are rarely ready for analysis when first exported from a data-capture system, spreadsheet, laboratory platform or clinical record. Variables may use inconsistent names, repeated measurements may occupy unsuitable columns, dates may be stored as text, category labels may vary and derived outcomes may not yet exist.

Data preparation is the controlled process of converting collected data into a structure that supports the prespecified analysis while preserving clinical meaning, provenance and confidentiality. Provenance describes where a value came from and how it was transformed.

The goal is not to create a visually attractive spreadsheet. The goal is to produce an analysis-ready dataset that another qualified analyst can understand and reproduce from the preserved source data.

Why this matters

Poorly structured data create avoidable errors. A value of 140 is uninterpretable unless the variable, unit, time point and measurement rule are known. A code of 9 may represent severe disease, missing information or the ninth participant. A spreadsheet containing merged cells, several values in one cell or colour-coded exclusions may be readable to its creator but difficult to validate or analyse.

Good organisation separates:

• source observations from derived variables;
• participant identifiers from direct personal identifiers;
• genuine zero values from missing values;
• measured dates from elapsed durations;
• categorical labels from numerical quantities; and
• raw data from later corrections and transformations.

A consistent rectangular structure—one variable per column, one observation per row and one type of observational unit per table—reduces unnecessary restructuring and supports analysis and visualisation [1], [2].

The data lifecycle

Source data

Source data are the original records from which study variables are obtained. Depending on the study, these may include electronic case-report forms, laboratory results, device files, imaging measurements or clinical records.

Source data should not be overwritten merely to simplify analysis. Corrections should follow the study’s documented data-management process and preserve an audit trail where required.

Raw exported data

The raw export is the first stable copy transferred from each source system for processing. It should be stored read-only or otherwise protected from accidental editing.

Raw export files may not be identical to source records because the source system may apply export formats, labels or date conversions. The transfer date, source system, export settings and file checksum should therefore be documented where appropriate.

Working data

A working dataset is an intermediate file created during validation, restructuring, recoding or merging. It should be reproducibly generated from the raw exports.

Analysts should avoid a chain of manually renamed files such as:

data_final.xlsx
data_final2.xlsx
data_really_final.xlsx

A version-controlled script and documented folder structure provide a clearer record.

Analysis dataset

The analysis dataset contains the variables and observations required for a specified analysis, using documented definitions and derivations.

It is not necessarily one universal file. The primary analysis, safety analysis and repeated-measurement analysis may require different but traceable datasets.

Plan the observation structure

One row per participant

A participant-level table is suitable when each variable has one value per participant.

Table 1. Participant-level structure

Note: SBP = systolic blood pressure. Each participant appears once.

This structure is convenient for a primary analysis using one baseline and one follow-up measurement.

One row per participant-visit

Repeated measurements are often better represented in a long-format visit table.

Table 2. Visit-level structure

Note: The combination of participant identifier and visit week should be unique when only one clinic assessment is permitted per visit.

Long format makes the repeated structure explicit. It prevents the analyst from treating several measurements from one participant as independent participants.

One row per event or measurement

Some data require their own table:

• one row per adverse event;
• one row per medication change;
• one row per home blood-pressure reading;
• one row per hospital admission.

Trying to place an unknown number of events into columns such as event1, event2 and event3 creates artificial limits and difficult missing-value patterns.

Create a data dictionary

A data dictionary describes each variable in a form that supports collection, validation and analysis. Metadata-driven systems such as REDCap use structured variable definitions to support reproducible data capture [3].

Table 3. Example data-dictionary entries

Note: A range check identifies values requiring review; it does not prove that values within the range are correct.

A useful dictionary includes:

• stable variable name;
• descriptive label;
• clinical definition;
• role in the study;
• data type and measurement scale;
• unit;
• permitted values;
• category coding;
• missing-value representation;
• source;
• derivation;
• timing;
• validation checks; and
• notes on confidentiality.

Variable names and category coding

Use variable names that are:

• unique;
• stable;
• concise but meaningful;
• free from spaces and ambiguous punctuation;
• consistent across files; and
• not dependent on display order.

Names such as v1, result2 and final_bp are difficult to interpret. Names such as sbp_baseline, sbp_week12 and death_30d communicate variable and timing.

Category coding should be documented. Numerical codes remain labels when the variable is categorical.

For example:

allocated_group:
T = Telemonitoring
U = Usual care

Using 1 and 2 is also possible, but the dictionary must make clear that their difference and average have no clinical meaning.

Do not encode information only through:

• cell colour;
• bold font;
• comments;
• blank rows;
• column position; or
• worksheet names.

These features are easily lost during export.

Identifiers and confidentiality

A study identifier distinguishes participants without directly revealing identity. It should be unique, stable and stored as text when leading zeros matter.

Direct identifiers, such as names, national identification numbers and telephone numbers, should not be included in the analysis dataset unless essential and authorised.

The simulated study uses:

• a restricted linkage table containing participant_id and contact details;
• a clinical dataset containing participant_id but no direct identifiers; and
• a separate randomisation identifier retained for verification.

Access should follow the protocol, ethics approval, institutional policy and applicable law. Current good-clinical-practice guidance also emphasises reliable data, metadata, traceability and proportionate control of computerised systems [4]. De-identification does not eliminate every re-identification risk, especially when rare characteristics and dates are combined.

Dates, times and units

Dates should be stored in a recognised date format, not as ambiguous text such as 03/04/26, which may mean 3 April or 4 March.

For exchange and documentation, ISO 8601 formats are useful:

2026-04-03
2026-04-03T14:30:00+08:00

Record time zone when relevant to device, multicentre or international data.

Durations should be derived by subtracting valid date-times and stored with explicit units:

days_from_randomisation
minutes_to_treatment
followup_days

Numerical variables should use one prespecified unit. Mixing kg and lb, or mmol/L and mg/dL, in one column can create clinically serious errors. If conversion is required, preserve the source value and unit and document the conversion equation.

Missing values

Missing information should be represented explicitly by the data system or analysis software, not by plausible numerical values such as:

• 0;
• 99;
• 999;
• -1; or
• a blank whose meaning is uncertain.

Distinguish reasons when they matter:

• not measured;
• participant declined;
• not applicable;
• lost to follow-up;
• value pending;
• device failure.

The analysis variable may use one recognised missing value while a separate reason variable preserves the explanation.

Missing data are examined in BIO-P02-M03.

Derived variables

A derived variable is calculated from one or more source variables using a prespecified rule.

Examples include:

• mean week-12 systolic blood pressure from repeated readings;
• age at randomisation from date of birth and randomisation date;
• body mass index from weight and height;
• follow-up duration from dates; and
• adherence percentage from completed and expected transmissions.

Each derivation should state:

• input variables;
• formula or algorithm;
• unit;
• handling of missing or invalid inputs;
• timing rule;
• rounding rule; and
• version of any external classification.

Do not replace source variables with derived values.

Merging files safely

Files should be joined using verified keys.

Before merging:

1. confirm that the key has the expected uniqueness;
2. check key format in both files;
3. identify unmatched records;
4. examine duplicated matches;
5. confirm the expected number of rows after the join; and
6. document which source takes precedence when values conflict.

A many-to-many join can multiply rows silently. For example, two duplicate participant records in one file and three in another may produce six joined rows.

Simulated linked data tables

simulated data

Table 4. Participant table

participant_id	clinic	allocated_group
HTM001	A	Telemonitoring
HTM002	B	Usual care
HTM003	A	Telemonitoring

Table 5. Visit table

participant_id	visit_week	sbp_clinic
HTM001	0	154 mmHg
HTM001	12	136 mmHg
HTM002	0	150 mmHg
HTM002	12	143 mmHg
HTM003	0	158 mmHg

Note: HTM003 has no week-12 row. Absence of a row should be distinguished from a row containing an explicitly missing measurement.

Worked example

FAIR principles and reproducible research data MMed extension

The FAIR principles state that digital research objects should be findable, accessible, interoperable and reusable [5]. FAIR does not mean that confidential participant-level data must be openly accessible.

For medical data, responsible reuse may require:

• controlled access;
• metadata that describe the dataset without exposing identities;
• documented variables and provenance;
• persistent identifiers for approved data objects;
• standard formats and vocabularies;
• licences or data-use conditions; and
• reproducible code.

Data can be FAIR while remaining appropriately restricted. Ethical, legal and governance requirements determine who may access them.

Suggested reporting

Common mistakes

Knowledge check

Why should the raw export be preserved unchanged?

Answer and reasoning

It provides a stable record of what was received from the source system. If a derivation, correction or exclusion is later questioned, the analysis dataset can be regenerated and compared with the preserved export.

A participant has four clinic visits. Should every visit be placed in a separate participant column?

Answer and reasoning

Not necessarily. A long visit table with one row per participant-visit often represents repeated data more clearly and scales to varying numbers of visits. A participant-level wide file may later be derived for a specific analysis.

Why is 999 an unsafe general code for a missing blood-pressure value?

Answer and reasoning

It can be mistaken for an observed number, affect summaries and require special recoding in every analysis. A recognised missing value should be used, with a separate variable documenting the reason when relevant.

What should be checked before joining a participant table to a visit table?

Answer and reasoning

The analyst should verify the key format and uniqueness expected in each table, identify unmatched identifiers, examine duplicates and confirm that the resulting row count matches the intended one-to-many relationship.

Does de-identification mean that the analysis dataset can always be shared publicly?

Answer and reasoning

No. Combinations of dates, rare characteristics and other variables may permit re-identification. Access must still follow ethics approval, consent, institutional governance and applicable legal requirements.

Key take-home messages

References

1. Broman KW, Woo KH. Data organization in spreadsheets. Am Stat. 2018;72(1):2–10. https://doi.org/10.1080/00031305.2017.1375989
2. Wickham H. Tidy data. J Stat Softw. 2014;59(10):1–23. https://doi.org/10.18637/jss.v059.i10
3. Harris PA, Taylor R, Thielke R, Payne J, Gonzalez N, Conde JG. Research electronic data capture (REDCap)—a metadata-driven methodology and workflow process for providing translational research informatics support. J Biomed Inform. 2009;42(2):377–381. https://doi.org/10.1016/j.jbi.2008.08.010
4. International Council for Harmonisation of Technical Requirements for Pharmaceuticals for Human Use. ICH E6(R3) guideline for good clinical practice: principles and Annex 1. Geneva: ICH; 2025. https://database.ich.org/sites/default/files/ICH_E6%28R3%29_Step4_FinalGuideline_2025_0106.pdf
5. Wilkinson MD, Dumontier M, Aalbersberg IJ, Appleton G, Axton M, Baak A, et al. The FAIR guiding principles for scientific data management and stewardship. Sci Data. 2016;3:160018. https://doi.org/10.1038/sdata.2016.18