0
$\begingroup$

I have a table that looks like this:

enter image description here

I would like to determine the values for each of the different categories in the columns, such that col1*col2*col3 equal what's on the prod column. I've been given constrains on the values each the unknowns can have, and also the table has more entries than shown, and also can have more unknowns.

For example, the following table shows one possible solution:

enter image description here

Hence A=6.77, B=1.93, foo=3.70, ... etc.

Would it be possible to find solutions to this or approximate ones? Or what would I need to make this problem solvable?

Thanks!

$\endgroup$
  • 1
    $\begingroup$ This is simple linear algebra. You have several equations, $A\times \text{foo}\times a=100.9,B\times\text{bar}\times b=78.2,$ etc. Taking logs, you get a system of linear equations in logs of the variables, which can be solved using Gaussian elimination. If no solutions exist, you can find an approximate solution using the pseudo-inverse. $\endgroup$ – Mike Earnest Mar 6 at 17:12
  • $\begingroup$ Thanks! I had thought similarly but then I ran into problems when having to get as many linearly independent equations as unknowns. Do you know how to do this? I thought of getting all possible combinations of col1*col2*col3 given the possible values of col1, col2 and col3, but when I check linear independence of all these combinations, I get less than the number of unknowns... any ideas? $\endgroup$ – tsando Mar 6 at 17:43
  • 2
    $\begingroup$ There is nothing wrong with having fewer equations than unknowns. It just means you may have multiple solutions. In that case, you do not have enough information to recover the variables uniquely. If any solution will work, then just pick one. $\endgroup$ – Mike Earnest Mar 6 at 17:45
  • $\begingroup$ So when you say 'just pick one' what do you mean? Would gaussian elimination or the pseudo-inverse you mention be able to provide at least one of these possible solutions? $\endgroup$ – tsando Mar 6 at 17:48
  • 1
    $\begingroup$ @tsando The pseudo-inverse will indeed give you a solution (in fact, it will give the shortest possible vector). Otherwise, you can use Gaussian elimination to get a full characterization of all solutions. For an undetermined system, this will involve parameters: something like $\{x=10+s-t,y=3+2t,z=0\mid s,t \in \Bbb R\}$ for example. $\endgroup$ – Théophile Mar 6 at 20:38
0
$\begingroup$

After much research and advice from the comments above, I managed to find a solution using numerical methods in python. Worth to highlight is that I could not use the pseudo-inverse method suggested above (a.k.a. Linear least squares) because the python implementations (scipy.sparse.linalg.lsqr and scipy.optimize.lsq_linear) do not allow for bounds on variables and constrains, so I had to use the more generic scipy.optimize.minimize implementation, which does allow constrains. This can be framed as an optimisation problem of finding the point on the constraint plane which minimizes the quantity ||Ax-b||^2. A good code example of this can be found here: https://stackoverflow.com/questions/31098228/solving-system-using-linalg-with-constraints

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.