将子项目分配给 pandas 中的主要项目 [英] Allocating the Sub items to the main Items in pandas
问题描述
我正在寻找熊猫/python解决方案来根据发票的主要代码对发票中的项目进行汇总/分组. 请参考第一张所附图片
I'm looking for pandas/python solution to summarize/group items in an invoice, based on it's main code. please refer the first attached image
具有MainCode的每个商品价值(价格X数量)应添加到商品#total中.寻找类似的颜色. (请注意,有些项目可能会重复.例如:绿色和蓝色)不应添加数量.仅值.
each item value (price X qty) which has a MainCode should be added to the the item # total. Look for the Similar color. (Please Note some items may be repeated. eg : Green & Blue ) Qty SHOULD NOT be added. only the Values.
答案应如下
我是Pandas& amp;的新手由于这是一个高级问题,因此我无法提供任何代码.因此,张贴在这里.请指示我解决这个问题...
I'm New to Pandas & Since This is a bit Advanced problem, I couldn't come up with any code. hence, posted here. please direct me to solve this...
注意事项
- 项目组合可以重复.例如,请参见Green&蓝色
- 一旦完成子项目应被删除(例如,项目371被删除).
- 总价格应等于单个商品的价格X数量的总和
- 在此示例中,所有子项目代码均为371.但可能有多个代码.例如371、58等
- 子项目371可以不带MainCode的方式单独出售.在这种情况下,不应该分配&它应该保留.
更新的数据
推荐答案
希望您喜欢它.下次,请不要以图像形式而是以文本形式提供输入数据.
Hope you enjoy it. Next time please provide input data not as images but as text.
import pandas as pd
data_raw = [[260, 1500, 3, 0, 4500], [260, 1500, 1, 0, 1500], [258, 1500, 4, 0, 6000], [1054, 1200, 1, 0, 1200],
[371, 700, 3, 260, 2100], [371, 700, 1, 260, 700], [371, 700, 1, 1054, 700], [371, 700, 4, 258, 2800]]
data = pd.DataFrame(data_raw, columns=['item', 'price', 'qty','Main code','Total'])
remove_index= []
for index, row in data.iterrows():
try:
# find item in Main code
main_code_data = data.loc[data['Main code'] == row['item']]
# merge values
data.at[index, 'Total'] = row['Total'] + row['qty']* (main_code_data['Total'].values[0]/main_code_data['qty'].values[0])
# get indexes to remove
for item in main_code_data.index:
remove_index.append(item)
except:
# if no match
pass
# remove used lines
data = data.drop(remove_index)
输出:
item price qty Main code Total
0 260 1500 3 0 6600
1 260 1500 1 0 2200
2 258 1500 4 0 8800
3 1054 1200 1 0 1900
这篇关于将子项目分配给 pandas 中的主要项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!