矩阵的每小时和年优化问题

Question

给定一个像下面这样的矩阵：

                                    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

我有一个线性优化问题，需要把A列中的数分配给不同的用户，目标是分配后尽量少剩下的部分，并且每个用户得到的不能超过他们能接受的数量（也就是他们各自那一列的数）。

我们有以下的目标函数、约束条件和界限：

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)

我已经创建了一个程序，可以逐行逐小时地解决这个问题，所以我总共运行了8,760个优化问题，得到了每个用户每小时的beta值。这是我问题的最佳解决方案，能够优化分配并最小化剩余。

不过，我还需要解决一个年度优化的问题。也就是说，我需要为每个用户得到一个独特的beta值，这个值能使得一整年的结果最小化；这是用Excel Solver能做到的最好结果，我需要把我的解决方案和这个结果进行比较，以便展示在每次分析中我的解决方案更优（如果有3个用户，我就会有3个beta）。

我得到了以下的beta值，目标是最小化[(sum(User1)-beta1*sum(A))+(sum(User2)-beta2*sum(A))+…+(sum(User9)-beta9*sum(A))]，但是用Solver在矩阵和向量上的解决方案比在总和上的更优。

我将我的尝试分享出来，基于每小时优化的问题。

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()

这是我用来模拟24小时的DF：

{'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}}