19 - Data Cleaning in MySQL

19 - Data Cleaning in MySQL


19 - Data Cleaning in MySQL

https://youtu.be/4UltKCnnnTA?si=vccggkAZ_aNDGFcq

TOTAL: 51:10

NOW: 06:30

-- SQL Project - Data Cleaning

-- https://www.kaggle.com/datasets/swaptr/layoffs-2022 => lấy file CSV từ link này (link local: D:\LEARNING\KHÁC\BI_DEVELOPER\SQL\ALEX_THE_ANALYSIS\01-MYSQL\layoffs.csv)

-- tạo mới 01 schema tên world_layoffs -> nhấp phải lên mục Table - chọn Table Data Import Wizard - Create New...Drop if Exist - Next


SELECT *
FROM world_layoffs.layoffs;


-- first thing we want to do is create a staging table. This is the one we will work in and clean the data. We want a table with the raw data in case something happens
-- Tạo 01 table rỗng giống hệt world_layoffs

CREATE TABLE world_layoffs.layoffs_staging
LIKE world_layoffs.layoffs;

-- Copy ALL data world_layoffs vào table mới tạo -> thao tác này để backup world_layoffs

INSERT layoffs_staging
SELECT * FROM world_layoffs.layoffs;

-- MỤC TIÊU BÀI 19:
-- now when we are data cleaning we usually follow a few steps
-- 01. check for duplicates and remove any
-- 02. standardize data and fix errors
-- 03. Look at null values and see what
-- 04. remove any columns and rows that are not necessary - few ways


-- 01. Remove Duplicates

-- First let's check for duplicates


SELECT *
FROM world_layoffs.layoffs_staging
;

SELECT company, industry, total_laid_off,`date`,
        ROW_NUMBER() OVER (
            PARTITION BY company, industry, total_laid_off,`date`) AS row_num
    FROM
        world_layoffs.layoffs_staging;


SELECT *
FROM (
    SELECT company, industry, total_laid_off,`date`,
        ROW_NUMBER() OVER (
            PARTITION BY company, industry, total_laid_off,`date`
            ) AS row_num
    FROM
        world_layoffs.layoffs_staging
) duplicates
WHERE
    row_num > 1;
   
-- let's just look at oda to confirm
SELECT *
FROM world_layoffs.layoffs_staging
WHERE company = 'Oda'
;
-- it looks like these are all legitimate entries and shouldn't be deleted. We need to really look at every single row to be accurate

-- these are our real duplicates
SELECT *
FROM (
    SELECT company, location, industry, total_laid_off,percentage_laid_off,`date`, stage, country, funds_raised_millions,
        ROW_NUMBER() OVER (
            PARTITION BY company, location, industry, total_laid_off,percentage_laid_off,`date`, stage, country, funds_raised_millions
            ) AS row_num
    FROM
        world_layoffs.layoffs_staging
) duplicates
WHERE
    row_num > 1;

-- these are the ones we want to delete where the row number is > 1 or 2or greater essentially

-- now you may want to write it like this:
WITH DELETE_CTE AS
(
SELECT *
FROM (
    SELECT company, location, industry, total_laid_off,percentage_laid_off,`date`, stage, country, funds_raised_millions,
        ROW_NUMBER() OVER (
            PARTITION BY company, location, industry, total_laid_off,percentage_laid_off,`date`, stage, country, funds_raised_millions
            ) AS row_num
    FROM
        world_layoffs.layoffs_staging
) duplicates
WHERE
    row_num > 1
)
DELETE
FROM DELETE_CTE
;


WITH DELETE_CTE AS (
    SELECT company, location, industry, total_laid_off, percentage_laid_off, `date`, stage, country, funds_raised_millions,
    ROW_NUMBER() OVER (PARTITION BY company, location, industry, total_laid_off, percentage_laid_off, `date`, stage, country, funds_raised_millions) AS row_num
    FROM world_layoffs.layoffs_staging
)
DELETE FROM world_layoffs.layoffs_staging
WHERE (company, location, industry, total_laid_off, percentage_laid_off, `date`, stage, country, funds_raised_millions, row_num) IN (
    SELECT company, location, industry, total_laid_off, percentage_laid_off, `date`, stage, country, funds_raised_millions, row_num
    FROM DELETE_CTE
) AND row_num > 1;

-- one solution, which I think is a good one. Is to create a new column and add those row numbers in. Then delete where row numbers are over 2, then delete that column
-- so let's do it!!

ALTER TABLE world_layoffs.layoffs_staging ADD row_num INT;


SELECT *
FROM world_layoffs.layoffs_staging
;

CREATE TABLE `world_layoffs`.`layoffs_staging2` (
`company` text,
`location`text,
`industry`text,
`total_laid_off` INT,
`percentage_laid_off` text,
`date` text,
`stage`text,
`country` text,
`funds_raised_millions` int,
row_num INT
);

INSERT INTO `world_layoffs`.`layoffs_staging2`
(`company`,
`location`,
`industry`,
`total_laid_off`,
`percentage_laid_off`,
`date`,
`stage`,
`country`,
`funds_raised_millions`,
`row_num`)
SELECT `company`,
`location`,
`industry`,
`total_laid_off`,
`percentage_laid_off`,
`date`,
`stage`,
`country`,
`funds_raised_millions`,
        ROW_NUMBER() OVER (
            PARTITION BY company, location, industry, total_laid_off,percentage_laid_off,`date`, stage, country, funds_raised_millions
            ) AS row_num
    FROM
        world_layoffs.layoffs_staging;

-- now that we have this we can delete rows were row_num is greater than 2

DELETE FROM world_layoffs.layoffs_staging2
WHERE row_num >= 2;


-- 02. Standardize Data

SELECT *
FROM world_layoffs.layoffs_staging2;

-- if we look at industry it looks like we have some null and empty rows, let's take a look at these
SELECT DISTINCT industry
FROM world_layoffs.layoffs_staging2
ORDER BY industry;

SELECT *
FROM world_layoffs.layoffs_staging2
WHERE industry IS NULL
OR industry = ''
ORDER BY industry;

-- let's take a look at these
SELECT *
FROM world_layoffs.layoffs_staging2
WHERE company LIKE 'Bally%';
-- nothing wrong here
SELECT *
FROM world_layoffs.layoffs_staging2
WHERE company LIKE 'airbnb%';

-- it looks like airbnb is a travel, but this one just isn't populated.
-- I'm sure it's the same for the others. What we can do is
-- write a query that if there is another row with the same company name, it will update it to the non-null industry values
-- makes it easy so if there were thousands we wouldn't have to manually check them all

-- we should set the blanks to nulls since those are typically easier to work with
UPDATE world_layoffs.layoffs_staging2
SET industry = NULL
WHERE industry = '';

-- now if we check those are all null

SELECT *
FROM world_layoffs.layoffs_staging2
WHERE industry IS NULL
OR industry = ''
ORDER BY industry;

-- now we need to populate those nulls if possible

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

-- and if we check it looks like Bally's was the only one without a populated row to populate this null values
SELECT *
FROM world_layoffs.layoffs_staging2
WHERE industry IS NULL
OR industry = ''
ORDER BY industry;

-- ---------------------------------------------------

-- I also noticed the Crypto has multiple different variations. We need to standardize that - let's say all to Crypto
SELECT DISTINCT industry
FROM world_layoffs.layoffs_staging2
ORDER BY industry;

UPDATE layoffs_staging2
SET industry = 'Crypto'
WHERE industry IN ('Crypto Currency', 'CryptoCurrency');

-- now that's taken care of:
SELECT DISTINCT industry
FROM world_layoffs.layoffs_staging2
ORDER BY industry;

-- --------------------------------------------------
-- we also need to look at

SELECT *
FROM world_layoffs.layoffs_staging2;

-- everything looks good except apparently we have some "United States" and some "United States." with a period at the end. Let's standardize this.
SELECT DISTINCT country
FROM world_layoffs.layoffs_staging2
ORDER BY country;

UPDATE layoffs_staging2
SET country = TRIM(TRAILING '.' FROM country);

-- now if we run this again it is fixed
SELECT DISTINCT country
FROM world_layoffs.layoffs_staging2
ORDER BY country;


-- Let's also fix the date columns:
SELECT *
FROM world_layoffs.layoffs_staging2;

-- we can use str to date to update this field
UPDATE layoffs_staging2
SET `date` = STR_TO_DATE(`date`, '%m/%d/%Y');

-- now we can convert the data type properly
ALTER TABLE layoffs_staging2
MODIFY COLUMN `date` DATE;


SELECT *
FROM world_layoffs.layoffs_staging2;


-- 03. Look at Null Values

-- the null values in total_laid_off, percentage_laid_off, and funds_raised_millions all look normal. I don't think I want to change that
-- I like having them null because it makes it easier for calculations during the EDA phase

-- so there isn't anything I want to change with the null values


-- 04. remove any columns and rows we need to

SELECT *
FROM world_layoffs.layoffs_staging2
WHERE total_laid_off IS NULL;


SELECT *
FROM world_layoffs.layoffs_staging2
WHERE total_laid_off IS NULL
AND percentage_laid_off IS NULL;

-- Delete Useless data we can't really use
DELETE FROM world_layoffs.layoffs_staging2
WHERE total_laid_off IS NULL
AND percentage_laid_off IS NULL;

SELECT *
FROM world_layoffs.layoffs_staging2;

ALTER TABLE layoffs_staging2
DROP COLUMN row_num;


SELECT *
FROM world_layoffs.layoffs_staging2;

Nhận xét

Đăng nhận xét

Bài đăng phổ biến từ blog này

🌈 Tự Động Highlight Code Trong Blogger

Phần style pre code { white-space: pre-wrap; word-break: break-word; } .code-wrapper { position: relative; } .copy-button { position: absolute; top: 8px; right: 8px; background: #444; color: #fff; border: none; font-size: 0.75rem; padding: 4px 8px; border-radius: 6px; cursor: pointer; opacity: 0.7; z-index: 9; } .copy-button:hover { opacity: 1; } Chèn trước thẻ đóng head Code nhiều dòng hay 01 dòng xài giống nhau: const msg = "Xin chào Tiến sĩ M!"; console.log(msg); Code 01 dòng: document.querySelector() Thẻ nào quá phức tạp thì xài như sau: < pre class = "language-js" data-keep-markup >   < code >     Nội dung...   </ code > </ pre > Chèn trước thẻ đóng body < b : if cond = ...
Đọc thêm

🧭CRUD CHUẨN LARAVEL

Errors log: Cài  Laravel  trên host nhưng k phù hợp  php  version Dùng lệnh  artisan  trong  Terminal  nhưng chưa đứng trong thư mục chứa source  Laravel Lỗi  ABCController not found  khi chạy artisan -> do thiếu khai báo namespace ở đầu file web.php Dưới đây là 👉 LỘ TRÌNH 3 NGÀY – CRUD CHUẨN LARAVEL Thiết kế cho người đã có nền tảng lập trình , học để làm thật , không lan man. 🧭 MỤC TIÊU SAU 3 NGÀY ✔ Hiểu chuẩn luồng Laravel ✔ Tự tay xây CRUD hoàn chỉnh ✔ Code đọc được – mở rộng được – không bừa ✔ Sẵn sàng làm app nội bộ / dashboard / CMS 📅 NGÀY 1 – NỀN TẢNG & CRUD CƠ BẢN (80%) ⏱️ ~2–3 giờ 🎯 Mục tiêu ngày 1 Hiểu luồng MVC Làm CRUD chạy được Không cần đẹp – chỉ cần đúng 🧩 1️⃣ Tạo project CRUD mẫu Ví dụ: Post (id, title, content, status) php artisan make:model Post -mcr ➡️ Tạo: Model: app/Models/Post.php Migration Controller resource 🧱 2️⃣ Migration – Tạo bảng Gõ đoạn code sau để tạo bảng trong file databa...
Đọc thêm

🚀01 giờ học cách sử dụng Developer Console

Nếu chỉ có 1 giờ để học cách sử dụng Developer Console (trong Chrome, Edge, Brave...), thì đây là lộ trình cấp tốc và thực chiến, đủ để: ✅ Kiểm tra lỗi ✅ Thử nghiệm code JavaScript ✅ Lấy dữ liệu từ DOM ✅ Giả lập thay đổi giao diện 🚀 LỘ TRÌNH 1 GIỜ SỬ DỤNG CONSOLE 🧱 BƯỚC 1: MỞ CONSOLE (1 phút) Cách mở nhanh : Chuột phải → Inspect → Chọn tab Console Hoặc dùng phím tắt: Windows/Linux : Ctrl + Shift + I rồi Ctrl + ~ macOS : Cmd + Option + I 🔍 BƯỚC 2: ĐỌC & GHI LOG (5 phút) Hành động Câu lệnh Ghi log ra console console.log("Hello, Tiến sĩ M!"); Cảnh báo console.warn("Cẩn thận!") Báo lỗi console.error("Đây là lỗi") In ra object/table console.table(document.links) 👉 Mẹo : Dùng console.clear() để xóa màn hình console. 🌐 BƯỚC 3: TRUY CẬP DOM & LẤY DỮ LIỆU (10 phút) Mục tiêu Câu lệnh Lấy 1 phần tử document.querySelector('h1') Lấy nhiều phần tử document.querySelector...
Đọc thêm