|
#2
sheeboard2023-06-15 23:39
|
程序代码：import pandas as pd
import random
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
# 文件选择框
file_path = filedialog.askopenfilename()
# 读取表格数据
df = pd.read_excel(file_path)
# 定义一个全局变量来保存用户选择的规则
selected_rules = []
# get_input()函数
def get_input():
x = num_entry.get()
root.destroy()
return int(x), selected_rules
# 定义一个函数用于将被勾选的选项保存到一个列表中
def toggle_check(item, var):
if var.get() == item[1] and item not in selected_rules:
selected_rules.append(item)
elif item in selected_rules:
selected_rules.remove(item)
update_selected_rules()
# 弹出输入框和单选框，获取输入的数值和选择的规则
root = tk.Tk()
tk.Label(root, text="请输入分配量(600/33=18)：").grid(column=0, row=0, sticky='w')
num_entry = tk.Entry(root)
num_entry.grid(column=0, row=1)
tk.Label(root, text="请选择分配规则：").grid(column=0, row=2, sticky='w')
# 增加一个全选复选框
select_all_var = tk.IntVar()
select_all_checkbox = tk.Checkbutton(root, text="全选", variable=select_all_var, anchor='w')
select_all_checkbox.grid(column=0, row=3, sticky='w')
rules = [("按平均数量分配", "quantity", 1), ("按平均金额分配", "amount", 2)]
# 创建一个字典用来保存复选框
checkbox_dict = {}
# 绑定全选复选框的选择事件
def select_all():
if select_all_var.get():
for mode in checkbox_dict:
checkbox_dict[mode].select()
selected_rules.clear()
selected_rules.extend(rules)
else:
for mode in checkbox_dict:
checkbox_dict[mode].deselect()
selected_rules.clear()
update_selected_rules()
select_all_var.trace_add('write', lambda *args: select_all())
def update_selected_rules():
global selected_rules
selected_rules = []
for item in rules:
if checkbox_dict[item[1]].cget('selectcolor') == 'white':
selected_rules.append(item)
rule_var = tk.StringVar()
rule_var.set("quantity")
for i, (text, mode, order) in enumerate(rules):
# 使用字典保存复选框
checkbox_dict[mode] = tk.Checkbutton(root, text=text, variable=rule_var,
onvalue=mode, offvalue="", anchor='w',
command=lambda item=(text, mode, order): toggle_check(item, rule_var))
checkbox_dict[mode].grid(column=0, row=i+4, sticky='w')
button = tk.Button(root, text="确定", command=root.quit)
button.grid(column=0, row=len(rules)+4)
selected_rules = [] # 初始化选中的规则为空列表
root.mainloop()
# 获取输入的数值和选择的规则并使用
num_accounts, selected_rules = get_input()
print(num_accounts, selected_rules) # 输出输入的数值和选中的规则
# 抽取所有存在的账号名称
existing_accounts = df['账号'].dropna().astype(str)
accounts = existing_accounts.value_counts().index.tolist()
# 根据选择的规则计算每个账号需要分配的id数或金额
num_ids = len(df)
if ("按平均数量分配", "quantity", 1) in selected_rules:
average_amount = num_ids // num_accounts
account_distribution = "按平均数量分配"
print("662")
else:
total_amount = df['金额'].sum()
average_amount = total_amount / num_accounts
account_total_amounts = df.groupby('账号')['金额'].sum().to_dict()
account_distribution = "按平均金额分配"
print("66")
# 根据平均金额计算每个账号需要分配的id数
num_ids_per_account = {}
for account, total_amount in account_total_amounts.items():
num_ids_per_account[account] = int(total_amount / average_amount * num_ids / total_amount)
total_ids = sum(num_ids_per_account.values())
if total_ids != num_ids:
# 调整num_ids_per_account使得总和等于num_ids
if total_ids > num_ids:
excess_ids = total_ids - num_ids
while excess_ids > 0:
for account in list(num_ids_per_account.keys()):
if num_ids_per_account[account] > 0:
num_ids_per_account[account] -= 1
excess_ids -= 1
if excess_ids == 0:
break
else:
remaining_ids = num_ids - total_ids
while remaining_ids > 0:
for account in list(num_ids_per_account.keys()):
num_ids_per_account[account] += 1
remaining_ids -= 1
if remaining_ids == 0:
break
# 按金额排序
df_sorted = df.sort_values(by=['金额'])
# 分配账号
assigned_accounts = []
account_id_counts = {}
account_total_amounts = {}
for i in range(num_ids):
# 如果已分配该账号的id数量达到了上限，则在剩余账号中筛选
while True:
if len(accounts) == 0:
raise ValueError('账号数量不够，请减少分配量或添加更多账号')
account = random.choice(accounts)
if selected_rules == "quantity":
if assigned_accounts.count(account) < num_ids_per_account:
assigned_accounts.append(account)
break
else:
if account not in account_total_amounts:
assigned_accounts.append(account)
account_id_counts[account] = 0
account_total_amounts[account] = 0
elif account_total_amounts[account] + df_sorted.loc[df_sorted.index[i], '金额'] > average_amount:
accounts.remove(account)
continue
assigned_accounts.append(account)
df_sorted.loc[df_sorted.index[i], '账号'] = account
account_id_counts[account] += 1
account_total_amounts[account] += df_sorted.loc[df_sorted.index[i], '金额']
break
#-----------------------
# 对于按平均金额分配的规则，重新计算每个账号需要分配的id数
if selected_rules == "amount":
num_accounts = len(accounts)
total_amount = sum(account_total_amounts.values())
average_amount = total_amount / num_accounts
# 分配订单
num_ids_per_account = {}
total_ids = 0
for account in accounts:
num_ids = int(account_total_amounts[account] / average_amount * num_ids / total_amount)
num_ids_per_account[account] = num_ids
total_ids += num_ids
# 调整num_ids_per_account使得总和等于num_ids
if total_ids != num_ids:
if total_ids > num_ids:
excess_ids = total_ids - num_ids
for account in sorted(num_ids_per_account.keys(), key=num_ids_per_account.get, reverse=True):
if num_ids_per_account[account] > 0:
num_ids_per_account[account] -= 1
excess_ids -= 1
if excess_ids == 0:
break
else:
remaining_ids = num_ids - total_ids
for account in sorted(num_ids_per_account.keys(), key=num_ids_per_account.get):
num_ids_per_account[account] += 1
remaining_ids -= 1
if remaining_ids == 0:
break
assigned_amounts = {account: num_ids_per_account[account] * average_amount for account in accounts}
# 根据应分配金额分配订单
for account in account_total_amounts:
assigned_accounts = []
excess_ids = df_sorted[df_sorted['账号'] == account]
# 分配其它账号的订单，直到当前账号的订单数量达到应分配数量
while len(excess_ids) < assigned_amounts[account]:
if len(accounts) <= 1:
raise ValueError('账号数量不够，请减少分配量或添加更多账号')
other_account = random.choice(accounts)
if other_account != account:
excess_ids_other = df_sorted[df_sorted['账号'] == other_account]
excess_ids_to_assign = excess_ids_other.iloc[:assigned_amounts[account] - len(excess_ids)]
# 判断是否需要在多个账号之间分配金额
if abs(excess_ids_to_assign['金额'].sum() - excess_ids['金额'].sum()) > 2000:
diff = excess_ids_to_assign['金额'].sum() - excess_ids['金额'].sum()
if diff > 0:
excess_ids_to_assign = excess_ids_other.iloc[
:assigned_amounts[account] - len(excess_ids) + 1]
excess_ids_to_assign['账号'] = account
excess_ids = pd.concat([excess_ids, excess_ids_to_assign.iloc[:-1]])
excess_ids_other.drop(excess_ids_to_assign.iloc[:-1].index, inplace=True)
else:
excess_ids_to_assign = excess_ids_other.iloc[
:assigned_amounts[account] - len(excess_ids) - 1]
other_account_ids = excess_ids[excess_ids['ID'].str.startswith(other_account)]
excess_ids_to_assign.loc[other_account_ids.iloc[0].name, '金额'] += diff
excess_ids_to_assign['账号'] = other_account
excess_ids = pd.concat([excess_ids, excess_ids_to_assign])
excess_ids_other.drop(excess_ids_to_assign.index, inplace=True)
else:
excess_ids_to_assign['账号'] = account
excess_ids = pd.concat([excess_ids, excess_ids_to_assign])
excess_ids_other.drop(excess_ids_to_assign.index, inplace=True)
assigned_accounts.append(other_account)
accounts.remove(other_account)
if len(excess_ids) >= assigned_amounts[account]:
break
# 分配当前账号的订单
excess_ids = excess_ids.sample(frac=1) # 随机打乱顺序
excess_ids_to_assign = excess_ids.iloc[:assigned_amounts[account]]
excess_ids_to_assign['账号'] = account
excess_ids.drop(excess_ids_to_assign.index, inplace=True)
account_id_counts[account] += len(excess_ids_to_assign)
assigned_accounts.append(account)
# 调整账户之间的总金额
while True:
account_total_amounts = {account: df_sorted[df_sorted['账号'] == account]['金额'].sum() for account in
accounts}
max_amount_account = max(account_total_amounts, key=account_total_amounts.get)
min_amount_account = min(account_total_amounts, key=account_total_amounts.get)
diff = account_total_amounts[max_amount_account] - account_total_amounts[min_amount_account]
if diff < 5000:
break
excess_ids = df_sorted[df_sorted['账号'] == max_amount_account]
excess_ids_to_assign = excess_ids.sample(n=int(diff / 2))
excess_ids_to_assign['账号'] = min_amount_account
excess_ids.drop(excess_ids_to_assign.index, inplace=True)
account_id_counts[min_amount_account] += len(excess_ids_to_assign)
if len(excess_ids) == 0:
accounts.remove(max_amount_account)
continue
excess_ids = df_sorted[df_sorted['账号'] == min_amount_account]
excess_ids_to_assign = excess_ids.sample(n=int(diff / 2))
excess_ids_to_assign['账号'] = max_amount_account
excess_ids.drop(excess_ids_to_assign.index, inplace=True)
account_id_counts[max_amount_account] += len(excess_ids_to_assign)
# 剩余订单随机分配给其他账号
for i in range(len(excess_ids)):
while True:
if len(accounts) <= 1:
raise ValueError('账号数量不够，请减少分配量或添加更多账号')
other_account = random.choice(accounts)
if other_account != account and assigned_accounts.count(other_account) < num_ids_per_account[
other_account]:
excess_ids.loc[excess_ids.index[i], '账号'] = other_account
excess_ids.drop(excess_ids.index[i], inplace=True)
account_id_counts[other_account] += 1
assigned_accounts.append(other_account)
accounts.remove(other_account)
break
df_sorted = df_sorted.sort_values(by=['账号', '金额'])
# 输出表格和分配结果
save_path = filedialog.asksaveasfilename(defaultextension=".xlsx")
if save_path:
df_sorted.to_excel(save_path, index=False)
print('文件保存成功！')
print("账号分配结果：")
for account, count in account_id_counts.items():
print("{}: {}个id ({:.2f}元)".format(account, count, account_total_amounts.get(account, 0)))
print(account_distribution)
else:
print('未选择文件保存路径！')
import random
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
# 文件选择框
file_path = filedialog.askopenfilename()
# 读取表格数据
df = pd.read_excel(file_path)
# 定义一个全局变量来保存用户选择的规则
selected_rules = []
# get_input()函数
def get_input():
x = num_entry.get()
root.destroy()
return int(x), selected_rules
# 定义一个函数用于将被勾选的选项保存到一个列表中
def toggle_check(item, var):
if var.get() == item[1] and item not in selected_rules:
selected_rules.append(item)
elif item in selected_rules:
selected_rules.remove(item)
update_selected_rules()
# 弹出输入框和单选框，获取输入的数值和选择的规则
root = tk.Tk()
tk.Label(root, text="请输入分配量(600/33=18)：").grid(column=0, row=0, sticky='w')
num_entry = tk.Entry(root)
num_entry.grid(column=0, row=1)
tk.Label(root, text="请选择分配规则：").grid(column=0, row=2, sticky='w')
# 增加一个全选复选框
select_all_var = tk.IntVar()
select_all_checkbox = tk.Checkbutton(root, text="全选", variable=select_all_var, anchor='w')
select_all_checkbox.grid(column=0, row=3, sticky='w')
rules = [("按平均数量分配", "quantity", 1), ("按平均金额分配", "amount", 2)]
# 创建一个字典用来保存复选框
checkbox_dict = {}
# 绑定全选复选框的选择事件
def select_all():
if select_all_var.get():
for mode in checkbox_dict:
checkbox_dict[mode].select()
selected_rules.clear()
selected_rules.extend(rules)
else:
for mode in checkbox_dict:
checkbox_dict[mode].deselect()
selected_rules.clear()
update_selected_rules()
select_all_var.trace_add('write', lambda *args: select_all())
def update_selected_rules():
global selected_rules
selected_rules = []
for item in rules:
if checkbox_dict[item[1]].cget('selectcolor') == 'white':
selected_rules.append(item)
rule_var = tk.StringVar()
rule_var.set("quantity")
for i, (text, mode, order) in enumerate(rules):
# 使用字典保存复选框
checkbox_dict[mode] = tk.Checkbutton(root, text=text, variable=rule_var,
onvalue=mode, offvalue="", anchor='w',
command=lambda item=(text, mode, order): toggle_check(item, rule_var))
checkbox_dict[mode].grid(column=0, row=i+4, sticky='w')
button = tk.Button(root, text="确定", command=root.quit)
button.grid(column=0, row=len(rules)+4)
selected_rules = [] # 初始化选中的规则为空列表
root.mainloop()
# 获取输入的数值和选择的规则并使用
num_accounts, selected_rules = get_input()
print(num_accounts, selected_rules) # 输出输入的数值和选中的规则
# 抽取所有存在的账号名称
existing_accounts = df['账号'].dropna().astype(str)
accounts = existing_accounts.value_counts().index.tolist()
# 根据选择的规则计算每个账号需要分配的id数或金额
num_ids = len(df)
if ("按平均数量分配", "quantity", 1) in selected_rules:
average_amount = num_ids // num_accounts
account_distribution = "按平均数量分配"
print("662")
else:
total_amount = df['金额'].sum()
average_amount = total_amount / num_accounts
account_total_amounts = df.groupby('账号')['金额'].sum().to_dict()
account_distribution = "按平均金额分配"
print("66")
# 根据平均金额计算每个账号需要分配的id数
num_ids_per_account = {}
for account, total_amount in account_total_amounts.items():
num_ids_per_account[account] = int(total_amount / average_amount * num_ids / total_amount)
total_ids = sum(num_ids_per_account.values())
if total_ids != num_ids:
# 调整num_ids_per_account使得总和等于num_ids
if total_ids > num_ids:
excess_ids = total_ids - num_ids
while excess_ids > 0:
for account in list(num_ids_per_account.keys()):
if num_ids_per_account[account] > 0:
num_ids_per_account[account] -= 1
excess_ids -= 1
if excess_ids == 0:
break
else:
remaining_ids = num_ids - total_ids
while remaining_ids > 0:
for account in list(num_ids_per_account.keys()):
num_ids_per_account[account] += 1
remaining_ids -= 1
if remaining_ids == 0:
break
# 按金额排序
df_sorted = df.sort_values(by=['金额'])
# 分配账号
assigned_accounts = []
account_id_counts = {}
account_total_amounts = {}
for i in range(num_ids):
# 如果已分配该账号的id数量达到了上限，则在剩余账号中筛选
while True:
if len(accounts) == 0:
raise ValueError('账号数量不够，请减少分配量或添加更多账号')
account = random.choice(accounts)
if selected_rules == "quantity":
if assigned_accounts.count(account) < num_ids_per_account:
assigned_accounts.append(account)
break
else:
if account not in account_total_amounts:
assigned_accounts.append(account)
account_id_counts[account] = 0
account_total_amounts[account] = 0
elif account_total_amounts[account] + df_sorted.loc[df_sorted.index[i], '金额'] > average_amount:
accounts.remove(account)
continue
assigned_accounts.append(account)
df_sorted.loc[df_sorted.index[i], '账号'] = account
account_id_counts[account] += 1
account_total_amounts[account] += df_sorted.loc[df_sorted.index[i], '金额']
break
#-----------------------
# 对于按平均金额分配的规则，重新计算每个账号需要分配的id数
if selected_rules == "amount":
num_accounts = len(accounts)
total_amount = sum(account_total_amounts.values())
average_amount = total_amount / num_accounts
# 分配订单
num_ids_per_account = {}
total_ids = 0
for account in accounts:
num_ids = int(account_total_amounts[account] / average_amount * num_ids / total_amount)
num_ids_per_account[account] = num_ids
total_ids += num_ids
# 调整num_ids_per_account使得总和等于num_ids
if total_ids != num_ids:
if total_ids > num_ids:
excess_ids = total_ids - num_ids
for account in sorted(num_ids_per_account.keys(), key=num_ids_per_account.get, reverse=True):
if num_ids_per_account[account] > 0:
num_ids_per_account[account] -= 1
excess_ids -= 1
if excess_ids == 0:
break
else:
remaining_ids = num_ids - total_ids
for account in sorted(num_ids_per_account.keys(), key=num_ids_per_account.get):
num_ids_per_account[account] += 1
remaining_ids -= 1
if remaining_ids == 0:
break
assigned_amounts = {account: num_ids_per_account[account] * average_amount for account in accounts}
# 根据应分配金额分配订单
for account in account_total_amounts:
assigned_accounts = []
excess_ids = df_sorted[df_sorted['账号'] == account]
# 分配其它账号的订单，直到当前账号的订单数量达到应分配数量
while len(excess_ids) < assigned_amounts[account]:
if len(accounts) <= 1:
raise ValueError('账号数量不够，请减少分配量或添加更多账号')
other_account = random.choice(accounts)
if other_account != account:
excess_ids_other = df_sorted[df_sorted['账号'] == other_account]
excess_ids_to_assign = excess_ids_other.iloc[:assigned_amounts[account] - len(excess_ids)]
# 判断是否需要在多个账号之间分配金额
if abs(excess_ids_to_assign['金额'].sum() - excess_ids['金额'].sum()) > 2000:
diff = excess_ids_to_assign['金额'].sum() - excess_ids['金额'].sum()
if diff > 0:
excess_ids_to_assign = excess_ids_other.iloc[
:assigned_amounts[account] - len(excess_ids) + 1]
excess_ids_to_assign['账号'] = account
excess_ids = pd.concat([excess_ids, excess_ids_to_assign.iloc[:-1]])
excess_ids_other.drop(excess_ids_to_assign.iloc[:-1].index, inplace=True)
else:
excess_ids_to_assign = excess_ids_other.iloc[
:assigned_amounts[account] - len(excess_ids) - 1]
other_account_ids = excess_ids[excess_ids['ID'].str.startswith(other_account)]
excess_ids_to_assign.loc[other_account_ids.iloc[0].name, '金额'] += diff
excess_ids_to_assign['账号'] = other_account
excess_ids = pd.concat([excess_ids, excess_ids_to_assign])
excess_ids_other.drop(excess_ids_to_assign.index, inplace=True)
else:
excess_ids_to_assign['账号'] = account
excess_ids = pd.concat([excess_ids, excess_ids_to_assign])
excess_ids_other.drop(excess_ids_to_assign.index, inplace=True)
assigned_accounts.append(other_account)
accounts.remove(other_account)
if len(excess_ids) >= assigned_amounts[account]:
break
# 分配当前账号的订单
excess_ids = excess_ids.sample(frac=1) # 随机打乱顺序
excess_ids_to_assign = excess_ids.iloc[:assigned_amounts[account]]
excess_ids_to_assign['账号'] = account
excess_ids.drop(excess_ids_to_assign.index, inplace=True)
account_id_counts[account] += len(excess_ids_to_assign)
assigned_accounts.append(account)
# 调整账户之间的总金额
while True:
account_total_amounts = {account: df_sorted[df_sorted['账号'] == account]['金额'].sum() for account in
accounts}
max_amount_account = max(account_total_amounts, key=account_total_amounts.get)
min_amount_account = min(account_total_amounts, key=account_total_amounts.get)
diff = account_total_amounts[max_amount_account] - account_total_amounts[min_amount_account]
if diff < 5000:
break
excess_ids = df_sorted[df_sorted['账号'] == max_amount_account]
excess_ids_to_assign = excess_ids.sample(n=int(diff / 2))
excess_ids_to_assign['账号'] = min_amount_account
excess_ids.drop(excess_ids_to_assign.index, inplace=True)
account_id_counts[min_amount_account] += len(excess_ids_to_assign)
if len(excess_ids) == 0:
accounts.remove(max_amount_account)
continue
excess_ids = df_sorted[df_sorted['账号'] == min_amount_account]
excess_ids_to_assign = excess_ids.sample(n=int(diff / 2))
excess_ids_to_assign['账号'] = max_amount_account
excess_ids.drop(excess_ids_to_assign.index, inplace=True)
account_id_counts[max_amount_account] += len(excess_ids_to_assign)
# 剩余订单随机分配给其他账号
for i in range(len(excess_ids)):
while True:
if len(accounts) <= 1:
raise ValueError('账号数量不够，请减少分配量或添加更多账号')
other_account = random.choice(accounts)
if other_account != account and assigned_accounts.count(other_account) < num_ids_per_account[
other_account]:
excess_ids.loc[excess_ids.index[i], '账号'] = other_account
excess_ids.drop(excess_ids.index[i], inplace=True)
account_id_counts[other_account] += 1
assigned_accounts.append(other_account)
accounts.remove(other_account)
break
df_sorted = df_sorted.sort_values(by=['账号', '金额'])
# 输出表格和分配结果
save_path = filedialog.asksaveasfilename(defaultextension=".xlsx")
if save_path:
df_sorted.to_excel(save_path, index=False)
print('文件保存成功！')
print("账号分配结果：")
for account, count in account_id_counts.items():
print("{}: {}个id ({:.2f}元)".format(account, count, account_total_amounts.get(account, 0)))
print(account_distribution)
else:
print('未选择文件保存路径！')