Data Cleaning Project - World Layoffs Dataset

cleaning_project_world_layoffs.sql

-- PREPARING A DATABASE

-- Use the newly created database
USE world_layoffs;

-- Create table structure
CREATE TABLE layoffs (                                                                                                      
    company TEXT,                                                  
    location TEXT,
    industry TEXT,
    total_laid_off TEXT,
    percentage_laid_off TEXT,
    date TEXT,
    stage TEXT,
    country TEXT,
    funds_raised_millions TEXT
);

-- Load data to the database
LOAD DATA LOCAL INFILE '/Users/mashukan/Documents/1. Data Analytics/Alex the Analyst/Projects/World_layoffs/layoffs.csv' 
INTO TABLE layoffs 
FIELDS TERMINATED BY ','   
ENCLOSED BY '"'   
LINES TERMINATED BY '\n'   
IGNORE 1 ROWS;

-- CREATING A COPY OF THE DATABASE
-- Create a new table with the same columns
CREATE TABLE layoffs_staging
LIKE layoffs;

SELECT *
FROM layoffs_staging;

-- Copy data to the new table
INSERT layoffs_staging
SELECT * 
FROM layoffs;


-- CLEANING DATA

-- 1. LOOKING FOR DUPLICATES

-- CTE to find duplicates
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;

-- Look closer to one of the duplicates to make sure they are identical
SELECT *
FROM layoffs_staging
WHERE company = 'Casper';

-- Make a new empty table like "layoffs_staging" but with the column `row_num`
CREATE TABLE `layoffs_staging2` (
  `company` text,
  `location` text,
  `industry` text,
  `total_laid_off` text,
  `percentage_laid_off` text,
  `date` text,
  `stage` text,
  `country` text,
  `funds_raised_millions` text,
  `row_num` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Insert information into the new table 
INSERT INTO layoffs_staging2
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;

-- Check that data has been inserted
SELECT *
FROM layoffs_staging2
LIMIT 10;

-- Look for duplicates in the new table
SELECT *
FROM layoffs_staging2
WHERE row_num > 1;

-- Delete duplicates in a new table
DELETE
FROM layoffs_staging2
WHERE row_num > 1;

-- Make sure that the duplicates were deleted
SELECT *
FROM layoffs_staging2
WHERE row_num > 1;


-- 2. STANDARTIZING DATA

-- Make sure that there are no spaces before or after the company name
SELECT company, TRIM(company)
FROM layoffs_staging2;

UPDATE layoffs_staging2
SET company = TRIM(company);

SELECT DISTINCT company
FROM layoffs_staging2
ORDER BY 1;

-- Check that industry names are unique
SELECT DISTINCT industry
FROM layoffs_staging2
ORDER BY 1;

-- There are 3 similar names: 'Crypto', 'CryptoCurrency' and 'Crypto Currency', checking them out
SELECT *
FROM layoffs_staging2
WHERE industry LIKE 'Crypto%';

-- Rename all of them to 'Crypto'
UPDATE layoffs_staging2
SET industry = 'Crypto' 
WHERE industry LIKE 'Crypto%';

-- Check that everything is OK with 'location'
SELECT DISTINCT location
FROM layoffs_staging2
ORDER BY 1;

-- Checking that everything is OK with 'stage'
SELECT DISTINCT stage
FROM layoffs_staging2
ORDER BY 1;

-- Checking that everyting is OK with 'country'
SELECT DISTINCT country
FROM layoffs_staging2
ORDER BY 1;

-- There are unnecessary dots after some country names; removing them
SELECT DISTINCT country, TRIM(TRAILING '.' FROM country)
FROM layoffs_staging2
ORDER BY 1;

UPDATE layoffs_staging2
SET country = TRIM(TRAILING '.' FROM country)
WHERE country LIKE "United States%";

-- The type of data in the column `date` is TEXT but it should be DATE
SELECT `date`
FROM layoffs_staging2;
-- There is 'month-day-year' order in our `date' column
-- Change it to the standard SQL date format
SELECT `date`,
STR_TO_DATE(`date`, '%m/%d/%Y')
FROM layoffs_staging2;

UPDATE layoffs_staging2
SET `date` = STR_TO_DATE(`date`, '%m/%d/%Y');

-- Update the type of colunm to DATE
ALTER TABLE layoffs_staging2
MODIFY COLUMN `date` DATE;

SELECT *
FROM layoffs_staging2
LIMIT 10;

-- A few columns (total_laid_off, funds_raised_millions and percentage_laid_off) should be updated to numeric types
-- Select values that are NOT integers in 'total_laid_off'
SELECT total_laid_off 
FROM layoffs_staging2 
WHERE total_laid_off REGEXP '[^0-9]';
-- There are no such values so everything is OK

-- Select values that are NOT decimals in 'funds_raised_millions'
SELECT funds_raised_millions 
FROM layoffs_staging2 
WHERE funds_raised_millions NOT REGEXP '^[0-9]+(\.[0-9]+)?$';

-- There are some values "NULL" (as a string, not SQL NULL), so they should be fixed
UPDATE layoffs_staging2 
SET funds_raised_millions = NULL 
WHERE funds_raised_millions LIKE "NULL%";

-- Select values of 'percentage_laid_off' that are NOT decimals
SELECT percentage_laid_off 
FROM layoffs_staging2 
WHERE percentage_laid_off NOT REGEXP '^[0-9]+(\.[0-9]+)?$';
-- There are no such values so everything is OK


-- Modify data types
ALTER TABLE layoffs_staging2 
MODIFY COLUMN total_laid_off INT;

ALTER TABLE layoffs_staging2 
MODIFY COLUMN funds_raised_millions DECIMAL(10,2);

ALTER TABLE layoffs_staging2 
MODIFY COLUMN percentage_laid_off DECIMAL(10,4);


-- 3. DEALING WITH MISSING VALUES

-- Look at the missing values in 'industry' column
SELECT *
FROM layoffs_staging2
WHERE industry IS NULL 
OR industry = '';

-- There are some blank values, let's see if we can restore them from our data
-- For example, there is a missing 'industry' value for the row where the company is 'Airbnb'
SELECT *
FROM layoffs_staging2
WHERE company = 'Airbnb';
-- We can see that the industry for Airbnb is 'travel'

-- We can restore some lost values for 'industry' from other rows, let's find all of them
SELECT t1.company, t1.location, t1.industry, t2.industry
FROM layoffs_staging2 AS t1
JOIN layoffs_staging2 AS t2
	ON t1.company = t2.company
    AND t1.location = t2.location
WHERE (t1.industry IS NULL OR t1.industry = '')
AND t2.industry IS NOT NULL;

-- Some values are blank, some values are NULL, let's make them all NULL
UPDATE layoffs_staging2
SET industry = NULL
WHERE industry = '';

-- Restore missing data in the 'industry' column
UPDATE layoffs_staging2 AS t1
JOIN layoffs_staging2 AS t2
	ON t1.company = t2.company
    AND t1.location = t2.location
SET t1.industry = t2.industry
WHERE t1.industry IS NULL
AND t2.industry IS NOT NULL;

-- We have left just one row with industry=NULL. If we check, we can see that there's only 
-- one record with this company, so we don't have information which industry it belongs to:
SELECT *
FROM layoffs_staging2
WHERE company LIKE 'Bally%';


-- 4. REMOVING UNNECESSARY DATA 

-- Look for useless rows - where 'total_laid_off' and 'percentage_laid_off' are both NULLs
SELECT *
FROM layoffs_staging2
WHERE total_laid_off IS NULL
AND percentage_laid_off IS NULL;

-- There are 361 rows, and we don't have any information to restore them, so we'll just delete them
DELETE 
FROM layoffs_staging2
WHERE total_laid_off IS NULL
AND percentage_laid_off IS NULL;

-- Delete column row_num, because we don't need it anymore
ALTER TABLE layoffs_staging2
DROP COLUMN row_num;

SELECT COUNT(*)
FROM layoffs_staging2;

layoffs.csv

layoffs_staging2.csv