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.
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.
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.
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?
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.
I'm so proud to have made it this far! All made very possible by Alex's natural teaching skills. Thanks Alex! This is absolutely going on my portfolio! 👍
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.
I have started my journey of learning data analytics.... And with the help of your content i understand so many things quickly and easily... Hopefully i will get the job at the end of thid course.... Thanks a lot alex... From pak
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.
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❤
For anyone that struggled with the date error code, I've spent countless troubleshoot and was able to find out what was causing the issues. In my case it was the DATE not showing as NULL but showing as 'None' or blank like this ' ' to fix this, you will need to update it as NULL depending if yours is showing 'None' or ' ' update layoffs_staging2 set `date` = NULL where `date` = 'None' OR ' ' (NO SPACE, DEPENDING WHAT YOURS IS SHOWING) p.s Thank you Alex for this project. it was really helpful!
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.
Thank you, Alex. I can't believe I did my first Data Cleaning project. I have been following the playlist. I did some cleaning by myself. My dataset is more refined though. Lots of love and respect!
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
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..!
...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!
39:40 I'm learning a lot from this tutorial, so thank you! I'm looking at the SQL command and I can't help thinking it would look less confusing to a co-worker if it were a FOR LOOP to get a similar result.
Alex congratulating me for making it to the end like i have a choice. i have made it to the end like 3 times now😂. great work man. i bless the day i discovered your channel.
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
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;
Hi, your comments appreciated on followin removing duplicates sql statement in very easy way: Create table test (TABLE NAME) select distinct * from layof;
in 31:30 after updating code UPDATE layoffs_staging2 SET `date` = STR_TO_DATE (`date`,'%m/%d/%Y') I get error like Error Code: 1411. Incorrect datetime value: 'NULL' for function str_to_date the chatgpt says There could be differences in your MySQL server configuration (like sql_mode settings) compared to the instructor's environment, which may allow his code to handle NULL values more gracefully. Specifically, if STRICT_TRANS_TABLES is enabled in your sql_mode, MySQL will enforce stricter validation rules, causing the NULL error you're seeing. anyone else have the same issue?
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 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.
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?
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.
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ˋ
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`
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 ?
The only thing I am interested in is, how you can make MySQL this interesting, these videos are so great that I am watching it till the end from the very beginning. Thanks Alex 👍
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.
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?
Also i wanted to add for the null values when it came to total_laid_off and percentage_laid_off, for companies that had multiple rows and some of those multiple rows had null values but at least one had all values we could have gotten the values for those one by using eg: total_laid_off = 20 and percentage_laid_off = 0.5 then we can assume that that company had a total of 40 and then used that to populate the similar rows for the company if it had say the percentage laid off or the total laid off but that only works if hiring did not happen and a different date could still share the same base population as the one we found
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.
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.
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!!
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;
That was absolutely very informative and helpful. it's a real-world data. I guess there are a lot of dataset that are more messy which would a different approach.
When removing duplicates, should we not factor in the date? reason being, when using your window function and taking all columns, there are some companies which have the same data across all columns except the date so that would in turn mean it is not an actual duplicate. Examples to check ('Spotify', 'Olist' , 'Microsoft').
Good evening, I have a problem with downloading data in mysql. The file layoffs.csv in the description has 2361 row and after downloading on my pc too. After importing the data into MySQL using the method in this episode (table data import wizard), only the first 564 rows remain in my table. Has anyone encountered this problem before? At the same time I take this opportunity to thank Alex for the work and time spent
copied this from @nandikran "If you are facing only 500+ rows of data getting imported where as there are 2000+ rows of data, here is how to fix it: Step 1: Convert your csv file to JSON using any online CSV to JSON converter. Step 2: You will have 'None' instead of NULL in your data now. Run this query UPDATE layoffs SET company = CASE company WHEN 'None' THEN NULL ELSE company END, location = CASE location WHEN 'None' THEN NULL ELSE location END, industry = CASE industry WHEN 'None' THEN NULL ELSE industry END, total_laid_off = CASE total_laid_off WHEN 'None' THEN NULL ELSE total_laid_off END, percentage_laid_off = CASE percentage_laid_off WHEN 'None' THEN NULL ELSE percentage_laid_off END, `date` = CASE `date` WHEN 'None' THEN NULL ELSE `date` END, country = CASE country WHEN 'None' THEN NULL ELSE country END, funds_raised_millions = CASE funds_raised_millions WHEN 'None' THEN NULL ELSE funds_raised_millions END; Step 3: You will have all fields as Text, to convert the wrong ones into Int, run this query ALTER TABLE world_layoffs.layoffs CHANGE COLUMN total_laid_off total_laid_off INT NULL DEFAULT NULL , CHANGE COLUMN funds_raised_millions funds_raised_millions INT NULL DEFAULT NULL ; Now your data is identical to Alex's"
Timestamp: 27:30: When you did the update statement, there was no need to put the like statement as just doing it like that would have removed the full-stop at the end of every country, and I do not believe any country requires a full-stop.
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.
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 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.
Thanks Alex for such a informative tutorial on cleansing. But how would we handle some of the remaining null values in total_laid_off, percentage_laid_off and funds_raised_million columns?
Any idea why not just update the 3 missing industry names at 42:00 mark with 3 different update statements, instead of using a join? Seems overcomplicated, no? Or is your way better because it represents working with massive ammounts of data :D
@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.
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...
@@chandrabose867 no job for you
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
@@obeliskphaeton yeap if you change it before importing for some reason does not work, thanks!!
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.
Thank You!
just click the bar under Tools
Alex! This is why I subscribed, thank you so much for doing this in MySQL!!
Thanks for the kind words! I made it to the end and learned a lot while working on the project simultaneously.
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.
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?
Amazing Alex, this is exactly what I'm looking for my project too. Thank you so much
I love the way you teach. It is so important to see the errors when learning something new.
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.
I'm so proud to have made it this far!
All made very possible by Alex's natural teaching skills.
Thanks Alex! This is absolutely going on my portfolio! 👍
Patiently waiting for the exploratory aspect of the clean data.
Thanks very much
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.
how did you download the datasets from his github??
Finally completed this after a second trial and now I have my first project on MySQL
I have started my journey of learning data analytics.... And with the help of your content i understand so many things quickly and easily... Hopefully i will get the job at the end of thid course.... Thanks a lot alex... From pak
Hey, me too. btw, would you like to share the name of city you are from?
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.
Thanks for this video, Alex! Really need it
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❤
watch till the end it's awesome Alex ! thanks so much
For anyone that struggled with the date error code, I've spent countless troubleshoot and was able to find out what was causing the issues.
In my case it was the DATE not showing as NULL but showing as 'None' or blank like this ' '
to fix this, you will need to update it as NULL depending if yours is showing 'None' or ' '
update layoffs_staging2
set `date` = NULL
where `date` = 'None' OR ' ' (NO SPACE, DEPENDING WHAT YOURS IS SHOWING)
p.s
Thank you Alex for this project.
it was really helpful!
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.
Alex, You're so natural. The Best yet!
exactly what i was looking for! thanks a lot 🙌🏻
Thank you, Alex. I can't believe I did my first Data Cleaning project. I have been following the playlist. I did some cleaning by myself. My dataset is more refined though. Lots of love and respect!
Awesome! I can't believe I followed through to the end. You're an incredible tutor.
Alex videos are always so real authentic and so relevant!
Thanks Alex, Can't wait to Start this project
Great work, Alex. we love you
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
Alex The Analyst, You are a Blessing to this Generation...
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..!
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.
...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!
39:40 I'm learning a lot from this tutorial, so thank you!
I'm looking at the SQL command and I can't help thinking it would look less confusing to a co-worker if it were a FOR LOOP to get a similar result.
Alex congratulating me for making it to the end like i have a choice. i have made it to the end like 3 times now😂. great work man. i bless the day i discovered your channel.
Thank you Alex for the kind words at the end of this video. It made me feel good 😊
Has anybody told you that you are not just good but you are AWESOME !👑
Thanks for doing a project in SQL. Waited for long.
Made it to the end!! Thank you Alex. Much love from Tanzania:)
twinsies
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
if you got any answers regarding this, please share that with us too.
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;
Thank you Alex! I have learnt so much from this incredible Data cleaning project.
I shouldn't be having this much fun doing homework. Thank you Alex!
"Deleting data is a very interesting thing to do, you have to be confident. Am i 100 confident? not really", LOL 😂
Thanks a lot for simplifying MYSQL Alex!
Hi,
your comments appreciated on followin removing duplicates sql statement in very easy way:
Create table test (TABLE NAME)
select distinct * from layof;
A great tutorial, Thanks Alex the Analyst. It also helps me connect with SQL coding which I have learned in the tutorials. 😃😃
in 31:30 after updating code
UPDATE layoffs_staging2
SET `date` = STR_TO_DATE (`date`,'%m/%d/%Y')
I get error like Error Code: 1411. Incorrect datetime value: 'NULL' for function str_to_date
the chatgpt says
There could be differences in your MySQL server configuration (like sql_mode settings) compared to the instructor's environment, which may allow his code to handle NULL values more gracefully. Specifically, if STRICT_TRANS_TABLES is enabled in your sql_mode, MySQL will enforce stricter validation rules, causing the NULL error you're seeing.
anyone else have the same issue?
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.
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?
i did same, its easier but i think he try to teach us every way
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ˋ
Hey, I've had the same issue and I had to click on the row_num column so it will show it in the ascending order
Thanks for sharing this helpful content, Alex! We need more of this.
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
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 ?
did you find solution for that
just on time 🤩
The only thing I am interested in is, how you can make MySQL this interesting, these videos are so great that I am watching it till the end from the very beginning. Thanks Alex 👍
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.
while importing the dataset, it is automatically removing many rows. It has only imported 534 rows. Can anyone help me with this?
same issue
can you make a tutorial on how to connect this mysql database in power bi and make a dashboard for it?
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
learned a lot from this project. Special Thanks to Alex
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?
Reload the file and redo the query.
@@Chinmayimanvi hi can you help me with that please?
Thanks a lot . First time watching your video and u were great 👍
Also i wanted to add for the null values when it came to total_laid_off and percentage_laid_off, for companies that had multiple rows and some of those multiple rows had null values but at least one had all values we could have gotten the values for those one by using eg: total_laid_off = 20 and percentage_laid_off = 0.5 then we can assume that that company had a total of 40 and then used that to populate the similar rows for the company if it had say the percentage laid off or the total laid off but that only works if hiring did not happen and a different date could still share the same base population as the one we found
Hey alex , thanks for the video.
Please cover data cleaning in stata or R as well.
Great , The Alex. thank you so much. I have learned a lot from basic to Cleaning Data.
Thank you once again.
thank you alex! this work is very much appreciated! more power!
Excellent my friend, I will come back soon and support the channel.
Took me all day but yayyy I’m done my first sql project!!
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
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.
Truly Blessing for us.
Alex u r the best! Thank you so very much... Plzzz do more videos on data cleaning.
i Appreciat your work Alex, well done
Incredible tutorial Alex. Thank you!
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.
This was very insightful, thank you so much for this Alex.
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 '. ' ?
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!!
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;
@@adeope3702 is the != 'NULL' part checking for the literal text of NULL? That maybe the issue I"m coming up with.
@@adeope3702 Thanks a lot for the solution.
Please how did you manage to import it? I'm also having importing issues
That was absolutely very informative and helpful. it's a real-world data. I guess there are a lot of dataset that are more messy which would a different approach.
When removing duplicates, should we not factor in the date? reason being, when using your window function and taking all columns, there are some companies which have the same data across all columns except the date so that would in turn mean it is not an actual duplicate. Examples to check ('Spotify', 'Olist' , 'Microsoft').
Good evening, I have a problem with downloading data in mysql. The file layoffs.csv in the description has 2361 row and after downloading on my pc too. After importing the data into MySQL using the method in this episode (table data import wizard), only the first 564 rows remain in my table. Has anyone encountered this problem before? At the same time I take this opportunity to thank Alex for the work and time spent
copied this from @nandikran
"If you are facing only 500+ rows of data getting imported where as there are 2000+ rows of data, here is how to fix it:
Step 1: Convert your csv file to JSON using any online CSV to JSON converter.
Step 2: You will have 'None' instead of NULL in your data now. Run this query
UPDATE
layoffs
SET
company = CASE company WHEN 'None' THEN NULL ELSE company END,
location = CASE location WHEN 'None' THEN NULL ELSE location END,
industry = CASE industry WHEN 'None' THEN NULL ELSE industry END,
total_laid_off = CASE total_laid_off WHEN 'None' THEN NULL ELSE total_laid_off END,
percentage_laid_off = CASE percentage_laid_off WHEN 'None' THEN NULL ELSE percentage_laid_off END,
`date` = CASE `date` WHEN 'None' THEN NULL ELSE `date` END,
country = CASE country WHEN 'None' THEN NULL ELSE country END,
funds_raised_millions = CASE funds_raised_millions WHEN 'None' THEN NULL ELSE funds_raised_millions END;
Step 3: You will have all fields as Text, to convert the wrong ones into Int, run this query
ALTER TABLE world_layoffs.layoffs
CHANGE COLUMN total_laid_off total_laid_off INT NULL DEFAULT NULL ,
CHANGE COLUMN funds_raised_millions funds_raised_millions INT NULL DEFAULT NULL ;
Now your data is identical to Alex's"
awesome work Alex! and thanks for providing this kind of contents
Timestamp: 27:30: When you did the update statement, there was no need to put the like statement as just doing it like that would have removed the full-stop at the end of every country, and I do not believe any country requires a full-stop.
Hey Alex, thank you for this awesome bootcamp, can you make a video about how to use this project on our portofolio?
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.
11:55 what if there are too many columns and we are not able to write every column name in partition by
thanks alex for this data cleaning practice
Thanks Alex for such a informative tutorial on cleansing. But how would we handle some of the remaining null values in total_laid_off, percentage_laid_off and funds_raised_million columns?
This was an amazing tutorial!! Thank you
Thanks alot, Alex. you're doing an amazing job.🥰💜
Excellent Alex!!! You read my mind, man! This is just what I needed to put in my portfolio. THANK YOU
Looking forward to next project
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
@@nicolekanigina hi did you solve this problem?
Any idea why not just update the 3 missing industry names at 42:00 mark with 3 different update statements, instead of using a join? Seems overcomplicated, no? Or is your way better because it represents working with massive ammounts of data :D
@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.
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?
hi
Can we use UPDATE statement to change the type of date column ,@ 28:44 ?
Quick question: How come you didn't get a third table (layoffs_staging2)? Mine did after choose a create statement around 14:42.