1 year ago

#355298

test-img

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: enter image description here

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

Accepted video resources