python - 根据现有列中的值将随机值分配给数据框列

Question

我正在尝试使用员工列中的值填充数据框的“分配”列。我已将示例分配值粘贴到下表中。分配值不能等于该行的管理器列或从属列中的值。赋值也不应该重复。使用该示例，由于 Sue 和 Mary 已被分配，因此他们不应再可以进行分配。

我找到了这些说明 how to do something similar in excel以及这篇文章generating a value from a random number with exclusions ，但我对如何在 python 中执行中间步骤感到困惑。

+----------+---------+-------------+-------------------+----------------------------------------+
| Employee | Manager | Subordinate | Manager Exclusion | Subordinate Exclusion   |  Assignment  |
+----------+---------+-------------+-------------------+----------------------------------------+
| Jim      | Joe     |             |                 2 |                         |      Mary    |
| Joe      |         | Jim         |                   |                    1    |      Sue     |
| Sue      |         | David       |                   |                    5    |              |
| Kelly    | David   |             |                 5 |                         |              |
| David    | Sue     | Kelly       |                 3 |                    4    |              |
| Mary     | Jim     |             |                 1 |                         |              |
+----------+---------+-------------+-------------------+----------------------------------------+

Answer 1

这是一种使用循环和“随机”函数来实现此目的的方法。似乎可能有更有效的方法，但这对于一个简短的列表来说已经足够快了:

import pandas as pd
import random 
import numpy as np

#Create the data above

data = {
        'Employee':['Jim','Joe','Sue','Kelly','David','Mary'],
        'Manager':['Joe','','','David','Sue','Jim'],
        'Subordinate':['','Jim','David','','Kelly',''] }
df = pd.DataFrame(data)

#Create list to store 'used' assignments
used = []

#Make blank column for the Assignments

df['Assigned'] = ''

#Loop through the dataframe
for index,row in df.iterrows(): 
#Iterate through random choices until it chooses one that meets the criteria
    while df.iat[index,3] == '':
        rname =  random.choice(df['Employee']) #Get random name from column 1
        if (rname != row['Manager']) and (rname != row['Subordinate']) and (rname != row['Employee']) and (rname not in used):
            df.iat[index,3] = rname #If it met the criteria, assign it.
            used.append(rname) #Add to 'used' list so it won't be used again