import pandas as pd import math file_path = 'D:/Github/docs/config/MergeData.xlsx' df = pd.read_excel(file_path, engine='openpyxl') df = df.drop(index=0) df_emitter = df[(df['Type'] == 'Emitter') & (df['Emit_Product'].notnull())] df_emitter = df_emitter.assign(Dynamic=df_emitter['Emit_Product']) def limitLv(x): if x == 0: return 5 if x == 1: return 3 if x == 2: return 2 if x == 3: return 1 def minLv(x): return min(x, lv) def getDynamicValue1(df, index): row = df.loc[index] d = {} Emit_List = row['Emit_List'].split(',') Product = row['Emit_Product'].split(',') main = row['Emit_Product'].split(',')[0] if len(Emit_List) == 1: a2 = Emit_List[0].split('=') color_values = df[df["Id"] == int(a2[0])]["Color"].values[0] d[color_values] = 0 return d else: sumN = 0 for i in Product: d[i] = 0.0 for i in Emit_List : a2 = i.split('=') color = df[df["Id"] == int(a2[0])]["Color"].values[0] d[color] += float(a2[1])*(minLv(int(a2[0])%10)) sumN += float(a2[1])*(minLv(int(a2[0])%10)) c = {} # mainNum = d[main] for k, i in d.items(): x = sumN/i c[k] = math.floor(math.log(x, 2)) return c def getDynamicValueJ(df, index): row = df.loc[index] d = {} Emit_List = row['Emit_List'].split(',') Product = row['Emit_Product'].split(',') x1 = 30 y = 16 c = {} if len(Emit_List) == 1: c["Detective Tools"] = round(math.floor(math.log((x1+y)/y, 2))) return c else: for i in Product: d[i] = 0.0 for i in Emit_List : a2 = i.split('=') color = df[df["Id"] == int(a2[0])]["Color"].values[0] if color == "Detective Bag": d["Detective Tools"] += float(a2[1])*(minLv(int(a2[0])%10)) else: d[color] += float(a2[1])*(minLv(int(a2[0])%10)) x2 = x1*(d["Detective Tools"]/d["Detective Outfit"]) c["Detective Tools"] = round(math.ceil(math.log((x1+x2+y)/y, 2))) c["Detective Outfit"] = round(math.floor(math.log((x1+x2)/x2, 2))) return c def getDynamicValueM(df, index): row = df.loc[index] d = {} Emit_List = row['Emit_List'].split(',') Product = row['Emit_Product'].split(',') x1 = 112 y = 16 c = {} if len(Emit_List) == 1: c["Dye"] = round(math.floor(math.log((x1+y)/y, 2))) return c else: for i in Product: d[i] = 0.0 for i in Emit_List : a2 = i.split('=') color = df[df["Id"] == int(a2[0])]["Color"].values[0] if color == "Flower": d["Dye"] += float(a2[1])*(minLv(int(a2[0])%10)) else: d[color] += float(a2[1])*(minLv(int(a2[0])%10)) x2 = x1*(d["Dye"]/d["Aromatherapy"]) c["Dye"] = round(math.ceil(math.log((x1+x2+y)/y, 2))) c["Aromatherapy"] = round(math.floor(math.log((x1+x2)/x2, 2))) return c def getDynamicValueR(df, index): row = df.loc[index] d = {} Emit_List = row['Emit_List'].split(',') Product = row['Emit_Product'].split(',') x1 = 112 y = 15 c = {} if len(Emit_List) == 1: c["Outdoor Toys"] = round(math.floor(math.log((x1+y)/y, 2))) return c else: for i in Product: d[i] = 0.0 for i in Emit_List : a2 = i.split('=') color = df[df["Id"] == int(a2[0])]["Color"].values[0] if color == "Tent": d["Outdoor Toys"] += float(a2[1])*(minLv(int(a2[0])%10)) else: d[color] += float(a2[1])*(minLv(int(a2[0])%10)) x2 = x1*(d["Bird Watching"]/d["Outdoor Toys"]) c["Outdoor Toys"] = round(math.ceil(math.log((x1+x2+y)/y, 2))) c["Bird Watching"] = round(math.floor(math.log((x1+x2)/x2, 2))) return c def getDynamicValueW(df, index): row = df.loc[index] d = {} Emit_List = row['Emit_List'].split(',') Product = row['Emit_Product'].split(',') x1 = 31 y = 16 c = {} if len(Emit_List) == 1: c["Curtains"] = 1 return c else: for i in Product: d[i] = 0.0 for i in Emit_List : a2 = i.split('=') color = df[df["Id"] == int(a2[0])]["Color"].values[0] if color == "Handmade Fabric Art": d["Cushion"] += float(a2[1])*(minLv(int(a2[0])%10)) else: d[color] += float(a2[1])*(minLv(int(a2[0])%10)) x2 = x1*(d["Cushion"]/d["Curtains"]) c["Cushion"] = round(math.ceil(math.log((x1+x2+y)/y, 2))) c["Curtains"] = round(math.floor(math.log((x1+x2)/x2, 2))) return c def getDynamicValueC(df, index): x1 = 16 y = 16 c = {} c["Pet House"] = round(math.ceil(math.log((x1+y)/y, 2))) return c def getDynamicValueH(df, index): x1 = 112 y = 16 c = {} c["Jewelry Accessories"] = round(math.ceil(math.log((x1+y)/y, 2))) return c def getDynamicValue3(df, id): return df[df['Id'] == id]['Dynamic'].values[0] for i in range(0,4): lv = limitLv(i) for index, row in df_emitter.iterrows(): if row['Emit_ID'] == 'A': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'B': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'C': d = getDynamicValueC(df, index) if row['Emit_ID'] == 'D': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'E': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'F': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'G': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'H': d = getDynamicValueH(df, index) if row['Emit_ID'] == 'I': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'J': d = getDynamicValueJ(df, index) if row['Emit_ID'] == 'K': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'L': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'M': d = getDynamicValueM(df, index) if row['Emit_ID'] == 'N': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'O': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'P': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'Q': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'R': d = getDynamicValueR(df, index) if row['Emit_ID'] == 'S': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'T': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'U': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'V': d = getDynamicValue1(df, index) if row['Emit_ID'] == 'W': d = getDynamicValueW(df, index) if row['Emit_ID'] == 'X': d = getDynamicValue1(df, index) # 将字典格式化为 "key=value,..." 并写入原 DataFrame 的 'X' 列，然后打印验证 dynamic_str = ",".join(f"{k}={v}" for k, v in d.items()) # 构造列名，避免将 int 直接与 str 拼接导致 TypeError col_name = 'Dynamic' if i == 0 else f'Dynamic{i}' df.at[index, col_name] = dynamic_str print(dynamic_str) # 保存回 Excel（会覆盖原文件，已在脚本开始处有备份逻辑可扩展） save_file_path = './dynamicLv_output.xlsx' df.to_excel(save_file_path, index=False, engine='openpyxl') print(f"Saved updated file to {save_file_path}")