如何用Python从多Excel工作表提取值并按指定公式计算

现有三个Excel工作表sheet_a、sheet_b、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

需要根据sheet_a中的成本公式、sheet_b的映射关系，提取sheet_c中的成本值完成计算，最终得到与sheet_a中type顺序一致的结果列表，预期输出为[7, 3, NaN, 3]（type_c因为chocolate在sheet_c中无对应成本值，结果为NaN）。

可以用Python的pandas库完成计算，步骤如下：

• 先将三个工作表的数据加载为DataFrame（假设已读取完成，以下为模拟数据）
• 合并sheet_b与sheet_c，关联原料与成本，得到每个dish对应的A/B成本值
• 遍历sheet_a的每一行，替换公式中的A、B为对应成本值，执行计算；若A或B存在缺失值，直接返回NaN

代码实现：

import pandas as pd
import numpy as np

# 模拟读取三个工作表的数据
sheet_a = pd.DataFrame({
    'type': ['type_a', 'type_b', 'type_c', 'type_d'],
    'formula': ['A+B', 'A', 'A/(A+B)', 'A/B']
})

sheet_b = pd.DataFrame({
    'dish': ['type_a', 'type_a', 'type_b', 'type_c', 'type_c', 'type_d', 'type_d'],
    'ingredient': ['fish', 'potato', 'bread', 'chocolate', 'carrot', 'potato', 'orange'],
    'map': ['B', 'A', 'A', 'B', 'A', 'A', 'B']
})

sheet_c = pd.DataFrame({
    'ingredient': ['fish', 'bread', 'carrot', 'potato', 'orange'],
    'cost': [1, 3, 2, 6, 2]
})

# 合并sheet_b和sheet_c，关联原料成本，保留缺失值
merged_data = pd.merge(sheet_b, sheet_c, on='ingredient', how='left')
# 转换为以dish为键，A/B成本为值的字典
cost_dict = merged_data.pivot(index='dish', columns='map', values='cost').to_dict('index')

# 计算每个type的结果
result_list = []
for _, row in sheet_a.iterrows():
    current_type = row['type']
    formula = row['formula']
    # 获取当前type对应的A、B成本
    ab_costs = cost_dict.get(current_type, {})
    a_cost = ab_costs.get('A', np.nan)
    b_cost = ab_costs.get('B', np.nan)
    
    # 若A或B成本缺失，直接添加NaN
    if pd.isna(a_cost) or pd.isna(b_cost):
        result_list.append(np.nan)
        continue
    
    # 替换公式中的A、B为实际成本，执行计算
    try:
        calculated_val = eval(formula.replace('A', str(a_cost)).replace('B', str(b_cost)))
        result_list.append(calculated_val)
    except:
        result_list.append(np.nan)

print(result_list)
# 输出: [7.0, 3.0, nan, 3.0]

内容的提问来源于stack exchange，提问作者pragmatic learner