Data Cleaning in MySQL | Full Project

Full MySQL Course: www.analystbuilder.com/course...
In this lesson we are going to be building a data cleaning project in MySQL!
Download Dataset: github.com/AlexTheAnalyst/MyS...
GitHub Code: github.com/AlexTheAnalyst/MyS...
____________________________________________
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.io/5bBd62
📖Data Analysis with Python - coursera.pxf.io/BXY3Wy
📖IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
📖Tableau Data Visualization - coursera.pxf.io/MXYqaN
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/AlexTheAnalyst
📱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

[@anyamunkh1787]

Watched all the ads without even skipping, that's how much I am grateful for your work and time you put into this.

[@user-sp8sw7vt5k]

Timestamps: Removing Duplicates: 8:00 Standardizing Data: 17:32 Null/Blank Values: 33:30 Remove Unnecessary Columns/Rows: 46:12 Great video!

[@obeliskphaeton]

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.

[@harshitthakur8300]

Life saver I was just searching for this

[@tejjm4263]

Thanks for the kind words! I made it to the end and learned a lot while working on the project simultaneously.

[@10cutie207]

Alex! This is why I subscribed, thank you so much for doing this in MySQL!!

[@ibrahimdenisfofanah6420]

Patiently waiting for the exploratory aspect of the clean data. Thanks very much

[@duurduranto]

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.

[@user-uj2om9it6u]

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.

[@utkarshrana39]

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 != '');

[@alphaghost4330]

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?

[@AnnNguyenHo]

Amazing Alex, this is exactly what I'm looking for my project too. Thank you so much

[@newenglandnomad9405]

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.

[@muneebbolo]

Thanks for sharing this helpful content, Alex! We need more of this.

[@peaceandlove8862]

Alex videos are always so real authentic and so relevant!

[@alamsgodwin6179]

Thanks Alex, Can't wait to Start this project

[@ahmadmarzodandy6054]

Thanks for this video, Alex! Really need it

[@gauravtanwar8886]

exactly what i was looking for! thanks a lot 🙌🏻

[@cjbrown3396]

watch till the end it's awesome Alex ! thanks so much

[@michaelp9061]

Incredible tutorial Alex. Thank you!

[@luckyraji6828]

Thanks alot.This tutorial is on point and timely.This is what I have been looking for

[@zakhelembhele7046]

Alex, You're so natural. The Best yet!

[@cgadison]

This was very insightful, thank you so much for this Alex.

[@thamizarasan1913]

Thanks for doing a project in SQL. Waited for long.

[@MrWonderninja]

Learned a lot following along through this, excited to follow the EDA next!

[@hazoom]

i Appreciat your work Alex, well done

[@rokibhasan5184]

Looking forward to next project

[@rakeshbasak6842]

awesome work Alex! and thanks for providing this kind of contents

[@Ladyhadassah]

Great work, Alex. we love you

[@ratnakshtyagi3564]

thanks alex for this data cleaning practice

[@sujayy6851]

Thanks a lot for simplifying MYSQL Alex!

[@alice60372]

Alex u r the best! Thank you so very much... Plzzz do more videos on data cleaning.

[@derrickmedina2796]

Great work! Love how it was all broken down

[@SafiaSingla]

This was an amazing tutorial!! Thank you

[@zaidahmad9735]

Hey alex , thanks for the video. Please cover data cleaning in stata or R as well.

[@leosch80]

Excellent Alex!!! You read my mind, man! This is just what I needed to put in my portfolio. THANK YOU

[@hasanrazakhan7154]

Thanks Alex for this amazing video

[@krystalbrantley4996]

Thank you so much for sharing your expertise. I learned and I laughed (your comments 😄) throughout the tutorial. You're awesome!

[@AlexTheAnalyst]

Haha glad to hear it! Learning should be fun :D

[@yustone]

Thanks, I really like this project

[@user-tz5cq6vh5k]

Truly Blessing for us.

[@harshitthakur8300]

Great video and easy to learn from these kind of videos.

[@medhaniemahari4265]

...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!

[@Arslan-nm3rz]

Thank you very much Alex!!!

[@AnalystInMaking]

Has anybody told you that you are not just good but you are AWESOME !👑

[@kahinaabbas9792]

just on time 🤩

[@dennisbunarta1190]

I love this channel.. God bless you alex

[@bayonette1]

it was a very useful project, thank you very much 🙌

[@chethuchethu6530]

great explanation

[@muneebbolo]

I did get all the results as you get in the except third step of removing the null or blank values anyways I hope in future I will get the desired results as much as possible I can in the process of data cleaning.

[@TheSupersayan6]

can you make a tutorial on how to connect this mysql database in power bi and make a dashboard for it?

[@tcrawford8430]

Thank you 🙏

[@truthgaming2296]

thank you alex, i love your explanation very much thank you XD

[@charlesolukoju4809]

Alex, thank you very much to this Please I'm having issues downloading the data set

[@muneebbolo]

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 😀

[@derrickmedina2796]

Should I add this to my portfolio or finish the analysis part then add that part together

[@tanishquegupta]

Hi Alex, Can you explain how you are using ROW_NUMBER() without using ORDER BY?

[@user-wg1kf5rc3h]

Appreciated

[@allyoucaneat12]

Hi Alex, thanks for the good work. A quick question, when working on a dataset and in the process of cleaning you have an age column with a negative value, is it advisable to delete the negative age values or to assign another age to them. Also where you have someone less than 20 years with a PhD degree, should I assign another age or delete the rows

[@AlexTheAnalyst]

Those would be questions I would ask the person I got the data from. Since we don't have that you just have to do the best you can. If you think the negative should be positive that may be the right approach? Or deleting that row altogether may correct. Hard to say if we can't go back to the client or stakeholder.

[@irfankhan-qj4mu]

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

[@alphaghost4330]

i matched some unknowns in the stage column by doing a self join on company column, should i update those unknown values?

[@USA_Diariez]

At 27:20 why are we using where country like USA? If we skip the where condition wont it fix all places in the column where there is a '. ' ?

[@mcxineupdates1117]

I'm unable to create the table using the copy to clipboard method , please is there any allternative?

[@Artemis_1023]

Hey Alex! Quit my developer job of 2.3 years to pursue the field of data. I have 1 year career gap now. Do u think it’ll affect my job search and selection process? P.S. ~ absolutely love your stuff!!

[@sayematazinshaikh6656]

Nice!

[@yousraahmedd]

Regarding populating the rows, Can I google missing info such as the country if it's missing?

[@zeboulounyoan5723]

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???

[@ppiercejr]

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.

[@kevinmugo2662]

When it comes to the part on dealing with null values , Specifically on checking where company is 'Airbnb' am getting 4 rows instead of 2 . It is a clear duplicate and I can't figure out why.

[@muneebbolo]

I would love to know more about the SCHEMAS section of MySQL because I am still learning it so please suggest me a video on this topic if you have already made Thanks in advance.

[@Super8-wf7iy]

when will you upload the next part ?

[@alamsgodwin6179]

What software are you using?

[@sanchitsharma2899]

Is there any other way then manually scrolling through data to standardize it?

[@maxwelltsangya3810]

Null Handling: 46:29 these are coming from the dates as it appears like on these dates, there were no layoffs, but same companies might have layed people off on other days.

[@user-su9br6zz7e]

Where do you get the data sets?

[@georgek398]

why does the data appear to be pre-cleaned? I'm not seeing the duplicates and I'm not seeing the different crypto industries...

[@AlexTheAnalyst]

Are you getting the data from the github link?

[@georgek398]

@@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

[@ichigokurosaki6470]

⁠@@georgek398Have you figured out how to change the null strings into null values? I’m stuck at that at the moment

[@kalMHe]

So, I have been watching all the videos from start until this video. I have been using MSSMS for the training. I now see you used Workbench. I do not see the options you have in work bench to create a new database in SQL server management studio inorder to be able to pull the file i downloaded. I installed Workbench but does not seem to operate as I expected. Not sure what I am missing.

[@kusumsharma7293]

Hello sir thank you for making such precious videos, I have subscribed you, I follow your all recent videos, but what you did here , I tried to do, but I m doing all those things in MySQL workbench, with duplicates _cte, to remove duplicate, it doesn’t work , here, could you please suggest me what should I do for MySQL workbench

[@ryanjames3235]

I'm trying to do this project following the steps but I'm having difficulty creating a new table from the exiting table, the like function is not working , same as the as and select from statement

[@sachinsrivastava2177]

CTE is not working in MySQL version 8.03 it is showing syntax error , How I can fix this?

[@alacart3608]

I have been following you for some time now, I live in London and I am currently trying to switch careers. My background is in Accounting and Finance but I wish to be a Business Intelligence Professional. I am writing to ask if you do Mentorship or one-to-one training? I will be expecting your response

[@razeeop5673]

what if we clean our dataset in pandas and then EDA in SQL nut how can we open that pandas's dataset in SQL?

[@harshitthakur8300]

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.

[@johithsriman2220]

Iam using oracle sql developer when i use update and join it throws me an error when i check the error in stack over flow it says that Oracle does not support JOIN clause in UPDATE statements. how can i over come this error

[@J.L1212-dv9tr]

Hi, Im not sure about this but did he forgot to delete the duplicate?

[@EzeokekeJoseph]

Please can you do a video on the Covid data with this software….im lost trying to import the Covid in MySQL workbench…I’ve tried and it’s still now showing up…please help out

[@rnielvhinsieans.elpidama6935]

question can i still follow this if I use PostgeSQL/PgAdmin?

[@huntercoleman1347]

Yes. The SQL will largely be the same. Except importing the data will be different. In PostgreSQL, you could try something like: COPY the_table_name FROM 'C:\Path_to_file\file_name.csv' WITH (FORMAT CSV, HEADER); Note: The table must already exist. HEADER means that your csv file on your computer has a header, which the database import should ignore. If you decide to import via the PgAdmin GUI, it will be different than MySQL workbench. Also, when Alex used STR_TO_DATE in MySQL, the equivalent in PostgreSQL is TO_DATE, and you don't need the percent signs before the format characters.

[@elainemcclure6388]

Great video! I converted my CSV to JSON after facing some import issues and that worked - thank you! I am now trying to run this section of code: UPDATE layoffs_staging2 SET `date` = STR_TO_DATE(`date`, '%m/%d/%Y'); And receiving the following error: 1411. Incorrect datetime value: 'NULL' for function str_to_date Any advice here? The previous chunk of code using str_to_date provided the column with the correct date formats, so I'm not sure why it isn't able to update. Thanks in advance!!

[@adeope3702]

I encountered same but was resolved with a case statement, UPDATE layoffs_stagging2 SET `date` = CASE WHEN `date` IS NOT NULL AND `date` != 'NULL' THEN STR_TO_DATE(`date`, '%m/%d/%Y') ELSE NULL END;

[@SomeStatus]

@@adeope3702 is the != 'NULL' part checking for the literal text of NULL? That maybe the issue I"m coming up with.

[@safayatahmed1976]

@@adeope3702 Thanks a lot for the solution.

[@youzuko]

how did you import the csv w/o error? did you clean up the csv first by putting "NULL" into blank cellls?

[@AlexTheAnalyst]

Should be able to use the import wizard and let mysql auto detect the data types and import it as is.

[@adeope3702]

​@@AlexTheAnalyst After importing, it won't read the whole row, only 580. What could be the problem?

[@matchagrlie]

@@adeope3702 Im getting the same thing. Only 580 records imported, did you ever figure it out?

[@MandehPandeh]

Hey Alex, thank you so much for this guided project! So excited to begin. But when I went to import the dataset into MySQL, It only loaded 564 rows. Any idea on why that may be?

[@Sophie-codes]

ohemgee, I'm having the same problem! I tried to search online for answers but still no luck...If anyone knows how to fix it, I'd really appreciate your help. Thanks!

[@MandehPandeh]

@@Sophie-codes So I read some of the other comments lol, seems to be a lot of people having the same problem. Someone said to convert to .json and that worked for me!

[@Sophie-codes]

@@MandehPandeh Oh I see, thank you so much for your help! It did work when I changed it to json :)

[@potato-sweet]

i'm unfortunately stuck right at the beginning T_T when importing the table, my own mysql shows that only 564 records have been imported but i know theres a total of 2631. i've managed to find a query to import the data and it shows the following error: Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides anyone else facing this issue and know how to solve it?

[@Watema23]

Hi Alex, Where can I go to dowlaod the layoffs dataset?

[@utkarshrana39]

Link in the description!

[@user-uj2om9it6u]

What version of MySql are you using?. I’m on 8.x and I did not need the backticks on the date column.

[@ppiercejr]

you do not need the backticks on it, it is used by convention to specify that it is a column and not the keyword. In this case, MySQL knows that you are putting in column names so it works, but there are instances where date will be the keyword and may give you undesirable results. This is a case of ensuring that it does what you want and not letting it do what it thinks is right.

[@AbhishekSharma-vm7tr]

i am getting null when i am trying to convert text date into date format what can i do

[@kayodeanjorin8914]

How can I download the data set

[@siddharthrai2181]

i have run this query in mysql workbench : SELECT `date`, STR_TO_DATE(`date`,'%m/%d/%Y') FROM layoffs_staging2; it work completely fine but when i try to update this : UPDATE layoffs_staging2 SET `date` = STR_TO_DATE(`date`,'%m/%d/%Y'); NOw it gives me Error :-incorrect datetime value: 'NULL' for function str_to_date , Please help me solve this problem

[@ikersanchez8222]

18:45 SET SQL_SAFE_UPDATES = 0; worked for me. Didn't have to restart =)

[@cynthiaryu5628]

Hi Alex! Help⁉️l purchased the bundle course$174 package earlier this year but this package is offering 50% off now and adding tableau and Python interview course. Can l have a access to the new added courses? Thanks!

[@AlexTheAnalyst]

Hey - the discount in yellow shows a discount for courses you've purchased. If you own all the courses, purchasing it again wont give access to new courses. It's a new upgrade feature we implemented :)

[@cynthiaryu5628]

@@AlexTheAnalyst So the new package is less expensive and includes more courses? Was it previously more costly and offered fewer courses? 🥺

[@bharathlingampalli4708]

while importing the dataset, it is automatically removing many rows. It has only imported 534 rows. Can anyone help me with this?

[@newenglandnomad9405]

Courtesy fyi, i'm using Windows 10, my sql workbench 8 version 8.0.32 , at 15:02 when you do clipboard create table, it won't allow my version to add 'num row" INT, some kind of identifier error. I ended up just having to manually write the create table statement but it worked that way. No idea why.

[@SafiaSingla]

you might have forgotten to add a comma after the previous column. made that mistake too

[@tanishquegupta]

@@SafiaSingla thanks man, you saved me a hell lot of time

[@mohammadalom4854]

Hello, great video. However, I'm having trouble transferring all the data from the file to MySQL. Do you have any suggestions?

[@nicolekanigina4012]

Bro, same here. I only get around 500 imported.

[@mohammadalom4854]

@@nicolekanigina4012 Did u end up finding a solution?

[@inshakhan3356]

I am unable to import the data file in SSMS, its showing errors while importing

[@The_Fitness_Vault]

Hey Alex, around the 17:00 mark, where we delete anything that has a row_num > 1, I get an error: "Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.". How do I fix this? I disabled safe mode in preferences and the error is still there?

[@AlexTheAnalyst]

Did you get out of MySQL and boot it back up? That usually does the trick

[@ichigokurosaki6470]

Continue in the video a little bit further and he will explain how to disable it. You basically have to go into the settings and disable something. Or you could ask chatgbt like I did

[@things_that]

The laugh of triumph at 43:18-19 😂😂😂