Excelython — Part 6: IF statement in Python

IF is one of the most vital words in human history. It reflects the curiosity of our brain by asking questions that their answers led us to remarkable innovations through humanity’s timeline.

IF is also a vital statement in Excel. How many of you haven’t done an IF, to IF, to IF ………….. to IF statement that finally got you what you want.

IF(“I am going to continue philosophy” = True;”You will not learn how to do it in Python”;”You will get something out from this page”), so I jump to our project’s next step.

Project Step 3

In order to be able to check if we need any extra orders for our inventory, we need to have the following information in File_1:

1. What do we have in our warehouse. — Done from previous Part.
2. What do we have in our stores and how much we need to replenish.

Everyday each store has a certain amount of stock. This stock might be higher = Overstock or lower = Understock, than the minimum levels of stock that we have set in the system.

So if we did a pivot table on an hypothetical Overstock-Understock Column in File_3, the result would it be wrong as the Overstock stores, would trim the quantities that we need to replenish in Understock stores.

For that reason instead of a simple

Overstock-Understock = Current Inventory-Min Stock

we should do

Overstock-Understock = IF(Current Inventory-Min Stock>0; 0;Current Inventory-Min)

3. What we are going to sell until the next time the stores will be replenished.

For the stores that are Understock, if there is availability in the Warehouse, they will get replenished the quantity that they are missing to hit their minimum level of stock + the forecasted sales quantity until the next time they will get replenished.

In this part we will deal with the first part of #2.

Excel Step 1

In File 3 we will create a new column with name = “Store Code Need”

Excel Step 2

In File 3, on the first cell bellow the new created column, let’s write an IF statement as before

=IF(Current Inventory-Min Stock>0; 0;Min-Current Inventory)

Drag until the last row this formula and your new column will have the data you need.

Python Step 1

In order to do it in Python dataframe, we will use the Numpy library that we imported in the beginning of the script.

The stracture of writing the code is exactly as it is in excel.

IF(a condition in row is something ; then do this; else that).

So, let’s take the File_3 Dataframe. It looks like this

We will create a new column with name = “Store Code Need”.

To do this we will write the code below

File_3["Store Code Need"] = np.where(File_3["Current Inventory"] - File_3["Min Stock"]>0,0,File_3["Min Stock"] - File_3["Current Inventory"])

As you see, instead of IF we write np.where and the delimeter it is comma.

This will return the dataframe with the new calculated column “Store Code Need”.

The next step that we need to do is to create a Pivot Table in order to keep the aggregated sum that we are missing per SKU.

This is what we will see in the next part.

Regards

Go to Part 7