我有一个账户经理ID的数据框架,以及他们分配的账户总数(df_am(:
account_manager_id account_total
0 1 8
1 2 3
2 3 3
3 4 1
4 5 7
5 6 2
我有第二个账户数据帧要分配给账户经理(df_poc(:
point_of contact account_no
0 John 100
1 Bob 78
2 Sally 125
3 Greg 128
4 Bret 78
5 Corey 100
6 Chad 100
7 Mavis 8
8 Andre 632
9 Hunter 157
10 Debra 12
我需要将帐户平均分配给一个帐户经理,请注意,account_no可以有多个相同的帐户,具有不同的point_of_contact,因此这些帐户需要分配给同一个account_manager_id。
为了做到这一点,我想检查df_poc中唯一的account_no,并将其分配给account_total中总数最低的客户经理,然后再次计算总数,然后转到下一个account_no。
例如,account_manager_id 4将获得第一个account_no,因为到目前为止他们只有一个帐户。由于是account_no 100,并且有3个poc,account_manager_id 4将获得全部3个,使其总数达到4。
这将使account_manager_id 6成为2上最低的,并且account_no 78将被分配给它们。
我们现在有3个客户经理,有3个账户(2、3、6(。我在这里没有偏好,所以我只将其分配给第一个account_manager。将account_manager_id 2设置为4,将3和6保留为3,依此类推。
我真的希望你能看到我正在努力实现的目标。如果你有更好的解决方案,请告诉我。
期望结果,df_am:
account_manager_id account_total
0 1 8
1 2 5
2 3 5
3 4 5
4 5 7
5 6 5
所需结果,df_poc:
point_of contact account_no account_manager_id
0 John 100 4
1 Bob 78 6
2 Sally 125 2
3 Greg 128 3
4 Bret 78 6
5 Corey 100 4
6 Chad 100 4
7 Mavis 8 2
8 Andre 632 3
9 Hunter 157 4
10 Debra 12 6
正如你所希望看到的,account_managers 8和5从未获得过一个账户,以便其他account_manager赶上他们的总数。
我一直在使用一个带有.min的循环(iterrows(来让客户经理进行分配,但是这种方法不会考虑多个account_no,并且会导致账户被拆分为多个account_manager。
lowest = df_am[df_am["account_total"] == df_am["account_total"].min()] #to get lowest total
lowest = lowest.iloc[:1] #keep first account manager if multiples on same total
谢谢你,任何帮助都很感激。
解决了这个问题,我创建了一个唯一account_no的新数据帧,并计算了该帐户的point_of_contact。
account_no counts
0 100 3
1 78 2
2 125 1
3 128 1
4 8 1
5 632 1
6 157 1
7 12 1
然后我对新的df(df_check(进行了迭代:
for i, row in df_check.iterrows(): #do not like using iterrow but see alternative
account = row["account_no"]
count = row["counts"]
lowest = df_am[df_am["account_total"] == df_am["account_total"].min()] #get the account manager will the fewest accounts
lowest = lowest.iloc[:1] #if more than one with fewest this ensure there is always one account manager
lowest["account_no"] = account #add the account number to assign to the account manager
new_count = lowest["account_total"] #takes the total accounts for the account
lowest = lowest.drop(["account_total"], axis=1) #drop from df as we no longer need this.
user = lowest["account_manager_id"].astype('int')
df_check.at[i, "id"] = user #assigns the account manager with the fewest accounts to the account
new_count = new_count + count #takes the account managers old total and adds the account count giving them a new total
df_am.at[user, "account_total"] = new_count #adds the new total to the account manager