Given a matrix like the following:
A User1 User2 … User9
2015-01-01 01:00:00 8,72 58 3 2
2015-01-01 02:00:00 24,46 57 4 3
2015-01-01 03:00:00 37,02 62 4 2
… 42,89 59 3 2
2015-12-31 23:00:00 46,80 59 4 2
I have a linear optimization problem where I have to distribute the amount in column A among a variable number of users, so that after the distribution I have as little left over as possible, and never give each user more than he can receive (more than what is in his column).
We have the following objective function, the following constraints, and the following bounds:
target_function = minimize[(User1 – beta1*A)+(User2-beta2*A)+…+(User9-beta9*A)]
constraint1 -> (User1 – beta1*A) >= 0
constraint2 -> User2 – beta2*A) >= 0
…
constraint9 -> (User9 – beta9*A) >= 0
beta1+beta2+…+beta9 = 1
beta1 = (0,1); beta2 = (0,1)…beta9=(0,1)
I have created a program that solves the above problem on an hourly, row-by-row basis, so I run 8,760 optimization problems, and get beta for each user and each hour. This is the optimal solution for my problem; the one that optimizes the allocation and minimizes the surplus.
However, I need to solve the same problem for an annual optimization. That is, I need to obtain a unique beta for each user for the whole year, which minimizes the annual; this is the best that could be done with Excel Solver, and I need to compare my solution with this one so that it can be seen that in each analysis my solution is more optimal (if I have 3 users, I would have 3 beta).
I have obtained the beta for minimize[(sum(User1)-beta1sum(A))+(sum(User2)-beta2sum(A))+…+(sum(User9)-beta9*sum(A)), but the solution with Solver on matrices and vectors is more optimal than on the total sum.
I pass my attempt, based on the problem solved for hourly optimization.
from datetime import
import numpy as np
import pandas as pd
import os
import scipy.optimize as opt
ruta = 'C://Users//F1K3G//Documents//Py//PRUEBAS DE OPTIMIZACIÓN//'
def f_objetivo(x):
funcion_objetivo = 0
for i in range(0, len(DF.columns)-2):
funcion_objetivo = funcion_objetivo + (DF[DF.columns[i]]-x(i-1)*DF[DF.columns[0]])
return funcion_objetivo
def g1(x): return ((DF[DF.columns[1]]-x(0)*DF[DF.columns[0]]))
def g2(x): return ((DF[DF.columns[2]]-x(1)*DF[DF.columns[0]]))
def g3(x): return ((DF[DF.columns[3]]-x(2)*DF[DF.columns[0]]))
def g4(x): return ((DF[DF.columns[4]]-x(2)*DF[DF.columns[0]]))
def g5(x): return ((DF[DF.columns[5]]-x(4)*DF[DF.columns[0]]))
def g6(x): return ((DF[DF.columns[6]]-x(5)*DF[DF.columns[0]]))
def g7(x): return ((DF[DF.columns[7]]-x(6)*DF[DF.columns[0]]))
def g8(x): return ((DF[DF.columns[8]]-x(7)*DF[DF.columns[0]]))
def g9(x): return ((DF[DF.columns[9]]-x(8)*DF[DF.columns[0]]))
def g51(x):
restriccion_betas = 0
for i in range(0, len(DF.columns)-2):
restriccion_betas = restriccion_betas + x[i]
restriccion_betas = restriccion_betas - 1
return restriccion_betas
def optimizar():
semilla = []
cons = list()
bnds = list()
for i in range(0, len(DF.columns)-2):
semilla.append(1/(len(DF.columns)-2))
for i in range(0, len(DF.columns)):
if i == (len(DF.columns)-2):
restriccion = {'type': 'eq', 'fun': g51}
else:
funcion = globals()["g"+str(i+1)]
restriccion = {'type': 'ineq', 'fun': funcion}
cons.append(restriccion)
cons = tuple(cons)
for i in range(0, len(DF.columns)-2):
bnds.append((0,1))
bnds = tuple(bnds)
result = opt.minimize(f_objetivo, semilla, bounds=bnds, constraints=cons)
print(result.message)
print(result.x)
print(f_objetivo(result.x))
return result.x
DF = pd.read_excel(
ruta + 'BBDD.xlsx',
sheet_name='CURVAS DE CONSUMO', skiprows=(0), usecols=range(0, 8), index_col=None,
keep_default_na=False, na_values=(""))
beta = optimizar()
Here my DF to simulate for 24 hours:
{'Unnamed: 0': {0: Timestamp('2015-01-01 01:00:00'), 1: Timestamp('2015-01-01 02:00:00'), 2: Timestamp('2015-01-01 03:00:00'), 3: Timestamp('2015-01-01 04:00:00'), 4: Timestamp('2015-01-01 05:00:00'), 5: Timestamp('2015-01-01 06:00:00'), 6: Timestamp('2015-01-01 07:00:00'), 7: Timestamp('2015-01-01 08:00:00'), 8: Timestamp('2015-01-01 09:00:00'), 9: Timestamp('2015-01-01 10:00:00'), 10: Timestamp('2015-01-01 11:00:00'), 11: Timestamp('2015-01-01 12:00:00'), 12: Timestamp('2015-01-01 13:00:00'), 13: Timestamp('2015-01-01 14:00:00'), 14: Timestamp('2015-01-01 15:00:00'), 15: Timestamp('2015-01-01 16:00:00'), 16: Timestamp('2015-01-01 17:00:00'), 17: Timestamp('2015-01-01 18:00:00'), 18: Timestamp('2015-01-01 19:00:00'), 19: Timestamp('2015-01-01 20:00:00'), 20: Timestamp('2015-01-01 21:00:00'), 21: Timestamp('2015-01-01 22:00:00'), 22: Timestamp('2015-01-01 23:00:00'), 23: Timestamp('2015-01-01 00:00:00')}, 'A': {0: 0.0, 1: 0.0, 2: 0.0, 3:
0.0, 4: 0.0, 5: 0.0, 6: 0.0, 7: 0.0, 8: 8.717808, 9: 24.463867999999998, 10: 37.023932, 11: 42.885964, 12: 46.796772, 13: 44.611467999999995, 14: 32.705155999999995, 15: 7.388567999999999, 16: 0.23240799999999995, 17: 0.0, 18: 0.0, 19: 0.0, 20: 0.0, 21: 0.0, 22: 0.0, 23: 0.0}, 'User 1': {0: 33.702759400187, 1: 33.702759400187, 2: 34.4354280827998, 3: 39.5641088610891, 4: 39.5641088610891, 5: 41.0294462263146, 6: 43.2274522741529, 7: 56.4154885611826, 8: 57.8808259264081, 9: 57.1481572437953, 10: 61.5441693394719, 11: 59.3461632916336, 12: 58.6134946090209, 13: 60.8115006568592, 14: 61.5441693394719, 15: 65.9401814351485, 16: 72.5341995786633, 17: 73.9995369438888, 18: 76.9302116743399, 19: 71.0688622134378, 20: 71.8015308960506, 21: 72.5341995786633, 22: 57.1481572437953, 23: 35.1680967654125}, 'User 2': {0: 4, 1: 4, 2: 3, 3: 4, 4: 4, 5: 3, 6: 4, 7: 4, 8: 3, 9: 4, 10: 4, 11: 3, 12: 4, 13: 3, 14: 4, 15: 4, 16: 3, 17: 4, 18: 4, 19: 3, 20: 4, 21: 4, 22: 3, 23: 4}, 'User 3': {0: 3, 1: 2, 2: 2, 3: 2, 4: 3, 5: 2, 6: 2, 7: 2, 8: 2, 9: 3, 10: 2, 11: 2, 12: 2, 13: 2, 14: 3, 15: 1, 16: 2, 17: 3, 18: 2, 19: 2, 20: 2, 21: 2, 22: 2, 23: 2}, 'User 4': {0: 0.018, 1: 0.017, 2: 0.018, 3: 0.018, 4:
0.018, 5: 0.017, 6: 0.018, 7: 0.018, 8: 0.018, 9: 0.017, 10: 0.018, 11: 0.017, 12: 0.382, 13: 0.478, 14: 0.017, 15: 0.018, 16: 0.017, 17:
0.017, 18: 0.207, 19: 0.22, 20: 0.221, 21: 0.221, 22: 0.222, 23: 0.222}, 'User 5': {0: 12.0, 1: 10.0, 2: 10.0, 3: 10.0, 4: 10.0, 5: 10.0, 6: 10.0, 7: 10.0, 8: 10.0, 9: 15.0, 10: 18.0, 11: 20.0, 12: 21.0, 13: 22.0, 14: 21.0, 15: 22.0, 16: 21.0, 17: 26.0, 18: 22.0, 19: 18.0, 20: 19.0, 21: 18.0, 22: 19.0, 23: 18.0}, 'User 6': {0: 4, 1: 3, 2: 4, 3: 3, 4: 3, 5: 4, 6: 4, 7: 4, 8: 3, 9: 3, 10: 3, 11: 3, 12: 2, 13: 3, 14: 3, 15: 3, 16: 3, 17: 2, 18: 4, 19: 5, 20: 4, 21: 4, 22: 4, 23: 4}}