1 year ago
#355298
JCV
Perform goal seek in pandas dataframe
I'm used to working with Excel and solving equations by changing cells values, I would run this macro in Excel to solve my problem:
Sub Qtn()
'Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For l = 1 To 30
If Cells(l, var_1).Value = "" Then
Cells(l, n_solver).Value = ""
GoTo outro
End If
Cells(l, dif).GoalSeek Goal:=0, ChangingCell:=Cells(l, n_solver)
outro:
Next l
'Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
But I'm moving to python because is better for plotting and analyzing larger datasets. The system of equations I would like to solve is:
Where, X1, X2, X3, a,b,c are columns from Pandas dataframe This is my data:
import numpy as np
import pandas as pd
b = np.linspace(300,400,30)
a = np.random.randint(700,18000,30)
c = np.random.uniform(1.4,4.0,30)
df = pd.DataFrame({'A':a, 'B':b, 'C':c})
and I need to create new columns ['var_1'],['var_2'],['var_3'],['n'],['dif'] that are dependent. In excel I would goal seek column ['diff'] to 0 by changing the values column ['n'], and the other columns would contain their functions and update automatically.
How can I do that using my functions below and the pandas dataframe (df) above?
def var_1(var_2,c):
y = ((3.47-np.log10(var_2))**2+(log10(c)+1.22)**2)**0.5
return y
def var_2(a,b,var_3):
y = (a/101.32) * (101.32/b)** var_3
return y
# n_calculate
def var_3(b,var_1):
y = 0.381 * var_1 + 0.05 * (b/101.32) -0.15
return y
def dif(var_3,n_solver):
y = var_3 - n_solver
return y
Edit
After some research, this is what I think would work, but the results are my initial guess, in Excel, first I give the first guess for z, then I replace this guess in the other 2 equations and evaluate the difference between the guess and the new value of z, and iterate until the difference between the old and new z is zero. I thought that fsolve would do the same, but I have the right results from excel, and the results from fsolve are wrong
import numpy as np
import pandas as pd
import scipy.optimize as opt
a = np.linspace(300,400,30)
b = np.random.randint(700,18000,30)
c = np.random.uniform(1.4,4.0,30)
df = pd.DataFrame({'A':a, 'B':b, 'C':c})
def func(zGuess,*Params):
x,y,z = zGuess
a,b,c = Params
eq_1 = (((3.47-np.log10(y))**2+(np.log10(c)+1.22)**2)**0.5) - x
eq_2 = ((a/101.32) * (101.32/b)** z) - y
eq_3 = (0.381 * x + 0.05 * (b/101.32) -0.15) - z
return eq_1,eq_2,eq_3
zGuess = np.array([2.6,20.2,0.92])
df['x'],df['y'],df['z'] = zip(*df.apply(lambda x: opt.fsolve(func,zGuess,args=(x['A'],x['B'],x['C'])),1) )
Thank you for your help!
python
dataframe
lambda
solver
scipy-optimize
0 Answers
Your Answer