找到最小营业额账户

Question

我的客户在Excel中有许多不同的经纪账户。对于每个账户，它都有账户名称、账户类型、股票名称和每个股票的分配。每个账户的股票数量各不相同。文件如下所示：

Account Name  Account Type   Stock Name  Stock Allocation     
MN001         #1             ABC         40%    
MN001         #1             ABD         60%

MN002         #2             ABC         50%    
MN002         #2             ABD         40%    
MN002         #2             EFG         10%    

MN003         #3             ABC         20%    
MN003         #3             ABD         40%    
MN003         #3             EFG         40%    

MN004         #4             ABC         30%    
MN004         #4             ABD         70%    

我们要从帐户类型#1（MN001）移到其他帐户类型（MN002、MN003或MN004）。标准是找到营业额最低的账户。例如，MN001和MN002之间的营业额为ABC（0.1）+ABD（0.2）+EFG（0.1）=0.4。MN001和MN003之间的营业额为ABC（0.2）+ABD（0.2）+EFG（0.4）=0.8。MN001和MN004之间的营业额为ABC（0.1）+ABD（0.1）=0.2。MN004的营业额最低。你知道吗

因此，期望的输出：

From Account/Account Type    To Account/Account Type    Minimum TurnOver
MN001        #1              MN004      #4              0.2 

MN002        #2              MN001      #1              0.4

用Excel做这件事似乎很难。可以用Python实现吗？非常感谢你的帮助！你知道吗

Answer 1

是的，它可以在Python中完成，它可以看起来像这样。你知道吗

accounts = [{'ABC':0.4, 'ABD':0.6}, {'ABC':0.5, 'ABD':0.4, 'EFG':0.1}]

这是您的帐户，MN001是accounts[0]，MN002是accounts[1]，依此类推。。你知道吗

def compute_turnover(first_account, second_account):
    turnover = 0

    for stock_name, stock_allocation in first_account.items():
        other_allocation = second_account.get(stock_name) 
        if other_allocation is not None:
            turnover += abs(stock_allocation - second_account[stock_name])
        else:
            turnover += stock_allocation

    for stock_name, stock_allocation in second_account.items():
        other_allocation = first_account.get(stock_name) 
        if other_allocation is None:
            turnover += stock_allocation

    return turnover

如上所述定义一个compute\u周转函数，用MN001和MN002调用它，得到预期的结果

>>> compute_turnover(accounts[0], accounts[1])
0.4

现在让我们假设您有一个list1和一个list2和目标帐户。您只需要在python字典中转换这两个列表，然后按如下所示进行迭代

list1 = [{'ABC':0.4, 'ABD':0.6}, {'ABC':0.5, 'ABD':0.4, 'EFG':0.1}]
list2 = [{'ABC':0.4, 'ABD':0.6}, {'ABC':0.5, 'ABD':0.4, 'EFG':0.1}]

turnover_accounts = []
for account in list1:
    min_turnover = 1e9
    turnover_account = None

    for target_account in list2:
        if compute_turnover(account, target_account) < min_turnover:
            min_turnover = compute_turnover(account, target_account)
            turnover_account = target_account

    turnover_accounts.append(turnover_account)

您的turnover_accounts列表将包含营业额最低的目标客户的索引。你知道吗