How to - finding solution for variables in many matrices with multiple constraints using Python w/ Gekko?

Question

this is my first post here.

I have a real-life financial Budgeting problem.

Having a total Budget values for BusinessUnit per each of 30 countries in my scope plus 4 quarters, I need to distribute (find) correct values for many (like up to 30) Product lines per BusinessUnit for each country total in each quarter. I also have target values for each country FullYear. Correct values are defined by constrains:

1. each quarter for each country sums up to total BusinessUnit (columns, simple sum) - constrain

2. each Product line sums up to FullYear for one Country (rows, simple sum) - constrain

3. for every Product line total (simple sum) for those 30 countries we calculate seasonality (Quarter[n] / FullYear) which should also be equal to a given constrain.

4. finally all the values I'm looking for needs to sum up to Ultimate total constrained value (with ~99% of accuracy) = total Budget target for the next year - constrain

5. please check screenshot of an example structure of the data I have in my excel input file.

For years I have been using excel solver tool, quite successfully. This year complexity grew so significantly excel solver is no longer working - too many variables and constraints. As I use python and pandas on daily basis I decided to lookup for the solution with those tools, but as I have no experience with linear programming / solution finding / decision making etc. I really do not understand much of the documentation. Looking at Gekko package tutorials I think it might be useful to solve the problem, but I don't know how to apply any of the features that Gekko includes.

What I'm also looking for is use of Pandas for this exercise to copy/paste big chunk of data (io excel / jupyter notebook) and easily create matrices / dictionaries (this I'm able to do anyways) to use with proposed solution.

Anyone could help here? Not necessarily with Gekko but in general - could you please advise how to approach the problem using Python? What tool / library I could use to find correct numbers distribution? If possible pls suggest snippets... thank you!

Thank you in advance

Answer 1

There are a few examples of using matrices in Gekko to get started:

• GEKKO - optimization in matrix form
• Follow up question: GEKKO optimization in matrix form
• GEKKO - MINLP in Matrix Form - Errors using m.axb()
• Indexing into a matrix with gekko array
• Using Gekko to minimize norm of matrix
• using GEKKO to simulate a State Space equation with huge arrays

There is also an example of using matrix operations with Gekko variables and Numpy with test_arrays.py.

import numpy as np
from gekko import GEKKO

m = GEKKO(remote=False)

# Random 3x3
A = np.random.rand(3,3)
# Random 3x1
b = np.random.rand(3,1)
# Gekko array 3x3
p = m.Array(m.Param,(3,3))
# Gekko array 3x1
y = m.Array(m.Var,(3,1))

# Dot product of A p
x = np.dot(A,p)
# Dot product of x y
w = np.dot(x,y)
# Dot product of p y
z = np.dot(p,y)
# Trace (sum of diag) of p
t = np.trace(p)

# solve Ax = b
s = m.axb(A,b)
m.solve()

Here is another example with test_matrix.py:

from gekko import GEKKO
import numpy as np
m = GEKKO(remote=False)
ni = 3; nj = 2; nk = 4
# solve AX=B
A = m.Array(m.Var,(ni,nj),lb=0)
X = m.Array(m.Var,(nj,nk),lb=0)
AX = np.dot(A,X)
B = m.Array(m.Var,(ni,nk),lb=0)
# equality constraints
m.Equations([AX[i,j]==B[i,j] for i in range(ni) \
                             for j in range(nk)])
m.Equation(5==m.sum([m.sum([A[i][j] for i in range(ni)]) \
                                    for j in range(nj)]))
m.Equation(2==m.sum([m.sum([X[i][j] for i in range(nj)]) \
                                    for j in range(nk)]))
# objective function
m.Minimize(m.sum([m.sum([B[i][j] for i in range(ni)]) \
                                 for j in range(nk)]))
m.solve()
print(A)
print(X)
print(B)

Pandas DataFrames can be used to initialize the matrix initial guess or matrix input parameters.