Populate the pandas column with the id of the smallest value from another pandas DF… here is a solution to the problem.
Populate the pandas column with the id of the smallest value from another pandas DF
I want to iterate through the Orders
list and assign an owner ID
to each order. id
is in a separate pandas dataframe
(I also tried changing it to Series
and OrderedDict
.) I want to find the minimum value from df
and use it for the first order
in orders, then count it plus count by 1 id
, repeat
until all orders
are filled.
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'] = ""
Owner:
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
Order:
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 Results:
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 to find the minimum for df.count
and tried to iterate through each one, but it’s hard to isolate each order.
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
Solution
This is one way to do this via 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