Excelython — Part 11: Format and Rename Columns in Python
We are almost done with the processing part of our Project. We have created so far a script where we have a total view of our stock and sales per product, pending orders, cost prices and proposed order quantities.
Not bad if you think that this is probably the half, if not more, of the information that a product manager needs to have each morning when his or her PC opens.
Project Step 9
The only thing that we need to take care for our project now are:
- Make our proposed orders integers, and round them in MOQ, if it applicable. In our simulation, for simplicity we will use a flat MOQ = 10, across all the products.
- Create a label that we can filter only the products that need to be ordered.
Excel Step 1
In order to make the result of the formula we created in Part 9, which gave us the proposed order quantity, an integer number there are two options.
Option 1
Create a new column with name = “Final Ordered Quantities” and write the formula below.
=MROUND(N2;10)
Option 2
Edit the column “Proposed Order Qty” and add all the content of the formula in and MROUND formula, like below.
= MROUND(=IF(((((F2+I2)-G2)+H2)-M2)+((M2/K2)*L2)<=0;0;((((F2+I2)-G2)+H2)-M2)+((M2/K2)*L2));10)
In both cases we want to round the number in 10 units as this is our MOQ.
If we had, where the case is probably for the most of us, different MOQ per product or supplier, we would create a new column with name = “MOQ” and we would get in that column the MOQ per product, with a VLOOKUP function, from the source that contains the information.
I will go with Option 2, as i don’t want to create a new column. It is up to you.
Excel Step 2
Create a new column with name = “New Orders”.
In this column we will apply a simple IF statement, where if the proposed order quantities are >0, then a label “Place New Order” will be applied otherwise a label “Do nothing” will appear.
=IF(N2>0;”Place New Order”;”Do nothing”)
File 1 now will look like this.
In Python, apart from what we did in excel, we will add some steps as we have to:
- Format the SKU column in 8-digit text, with leading zeros.
- Rename the Current Inventory column to Stores Current Inventory, as it appears in excel.
Python Step 1
Until now we never asked what type of data has our dataframe. Never trust that what you see is what you get!!!!
How will check what type of data is present in our dataframe?
Just type
File_1.dtypesAnd you will get printed in your console the below information
In our case we get what we see(exception!!!!!), but we need to transform the SKU column to 8-digit text with leading zeros.
This is, as usual, a very simple thing.
File_1["SKU"] = File_1["SKU"].astype(int).astype(str).str.zfill(8)The structure of the above line is:
- Select the column that you want to transform. In our case is File_1[“SKU”].
- Define that you want to be transformed as integer. In case you don’t do it, even in integer numbers, if the dtype is flow you would get
ex. Our SKU in the dataframe is 12. Instead of getting 00000012 after transformation you would get 00000012.0 which is not acceptable.
3. Define that you want to be transformed as string.
4. Define that you want to be treated as string.
5. .zfill is zero fill, place leading zeros until you have 8-digits.
Python Step 2
Rename is one of my best python functionalities. The reason is that you can rename a column with multiple ways.
I will show you the one that I found during the years the most easy to handle.
So what we have to do is to rename the “Current Inventory” column.
As we have previously said, in Python you start counting columns and rows from 0. So the column we want to rename is the 6th column.
File_1 = File_1.rename(columns={File_1.columns[6]: "Stores Current Inventory"})If you need to rename more than one column, you keep the same structure and you add the other column separated by comma, inside the dictionary.
ex.
File_1 = File_1.rename(columns={File_1.columns[6]: "Stores Current Inventory",
File_1.columns[1]: "New SKU"})Python Step 3
In Python there is no MROUND. It will be realy easy with a workaround to do the same.
We will create a function that looks like below.
def MOQ(x, base=10):
return base * round(int(x) / base)And then as we learned in Part 9 we will apply this function to the column “Proposed Order Qty”.
File_1["Proposed Order Qty"] = File_1["Proposed Order Qty"].apply(MOQ)Then our File_1 dataframe will look like this
Python Step 4
The next step of processing our main panel is to add the column with the label of the status of new order.
We will create a column with name = “New Order Label” and we will do a simple conditional statement as we learned in Part 6.
File_1["New Order Label"] = np.where(File_1["Proposed Order Qty"]>0,"Place New Order","Do nothing")Our File_1 dataframe now looks like this
Formatting is really important in Python, as the imported files are not always preformatted as we want. Imagine a column with Numerical and Text values. You are not able to do any calculation with this column as it is a mixed type column and you need to handle it with other workarounds. Dates is another pain. Separators, locals, etc. has to be really clear, so you can manipulate this data. At the end of the project I will give more details in formatting, for the most common types that can give you really hard time on finding the solution and at the end after 2–3 hours of searching you will find that it was just one word or one line of code.
We have almost complete the main panel as our project need. In the next part, after one more column addition we will learn how to save our dataframe in excel!!!!
Regards
