Python中的Excel求解器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4634317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Solver in Python
提问by haha
I'm trying to implement something like this
我正在尝试实现这样的东西
http://office.microsoft.com/en-us/excel-help/using-solver-to-rate-sports-teams-HA001124601.aspx
http://office.microsoft.com/en-us/excel-help/using-solver-to-rate-sports-teams-HA001124601.aspx
in python with python libraries only (not calling Excel solver).
在仅使用 python 库的 python 中（不调用 Excel 求解器）。
Can someone point me to the right libraries to be using + some dive-in tutorials to get started ?
有人可以指出我要使用的正确库 + 一些入门教程吗？
回答by Hugh Bothwell
You are looking for NumPy (matrix manipulation and number-crunching) and SciPy (optimization). To get started, see https://stackoverflow.com/questions/4375094/numpy-learning-resources
您正在寻找 NumPy（矩阵操作和数字运算）和 SciPy（优化）。要开始，请参阅https://stackoverflow.com/questions/4375094/numpy-learning-resources
I worked out the given example as follows:
我制定了给定的例子如下：
- I opened the sample Excel files in OpenOffice
- I copied the team-data (without headers) to a new sheet and saved as teams.csv
- I copied the game-data (without headers) to a new sheet and saved as games.csv
- 我在 OpenOffice 中打开了示例 Excel 文件
- 我将团队数据（不带标题）复制到新工作表并保存为 team.csv
- 我将游戏数据（不带标题）复制到新工作表并保存为 games.csv
then in Python:
然后在 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)
results in
结果是
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
Hyman: -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
which is the same result shown in the spreadsheet.
这与电子表格中显示的结果相同。
回答by ars
This page lists a number of solver libraries for Python which you might use:
此页面列出了一些您可能会使用的 Python 求解器库：
回答by Jeff Bauer
回答by Axel
PuLPis a linear programming modeller in python. It can do everything that the excel solver can do.
PuLP是 Python 中的线性规划建模器。它可以做 excel 求解器可以做的一切。
PuLP is a free open source software written in Python. It is used to describe optimisation problems as mathematical models. PuLP can then call any of numerous external LP solvers (CBC, GLPK, CPLEX, Gurobi etc) to solve this model and then use python commands to manipulate and display the solution.
PuLP 是一个用 Python 编写的免费开源软件。它用于将优化问题描述为数学模型。然后，PuLP 可以调用众多外部 LP 求解器（CBC、GLPK、CPLEX、Gurobi 等）中的任何一个来求解该模型，然后使用 python 命令来操作和显示解决方案。
There is a detailed introduction about PuLPand a manual on how to model optimization problems with PuLP in python.
有关于 PuLP的详细介绍和关于如何在 python 中使用 PuLP 对优化问题建模的手册。
Modelling example
建模示例
# 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)
