Cleaning Health Data

2.4.1 What is data cleaning?

Data cleaning is the process of identifying and addressing problems that could affect the reliability of a dataset.

• Missing values.
• Duplicate records.
• Inconsistent names.
• Incorrect formats.
• Invalid entries.
• Values that require investigation.

Cleaning does not mean changing any value that looks unusual. A value might be correct even if it is unexpected. The analyst must investigate before correcting or removing information.

2.4.2 Missing values

The patient count for Riverside Clinic is missing. We do not know whether the facility did not submit its report, the report was submitted but the value was omitted, the service was not provided, or the value was lost during data entry.

2.4.3 Duplicate records

These rows might be duplicates, but we cannot be certain from the table alone. Before deleting a record, check the source document or the dataset's record-identification rules. Two identical-looking records might represent legitimate separate submissions, depending on how the data was collected.

Remove Duplicates, then check the result

Excel can find repeated rows. Use it on a copy of the file, after you have decided which columns define a duplicate.

1. Save a copy of the workbook. Leave the original untouched.
2. Select the whole data table, including the header row.
3. Open the Data tab and choose Remove Duplicates.
4. Tick only the columns that should match, such as Facility and Month. Leave Patients unticked if two different counts for the same facility and month should stay visible for investigation.
5. Confirm. Excel reports how many duplicate rows it removed.
6. Compare the result with the source documents before you treat the cleaned sheet as final.

TRIM for extra spaces

A facility name can look the same and still fail a filter because of a space at the end. TRIM removes spaces at the beginning and end of text, and reduces repeated spaces inside the text.

1. In a new column, enter =TRIM(A2) if the facility name is in A2.
2. Fill the formula down the column.
3. Check that the cleaned names look right.
4. Copy the new column and Paste Values over the original names only after you have compared them.
5. TRIM does not decide that Riverside and Riverside Clinic are the same facility. That still needs an approved facility list.

2.4.4 Inconsistent text

• Central Clinic
• central clinic
• Central clinic

These values may refer to the same facility. Use an approved facility list or unique facility identifier to standardise the names. Avoid making assumptions based only on similar spelling.

2.4.5 Implausible values

Suppose a monthly report records 9,000 patient visits at a small clinic that usually records around 100. That value deserves investigation. It might be a typing error, a misplaced digit, a legitimate unusual event, or a difference in how visits were counted.

2.4.6 A practical data-cleaning workflow

1. Preserve the original dataset.
2. Examine the column headings and structure.
3. Check missing values.
4. Investigate possible duplicates.
5. Review inconsistent names and formats.
6. Flag implausible or invalid values.
7. Verify suspected errors against appropriate source information.
8. Make documented corrections.
9. Record unresolved issues.
10. Recheck the cleaned dataset.

Lesson 2.4 exercise

Identify three items that require investigation.