How to extract values from excel worksheets to get desired calculation using python

So I have 3 worksheets in excel, let's call them sheet_a, sheet_b and sheet_c.

sheet_a:
     type       formula
0  type_a           A+B
1  type_b             A
2  type_c       A/(A+B)
3  type_d           A/B


sheet_b:
     dish ingredient    map
0  type_a       fish     B
1  type_a     potato     A
2  type_b      bread     A
3  type_c  chocolate     B
4  type_c     carrot     A
5  type_d     potato     A
6  type_d     orange     B

sheet_c:
  ingredient  cost
0       fish   1
1      bread   3
2     carrot   2
3     potato   6
4     orange   2

What I'm finding tricky is trying to get the cost values, given the cost forumla in sheet_a, mapping values in sheet_b and then cost values in sheet_c.

So for example to get the cost value for type_a:

type_a:

>>> A+B
>>> potato + fish
>>> 6 + 1
7

What I want is an output list of values in the same order as they appear in sheet_a; type_a, type_b, type_c and type_d.

Expected output:

[7, 3, NaN, 3] - NaN because chocolate has no value in sheet_c.

So far I haven't been able to get the desired output I've managed to get up to a stage where I have a dictionary with keys as the type and values as a list of A and B combinations

You can use a combination of merge, pivot, and eval:

out = (sheet_a
       .merge(sheet_b.merge(sheet_c, on='ingredient')
                     .pivot(index='dish', columns='map', values='cost'),
              left_on='type', right_index=True)
       .groupby('formula', as_index=False, group_keys=False)
       .apply(lambda g: g.eval(f"out = {g.name}"))
       #[list(sheet_a)+['out']] # uncomment to remove intermediates
      )

Output:

     type  formula    A    B  out
0  type_a      A+B  6.0  1.0  7.0
1  type_b        A  3.0  NaN  3.0
2  type_c  A/(A+B)  2.0  NaN  NaN
3  type_d      A/B  6.0  2.0  3.0