[FIXED] Solving formula across multiple rows (similar to excel) in python?

Issue

I'm looking for some kind of functionality similar to excels solver in python. In python I have a function which when supplied an array of length N, returns an array of N also using some columns from a dataframe.

Below is a simple example of that I have, and what the target is.

import pandas as pd

df = pd.DataFrame(
        {'Column_1' : [1,2,3,4,5],
         'Column_2' : [5,4,3,2,1],
         'Column_3' : [10,8,6,4,2]
         })

def funs(x):
    
    return(x * df['Column_1'] * df['Column_2'] * df['Column_3'])

funs(x = [1,1,1,1,1])
Out[]: 
0    50
1    64
2    54
3    32
4    10
dtype: int64

From here I am looking for a function/method that I can supply 'funs' to and a target array. The function hopefully will generate the x such that funs(x) = target.

target = [5,10,15,10,5]

y = solve_func(funs(x), target) 

funs(y) == [5,10,15,10,5]

An easier approach in this case would be to define the outcome such that x = target/(col_1 * col_2 * col_3), but a solution like this isn't as trivial in the real example, hence why I wonder if something similar to how excel solver would work exists.

Hope this makes sense and I really appreciate any help.

Solution

The function scipy.optimize.fsolve finds zeros of functions, which can be used in your case as follows:

from scipy.optimize import fsolve

target = [5, 10, 15, 10, 5]

def residuals(x):
    """fsolve will try to make its target equal to all zeros"""
    return funs(x) - target

# Just like with Solver, you need an initial guess
initial_guess = [1, 2, 3, 4, 5]
sol = fsolve(residuals, initial_guess)

This results in sol = array([0.1, 0.15625, 0.27777778, 0.3125, 0.5]).

Answered By - chthonicdaemon