I want to create a vectorized approach (numpy) to populate/calculate the matrix called “qtyP” using input from the matrices “qtyC” and “rel”. It is easy to solve in for loops but I would like to do it in a smarter way. First I will describe my matrices.
Product vs week matrix (end result)
# This is the desired end-result matrix (5x6) # It describes how much of each product is available per week # Rows: Products (5) # Columns: Weeks (6) qtyP = [[0,3,0,10,22,53], [0,0,75,154,213,250], [0,0,75,154,213,250], [0,0,75,154,213,250], [0,0,17,68,133,180]] qtyP = np.matrix(qtyP)
Capacity vs week matrix
# This matrix (5x6) describes how much of each capacity is available per week # Rows: Capacities (5) # Columns: Weeks (6) qtyC = [[0,0,17,68,133,180], [0,0,75,154,213,250], [0,0,39,11,34,100], [0,0,357,648,861,1531], [0,3,0,10,22,53]] qtyC = np.matrix(qtyC)
Product vs capacity relationship matrix
# This matrix (5x5) details the relationship between "Products" and "Capacities". # If there is a 1 then that capacity is needed for the product # e.g. capacity 5 is need for product 1 # e.g. capacity 2 and 4 is needed for product 2 # Rows: Products (5) # Columns: Capacities (5) rel = [[0,0,0,0,1], [0,1,0,1,0], [0,1,0,1,0], [0,1,0,1,0], [1,0,0,1,0]] rel = np.matrix(rel)
How to get the end-result
I will explain how I did this with for loops so that it is better explained.
- For each row/product in qtyP check what capacities are needed by looking at rel
- For the capacities needed, find the minimum quantity of those in qtyC
- Populate for the current row in qtyP the minimum quantity of needed capacities for all six weeks
The rows in qtyP are the rows in rel and the rows in qtyC are the columns in rel. The reason to take the minimum quantity of the capacities included in a product is because that is what is possible to build. In this example I happen to have 5 products and 5 capacities, but it could be 3 products and 5 capacities as well. Then the rel matrix would be a (3×5), the qtyC matrix would be a (5×6) and the qtyP matrix would be a (3×6).
Is there a way to this in a vectorized approach? I tried something as the code below but I can’t seem to get it right on both the order of the products and how to include the minimum function.
qtyP = qtyC[rel[np.newaxis,:] != 0]
EDIT – Changing my answer based on the comments.
IIUC, here is what you can do –
- Since, before you can calculate the minimum, you need the different quantities for each product for each week, what you need to construct is a
broadcasting. For each
week (6), for each
product (5)get the product of the corresponding value with the
rel row (5).
- In this matrix you will now be able to take a
minimumover the last
axis=-1to get a
- However, because there are zeros, np.min wont work directly. So what you can do is use a
masked_arrayto mask the
0values and then find a
fillthe masked values if returned, by 0 and
transposeto get a (5,6) matrix.
b = qtyC.T[:,None,:] * rel #(6,5,5) c = np.ma.masked_array(b, mask=b==0).min(-1) qtyP = c.filled(0).T print(qtyP)
array([[ 0, 3, 0, 10, 22, 53], [ 0, 0, 75, 154, 213, 250], [ 0, 0, 75, 154, 213, 250], [ 0, 0, 75, 154, 213, 250], [ 0, 0, 17, 68, 133, 180]])
Do check and let me know if this handles your edge cases.