使用另一个pandas DF的min值中的id填充pandas列

I'm looking to iterate through a list of orders and assign an owner id to each order. The id is in a separate pandas dataframe (I've also tried changing this into a Series and OrderedDict. I would like to locate the min value from the df and use that for the first order in orders, then add 1 to the count of that ids count, and repeat until all the orders are filled.

我正在寻找迭代订单列表并为每个订单分配所有者ID。 id在一个单独的pandas数据框中(我也尝试将其更改为Series和OrderedDict。我想从df中找到min值并将其用于订单中的第一个订单,然后将1添加到计数中这些ID计数,并重复,直到所有订单都被填满。

Reproducible Example:

df = pd.DataFrame({'Id':['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'], 'count':[2, 3, 5, 6, 8, 9, 12, 13, 15, 55]})
orders = pd.DataFrame({'order_id':['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13']})
orders['newowner'] = ""

Owners:

df
  Id  count
0  a      2
1  b      3
2  c      5
3  d      6
4  e      8
5  f      9
6  g     12
7  h     13
8  i     15
9  j     55

Orders:

   order_id newowner
0        a1         
1        a2         
2        a3         
3        a4         
4        a5         
5        a6         
6        a7         
7        a8         
8        a9         
9       a10         
10      a11         
11      a12         
12      a13

expected result:

   order_id newowner
0        a1       a    # brings a up to 3 records
1        a2       a    # a and b are tied with 3, so it goes to a again (doesn't matter which gets it first)
2        a3       b    # now b has 3, and a has 4, so it goes to b
3        a4       a    # both have 4 so a
4        a5       b    # etc.
5        a6       a
6        a7       b
7        a8       c
8        a9       a
9       a10       b
10      a11       c
11      a12       a
12      a13       b

I've tried finding the min of the df.count, as well as tried to loop through each, but am having a hard time isolating each order.

我已经尝试找到df.count的min,并尝试遍历每个,但我很难隔离每个订单。

for order in orders.iteritems():
    order['newowner'] = df.count.min()

for order in orders.iteritems():
    for name in df.iteritems:
        idx = df[df.count == df.count.min()]['Id']
    order['newonwer'] = idx

2 个解决方案

#1

Here is one way via df.apply:

这是通过df.apply的一种方式:

def set_owner(order_id):
    min_idx = df['count'].idxmin()
    df.loc[min_idx, 'count'] += 1
    return df.loc[min_idx, 'Id']

orders['newowner'] = orders['order_id'].apply(set_owner)

orders
#    order_id newowner
# 0        a1        a
# 1        a2        a
# 2        a3        b
# 3        a4        a
# 4        a5        b
# 5        a6        a
# 6        a7        b
# 7        a8        c
# 8        a9        a
# 9       a10        b
# 10      a11        c
# 11      a12        d
# 12      a13        a

df
#   Id  count
# 0  a      8
# 1  b      7
# 2  c      7
# 3  d      7
# 4  e      8
# 5  f      9
# 6  g     12
# 7  h     13
# 8  i     15
# 9  j     55

#2

I'm not sure this is the way I'd do it. I'd probably look for a way to use df.apply if possible. But I think this code will give you the expected results.

我不确定这是我做的方式。如果可能的话,我可能会寻找一种方法来使用df.apply。但我认为这段代码会给你预期的结果。

for idx, order in orders.iterrows():
    idxmin = df['count'].idxmin()
    df.loc[idxmin, 'count'] += 1
    order['newowner'] = df.loc[idxmin,'Id']

#1