The Rounds Journal · Health data

Cleaning healthcare data: a worked example with synthetic records

Practice checking dates, duplicates and missing values with six synthetic appointment records. Learn why a clean-looking table can still give a misleading answer.

Written byThe Rounds editorial team
Published
Reading time5 min read

Data cleaning is the work of making records usable for a defined question while preserving a clear account of what changed. It is not simply removing blanks or making every column look tidy. This example uses invented appointment records; it contains no patient information.

Begin with the question and definitions

Suppose a fictional clinic wants to understand recorded arrival-to-review time. The unit of analysis is an appointment, not a person. The appointment identifier should be unique. Arrival and review timestamps use the same time zone, and the interval is review time minus arrival time. These definitions belong in a small data dictionary before you calculate an average.

AppointmentArrivalReviewWhat to check
A0109:0009:20Complete interval: 20 minutes.
A0209:0509:35Complete interval: 30 minutes.
A0209:0509:35Possible duplicate record.
A0309:15MissingNo observed review interval.
A0410:1009:55Negative interval: investigate timestamps.
A0509:3010:10Complete interval: 40 minutes.

Keep the source and record your decisions

Retain an unchanged source copy in the approved location. Make transformations in a separate working version and record the rule, reason and affected records. Before dropping A02, verify that the identifier and source process establish a true duplicate; two similar rows are not always the same event.

Do not turn A03’s missing review time into zero. Zero would mean no wait, which was not observed. Do not silently reverse A04’s timestamps or take an absolute value to make the interval positive. Check the source; if it cannot be resolved, mark the interval invalid for this calculation.

Calculate only after the rules are explicit

For this exercise, assume the source confirms A02 is duplicated and A04 cannot be corrected. The three complete valid intervals are 20, 30 and 40 minutes. Their mean is 30 minutes: (20 + 30 + 40) ÷ 3. Report that this describes three valid appointments out of five unique appointment records, with one missing and one invalid interval.

The completeness rate for usable intervals in this tiny example is 3 ÷ 5, or 60%. The average alone hides the missing information. This is an illustrative calculation, not a real clinic benchmark or evidence that a service is performing well.

Ask whether missingness changes the story

If missing review times occur during the busiest periods, the available records may not represent the whole clinic. Investigate when and why information is missing. Compare the collection process before comparing teams. A difference in documentation can look like a difference in service performance.

Present the question, definitions, exclusions and denominator alongside the finding. Another analyst should be able to understand how you reached it.

Take the exercise further

  • Write a rule for handling appointments that cross midnight.
  • State how cancelled appointments should be identified and excluded from this question.
  • Explain which extra fields would help investigate missing review times.
  • Create a one-paragraph finding that includes the sample size and limitations.

Continue with choosing analytics study or data ownership and governance.

Sources and scope

WHO data quality assurance toolkit

Sources checked 15 November 2026. Examples and planning exercises are editorial guidance, not reported outcomes or individual admission decisions. Confirm current requirements directly before applying.

Turn the decisions into a cleaning log

Write one row in your log for each issue. For the duplicate A02, record the raw row identifiers, the evidence that they represent the same appointment and which record you retained. For A03, record that the review time was missing and that the appointment contributes to the count of unique appointments but does not contribute an observed waiting time. For A04, record the negative interval and the source check you would make. This keeps three very different issues from becoming a single unexplained reduction in the number of rows.

Compare the counts at each stage. The fictional input has six rows. After removal of one confirmed duplicate it has five unique appointments. Of those five, three have a usable interval. If a colleague gets four usable rows, compare the record IDs before comparing the means. They may have counted the duplicate or used a different rule for invalid timestamps. The log makes it possible to resolve the discrepancy and agree which version answers the question.

Test one change to a rule and explain the effect

Suppose A04 is found in a fictional source log with a corrected review time of 10:30. Its interval would then be 20 minutes. The usable count would rise from three to four, and the mean would become 27.5 minutes, based on intervals of 20, 30, 20 and 40 minutes. Keep the correction and its evidence with the output. If the source cannot establish the corrected time, leave A04 invalid and keep the original result of 30 minutes for the three usable appointments.

Use the example as a check on your own reports. Before sharing a chart, ask a colleague to locate the raw count, the accepted records, the exclusions and the evidence for any correction. If they can reproduce both the denominator and the result, your report is much easier to review and reuse.

Record the reporting period and the date you extracted the source. If a late correction arrives after the report has been circulated, label the updated result and tell readers which previous figure it replaces.

Explore more resources →