[FIXED] How to round a value to make it divisible by a specific number in Pandas?

Issue

Months_of_inventory = 2
Case_Qty = 20
df['Order'] = np.where(((df['Avg_Sales'] * Months_of_inventory) - df['On_Hand_Inventory']) <= 0,0, ((df['Avg_Sales'] * Months_of_inventory) - df['On_Hand_Inventory']))

The ceiling of (df['Avg_Sales'] * Months_of_inventory) - df['On_Hand_Inventory']) has to be the Case_Qty, meaning the result should be divisible by 20

I was trying to nest np.where to check if the result is or not divisible by the Case_Qty and if not to make it divisible by rounding it up (like in excel when using ceiling function) but I could not find the way of doing it in Pandas. Thank you!

Solution

You can use numpy.ceil:

import numpy as np

df = pd.DataFrame({'Qty': [0, 1, 19, 20, 21]})

Case_Qty = 20

df['Num_Case'] = np.ceil(df['Qty'].div(Case_Qty)).astype(int)

df['Total_space'] = np.ceil(df['Qty'].div(Case_Qty)).mul(Case_Qty)

df['Space_left'] = np.ceil(df['Qty'].div(Case_Qty)).mul(Case_Qty).sub(df['Qty'])

print(df)

output:

   Qty  Num_Case  Total_space  Space_left
0    0         0          0.0         0.0
1    1         1         20.0        19.0
2   19         1         20.0         1.0
3   20         1         20.0         0.0
4   21         2         40.0        19.0

Answered By - mozway