Excelython — Part 9: Writing Calculation Formulas in Python
Calculations in Python Pandas DataFrame can be done by interacting with the columns or the rows of the dataframe itself or by defining and apply them in a column or in a row.
The best part of applying a calculation to a dataframe is that you will never again have wrong results because of a filter or missed drag and drop action as I am pretty sure you have experienced in Excel.
Project Step 7
We will start the processing phase of the file now, in order to get the rest of the information we need.
From the Project Description we have covered the first two topics, as now we have a panel with all of our products sales and inventory and the pending orders we have per SKU.
Now we need to check in which products we need to create new orders, so we will not get stock outs.
In case you are a great product manager you should take into account also the forecast sales,the lead-time period and the safety stock levels, that are set for each product.
For purposes of simplification we will assume that all products have 1 week lead-time, we need to keep 2 weeks of stock in Warehouse and the forecast value is the average sales per week the last four weeks.
Definitely you can import the information of each lead-time you have set in each product and also import the forecast sales for the following weeks from your systems but this is not what we want to achieve now. If you have access to this type of data replace the below steps by importing them as we did for other information in previous steps.
Excel Step 1
In File 1 create a new column with name = “Lead-time(Days)”.
In our basic assumption we mentioned that this would be 1 week = 7 days, so add the value = 7 in all rows of the column.
Excel Step 2
Same way, we will create a new column with name = “Warehouse Stock Coverage(Days)”.
Based on our assumption this is 2 weeks = 14 days, so we add the value = 14 in all rows of the column.
Excel Step 3
In order to get the forcasted sales for the lead-time period we will create a new column with name = “Sales Forecast (Lead-time)” and we will write a simple formula as:
=(AVERAGE(B2:E2)/7)*K2
What we said before? We need to calculate the average sales per week, based on the last 4 weeks sales, divide it by 7 days in order to calculate the average daily sales and the returned result we multiply it by the lead-time period, which in our case is 7 days, so we can get an estimation of how much we are going to sell until the time the order we place today to our supplier, will be delivered in the point of sale.
File 1 now looks like this.
Python Step 1
In Python we have two options to define a column with standard values.
I will show you both ways on creating the column “Lead-time(Days)” but after that I am going to use the second one.
Option 1
File_1["Lead-time(Days)"] = 7Option 2
leadtime_days = 7
File_1["Lead-time(Days)"] = leadtime_daysI prefer the second option, as if something happens and the lead-time days have to be a different number because of business decesion or a supplier issue, then I will just need to change the value on clear defined variable.
Python Step 2
We will create a new column with name = “Warehouse Stock Coverage(Days)”.
We will define a variable with the fixed assumption’s value = 14 and we will set it on the new column.
coverage_days = 7
File_1["Warehouse Stock Coverage(Days)"] = coverage_daysPython Step 3
Now we will define the formula to calculate the “Sales Forecast (Lead-time)”, as we did in Excel, but in a pythonic way.
File_1["Sales Forecast (Lead-time)"] = ((File_1[["Sales LW","Sales W-2","Sales W-3","Sales W-4"]].mean(axis=1))/7)*leadtime_days# axis=1 as we need to do this calculation in each row of the dataframe
Our File_1 Dataframe now looks like this
We learned in that part on how we can create new columns with a specific value or with a calculated value.
The logic is very simple. Just define the way that each column interacts with other columns or with numbers, using the same logic of symbols and parentheses as in Excel and you will get the result per row(don’t forget axis=1 for all the calculations you need per row).
In the next Part we will finalize the processing part by generating an order qty for each product.
Regards
