Data Cleaning: A Meditative Practice for the Mindful Analyst

In data science field, there’s one task that’s often overlooked and underappreciated: data cleaning. Anomalies, missing values, and duplicates can feel like the digital equivalent of a messy mind in the chaotic world of dataset. In the rush of modern data analysis, it's easy to jump straight into the excitement of visualizations, predictions, models, and dashboards. But without a calm and focused entry into the data, we risk building on top of chaos.

That’s where data cleaning enters: the quiet, essential process that mirrors the centering calm of meditation. Both are acts of intentional attention, discipline, and awareness. Let’s take a breath and explore how the data cleaning process is like a meditative practice—where clarity, consistency, and inner peace (for both the dataset and the analyst) emerge.

Data Cleaning is Like a Meditation Process — In a Medical Database (With SQL)

In healthcare, data isn't just numbers—it's the fragile narrative of human lives. Each row tells the story of a patient: their symptoms, their struggles, and their journey toward healing. And just like meditation helps us tune into the subtle layers of our mind, data cleaning helps us unearth the true, unfiltered signal beneath the surface mess of a medical database. Let’s dive into a soothing, step-by-step transformation of a medical records table, while staying deeply mindful of its importance.

In this journey through data cleaning, think of SQL as your meditation mat. We sit down (query your data), observe (SELECT), and begin your mindful flow (UPDATE, DELETE, etc.).

The Dataset: patient_lab_results

Here’s what a raw, uncleaned version of our table might look like:

Let’s take a deep breath, observe this mess, and start mindfully cleaning.

Stage 1: Awareness: Gently Observing the Data

SELECT * FROM patients_lab_results;

Above is the SQL SELECT command. Like the first inhale of a meditation session, we begin with calm observation. In data work, it's a simple SELECT.

We observe the following:

• Inconsistent gender values (Male, female)

• Date formats (01/12/2024, 2024-01-13, etc.)

• Duplicate records (same test, patient, and date)

• Variations in test names (Glucose, glucose fasting)

• Units that need standardization (mg/dL, mg/DL, MG/dl)

• NULL values and text typos

  
  

Instead of reacting, we will respond with calmness.

Stage 2: Mindfulness: Cleaning the data using SQL

Let’s target the above observation one by one.

• Normalization of gender: SQL UPDATE command to normalize gender values in the patient_lab_results table

UPDATE patient_lab_results

SET gender = CASE

    WHEN LOWER(gender) IN ('male', 'm') THEN 'Male'

    WHEN LOWER(gender) IN ('female', 'f') THEN 'Female'

    ELSE NULL

END;

This feels like aligning diverse gender input into a unified, consistent form while gracefully letting go of the unknown!

• Standardization of the date formats: We can standardize the date format to a decided format. We can convert it using TO_DATE. The below ALTER SQL command to convert the test_date column into proper DATE format , handling both slashes and dashes. It's like bringing order to time through mindful transformation.

ALTER TABLE patient_lab_results

ALTER COLUMN test_date TYPE DATE

USING 

  CASE

    WHEN test_date LIKE '%/%/%' THEN TO_DATE(test_date, 'DD/MM/YYYY')

    WHEN test_date LIKE '%-%-%' THEN TO_DATE(test_date, 'DD-MM-YYYY')

    ELSE test_date::DATE

  END;

• Uniformity of test names: We can make the test name more standardized and consistent. Following are the SQL commands to standardize test_type entries by mapping keyword matches to consistent test names with intension and care.

UPDATE patient_lab_results

SET test_type = 'Fasting Glucose'

WHERE LOWER(test_type) LIKE '%glucose%';

UPDATE patient_lab_results

SET test_type = 'Blood Pressure'

WHERE LOWER(test_type) LIKE '%pressure%';

UPDATE patient_lab_results

SET test_type = 'Cholesterol'

WHERE LOWER(test_type) LIKE '%cholesterol%';

• Standardizing and cleaning the units of values: Done intentionally, bringing consistency to measures so each value flows in harmony across the dataset. Here, we can standardize the units of fasting glucose units and cholesterol units as follows,

1. Updating Fasting Glucose units

UPDATE patient_lab_results

SET unit  =  'mg/dL'

WHERE LOWER (TRIM(unit))  = 'mg/dl';

2. Updating Cholestrol units

   
   

UPDATE patient_lab_results

SET unit  =  'mmol/L'

WHERE LOWER (TRIM(unit))  = 'mmol/dl';

• Removing duplicate rows is like clearing the mind in stillness, removing the clutter. retaining only the pure. We will keep only one row from each group of duplicated rows as shown below,

WITH duplicates AS (

  SELECT ctid,

         ROW_NUMBER() OVER (

           PARTITION BY patient_id, test_type, test_date

           ORDER BY result_value  -- or DESC for highest

         ) AS rn

  FROM patient_lab_results

)

DELETE FROM patient_lab_results

WHERE ctid IN (

  SELECT ctid FROM duplicates WHERE rn > 1

);

• Handling NULLs and inappropriate data: In the same way as we clear the mind of distractions in meditation, we can either fill the void with balance which can be substituting missing data with thoughtful defaults or release what no longer serves the clarity of the dataset, removing incomplete rows.

  If we want to keep the rows by filling in appropriate defaults, we can do it as follows,

UPDATE pateint_lab_results

SET 	name = COALESCE(name, 'Unknwon'),

	test_type = COALESCE(test_type, 'Unknown')

WHERE name IS NULL

	OR test_type IS NULL;

 OR we can entirely delete the rows with incomplete data as follows,

DELETE FROM patient_lab_results

WHERE result_value IS NULL

	OR test_type IS NULL;

Stage 3: Clarity Emerges – Feels calming.

After the data cleaning stage our table, like a well-ordered mind looks more trustworthy, consistent and beautiful, ready for deeper insights.

 Now we can query our cleaned data and perform required analytics/ visualization. Below is an example query.

SELECT AVG(result_value) AS average_fasting_glucose

FROM patient_lab_results

WHERE result_value  IS NOT NULL 
	AND test_type = 'Fasting Glucose';  

There can be some additional steps taken for a medical dataset like:

• Using SNOMED terms or ICD codes as a look up table to normalize and standardize the dataset.

• Storing lab tests metadata as a separate reference table.

• Maintain audit logs of all the cleaning steps for compliance.

  
  

Data cleaning as a healing practice

In healthcare system, messy data can lead to wrong decision making which can turn dangerous or even fatal. Cleaning the data isn’t just a technical step but a responsibility. It’s a practice of care, discipline, and clarity along with honoring the patients behind the numbers.

• Every UPDATE is a breath.

• Every CASE is a conscious decision.

• Every DELETE is letting go.

  
  

So, next time when you come across a messy uncleaned raw data, pause. Take a deep breath. Don’t start in hustle but with presence of mind. You are not just cleaning data but creating space for truth or meaning to emerge!