Transcription
Hello! Recently, I needed to quickly write a program in Python related to Excel. I wrote it and remembered that I have a section on my channel called "Writing a Real Program." So today, I'm here to share how I did it.
[Music]
So, let's get back to my task. I had an Excel file in xlsx format. The order form looks like this: on the left, there is a column for product articles, and each row contains data for each article, such as name, category, price, and so on. I'm particularly interested in one column: the product subgroup.
As you can see, each article belongs to a specific product subgroup. There are about 70 subgroups and around 700 articles, meaning several articles belong to each subgroup. My task was to create a correspondence table between the product subgroups and all the articles that belong to them. I needed to process the entire order form this way and create a new file with this new correspondence table.
Of course, this could all be done using Excel formulas or the built-in Visual Basic language, but I'm not interested in that. I prefer using a more universal tool like Python and enjoy programming with it, unlike Excel formulas.
By the way, if you're a beginner programmer, I recommend not thinking about doing it manually or with formulas or even delegating it to someone else. Instead, take joy in solving the problem using your programming language. Your goal is to master and frequently practice the programming language you are learning, not to find ways to avoid using it.
Alright, I’m opening my IDE and creating a new Python file, which I will name "Subcategories.py."
[Music]
Since my file is of type xlsx, I need a library that works with this file type: OpenPyXL. I already have it installed. If you need to install it, you can do so by importing it. If I were working with older Excel files (XLS), I would use another library called xlrd. By the way, feel free to share in the comments if you know of any better libraries; it would be helpful for everyone to read.
OpenPyXL has official documentation, but I find it unhelpful; I can never find what I need there. Stack Overflow is much more useful for me in this case.
Alright, I’ll store the name of my file in a variable called `file_name`. Next, I need to load this file into memory. For that, I’ll use the function from the OpenPyXL library called `load_workbook`. Just in case, I’ll set the parameter `data_only` to True so that only values are read, not formulas.
So, the entire Excel file will be loaded into a variable called `wb`, which stands for Workbook. If anyone is interested, the type of this variable is an object of the Workbook class from the OpenPyXL module. Since an Excel file can have multiple sheets, I will select the one that was active when the file was saved. I do this using the `active` method and assign it to a variable called `sheet`. If anyone is curious, the type of this variable is an object of the Worksheet class from the same OpenPyXL module.
Now that all the preliminary work is done, I will work with the `sheet` variable. This sheet is where my order form table is located.
Now, let’s think about the architecture of my program. "Architecture" might be too grand a term for my small script, but nonetheless. I will iterate through each row of my order form and create a list in memory of subgroups and their corresponding articles. After that, once the list is formed, I will write it to a file.
As you can see, this is a good and complex task. To iterate through all the rows, I will use a good old for loop.
[Music]
First, I need to know the range of rows to iterate through. I’ll count using my fingers, eyes, and counting skills. The data starts from the seventh row. Now, I’ll find the number of the last row using the `max_row` method of the Worksheet class.
By the way, someone pointed out in my last video that I keep going to Google to read the description of some function or method. Why not check the source code directly? Indeed, let’s do that now. In PyCharm, I’ll hover over the method, press Command+B (I don’t know how it works on Windows), and here we go. PyCharm dives into the source code of the library and shows me the implementation of this method.
By the way, where is the source code of the library located? In the virtual environment folder, there’s a long path, and here’s the file with the Worksheet class. If I wanted, I could change something here, delete it, and everything would break. Cool, right?
Alright, I won’t change anything here. The `max_row` method returns the maximum row number containing data. It’s clear that row counting starts from one, not zero. I could study how this method is implemented, but I don’t find that knowledge particularly useful.
Okay, I’ll close this and move on. For verification, I’ll print the value of my `max_row` variable. It shows 748 rows.
To get the value stored in a specific cell in OpenPyXL, I use the `cell` method of the Worksheet class. Again, for verification, I’ll print what’s stored in the cell with the address row 7, column 2. I run the program, and the response doesn’t tell me much. To see the actual value from this cell, I’ll add `.value` and run the program again. Now it prints "article." I check in the Excel table, and yes, that’s the one from cell address row 7, column 2.
Well, now I’m fully equipped. I know the range of rows to iterate through, how to read values from cells by their address, and I can start writing the loop through the rows. But first, a minute of useful advertising.
[Music]
I enjoy programming in Python. You probably noticed that from my videos. Python is easy to start programming with; it has a simple syntax, so well-written code can be understood even by non-programmers. However, that doesn’t mean Python can be mastered quickly and easily. Like other programming languages, it requires deep study, time, and perseverance.
If you want to not just learn to write code but also receive serious training, develop analytical thinking, and get a job as a programmer, the Hexlet Developer program can help you. The course teaches web development in Python and includes mastering the language alongside the Django framework, learning HTML, CSS, algorithms, data structures, database architecture, SQL, and everything you need to know to get a job as a developer.
There’s a lot of practice; you’ll go through over 300 tasks in the training simulator, participate in Hexlet Open Source projects, create four projects for your GitHub portfolio, and have the opportunity to complete 150 test tasks from partners based on real project scenarios.
Starting your education with Hexlet is easy; the first seven courses with full practice are completely free. This way, you can assess whether the learning format suits you. You can also get a full refund within the first two weeks if you don’t like something.
In general, friends, check out the website via the link in the description or scan this QR code. Evaluate the Developer program and start learning today, especially since Hexlet is having a New Year sale, and you can get discounts on your education and gifts.
Well, hello again!
Now, I’m writing the range for our loop from row 7 to `max_row + 1`. Why +1? Because `range` does not include the end of the specified range. If I don’t add +1, I risk missing the value in the last row.
Inside the loop, I will read the article value from the second column and store it in a variable called `sku`, which is the English term for article (Stock Keeping Unit). From the product subgroup column, I will store the subgroup name in another variable called `sub_category`. Both variables, as you understand, will be of string type.
Of course, the same string will not be repeated; it will change with each iteration. I’ll determine the number of the required column for product subgroups by pointing at the screen. It’s the 12th column.
For verification, let’s print these values in the loop. So, it prints the article and the product subgroup. In empty rows, it prints None. To get rid of these unwanted empty values, I’ll add a condition inside the loop: if the `sku` value is empty, then do nothing and move to the next iteration of the loop.
I’ll print the `sku` and `sub_category` variables again. Now there are no empty values. Let’s clean up the code from unnecessary parts.
Now I have the article value and its product subgroup for each row. Next, I need to move on to the next step: forming a correspondence list in memory between product subgroups and all articles belonging to them.
For this purpose, a dictionary data type is perfect. You should know it: curly braces, key-value pairs. I will use the product subgroup name as the key and a list of articles belonging to that subgroup as the value.
It makes sense, right? When I studied Python theory, I didn’t quite understand why this dictionary data type was invented by programmers. But as soon as I started writing my first programs for data processing, I realized how genius these dictionaries are.
The entire modern internet is structured this way. Various data is transmitted using such dictionaries. All APIs, if you’ve heard of them, transmit huge volumes of data in JSON format, which is essentially a dictionary with key-value pairs. So, friends, love dictionaries; they are powerful and useful!
Now, let’s start forming this dictionary inside the loop. I’ll call it `sub_category_dict`. It will look something like this:
This means that in the dictionary named `sub_category_dict`, I will include the product subgroup as the key and assign the article as the value.
However, the IDE highlights it in red, indicating that it doesn’t recognize this variable. I’ll declare it at the beginning of the program as an empty dictionary.
I think you already understand that this dictionary, formed this way, doesn’t do what I need. Let’s confirm that. I’ll print it after the loop.
I run the program, and it’s not bad; there’s something there. The output shows the product subgroup as the key, a colon, the article as the value, a comma, and the next product subgroup, a colon, the article, and so on. But there’s only one article for each product subgroup.
I think it’s clear why. In this line, every time we encounter a repeated product subgroup, we overwrite the key-value pair. In a dictionary, keys must be unique; there cannot be two identical keys. So, every time, it simply gets overwritten with a new value.
I don’t need that; I need a list of all articles for that subgroup as the value. So, I’ll complicate my dictionary formation operation a bit.
[Music]
Let’s first write it and then explain.
So, I created this structure. The first two lines handle the case where we encounter a product subgroup for the first time while iterating through the order form. This line says that if the value of the `sub_category` variable is not among the keys of the `sub_category_dict`, then we create a new key-value pair in our dictionary, where the key is the product subgroup and the value is a list (the list data type) initialized with our article as the first element.
The next two lines handle the case where we have already seen this product subgroup in the order form. If we have already recorded it in the dictionary, it will be found among the keys, and then we use the list method to add the new article to the existing list.
If anything in this part is unclear, please read the section about the dictionary data type and operations with it.
Let’s run the program and check. Yes, that’s what I need: product subgroups and a list of their articles. Excellent!
[Music]
The only issue is that the output of the dictionary is not very pretty; it’s all in one long line. It’s not a big deal, but for aesthetics, I’ll use the `pprint` module from Python’s standard library. I’ll import it and print our dictionary using the `pprint` function instead of the standard print.
This isn’t necessary, but why not? I run the program, and it looks beautiful!
Now, I need to write my correspondence table to a file while it’s still in memory in the `sub_category_dict` variable, which is of dictionary type.
[Music]
So, I’ll write the data to a file in the classic way, as recommended in all Python textbooks. I’ll use `with open`, and my file name will have a .ini extension since I will later use it with another standard Python module, `configparser`. But we won’t discuss that today.
I’ll write it in 'w' mode since the file is opened for writing, and then I’ll simply iterate through the dictionary line by line, writing each subgroup and its articles.
If you’ve never iterated through a dictionary, it’s done like this: `for key, value in dictionary_name.items()`. In `key`, we will have the keys of the dictionary (product subgroups in my case), and in `value`, we will have the list of articles.
To write to the file, I need to format it in a specific way: first, the product subgroup name, then the equals sign, and then the articles separated by commas.
To achieve this format, I need to first create a string from the list of articles using the good old string method `join`. It goes through the list and concatenates it into a string with the specified separator (a comma). Then, I’ll form the final string for writing: the key (our product subgroup), then the equals sign, and then the string with the articles, and I’ll write this final string to the file.
I run the program and see that the file "categories" has appeared. I open it, and oh my! It’s all in one line. I need to add a newline character at the end of each line. It looks like this: `\n`. I’ll also add spaces for aesthetics.
I run it again and check. It looks beautiful! PyCharm even recognizes the configuration file and colors it correctly. Well done!
However, as you can see, the file is not sorted. It would be better if the product subgroups were in order, starting from number 1 and so on in ascending order. If I add something manually in the future, I’ll struggle to find subgroups without sorting.
The thing is, my `sub_category_dict` is not sorted in any way. What we printed on the screen using `pprint` was not entirely accurate; it prettified the reality. It not only arranged the elements nicely but also displayed an unsorted dictionary. However, in fact, our dictionary is not sorted, which can be confirmed using a regular print statement, as you just saw.
So, I need one more action before writing the file: sort the dictionary by keys.
[Music]
By the way, this is not such a simple task for a beginner programmer. The thing is, Python dictionaries do not have a built-in method for sorting, like `sort_by_key`, for example. So, we have to get a bit creative.
We can sort the dictionary by keys in various ways. Let’s find the most concise method on Google. I’ll add this line before writing the file.
It might be a bit complicated to understand; it uses the `sorted` function on the dictionary’s `items` method. It doesn’t return exactly what we need, so we have to convert it back to a dictionary type. But we won’t delve into that today.
Let’s check what we got. I’ll comment out the file writing for now. Yes, our dictionary is sorted. I’ll change the dictionary in the file writing loop to the sorted dictionary.
I run the program, check the generated file, and everything is beautifully sorted. The task is complete!
I’ll remove any unnecessary print statements, clean up the code, and here’s the final version of the program.
What I want to say is that my code is definitely not the most optimal. In no way should you take this video as the only way to do it. In programming, every task can be solved in dozens of different ways, so please share in the comments what you would have done differently. It would be very helpful for everyone to read.
Well, write programs, friends! Solve your real-life tasks, and only then will you clearly understand programming. All that boring theory from textbooks will suddenly come to life, bursting with colors and showing its power.
[Music]