Data Cleaning in MySQL | Full Project
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- Full MySQL Course: www.analystbui...
In this lesson we are going to be building a data cleaning project in MySQL!
Download Dataset: github.com/Ale...
GitHub Code: github.com/Ale...
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
📖Google Data Analyst Certification: coursera.pxf.i...
📖Data Analysis with Python - coursera.pxf.i...
📖IBM Data Analysis Specialization - coursera.pxf.i...
📖Tableau Data Visualization - coursera.pxf.i...
Udemy Courses:
📖Python for Data Science - bit.ly/3Z4A5K6
📖Statistics for Data Science - bit.ly/37jqDbq
📖SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
📖Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a member! I do Monthly Livestreams and you get some awesome Emoji's to use in chat and comments!
/ @alextheanalyst
____________________________________________
Websites:
💻Website: AlexTheAnalyst.com
💾GitHub: github.com/Ale...
📱Instagram: @Alex_The_Analyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
Watched all the ads without even skipping, that's how much I am grateful for your work and time you put into this.
Yes , it's true .. I'm grateful for this course , that too for free
will start doing this too
mam I'm from India can u get a job ? currently I'm looking for job if u got a job then tell me how you got the job it means a lot to me...
Timestamps:
Removing Duplicates: 8:00
Standardizing Data: 17:32
Null/Blank Values: 33:30
Remove Unnecessary Columns/Rows: 46:12
Great video!
I converted the 'date' column from text to Date format after importing. And when I ran the duplicate_cte, I only got 5 rows in output.
Note: I used date instead of 'date' in the partition by section.
Life saver I was just searching for this
nice video
Thanks for the kind words! I made it to the end and learned a lot while working on the project simultaneously.
3.15am South African time, I managed to finish the project. It was tough had to start over, missed some stuff but I did it. I just completed my Meta Data Analyst Professional Course and decided to do this Boot camp. As someone coming from the Beauty Industry, I was so lost. Thank you @AlextheAnalyst for helping people like me who want to learn and excel but cannot afford tuition for University or most Institutions.
Timestamp: 18:59: after unchecking the Safe updates mode , you don't have to restart MySQL; go to Query on the top left side of your window then select reconnect to server and then run your query.
Hey Alex! I'm from India, I have been following you for a months but really couldn't make any project. But from the first encounter of your content, I knew I'm gonna walk on your foot steps. I loved it and also I was looking for some data like this last 2 weeks, I did tried on most the US, Indian data bureau(s) and where not. Yesterday I decided to make this project at hand, AND WOW It was that data I was looking for. Thank you so much. This is my second project ever in SQL. I love it totally from the beginning to the end. And I had so much fun doing this project, literally. I was laughing with the funny parts, even overwhelmed in the end at that industry populating part juts like. I cheered yayy. I made a mistake too, I forgot to run the deleting query of the duplicates. I had to run from the start to find out where did I miss. I love your energy and how to take things so calmly and carry the learner with you till the very motive. I probably have written too much now. I am so excited to follow this project till the visualization part. And here's one more thing I tried, I wanna show you - In the populating part, we can do it without making the blanks, null. This is the query I tried,
UPDATE layoffs_staging2 t1
JOIN layoffs_staging2 t2
ON (t1.company = t2.company AND t1.location = t2.location)
SET t1.industry = t2.industry
WHERE (t1.industry IS NULL OR t1.industry = '') AND (t2.industry IS NOT NULL AND t2.industry != '');
hey i'm working on the latest layoff dataset, matched some unknowns in the stage column by doing a self join on company column, should i update those unknown values?
Hola Alex, soy de Argentina y estoy viendo todo el Bootcamp con subtítulos. Eres una persona maravillosa a la hora de explicar las cosas, siento como que un amigo me está explicando los temas. Muchas gracias por lo que hiciste.
Patiently waiting for the exploratory aspect of the clean data.
Thanks very much
Finally completed this after a second trial and now I have my first project on MySQL
Dear Alex,
I wanted to take a moment to express my heartfelt thanks for the incredibly useful lesson you provided on data cleaning. Your generosity in sharing your knowledge for free has been invaluable, and the techniques you've taught me will undoubtedly help me handle data more effectively in my work. I truly appreciate your time, effort, and willingness to help others grow in this essential skill. Thank you once again for your guidance and support!
Warm regards,
Saheel Mowlana
I don't know but I'm too thankful for you, I really hope see people could be so great like you in my daily life. Thank you, Alex, I hope you getting too much positive things with numbers of people who are got benefit from your videos.
Thanks Alex. Great video. The best tip so far was from the data cleaning vid. I didn’t realize that I could check the results before executing changes on the database. Like start_time, length(start_time), SUBSTRING(start_time,1,19) to check truncating that string prior to the real deal.
Alex! This is why I subscribed, thank you so much for doing this in MySQL!!
absolutely enjoyed the tutorial and learnt loads of stuff!! When you said at the end that not everyone is able to make till the end, I felt really good that I could follow along and made it till the end. :)
thank you very much Alex for putting out this tutorial for all of us newbies. Really looking forward to begin Exploratory data analysis tutorial!! Cheers..!
Outstanding video. I did follow most of it, the rest I'll rewind and study. Definitely going to be doing the code along myself and posting to my portfolio. Thanks for the very detailed walk through. I am trying to get better so I can try this as a side hustle while looking for a data job. I have a comfy IS help desk job, I'm just bored to death of it and not learning anything new.
watch till the end it's awesome Alex ! thanks so much
I must commend you Alex, had a little bit of trouble populating the null values following the instructions in this video, had to go and check the code on your Github which was super helpful and straightforward. Thanks for your lecture and am sure going to complete this boot camp playlist❤
Amazing Alex, this is exactly what I'm looking for my project too. Thank you so much
Made it to the end!! Thank you Alex. Much love from Tanzania:)
twinsies
Thanks Alex, Can't wait to Start this project
Alex, You're so natural. The Best yet!
...and another lesson taken... 47:35 "I can't trust that data, I really can't!" We should get to this level before confidently deleting 'useless' rows! As always, Alex you're the best! Thank you very much for all your contribution!
I'm having problem importing the data, the orginal data has 2000+ rows, but when I import it, it only has 564. Does anyone know how to fix this issue?
Same, if anyone knows a video I can watch to fix it just let me know. 👍🏽
@@Pato-rt1vh convert that .csv into a json!
I am facing the same issue. I just came to the comment section to see if anyone can bring some light to it. I really want to practice and do the project, it's frustrating to be stuck right before starting.
I’m having the same issue
I faced the same issue. I don't have a solution but a suggestion. Just follow through the video with whatever data u could import. Won't be perfect, but try to get what we're trying to do here. Then just try to practice data cleaning on some raw data which you can find on kaggle.
Thank you Alex for the kind words at the end of this video. It made me feel good 😊
I shouldn't be having this much fun doing homework. Thank you Alex!
Timestamp 24:27 This is how I resolved all those, just the way you taught us. It is usually caused by difference in character type.
SELECT location
FROM layoffs_staging2;
SELECT DISTINCT location, country
FROM layoffs_staging2;
SELECT *
FROM layoffs_staging2
WHERE location LIKE 'Mal%';
UPDATE layoffs_staging2
SET location = 'Malmo'
WHERE location LIKE 'Mal%';
SELECT *
FROM layoffs_staging2
WHERE location LIKE 'Flor%';
UPDATE layoffs_staging2
SET location = 'Florianopolis'
WHERE location LIKE 'Flor%';
SELECT *
FROM layoffs_staging2
WHERE location LIKE '%sseldorf';
UPDATE layoffs_staging2
SET location = 'Dusseldorf'
WHERE location LIKE '%sseldorf';
Hope this is okay?
Thanks for this video, Alex! Really need it
Has anybody told you that you are not just good but you are AWESOME !👑
Just finished doing this one, really fun and practical. Now heading to the EDA part.
A question, I am not sure how to post this projects in a portfolio. I normally publish projects in my github page when it's about web or app development but I've never done SQL projects before, how is it supposed to be published to make it properly visible for recruiters for example.
Thank Alex for all the value you share
Thanks a lot for simplifying MYSQL Alex!
Alex The Analyst, You are a Blessing to this Generation...
Thanks for sharing this helpful content, Alex! We need more of this.
Alex videos are always so real authentic and so relevant!
Trying to do this tutorial but whenever I use import wizard the csv is not fully read and only 564 lines are registered. Did anyone else have this issue?
Thanks for doing a project in SQL. Waited for long.
while you were updating the date format but there were null values and you could still go ahead and continue the update query. but when i tried to do the same it is not letting me do it and throws an erroe `Error Code: 1411. Incorrect datetime value: 'NULL' for function str_to_date`
hello, were you able to fix this? im facing the same error..please help
@@arnavchopra7708 hello,
I am facing the same problem. how did you fix this issue
@@okodedefaith6582 confirm if you are putting % before the Y
same bruh
@@arnavchopra7708 hey it's actually changed check the correct table
Thanks alot.This tutorial is on point and timely.This is what I have been looking for
In the data where there are null values for industry, those rows are repeated but however they did not show up in CTE (row num) and query for row_num>1.
e.g Bally's Interactive have three exactly the same rows with null values industry and total_laid_off.
How we remove these duplicate rows ?
exactly what i was looking for! thanks a lot 🙌🏻
Alex u r the best! Thank you so very much... Plzzz do more videos on data cleaning.
Thank you so much for sharing your expertise. I learned and I laughed (your comments 😄) throughout the tutorial. You're awesome!
Haha glad to hear it! Learning should be fun :D
Took me all day but yayyy I’m done my first sql project!!
This was very insightful, thank you so much for this Alex.
Excellent Alex!!! You read my mind, man! This is just what I needed to put in my portfolio. THANK YOU
Great , The Alex. thank you so much. I have learned a lot from basic to Cleaning Data.
Thank you once again.
Incredible tutorial Alex. Thank you!
About the duplicates: I used a subquery to reference the table in the 'WITH' statement, that way I get to delete duplicates without creating another table. Just wanna know if it's a valid procedure ☺. Love this course, learning a lot.❤
can u help me
with the query
Hey Alex I was wondering if you could help me troubleshoot what I'm doing incorrectly. All my row numbers are different when I run the query during the 9:31 duration of the video.
hiii, please im going through the same challenge at this point. Were you able to fix your issue? if so...please can you walk me through.
@@offodilesomtochukwu6268 same, im getting a HUGE list, not just 6 or 7 like in the video...
guess it doesnt matter since we're deleting them all anyways...
Probably a little late for you guys, but I was having the same issue, I noticed that everything was Partition correctly but not the date, the issue was in the quotation mark, make sure you are using the correct one: Like this --> ˋdateˋ
This was an amazing tutorial!! Thank you
thanks alex for this data cleaning practice
Learned a lot following along through this, excited to follow the EDA next!
Done something wrong, as I am coming back and forth as my data doesn't have any duplicates. Jeez! Any idea what is wrong?
I understand the ease of which using the upload tool makes it to create a table and import the data all at once, but I find that it makes it cumbersome in this case since there is no unique column. Is there a reason that you wouldn't create the schema for the table and create an auto incrementing id column that is the primary key to assign a unique id to every row, then use the row_number function to search for duplicate rows using all the columns except the id column. This would save you from having to create a duplicate table to store row_num as you could just use the id column to delete the duplicate records. This also seems like it would make your database easier to deal with since it would have a proper primary key. Sure, it is a meaningless primary key, but it would certainly make updating the data easier and faster in many cases.
Looking forward to next project
Thank you, Data guy, brilliant work)
Absolutely phenomenal work. Thank you very very much for this. Cleared some of the concepts and at the same time created a project. Absolutely love it.
Hey alex , thanks for the video.
Please cover data cleaning in stata or R as well.
Sir, if you write table_name .date(column name) , it works better,becoz 'date' does not work in my workbench ,dont but after all super cool you are Sir, thanks and respect from Pakistan
I made it to here.
#moving forward
😀 Are you going for the entire bootcamp?
@@elle8388 yes
@@elle8388 Yes I am
@@elle8388 yes I am
@@elle8388 yes
while importing the dataset, it is automatically removing many rows. It has only imported 534 rows. Can anyone help me with this?
same issue
awesome work Alex! and thanks for providing this kind of contents
hello guys, when i want to import the table into sql i am getting a table with about 500 rows, so there are actually missing a lot. I am only facing this problem on my mac, not using my windows pc. someone knows what the problem could be?
i have no idea.
i had to convert the csv into a json file on a 3rd party website. I'm sure there's a better solution that keeps it as CSV but that worked best for me. Still couldn't figure out the problem though, maybe it has something to do with how null values are read
@@nickc.440 okay thanks im gonna try that
i Appreciat your work Alex, well done
I find your method of removing duplicates too complicated. When inserting into the new table, I removed duplicates by running 'SELECT DISTINCT * ....' . Is there any downside to my method? Are there cases where it wouldn't work?
Oh oh me too! I used Union of the same table to get rid of duplicates, but SELECT DISTINCT * is just so much quicker and shorter! Thanks for sharing :)
Great job us!
PS: Alex if you're seeing nickelikem's comment please also see if there might be any downside to my method in the long run. Thanksss
@Alex The Analyst ,
Thank you so much for sharing a genuine Content .
Till now i have learnt lots of SQL Tutorial , (there is one issue on fixing Text Type to Date ) . I hope when you find this message definately help me out there.
Hello, great video. However, I'm having trouble transferring all the data from the file to MySQL. Do you have any suggestions?
Bro, same here. I only get around 500 imported.
@@nicolekanigina Did u end up finding a solution?
also having this problem
can you make a tutorial on how to connect this mysql database in power bi and make a dashboard for it?
Great work! Love how it was all broken down
Null values or Blank values was the trickiest part for me to understand I'm still trying to understand what is just happened with the column 😀
too real. im trying to understand how it went cos i didnt get it right
I have a question. I do the exact same thing, but there is nothing when I do the 'removing duplicates' part. I did all the processes again and just realized that mine is only 564 records. I don't know why. Can you explain how to fix it?
I'm having this issue, did you figure it out?
@@ericawelch4218 unfortunately, I have not found the solution. but just keep doing it and ignore those 'differences'.
@@ericawelch4218have you found the solution?
thanks @alex i convert the csv to json and it worked
Great work, Alex. we love you
Thank you for your help. I appreciate your videos a lot
Thank you so much for these lessons. I just had one question. when importing my .csv file onto mySQL, I was not able to get all of the data but only the 500 range. Could I get some help on how to load in all of the data. I am running all of this on a macbook.
I had the same issue and from reading other comments this seems to be an issue for anyone using mysql on mac. I found the best workaround was to convert this to a json. Just search online for a csv to json converter and use the json file when first importing.
However, this seemed to cause another issue where it replaced NULL values with the text "NULL" and that gave me errors later when converting the dates. So, to solve this, the first thing you will want to do after creating the 'layoffs_staging2' table is convert all those "NULL" text values to actual nulls using the following action:
UPDATE layoffs_staging2
SET
`company` = CASE WHEN `company` = 'NULL' THEN NULL ELSE `company` END,
`location` = CASE WHEN `location` = 'NULL' THEN NULL ELSE `location` END,
`industry` = CASE WHEN `industry` = 'NULL' THEN NULL ELSE `industry` END,
`total_laid_off` = CASE WHEN `total_laid_off` = 'NULL' THEN NULL ELSE `total_laid_off` END,
`percentage_laid_off` = CASE WHEN `percentage_laid_off` = 'NULL' THEN NULL ELSE `percentage_laid_off` END,
`date` = CASE WHEN `date` = 'NULL' THEN NULL ELSE `date` END,
`stage` = CASE WHEN `stage` = 'NULL' THEN NULL ELSE `stage` END,
`country` = CASE WHEN `country` = 'NULL' THEN NULL ELSE `country` END,
`funds_raised_millions` = CASE WHEN `funds_raised_millions` = 'NULL' THEN NULL ELSE `funds_raised_millions` END;
16:50, if the DELETE command doesn't work even if you untick the DELETE option in the Preferences, you can add this in the code: SET SQL_SAFE_UPDATES = 0;
It would be better if you divide video into sub topics so that if we want we can come back and see the seocific part of the video.
Nice video Alex. Awesome how u used self joins there. Blew my mind🤯
Here is my Small contribution:
when modifying the industry, you dont NEED to set to nulls. Instead try:
WHERE (t1.industry is NULL OR t1.industry ='')AND t2.industry LIKE '%_%';
#No point in doing : t2.industry IS NOT NULL, coz they are blank('')
#retruns additional useless rows with 2 NULL values
Thank's for you explanation!
Thank you so much for this, I enjoyed every step of this
Thanks Alex for this amazing video
Thanks, I really like this project
why does the data appear to be pre-cleaned? I'm not seeing the duplicates and I'm not seeing the different crypto industries...
Are you getting the data from the github link?
@@AlexTheAnalyst thanks for your response. I am getting it from github. with the help of the comments i believe i have figured it out. i had to convert the data to JSON, and raise the 'Limit to 1000 rows' dropdown menu to something higher than the length of this data. Otherwise I was about to give up, so perhaps a description update would help other viewers. Now I just have to change all the 'NULL' strings in the JSON data into actual NULL values. Thanks again
@@georgek398Have you figured out how to change the null strings into null values? I’m stuck at that at the moment
Can someone please tell me how to add this to my Portfolio/Resume (I'm a fresher)
Hey there! I am trying to transfer the data to sql but it is showing that 564 records are showing instead of 2361. Can someone please help
did you uncheck the anomaly removing function while importing your data
Tried doing it blind just doing the 4 steps you mentioned in doing data cleaning
i got atleast 70% right ( i miss stuff especially with the United States. one)
The laugh of triumph at 43:18-19 😂😂😂
Thanks alot, Alex. you're doing an amazing job.🥰💜
for some odd reason its importing 564 rows as opposed to the 2000 ish rows it supposed to have. Why is that the case. The excel file that I am downloading is showing 2000 ish rows as well.
This is happening to me as well! Did you happen to figure it out?
Your version of MySQL could be using a different encoder or something. I would convert it to a json file then try again using the same method. Sometimes this will fix the issue. You can just google "csv to json converter"
@@AlexTheAnalyst This worked!! Thank you so much, Alex.
@@AlexTheAnalyst Thank you so much! I have been able to successfully import all the rows in the database by converting the file from a csv file to a json file. This will be good to know in the future if I have trouble importing csv files.
@@elainemcclure6388 Yes! I have been able to fix it thanks to Alex's help.
Can someone explain why when I import the data at the beginning instead of 2361 records importing, i only get roughly around 550. Please help.
I’m having the same problem
have you managed to figure this out?
Yes convert to json the csv
@@samanthalee7064 how and where do you do that? when importing or convert right from download before you start the proccess
@AlexTheAnalyst I'm only able to import 536 out of the 2000+ rows using the import wizard. Please How do i resolve this. Ps: I'm using a mac
is it solved now? I'm facing the same issue
Truly Blessing for us.
Thanks a lot for your fantastic video, this helped me a lot.
I just wanted to put here that when I started using the same file for the project , I have faced issue with the `date` updation, that's not a singe update as you did(I dont know why , but it didnot work for me).
The query which I used was:
UPDATE layoffs_staging2
SET `date`=STR_TO_DATE(`date`, '%m/%d/%Y' )
WHERE `date` LIKE '%/%';
UPDATE layoffs_staging2
SET `date` = STR_TO_DATE(`date`, '%m-%d-%Y')
WHERE `date` LIKE '%-%' AND `date` NOT LIKE '____-__-__';
Because there are two types of date formats in the file, so we need two different updations.
Hope this might help someone who face the same issue.
Great video and easy to learn from these kind of videos.
Regarding the "Removing Duplicates" part. I tried to check if there are duplicates left by adding "WHERE company = 'Casper' ", but there are still duplicates showing up, but when i filtered the rows using the results grid, it does not show any duplicates. Does that confirm that the duplicates are deleted? Thanks in advance!
Hi, amazing video. explanations are awesome.
I just had a question: when I did the import only 564 records were imported. I don't understand why not all records are imported???
Try converting to JSON, worked for me
@@IbukunoluwaAkinwale thanks a lott !! this workedd
Thank you Alex. you're the best
while deleting or updating find error as safe update or delete so ether you can run this "SET SQL_SAFE_UPDATES = 0;"i or watch 19:07 first.
Excellent Learnings........!!!
Is it possible to add a column to the 'layoff_staging' table which would automatically contain the row number without creating a new table?
Also how can we create a column where each row is numbered like 1,2,3.... etc. without creating a new table?
I have just encountered an issue. I'm only able to import 564/2632 rows. The MySQL workbench isn't throwing an error either. If anyone has a workaround for this, do let me know. @AlexTheAnalyst Tagging you just in case if you have a solution to the issue.
im facing the same error please help
same issue. @alextheanalyst please help
Too me
try converting csv file to json file