我如何为这个在每个阶段都有移动平均线的 df 创建一个 while 循环? [英] How do I create a while loop for this df that has moving average in every stage?
问题描述
因此，我想通过查看平均销售额来确定将其发送给谁，从而将每个 ID 的出货量逐个分布在组中.
So I want to spread the shipments per ID in the group one by one by looking at avg sales to determine who to give it to.
这是我的数据框:
ID STOREID BAL SALES SHIP
1 STR1 50 5 18
1 STR2 6 7 18
1 STR3 74 4 18
2 STR1 35 3 500
2 STR2 5 4 500
2 STR3 54 7 500
当 SHIP(按 ID 分组)大于 0 时，计算 AVG (BAL/SALES) 并且每组最低的 AVG 为其列 BAL 提供 +1，为其列最终提供 +1.然后重复这个过程，直到 SHIP 为 0.每个阶段的 AVG 都不同，这就是为什么我希望它是一个 while 循环.
While SHIP (grouped by ID) is greater than 0, calculate AVG (BAL/SALES) and the lowest AVG per group give +1 to its column BAL and +1 to its column final. And then repeat the process until SHIP is 0. The AVG would be different every stage which is why I wanted it to be a while loop.
第一轮的示例输出如下.所以这样做直到 SHIP 为 0 并且每个 ID 的最终总和 = 到 SHIP:
Sample output of first round is below. So do this until SHIP is 0 and SUM of Final per ID is = to SHIP:
ID STOREID BAL SALES SHIP AVG Final
1 STR1 50 5 18 10 0
1 STR2 6 4 18 1.5 1
1 STR3 8 4 18 2 0
2 STR1 35 3 500 11.67 0
2 STR2 5 4 500 1.25 1
2 STR3 54 7 500 7.71 0
我在 SQL 中尝试了几种方法，我认为在 python 中执行它会更好，但我的循环并没有做得很好.这是我到目前为止尝试过的:
I've tried a couple of ways in SQL, I thought it would be better to do it in python but I haven't been doing a great job with my loop. Here's what I tried so far:
df['AVG'] = 0
df['FINAL'] = 0
for i in df.groupby(["ID"])['SHIP']:
if i > 0:
df['AVG'] = df['BAL'] / df['SALES']
df['SHIP'] = df.groupby(["ID"])['SHIP']-1
total = df.groupby(["ID"])["FINAL"].transform("cumsum")
df['FINAL'] = + 1
df['A'] = + 1
else:
df['FINAL'] = 0
推荐答案
这很有挑战性，因为组中不止一行可以有相同的平均计算.然后它会放弃分配.
This was challenging because more than one row in the group can have the same average calculation. then it throws off the allocation.
如果我理解正确的话，这适用于示例数据框.
This works on the example dataframe, if I understood you correctly.
d = {'ID': [1, 1, 1, 2,2,2], 'STOREID': ['str1', 'str2', 'str3','str1', 'str2', 'str3'],'BAL':[50, 6, 74, 35,5,54], 'SALES': [5, 7, 4, 3,4,7], 'SHIP': [18, 18, 18, 500,500,500]}
df = pd.DataFrame(data=d)
df['AVG'] = 0
df['FINAL'] = 0
def calc_something(x):
# print(x.iloc[0]['SHIP'])
for i in range(x.iloc[0]['SHIP'])[0:500]:
x['AVG'] = x['BAL'] / x['SALES']
x['SHIP'] = x['SHIP']-1
x = x.sort_values('AVG').reset_index(drop=True)
# print(x.iloc[0, 2])
x.iloc[0, 2] = x['BAL'][0] + 1
x.iloc[0, 6] = x['FINAL'][0] + 1
return x
df_final = df.groupby('ID').apply(calc_something).reset_index(drop=True).sort_values(['ID', 'STOREID'])
df_final
ID STOREID BAL SALES SHIP AVG FINAL
1 1 STR1 50 5 0 10.000 0
0 1 STR2 24 7 0 3.286 18
2 1 STR3 74 4 0 18.500 0
4 2 STR1 127 3 0 42.333 92
5 2 STR2 170 4 0 42.500 165
3 2 STR3 297 7 0 42.286 243
这篇关于我如何为这个在每个阶段都有移动平均线的 df 创建一个 while 循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
