英文:
How to extract values from excel worksheets to get desired calculation using python
问题
我有3个Excel工作表，我们称它们为sheet_a、sheet_b和sheet_c。
在sheet_a中:
type列formula列
在sheet_b中:
dish列ingredient列map列
在sheet_c中:
ingredient列cost列
我发现难以获得成本值，考虑到sheet_a中的成本公式，sheet_b中的映射值以及sheet_c中的成本值。
例如，要获取type_a的成本值:
type_a:
>>> A+B
>>> potato + fish
>>> 6 + 1
7
我想要的是一个值列表，与它们在sheet_a中出现的顺序相同; type_a、type_b、type_c和type_d。
预期输出:
[7, 3, NaN, 3] - NaN表示巧克力在sheet_c中没有值。
到目前为止，我还没有能够获得所需的输出，我已经到了一个阶段，其中我有一个字典，键是类型，值是A和B组合的列表。
英文:
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
答案1
得分: 1
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
)
输出：
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
(Note: I've provided the code in both English and Chinese, as requested. If you have any specific questions or need further assistance, feel free to ask.)
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论