Python中的Excel求解器

Question

我想在Python中实现类似这样的功能：

http://office.microsoft.com/en-us/excel-help/using-solver-to-rate-sports-teams-HA001124601.aspx

但是我不想使用Excel的求解器，只想用Python的库来完成。

有没有人能告诉我应该使用哪些库，以及一些入门教程，让我可以开始学习？

Answer 1

PuLP 是一个用 Python 编写的线性规划建模工具。它能做的事情和 Excel 的求解器一样多。

PuLP 是一个免费的开源软件，使用 Python 编写。它用于将优化问题描述为数学模型。然后，PuLP 可以调用许多外部的线性规划求解器（比如 CBC、GLPK、CPLEX、Gurobi 等）来解决这个模型，并使用 Python 命令来处理和展示解决方案。

这里有一个关于 PuLP 的 详细介绍，以及如何在 Python 中使用 PuLP 建模优化问题的手册。

建模示例

# Import PuLP modeler functions
from pulp import *

# Create the 'prob' variable to contain the problem data
prob = LpProblem("Example_Problem", LpMinimize)

# Declare decision variables
var_x = LpVariable(name="x", lowBound=0, cat="Continuous")
var_y = LpVariable(name="y", cat="Integer")

# The objective function is added to 'prob' first
prob += var_x + 2 * var_y

# The constraints are added to 'prob'
prob += var_x == (-1) * var_y
prob += var_x <= 15
prob += var_x > 0

# The problem is solved using PuLP's choice of Solver
prob.solve()

# The status of the solution is printed to the screen
print("Status:", LpStatus[prob.status])

# Each of the variables is printed with it's resolved optimum value
for v in prob.variables():
    print(v.name, "=", v.varValue)

Answer 2

这个页面列出了一些你可以在Python中使用的求解器库：

• Python求解器

Answer 3

你在寻找NumPy（用于处理矩阵和进行数字运算）和SciPy（用于优化）。

如果你想入门，可以看看这个链接：https://stackoverflow.com/questions/4375094/numpy-learning-resources

我按照给出的例子做了以下步骤：

• 我在OpenOffice中打开了示例Excel文件
• 我把团队数据（不包括标题）复制到一个新表格中，并保存为teams.csv
• 我把比赛数据（不包括标题）复制到另一个新表格中，并保存为games.csv

然后在Python中：

import csv
import numpy
import scipy.optimize

def readCsvFile(fname):
    with open(fname, 'r') as inf:
        return list(csv.reader(inf))

# Get team data
team = readCsvFile('teams.csv')  # list of num,name
numTeams = len(team)

# Get game data
game = readCsvFile('games.csv')  # list of game,home,away,homescore,awayscore
numGames = len(game)

# Now, we have the NFL teams for 2002 and data on all games played.
# From this, we wish to forecast the score of future games.
# We are going to assume that each team has an inherent performance-factor,
# and that there is a bonus for home-field advantage; then the
# relative final score between a home team and an away team can be
# calculated as (home advantage) + (home team factor) - (away team factor)

# First we create a matrix M which will hold the data on
# who played whom in each game and who had home-field advantage.
m_rows = numTeams + 1
m_cols = numGames
M = numpy.zeros( (m_rows, m_cols) )

# Then we create a vector S which will hold the final
# relative scores for each game.
s_cols = numGames
S = numpy.zeros(s_cols)

# Loading M and S with game data
for col,gamedata in enumerate(game):
    gameNum,home,away,homescore,awayscore = gamedata
    # In the csv data, teams are numbered starting at 1
    # So we let home-team advantage be 'team 0' in our matrix
    M[0, col]         =  1.0   # home team advantage
    M[int(home), col] =  1.0
    M[int(away), col] = -1.0
    S[col]            = int(homescore) - int(awayscore)


# Now, if our theoretical model is correct, we should be able
# to find a performance-factor vector W such that W*M == S
#
# In the real world, we will never find a perfect match,
# so what we are looking for instead is W which results in S'
# such that the least-mean-squares difference between S and S'
# is minimized.

# Initial guess at team weightings:
# 2.0 points home-team advantage, and all teams equally strong
init_W = numpy.array([2.0]+[0.0]*numTeams)  

def errorfn(w,m,s):
    return w.dot(m) - s

W = scipy.optimize.leastsq(errorfn, init_W, args=(M,S))

homeAdvantage = W[0][0]   # 2.2460937500005356
teamStrength = W[0][1:]   # numpy.array([-151.31111318, -136.36319652, ... ])

# Team strengths have meaning only by linear comparison;
# we can add or subtract any constant to all of them without
# changing the meaning.
# To make them easier to understand, we want to shift them
# such that the average is 0.0
teamStrength -= teamStrength.mean()

for t,s in zip(team,teamStrength):
    print "{0:>10}: {1: .7}".format(t[1],s)

得到的结果是

       Ari: -9.8897569
       Atl:  5.0581597
      Balt: -2.1178819
      Buff: -0.27413194
  Carolina: -3.2720486
      Chic: -5.2654514
      Cinn: -10.503646
      Clev:  1.2338542
      Dall: -8.4779514
       Den:  4.8901042
       Det: -9.1727431
        GB:  3.5800347
      Hous: -9.4390625
      Indy:  1.1689236
      Jack: -0.2015625
        KC:  6.1112847
     Miami:  6.0588542
      Minn: -3.0092014
        NE:  4.0262153
        NO:  2.4251736
       NYG:  0.82725694
       NYJ:  3.1689236
       Oak:  10.635243
      Phil:  8.2987847
      Pitt:  2.6994792
 St. Louis: -3.3352431
 San Diego: -0.72065972
        SF:  0.63524306
   Seattle: -1.2512153
     Tampa:  8.8019097
      Tenn:  1.7640625
      Wash: -4.4529514

这个结果和电子表格中显示的是一样的。