数据分析——迭代法求解方程

本例展示了如何利用迭代法求解方程，y值存储在excel中，求解的x值和loss值也存储在excel中，代码如下：

import math
import xlrd
import openpyxl
import warnings

warnings.filterwarnings('ignore')

# 表达式系数
a=350
b=370
c=-3/7
# 预测值与真实值差值阈值
loss_thres = 1
# 迭代次数阈值
epoch_thres = 990

results = []
def fun(x, y):
	global x_min, x_max, epoch
	# 表达式
	y_cal = a/math.sqrt(x) + b*math.exp(c*x)
	# 预测值与真实值差值
	loss = y_cal - y
	# 迭代条件
	if abs(loss) > loss_thres and epoch < epoch_thres:
		epoch += 1
		if loss > 0:
			x_min = x
		else:
			x_max = x
		x = (x_min+x_max)/2
		fun(x, y)
	else:
		results.append([x, loss])
		print(x,loss)

book = xlrd.open_workbook('test.xlsx')
sheet = book.sheet_by_name('Sheet1')
nrows = sheet.nrows
for i in range(nrows):
	# 二分法上限与下限
	x_min = 1
	x_max = 100
	x = (x_min+x_max)/2
	y = sheet.cell(i, 0).value
	epoch = 0
	fun(x, y)

book = openpyxl.load_workbook('test.xlsx')
sheet = book.get_sheet_by_name('Sheet1')
for i in range(len(results)):
	sheet.cell(i+1, 2).value = results[i][0]
	sheet.cell(i+1, 3).value = results[i][1]
book.save('test.xlsx')