该代码在存在多个非 CL 产品并且our_val_amt必须进行传播，以便最后几种产品可以得到0价值。我在上一个/上一个问题中询问了这个用例；但没有得到答复。您可能会遇到一些像这样的极端情况，需要执行全面的测试。
以下是更新后的逻辑。在每个处理行之前添加注释以解释其作用。
df = pd.DataFrame(data=[["compx","xx1","yy2",424,134,"CL",134],["compx","xx1","yy1",424,418,"XL",290],["compx","xx2","yy4",472,104,"CL",104],["compx","xx2","yy3",472,60,"DL",368],["compx","xx3","yy6",490,500,"CL",490],["compx","xx3","yy5",490,50,"XL",0],["compx","xx3","yy7",490,200,"DL",0],["compx","xx4","yy8",510,200,"CL",200],["compx","xx4","yy9",510,300,"CL",300],["compx","xx4","yy10",510,50,"CL",10],["compy","xx5","yy11",510,200,"CL",200],["compy","xx5","yy12",510,300,"CL",300],["compy","xx5","yy12",510,50,"CL",10],["compy","xx5","yy13",510,30,"DL",0],["compz","xx6","yy14",350,200,"CL",200],["compz","xx6","yy15",350,100,"CL",100],["compz","xx6","yy16",350,50,"XL",50],["compz","xx6","yy17",350,50,"DL",0],["compz","xx7","yy18",700,650,"DL",650],["compz","xx7","yy19",700,200,"DL",50],["compz","xx7","yy20",700,400,"XL",0]], columns=["name","val_id","fac_id","our_val_amt","val_against","product","new_field_expected"])
# Compute tuple of "our_val_amt", "val_against" and "product" for easy processing as one column. It is hard to process multiple columns with "transform()".
df["the_tuple"] = df[["our_val_amt", "val_against", "product"]].apply(tuple, axis=1)
def compute_new_field_for_cl(g):
# df_g is a tuple ("our_val_amt", "val_against", "product") indexed as (0, 1, 2).
df_g = g.apply(pd.Series)
df_g["new_field"] = df_g.apply(lambda row: min(row[0], row[1]) if row[2] == "CL" else 0, axis=1)
# Cumulative sum for comparison
df_g["cumsum"] = df_g["new_field"].cumsum()
# Previous row's sum for comparison
df_g["cumsum_prev"] = df_g["cumsum"].shift(periods=1)
# if our_val_amt >= sum then use min(our_val_amt, val_against)
# else if our_val_amt < sum then take partial of first record such that our_val_amt == sum else take `0` for the rest records
df_g["new_field"] = df_g.apply(lambda row: 0 if row["cumsum_prev"] > row[0] else row[0] - row["cumsum_prev"] if row["cumsum"] > row[0] else row["new_field"], axis=1)
return df_g["new_field"]
# Apply above function and compute new field values for "CL".
df["new_field"] = df.groupby("val_id")[["the_tuple"]].transform(compute_new_field_for_cl)
# Re-compute tuple of "our_val_amt", "val_against", "new_field" and "product".
df["the_tuple"] = df[["our_val_amt", "val_against", "new_field", "product"]].apply(tuple, axis=1)
def compute_new_field_for_not_cl(g):
# df_g is a tuple ("our_val_amt", "val_against", "new_field", "product") indexed as (0, 1, 2, 3).
df_g = g.apply(pd.Series)
# print(df_g)
cl_sum = df_g[df_g[3] == "CL"][2].sum()
if cl_sum > 0:
df_g["new_field"] = df_g.where(df_g[3] != "CL")[0] - df_g[df_g[3] == "CL"][2].sum()
df_g["new_field"] = df_g["new_field"].fillna(df_g[2])
# Cumulative sum for comparison
df_g["cumsum"] = df_g["new_field"].cumsum()
# if our_val_amt < sum then take diff (our_val_amt - sum) else take `0` for the rest records
df_g["new_field"] = df_g.apply(lambda row: 0 if row["cumsum"] > row[0] else row["new_field"], axis=1)
else:
df_g["new_field"] = df_g.apply(lambda row: min(row[0], row[1]) if row[3] != "CL" else row[2], axis=1)
# Cumulative sum for comparison
df_g["cumsum"] = df_g["new_field"].cumsum()
# Previous row's sum for comparison
df_g["cumsum_prev"] = df_g["cumsum"].shift(periods=1)
# if our_val_amt >= sum then use min(our_val_amt, val_against)
# else if our_val_amt < sum then take partial of first record such that our_val_amt == sum else take `0` for the rest records
df_g["new_field"] = df_g.apply(lambda row: 0 if row["cumsum_prev"] > row[0] else row[0] - row["cumsum_prev"] if row["cumsum"] > row[0] else row["new_field"], axis=1)
return df_g["new_field"]
# Apply above function and compute new field values for "CL".
df["new_field"] = df.groupby("val_id")[["the_tuple"]].transform(compute_new_field_for_not_cl)
df = df.drop("the_tuple", axis=1)
print(df)
Output:
name val_id fac_id our_val_amt val_against product new_field_expected new_field
0 compx xx1 yy2 424 134 CL 134 134.00
1 compx xx1 yy1 424 418 XL 290 290.00
2 compx xx2 yy4 472 104 CL 104 104.00
3 compx xx2 yy3 472 60 DL 368 368.00
4 compx xx3 yy6 490 500 CL 490 490.00
5 compx xx3 yy5 490 50 XL 0 0.00
6 compx xx3 yy7 490 200 DL 0 0.00
7 compx xx4 yy8 510 200 CL 200 200.00
8 compx xx4 yy9 510 300 CL 300 300.00
9 compx xx4 yy10 510 50 CL 10 10.00
10 compy xx5 yy11 510 200 CL 200 200.00
11 compy xx5 yy12 510 300 CL 300 300.00
12 compy xx5 yy12 510 50 CL 10 10.00
13 compy xx5 yy13 510 30 DL 0 0.00
14 compz xx6 yy14 350 200 CL 200 200.00
15 compz xx6 yy15 350 100 CL 100 100.00
16 compz xx6 yy16 350 50 XL 50 50.00
17 compz xx6 yy17 350 50 DL 0 0.00
18 compz xx7 yy18 700 650 DL 650 650.00
19 compz xx7 yy19 700 200 DL 50 50.00
20 compz xx7 yy20 700 400 XL 0 0.00
