python - 如何进行数据帧减法？

Question

我有以下数据框： Col1 是付款。

Col1     Value
Item1    100
Item2    200
Item3    300

Col2 是项目成本

Col2        Value
Project1    200
Project2    300
Project3    400

我基本上想将 Col1 与 Col2 中的项目相匹配。

如何获得以下数据框？

Col2         Value    Col1 
Project1     100      Item1
Project1     100      Item2
Project2     100      Item2
Project2     200      Item3
Project3     100      Item3
Project3     300    

我可以在熊猫中做些什么来获得这个结果吗？非常感谢

标签： pythonpandasdataframe

Answer 1

在这里，您可以找到逻辑，添加注释以进行解释：

# making list of project requirement and item value to iterate
item_cost = list(zip(payment['Col1'], payment['value']))
requirment = list(zip(project_cost['Col2'], project_cost['value']))

d = {}
for project, cost in requirment:
    item_used = []
    try:
        while cost > 0: 
            if item_cost[0][1] <= cost: # if item is having less cost than requirement
                cost -= item_cost[0][1] # making requirement less by item cost
                item_used.append((item_cost[0][1], item_cost[0][0]))
                item_cost = item_cost[1:] # removing item from item_cost once used
            else: 
                item_cost[0] = ((item_cost[0][0], item_cost[0][1] - cost))
                item_used.append((cost, item_cost[0][0]))
                cost = 0
    except: # when item list will be finished exception will be called
        item_used.append((cost,np.nan))
    d[project] = item_used
# d is dict having project as key and item used
project_cost['Col1'] = project_cost['Col2'].map(d)          
pr_cost = project_cost.explode("Col1")            
pr_cost['value'] = pr_cost['Col1'].apply(lambda x:x[0])            
pr_cost['Col1'] = pr_cost['Col1'].apply(lambda x:x[1])      

样本输出

根据您的新数据，我们只需将列名更改为上述代码：

item_cost = list(zip(payment['Payment'], payment['Value']))
requirment = list(zip(project_cost['Project'], project_cost['Cost']))
d = {}
for project, cost in requirment:
    item_used = []
    try:
        while cost > 0: 
            if item_cost[0][1] <= cost: # if item is having less cost than requirement
                cost -= item_cost[0][1] # making requirement less by item cost
                item_used.append((item_cost[0][1], item_cost[0][0]))
                item_cost = item_cost[1:] # removing item from item_cost once used
            else: 
                item_cost[0] = ((item_cost[0][0], item_cost[0][1] - cost))
                item_used.append((cost, item_cost[0][0]))
                cost = 0
    except: # when item list will be finished exception will be called
        item_used.append((cost,np.nan))
    d[project] = item_used
project_cost['bank_val'] = project_cost['Project'].map(d)          
pr_cost = project_cost.explode("bank_val")            
pr_cost['value'] = pr_cost['bank_val'].apply(lambda x:x[0])            
pr_cost['bank'] = pr_cost['bank_val'].apply(lambda x:x[1]) 
print(pr_cost.drop(columns = ['bank_val', 'Cost']))

输出：