用python实现sumifs的功能
方法一:
好用,推荐
# 使用提醒:
# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能
# 2. package包提供访问当前应用数据的功能,如获取元素、访问全局变量、获取资源文件等功能
# 3. 当此模块作为流程独立运行时执行main函数
# 4. 可视化流程中可以通过"调用模块"的指令使用此模块
import xbot
from xbot import print, sleep
from .import package
from .package import variables as glv
import pandas as pd
def main(args):
pass
def sumifs_excel(dataSourceList, targetAreaList,criteria_col_names,sum_col_names):
df1 = pd.DataFrame(dataSourceList[1:], columns=dataSourceList[0])
df2 = pd.DataFrame(targetAreaList[1:], columns=targetAreaList[0])
# 使用 groupby 方法对店铺名称和是否作废列进行分组
groups = df1.groupby(criteria_col_names)
# 对每个组应用sum方法,并将结果保存在一个字典中
result_dict = {group_name: groups.get_group(group_name)[sum_col_names].sum() for group_name in groups.groups.keys()}
# 将字典转换为DataFrame
result_df1 = pd.DataFrame(result_dict).T
result_df1 = result_df1.reset_index()
result_df1.columns = criteria_col_names+sum_col_names
# print(result_df1)
# 使用merge()函数根据多个列进行合并
merged_df = pd.merge(df2, result_df1, on=criteria_col_names, how='left')
# print(merged_df)
return merged_df.values.tolist()
# # 示例数据
# sheet1 = [['店铺名称', '款号', '是否作废', '购买单价', '金额'],
# ['淘宝旗舰店', '71204300', '作废', 149.0, 200],
# ['淘宝旗舰店', '32204100', '正常', 69.0, 100],
# ['测试专卖店', 'B1204300', '正常', 79.0, 150],
# ['淘宝旗舰店', '71204300', '正常', 119.0, 200],
# ['淘宝旗舰店', '64204150', '正常', 129.0, 300],
# ['淘宝旗舰店', 'E1204250', '正常', 129.0, 300],
# ['唯品会(OMS发货)', '71184021', '正常', 234.0, 400],
# ['淘宝旗舰店', 'E1204481', '正常', 369.0, 500]]
# sheet2 = [['店铺名称', '是否作废'], ['淘宝旗舰店', '作废'], ['淘宝旗舰店', '正常'], ['测试专卖店', '正常'], ['唯品会(OMS发货)', '正常']]
# sum_cols = ['购买单价', '金额']
# criteria_cols = ['店铺名称', '是否作废']
# # 调用函数并打印结果
# result = sumifs_excel(sheet1, sheet2, sum_cols, criteria_cols)
# print(result)
方法二:
缺点:运行速度特别慢,舍弃
你的代码中,每次循环都在进行DataFrame的查询操作,而DataFrame的查询操作是非常耗时的。特别是在你的情况下,你正在对一个很大的DataFrame进行查询,这会导致代码运行得非常慢。
import pandas as pd
def sumifs_excel(sheet1_data, sheet2_data, sum_col_names, criteria_col_names):
df1 = pd.DataFrame(sheet1_data[1:], columns=sheet1_data[0])
df2 = pd.DataFrame(sheet2_data[1:], columns=sheet2_data[0])
result = []
for index, row in df2.iterrows():
criteria = {col: row[col] for col in criteria_col_names}
sum_results = []
for sum_col_name in sum_col_names:
filter_statement = ' & '.join(['({} == "{}")'.format(key, criteria[key]) for key in criteria])
sum_result = df1.query(filter_statement)[sum_col_name].sum()
sum_results.append(sum_result)
result.append(sum_results)
for col, col_result in zip(sum_col_names, zip(*result)):
df2[col] = col_result
return df2.values.tolist()
# 示例数据
sheet1 = [['店铺名称', '款号', '是否作废', '购买单价', '金额'],
['淘宝旗舰店', '71204300', '作废', 149.0, 200],
['淘宝旗舰店', '32204100', '正常', 69.0, 100],
['测试专卖店', 'B1204300', '正常', 79.0, 150],
['淘宝旗舰店', '71204300', '正常', 119.0, 200],
['淘宝旗舰店', '64204150', '正常', 129.0, 300],
['淘宝旗舰店', 'E1204250', '正常', 129.0, 300],
['唯品会(OMS发货)', '71184021', '正常', 234.0, 400],
['淘宝旗舰店', 'E1204481', '正常', 369.0, 500]]
sheet2 = [['店铺名称', '是否作废'], ['淘宝旗舰店', '作废'], ['淘宝旗舰店', '正常'], ['测试专卖店', '正常'], ['唯品会(OMS发货)', '正常']]
sum_cols = ['购买单价', '金额']
criteria_cols = ['店铺名称', '是否作废']
# 调用函数并打印结果
result = sumifs_excel(sheet1, sheet2, sum_cols, criteria_cols)
print(result)