-- 1. Create backup
create table laptop_backup like laptop;
insert into laptop_backup select * from laptop;

-- 2. Check number of rows
select count(*) from laptop;

-- 3. Check memory consumption for reference
select DATA_LENGTH from information_schema.tables
where table_schema = 'data_cleaning'
and table_name = 'laptop';

-- 4. Drop non important cols
select * from laptop;
alter table laptop drop column `Unnamed: 0`;

-- 5. Drop null values
ALTER TABLE laptop
ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
FIRST;

DELETE FROM laptop
WHERE `id` IN( SELECT * FROM laptop
WHERE Company IS NULL AND TypeName IS NULL AND Inches IS NULL
AND ScreenResolution IS NULL AND Cpu IS NULL AND Ram IS NULL
AND Memory IS NULL AND Gpu IS NULL AND OpSys IS NULL AND
WEIGHT IS NULL AND Price IS NULL);

-- 6. Drop duplicates
DELETE FROM laptop
WHERE id NOT IN (
    SELECT id FROM (
        SELECT MIN(id) AS id
        FROM laptop
        GROUP BY Company, TypeName, Price
    ) t
);

-- 7. Clean RAM -> change col data type
select * from laptop;
alter table laptop modify column Inches decimal(10,1);

UPDATE laptop
SET Ram = REPLACE(Ram, 'GB', '');

alter table laptop modify column Ram INT;


-- 8. Clean weight -> change col type
UPDATE laptop
SET Weight = NULL
WHERE Weight = '';

UPDATE laptop
SET Weight = NULL
WHERE Weight NOT REGEXP '^[0-9]+(\\.[0-9]+)?$';

UPDATE laptop
SET Weight = REPLACE(Weight, 'kg', '');

alter table laptop modify column Weight decimal(10,2);

-- 9. ROUND price col and change to integer
UPDATE laptop
SET price = ROUND(price);
ALTER TABLE laptop
MODIFY price INT;

-- 10. Change the OpSys col
-- creating a category cols like mac, window , linux and other
	
update laptop 
set OpSys = case 
	when OpSys like '%mac%' then 'MacOS'
    when OpSys like 'window%' then 'Window'
    when OpSys like '%linux%' then 'Linux'
    when OpSys like 'No OS' then 'N/A'
    else 'other'
End;

-- 11. Gpu
alter table laptop 
add column GPU_Brand varchar(255) after Gpu,
add column GPU_name varchar(255) after GPU_Brand;

update laptop
set Gpu_Brand = substring_index(Gpu,' ',1);

update laptop
set GPU_name = replace(Gpu,Gpu_Brand,' ');

alter table laptop drop column Gpu;
-- 12. Cpu
alter table laptop 
add column CPU_Brand varchar(255) after Cpu,
add column CPU_name varchar(255) after CPU_Brand,
add column CPU_speed decimal(10,1) after CPU_name;

update laptop
set CPU_Brand = substring_index(Cpu,' ',1);

update laptop
set CPU_speed = cast(replace(substring_index(Cpu,' ',-1),'GHz','')as DECIMAL(10,2));

UPDATE laptop
SET CPU_name = REPLACE(REPLACE(Cpu, cpu_brand, ''), SUBSTRING_INDEX(REPLACE(Cpu, cpu_brand, ''), ' ', -1), '');

alter table laptop drop column Cpu;
Data Set We Used

Comments
