top of page

Cleaning the Chaos: Taming a COVID-19 Layoffs Dataset with MySQL

  • benlusic
  • Jun 16
  • 3 min read


Link to Github


Data cleaning isn't usually the flashiest part of data analytics, but it’s undeniably the most critical. If the underlying data is a mess, any dashboard or predictive model built on top of it is built on sand.

Recently, I decided to tackle a messy, real-world dataset containing global company layoffs during the COVID-19 pandemic. The goal? Transform raw, unformatted, and duplicate-ridden tracking data into a pristine, production-ready relational table.  

Here is a look under the hood at how I structured my data cleaning workflow using MySQL.  


The Blueprint

I approached the dataset with a strict four-step workflow:

  1. Remove Duplicates to protect data integrity.

  2. Standardize the Data so spelling and formatting discrepancies wouldn’t break future aggregations.

  3. Handle Null or Blank Values by imputing data where possible or safely removing unusable records.

  4. Remove Excess Columns to streamline performance.



Step 1: Safeguarding Raw Data & Hunting Duplicates

Before dropping or modifying a single row, I always create a staging table. Working directly on raw data is a recipe for disaster.

CREATE TABLE layoffs_staging LIKE layoffs;
INSERT layoffs_staging SELECT * FROM layoffs;

With my layoffs staging table ready, I set out to find duplicate entries. This turned out to be an iterative learning process. Initially, I applied a standard ROW_NUMBER() window function partitioned by just a handful of columns like company and date. However, I quickly realized the query was falsely flagging unique rows as duplicates because different departments or locations within the same company laid off workers on the same day.

To fix this, I expanded the PARTITION BY clause to include every single column in the dataset, ensuring a true match:  


WITH duplicate_cte AS (
    SELECT *, 
           ROW_NUMBER() OVER(
               PARTITION BY company, location, industry, total_laid_off, 
                            percentage_laid_off, 'date', stage, country, funds_raised_millions
           ) AS row_num 
    FROM layoffs_staging
)
SELECT * FROM duplicate_cte WHERE row_num > 1;

Because MySQL doesn't allow you to directly delete rows from a CTE, I built a secondary staging table (layoffs_staging_2) featuring an explicit row_num column. I inserted the partitioned data, verified the duplicates, and cleanly purged them:

DELETE FROM layoffs_staging_2 WHERE row_num > 1;

Step 2: Data Standardization

Standardization is where you catch the "human error" inherent in crowdsourced or manual data entry. I focused on three major culprits: whitespace, inconsistent naming, and incorrect data types.

  • Trimming Whitespace: Text fields frequently had trailing spaces that would cause identical companies to group separately. A quick TRIM() solved this:

UPDATE layoffs_staging_2 SET company = TRIM(company);

  • Fixing Typos & Variations: In the industry column, I noticed several variations of the crypto sector (e.g., "Crypto", "Crypto Currency"). I grouped them all under a single uniform umbrella: "Crypto". I applied a similar fix to trailing punctuation in the country column.

  • Fixing Date Formats: The date column was imported entirely as text (text), rendering it useless for time-series analysis. I used STR_TO_DATE to convert the string values into a proper SQL date format, then altered the table schema to modify the column to an actual DATE data type:

UPDATE layoffs_staging_2 SET `date` = STR_TO_DATE(`date`, '%m/%d/%Y'); ALTER TABLE layoffs_staging_2 MODIFY COLUMN `date` DATE;

Step 3: Imputing Missing Values

A common dilemma in data analytics is deciding what to do with missing data. Do you delete it, leave it, or populate it?

First, I found rows where the industry value was blank or null. Rather than leaving them empty, I checked if other rows for the exact same company had a populated industry. For instance, Airbnb had some entries missing an industry, but others properly labeled as "Travel".

I wrote a self-join to dynamically update the missing fields based on the company's existing data profile:

UPDATE layoffs_staging_2 t1 
JOIN layoffs_staging_2 t2 
    ON t1.company = t2.company
SET t1.industry = t2.industry
WHERE t1.industry IS NULL 
AND t2.industry IS NOT NULL;

When to Delete:

Conversely, I found several records where both total_laid_off and percentage_laid_off were null. Because the core purpose of this dataset is to analyze layoff rates and volumes, a record missing both metrics offers no analytical value. I safely dropped these rows.


Step 4: Final Cleanup

With the data fully scrubbed and verified, the final step was dropping the helper column (row_num) I created during the duplicate removal phase, keeping the database light and efficient.

ALTER TABLE layoffs_staging_2 DROP COLUMN row_num;

Key Takeaways

By treating data cleaning as a structured, defensive process, I successfully turned a highly fragmented dataset into a reliable asset. This project reinforced a vital reality of being a data analyst: getting your hands dirty in the database architecture is where the real insights begin.   





 
 
 

Comments

bottom of page