Akansha Yadav shows how to clean an Excel file with concatenated data using pandas. She splits category and amount columns on delimiters, pairs them with zip, and converts amounts to floats to build a normalized DataFrame you can save back to Excel.
Let's talk about data cleaning, episode number 2, where we have been provided with one data file which is something like this, where we have order ID, category, and amount. Our task is to make it something like this where everything is in the sorted manner. We have the categories in the different, different lines. So for this, we need one pandas library, and once we have installed pandas library, what next we need to do is we need to read our Excel file. I'm not downloading openpyxl because I already have. Then I'm creating one empty list to hold the transform data. Now here comes the main part which is going to do everything for us. So we are iterating through the rows of the DataFrame. So we already have a complete DataFrame. We don't have, uh, something unique, uh, data, but we just have the data which is in the messy format. So now we are going to iterate through the rows. So I have two things within the DataFrame. One is going to be index, another is going to be the row. So for index and row in df.iterrows, I'm going to split categories strings into individual categories simply. So I have categories and amount simultaneously, two columns I'm taking, and I'm going to split them on the basis of this, uh, standing line character. So I'm going to use it for the category and amount, both the things, and I'm applying the function called .split. row_category.split, row_amount.split, and this is the character that I'm using for the reference. Then I'm creating a new entry for each category corresponding amount. For each category and amount, I am going to zip the categories and amount. So this is going to be my transform data. As we have already created one particular, uh, list in which we are going to add our transform data, so we have order ID, and then I want row order ID, comma, category should be there, amount float amount should be there. It is going to be the decimal points value. So once we have the data, we have added the transform data, we are creating a new DataFrame from the transform data that we have just created. And then optionally you can save it to the new Excel file and you can display whether it is transformed or not. So now let's run everything. This is the entire code that I have written, and the output also you can see. This is my complete transformed data. So you can view it in the file generated as well. So that's how you can clean any data file.
