Akansha Yadav shows how to clean messy Excel data by splitting one raw column into four separate columns using pandas and regex. She extracts name, address, age, and gender from unstructured text with .str.extract, then saves the cleaned data to Excel and CSV files.
Now let's understand what exactly data cleaning is, and because data cleaning is very important for data analysis, data scientists, and machine learning or AI role even too. So let's discuss this in this episode 1. So let's suppose you have been provided with this raw data in which you have the data within one column only. Now your task is that you have to split this entire data into multiple columns to make it more meaningful. If you will look at the data, we have name, address, age, and gender, and this is what we are looking forward in the output. It should look something like this. You should have 4 different columns and data should be appropriately added in those columns as well. So now let's see what exactly we need to do for this. First thing that we need is we need a pandas library, which is supposed to be a best data analysis and data cleaning library. Second library that you need is openpyxl. Openpyxl because openpyxl is used to process the data which is in the Excel sheet. So as I have the data in the Excel sheet, I need this library. Now next thing is that we have to start writing the code. So first is we have to import pandas library. Once we have imported the pandas library, our task is to load the Excel file. So I have file path is equal to app.xlsx, and then I am reading that Excel sheet, and then I am checking whether the data is the same one which I have just loaded or not. Now comes the main part. Now within this main part, what I am doing is I am using the regex expression. Now what is regex? This is very important thing that you have to learn for being expert in data cleaning. So regex expression is basically regular expression that is used to extract the data, that is used to split the data as you want it and within the predefined pattern. So these terminologies you have to remember. So now let's look at the data and the code that is on my screen. So I have 4 different columns because in output also I want 4 different columns. First should be name, then should be address, then age, and then gender. Now name is going to be the first column. Now how to define name? What is the name in my data? If you will look at my data, I have one thing which you should notice. I have written df bracket and then first row of data. Why this first row of data is because in my case I don't have the column name. So in my case it is going to be the first row. So in your case it may be different, but, but it should be basically the syntax is that it should be the column name. So in my case, the column name is just the first row of data, so that is why I have written that. Okay, so now I'm giving it df_name is equal to df, the first column name, .str.extract. Now if you will look at the data, which is name Hussein Hakim, address age 17, gender male. Now within this data, I have one pattern that I can notice, that all the column names exist in my data. Name, address, age, and gender are already there in the data. Which is go— which is what I'm going to take for the reference. So what I have written is r quotation begins name bracket begins dot asterisk symbol question mark bracket close address. What I am trying to say is the expression that I have written which is within the bracket is— this is non-greedy match. Non-greedy match is there can be multiple things within these two characters, these two words. Basically name and address are two reference point points that I am providing to this expression. I am saying that you have to look something within, between name and address. So you have to look for something which is between name and address for name column. Then for the address column also, I am doing the same thing. I have written entirely the same code, only the words like target words are different. I am saying that now you have to look something in between address and age. Whatever comes up in between this, you have to get that for me. Now there comes one twist that for first and second row I have done the similar thing, but the third and fourth row I'm doing something different. Why am I doing that? After age, if you can notice that there is one single digit in every column, I have the same thing which is one single digit after that, okay? So that is what I am going to take it. So that particular digit I am going to take up and then For the gender, I am going to take word. So here /d+ stands for digit, and which is going to basically take digit out after age. And from— for the gender, I am taking /wp. /wp means this is going to extract a word which is going to represent the gender in our case, and /d+ is going to extract a digit which represents the age. So this is the entire regex expression which is going to basically clean your data, which is going to basically extract the meaningful data. Into multiple columns for you. Once you have done this, this is the major and important part in data cleaning, and you should learn it in detail. Now the next thing is, if you want to delete the raw data, it means your original data, you can do it with df.drop by mentioning this axis is equal to 1 because I wanted to go for the vertical-wise. I don't want it to go horizontal-wise. And then inplace is equal to true is used when you wanted to make the changes in the file itself. When you wanted to make the changes applied, whatever you have made it in the original file itself, that is why we write inplace=True. Once you have done all the data cleaning, you have dropped the raw data, then you can save your files. This stage is again optional. If you wanted to save the file, you can. If you don't want to, you can proceed with your further data analysis, data visualization, and model building. But I am showing you that how you can save your cleaned file as a new Excel file. So I'm providing cleaned file path, which is cleaned_data.xls. This should be the file name. Then df.to_excel. I'm saving two files, one in, in the Excel and second in the CSV. For CSV, I'm directly providing it within the brackets. Index I don't want, that is why I have kept index is equal to false. Once you have done all of that, I'm just printing one simple print statement saying that cleaned file saved to this particular path. So that I can see that whether the changes are made or not. Now let's see everything, how it will look in the Visual Studio Code. I have written the entirely same code over here, and I am going to run everything at once. And we have installed Python and the required library, and this is the main code. And you can see, this is the entire code and the output. So I already have cleaned up the file. You can do that too. Just make sure you follow up the regex expression. Make sure you are learning the things day by day. And this is very important. Data cleaning is very important. That is why I have started this series. Don't forget to subscribe to my channel because I'm going to come up with more episodes in this. If there is any messy file with you, you can drop that on this particular mail ID. I will help you via tutorial to how to clean it. Make sure that is a public data or make sure that is something which is related to maybe the public source. It shouldn't be the company's data. So I hope you found this video interesting, informative, and something you knew you have learned. So don't forget to subscribe to my channel. We'll meet in the next video. Bye-bye!
