0

I am trying to create a matrix with the following constraints.

  1. The column sum should be between 300 and 390, both values inclusive.
  2. Row sum should be equal to user-specified values per row.
  3. No non-zero value in the matrix should be less than 10.
  4. The count of non-zero values in a given column should not exceed 4.
  5. The columns should be arranged in a diagonal order.

if UserInput = [427.7, 12.2, 352.7, 58.3, 22.7, 31.9, 396.4, 29.4, 171.5, 474.5, 27.9, 200]

I want output matrix something like this,

Example matrix

Edit 1

I have tried the following approach using Pyomo, however, I got stuck on 5th constraint that column values should be diagonally aligned in the matrix

import sys
import math
import numpy as np
import pandas as pd

from pyomo.environ import *

solverpath_exe= 'glpk-4.65\\w64\\glpsol.exe'
solver=SolverFactory('glpk',executable=solverpath_exe)

# Minimize the following:
# Remaining pieces to be zero for all et values
# The number of cells containg non-zero values

# Constraints
# 1) Column sum, CS, is: 300 <= CS <= 390
# 2) Row sum, RS, is equal to user-specified values, which are present in the E&T ticket column of the file
# 3) Number of non-zero values, NZV, in each column, should be: 0 < NZV <= 4
# 4) The NZV in the matrix should be: NZV >= 10
# 5) The pieces are stacked on top of each other. So, a the cell under a non-zero value cell is zero, than all cells underneath should have zeros.

maxlen = 390
minlen = 300
npiece = 4
piecelen = 10

# Input data: E&T Ticket values
etinput = [427.7, 12.2, 352.7, 58.3, 22.7, 31.9,
           396.4, 29.4, 171.5, 474.5, 27.9, 200]


# Create data structures to store values
etnames  = [f'et{i}' for i in range(1,len(etinput) + 1)]
colnames = [f'col{i}' for i in range(1, math.ceil(sum(etinput)/minlen))] #+1 as needed

et_val = dict(zip(etnames, etinput))

# Instantiate Concrete Model
model2 = ConcreteModel()

# define variables and set upper bound to 390 
model2.vals = Var(etnames, colnames, domain=NonNegativeReals,bounds = (0, maxlen), initialize=0)

# Create Boolean variables
bigM = 10000
model2.y = Var(colnames, domain= Boolean)
model2.z = Var(etnames, colnames, domain= Boolean)


# Minimizing the sum of difference between the E&T Ticket values and rows 
model2.minimizer = Objective(expr= sum(et_val[r] - model2.vals[r, c]
                                      for r in etnames for c in colnames),
                             sense=minimize)

model2.reelconstraint = ConstraintList()
for c in colnames:
    model2.reelconstraint.add(sum(model2.vals[r,c] for r in etnames) <= bigM * model2.y[c])
    

# Set constraints for row sum equal to ET values
model2.rowconstraint = ConstraintList()
for r in etnames:
    model2.rowconstraint.add(sum(model2.vals[r, c] for c in colnames) <= et_val[r])

    
# Set contraints for upper bound of column sums
model2.colconstraint_upper = ConstraintList()
for c in colnames:
    model2.colconstraint_upper.add(sum(model2.vals[r, c] for r in etnames) <= maxlen)
    

# Set contraints for lower bound of column sums
model2.colconstraint_lower = ConstraintList()
for c in colnames:
    model2.colconstraint_lower.add(sum(model2.vals[r, c] for r in etnames) + bigM * (1-model2.y[c]) >= minlen)
    

model2.bool = ConstraintList()
for c in colnames:
    for r in etnames:
        model2.bool.add(model2.vals[r,c] <= bigM * model2.z[r,c])
    

model2.npienceconstraint = ConstraintList()
for c in colnames:
    model2.npienceconstraint.add(sum(model2.z[r, c] for r in etnames) <= npiece)

# Call solver for model
solver.solve(model2);

# Create dataframe of output
pdtest = pd.DataFrame([[model2.vals[r, c].value for c in colnames] for r in etnames],
                        index=etnames,
                        columns=colnames)

pdtest

Output

Output

5
  • This looks like an NP-complete problem to me. If you already know which near-diagonal elements are nonzero, it's linear system of equations (np.linalg.lstsq for column sums 395), but you'd have to iterate over combinations. You have 17 equations (12 hard and 7 soft) with 10 unknowns, but you may pick the unknowns, so you have a large, countable number of systems of equations and you may pick the one that satisfies the conditions. Generally, that is not possible unless you are lucky. Commented Jul 3, 2020 at 6:18
  • Hey, Han thanks for the reply. Do you have any "Brute Force" method in your mind to solve this problem? Commented Jul 3, 2020 at 9:11
  • Define which elements are can be considered to be nonzero, e.g. 4 for each column, making for 28 elements. Iterate over all integers from 0 to 2**28-1. Skip the ones that do not satisfy the requirements on number of nonzero elements. Use np.linalg.lstsq for the other ones. Discard the solutions that do not meet other requirements. But brute-forcing over 2**28 combinations does not look like a good idea. Better look into simulated annealing for this class of problems. Commented Jul 3, 2020 at 9:33
  • Han, can you give me a piece of code that uses np.linalg.lstsq func. that will definitely help me to headstart using that approach. Commented Jul 3, 2020 at 12:35
  • Can you be more specific by what constraint 5 means? I see in the code comment that you want to enforce some integrity to the block of values in a column, but it doesn't say much about relationship to diagonal? Commented Aug 22, 2020 at 23:43

2 Answers 2

1

I think you were on the right track with setting this up as an LP. It can be formulated as a MIP.

I haven't tinkered with any variety of inputs here, and I'm not sure you are guaranteed feasible results for all inputs with the constraints you have.

I penalized off-diagonal selection to encourage things on diagonal, and set up some "selection integrality" constraints to enforce block-selection.

Solves in about 1/10 of second...

# magic matrix

# Constraints
# 1) Column sum, CS, is: 300 <= CS <= 390
# 2) Row sum, RS, is equal to user-specified values, which are present in the E&T ticket column of the file
# 3) Number of non-zero values, NZV, in each column, should be: 0 < NZV <= 4
# 4) The NZV in the matrix should be: NZV >= 10
# 5) The pieces are stacked on top of each other. So, a the cell under a non-zero value cell is zero, than all cells underneath should have zeros.

import pyomo.environ as pyo

# user input
row_tots = [427.7, 12.2, 352.7, 58.3, 22.7, 31.9, 396.4, 29.4, 171.5, 474.5, 27.9, 200]
min_col_sum = 300
max_col_sum = 390
max_non_zero = 4
min_size = 10
bigM = max(row_tots)

m = pyo.ConcreteModel()

# SETS
m.I = pyo.Set(initialize=range(len(row_tots)))
m.I_not_first = pyo.Set(within=m.I, initialize=range(1, len(row_tots)))
m.J = pyo.Set(initialize=range(int(sum(row_tots)/min_col_sum)))

# PARAMS
m.row_tots = pyo.Param(m.I, initialize={k:v for k,v in enumerate(row_tots)})

# set up weights (penalties) based on distance from diagonal line
# between corners using indices as points and using distance-to-line formula
weights = { (i, j) : abs((len(m.I)-1)/(len(m.J)-1)*j - i) for i in m.I for j in m.J}
m.weight  = pyo.Param(m.I * m.J, initialize=weights)

# VARS
m.X = pyo.Var(m.I, m.J, domain=pyo.NonNegativeReals)
m.Y = pyo.Var(m.I, m.J, domain=pyo.Binary)          # selection indicator
m.UT = pyo.Var(m.I, m.J, domain=pyo.Binary)         # upper triangle of non-selects

# C1: col min sum
def col_sum_min(m, j):
    return sum(m.X[i, j] for i in m.I) >= min_col_sum
m.C1 = pyo.Constraint(m.J, rule=col_sum_min)

# C2: col max sum
def col_sum_max(m, j):
    return sum(m.X[i, j] for i in m.I) <= max_col_sum
m.C2 = pyo.Constraint(m.J, rule=col_sum_max)

# C3: row sum 
def row_sum(m, i):
    return sum(m.X[i, j] for j in m.J) == m.row_tots[i]
m.C3 = pyo.Constraint(m.I, rule=row_sum)

# C4: max nonzeros
def max_nz(m, j):
    return sum(m.Y[i, j] for i in m.I) <= max_non_zero
m.C4 = pyo.Constraint(m.J, rule=max_nz)


# selection variable enforcement
def selection_low(m, i, j):
    return min_size*m.Y[i, j] <= m.X[i, j]
m.C10 = pyo.Constraint(m.I, m.J, rule=selection_low)
def selection_high(m, i, j):
    return m.X[i, j] <= bigM*m.Y[i, j]
m.C11 = pyo.Constraint(m.I, m.J, rule=selection_high)

# continuously select blocks in columns.  Use markers for "upper triangle" to omit them

# a square may be selected if previous was, or if previous is in upper triangle
def continuous_selection(m, i, j):
    return m.Y[i, j] <= m.Y[i-1, j] + m.UT[i-1, j]
m.C13 = pyo.Constraint(m.I_not_first, m.J, rule=continuous_selection)
# enforce row-continuity in upper triangle
def upper_triangle_continuous_selection(m, i, j):
    return m.UT[i, j] <= m.UT[i-1, j]
m.C14 = pyo.Constraint(m.I_not_first, m.J, rule=upper_triangle_continuous_selection)
# enforce either-or for selection or membership in upper triangle
def either(m, i, j):
    return m.UT[i, j] + m.Y[i, j] <= 1
m.C15 = pyo.Constraint(m.I, m.J, rule=either)

# OBJ:  Minimze number of selected cells, penalize for off-diagonal selection
def objective(m):
    return sum(m.Y[i, j]*m.weight[i, j] for i in m.I for j in m.J)
#   return sum(sum(m.X[i,j] for j in m.J) - m.row_tots[i] for i in m.I) #+\
#           sum(m.Y[i,j]*m.weight[i,j] for i in m.I for j in m.J)
m.OBJ = pyo.Objective(rule=objective)
    

solver = pyo.SolverFactory('cbc')
results = solver.solve(m)

print(results)
for i in m.I:
    for j in m.J:
        print(f'{m.X[i,j].value : 3.1f}', end='\t')
    print()
print('\npenalty matrix check...')
for i in m.I:
    for j in m.J:
        print(f'{m.weight[i,j] : 3.1f}', end='\t')
    print()

Result

 300.0   127.7   0.0     0.0     0.0     0.0     0.0    
 0.0     12.2    0.0     0.0     0.0     0.0     0.0    
 0.0     165.6   187.1   0.0     0.0     0.0     0.0    
 0.0     0.0     58.3    0.0     0.0     0.0     0.0    
 0.0     0.0     22.7    0.0     0.0     0.0     0.0    
 0.0     0.0     31.9    0.0     0.0     0.0     0.0    
 0.0     0.0     0.0     300.0   96.4    0.0     0.0    
 0.0     0.0     0.0     0.0     29.4    0.0     0.0    
 0.0     0.0     0.0     0.0     171.5   0.0     0.0    
 0.0     0.0     0.0     0.0     10.0    390.0   74.5   
 0.0     0.0     0.0     0.0     0.0     0.0     27.9   
 0.0     0.0     0.0     0.0     0.0     0.0     200.0
Sign up to request clarification or add additional context in comments.

3 Comments

Amazing work. Thanks a lot for your efforts. I really liked this approach. however, I tried with shuffle values row_tots = [29.4, 58.3, 27.9, 427.7, 352.7, 31.9, 369.4, 474.5, 12.2, 22.7, 180.9, 171.5] this has produced almost desirable output except value in first column.
Yeah, I think there are parasitic cases like above where you give it many rows of low values in a row, then it struggles to make the column minimum by distributing the difference. Try tinkering with the penalty weights. I get decent results from above by changing it to: weights = { (i, j) : abs((len(m.I)-1)/(len(m.J)-1)*j - i)**3 for i in m.I for j in m.J} which makes the off-diagonal penalties exponentially higher.
@MananGajjar If this answered your question on how to approach this, you could close out the question by accepting the answer.
0

If you already know which near-diagonal elements are nonzero, it's linear system of equations (for the column sums 345 and the specified row sums), but you'd have to iterate over combinations. You have 19 equations with 10 unknowns (the number of nonzero items), which is not generally solvable. It gets a bit easier because you are allowed to pick the 10 unknowns helps and that 7 of the equations only need to be satisfied approximately, but I think as solution only exists if you're lucky (or it is an exercise that is desiged to have a solution).

Given that each of the 12 rows must have a correct sum, you'll need at least 12 nonzero elements. Most likely, you'll need at least two per row and at least two per column.

Finding the optimal set that has a solution is probably an NP-complete problem, which means that you have to systematically iterate over all combinations until you hit a solution.

For your example case, there are about m=31 matrix elements; iterating over all combinations is not possible. You need trial and error.

Here is an example code for allowing all 31 elements to be optimized using a numpy's least-squares solver.

import numpy as np

rowsums = np.array([427.7, 12.2, 352.7, 58.3, 22.7, 31.9, 396.4, 29.4, 171.5, 474.5, 27.9, 200])
nrows = len(rowsums)
ncols = 7
colsum_target = 345 # fuzzy target
    
mask = np.array([
       [1, 1, 0, 0, 0, 0, 0],
       [1, 1, 0, 0, 0, 0, 0],
       [1, 1, 1, 0, 0, 0, 0],
       [0, 1, 1, 0, 0, 0, 0],
       [0, 1, 1, 1, 0, 0, 0],
       [0, 0, 1, 1, 1, 0, 0],
       [0, 0, 1, 1, 1, 0, 0],
       [0, 0, 0, 1, 1, 1, 0],
       [0, 0, 0, 1, 1, 1, 0],
       [0, 0, 0, 0, 1, 1, 1],
       [0, 0, 0, 0, 0, 1, 1],
       [0, 0, 0, 0, 0, 1, 1]]).astype(bool)
assert mask.shape == (nrows, ncols)

m = mask.sum() # number of elements to fit

# idx is the index matrix, referring to the element in the x-vector
idx = np.full(mask.shape, -1, dtype=int)
k = 0
for i in range(nrows):
    for j in range(ncols):
        if mask[i, j]:
            idx[i, j] = k
            k += 1
print(f'Index matrix:\n{idx}')

# We're going to solve A @ x = b, where x are the near-diagonal elements
# Shapes: A (nrows+ncols, m); b (nrows+ncols,); x: (m,)
# and b are the ocnditions on the row and column sums.
# Rows A[:nrows] represent the conditions on row sums.
# Rows A[-ncols:] represent the conditions on the column sums.
A = np.zeros((ncol + nrow, m))
for i in range(nrows):
    for j in range(ncols):
        if mask[i, j]:
            A[i, idx[i, j]] = 1
            A[nrows+j, idx[i, j]] = 1
            
b = np.concatenate((rowsums, np.full(ncols, colsum_target, dtype=np.float64)))

# Force priority on row sums (>>1 to match row sums, <<1 to match column sums)
priority = 1000
A[:nrows, :] *= priority
b[:nrows] *= priority

# Get the solution vector x
x, _, _, _ = np.linalg.lstsq(A, b, rcond=None)

# map the elements of x into the matrix template
mat = np.concatenate((x, [0]))[idx] # extra [0] is for the -1 indices
round_mat = np.around(mat, 1)

row_sum_errors = np.around(mat.sum(axis=1)-rowsums, 6)
col_sums = np.around(mat.sum(axis=0), 2)

print(f'mat:\n{round_mat}\nrow_sums error:\n{row_sum_errors}')
print(f'column sums:\n{col_sums}')

This produces the output:

Index matrix:
[[ 0  1 -1 -1 -1 -1 -1]
 [ 2  3 -1 -1 -1 -1 -1]
 [ 4  5  6 -1 -1 -1 -1]
 [-1  7  8 -1 -1 -1 -1]
 [-1  9 10 11 -1 -1 -1]
 [-1 -1 12 13 14 -1 -1]
 [-1 -1 15 16 17 -1 -1]
 [-1 -1 -1 18 19 20 -1]
 [-1 -1 -1 21 22 23 -1]
 [-1 -1 -1 -1 24 25 26]
 [-1 -1 -1 -1 -1 27 28]
 [-1 -1 -1 -1 -1 29 30]]
mat:
[[210.8 216.9   0.    0.    0.    0.    0. ]
 [  3.1   9.1   0.    0.    0.    0.    0. ]
 [101.1 107.1 144.4   0.    0.    0.    0. ]
 [  0.   10.5  47.8   0.    0.    0.    0. ]
 [  0.  -28.6   8.7  42.6   0.    0.    0. ]
 [  0.    0.   -3.7  30.1   5.5   0.    0. ]
 [  0.    0.  117.8 151.6 127.    0.    0. ]
 [  0.    0.    0.   21.6  -3.   10.8   0. ]
 [  0.    0.    0.   69.   44.3  58.2   0. ]
 [  0.    0.    0.    0.  141.3 155.1 178.1]
 [  0.    0.    0.    0.    0.    2.5  25.4]
 [  0.    0.    0.    0.    0.   88.5 111.5]]
row_sums error:
[-0. -0. -0. -0. -0. -0. -0. -0. -0. -0. -0. -0.]
column sums:
[315.03 315.03 315.03 315.03 315.03 315.03 315.03]

The least-squares solver cannot handle hard constraints; if you see that one column is just a bit out of bounds (for example 299), you could use the same priority trick to make the solver try a bit harder for that column. You could try to disable elements that are small (for example <10), one by one. You could also try to use a linear programming optimizer, which is more suitable for a problem with both hard equality requirements and boundaries.

1 Comment

Thanks so much for the descriptive answer, this definitely helped me understand the underlying logic. This is near perfect solution however I have one more hard constraint Number 3 in the list that 'Non zero values should not be less than 10'. any workaround in your mind to tackle that?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.