pandas统计绩效示例

临时用python写了一个基于excel订单明细，来统计BD人员绩效的代码块，里面用到了pandas中一些常用的操作，比如读写excel、移除空数据、分组查询、更改某列的数据等等。

import datetime
import pandas as pd
from openpyxl import Workbook

#明细excel文件
detail_file = ''
#统计结果文件
result_file = ''

start = datetime.datetime.now()

print('开始运行时间：'+start.strftime("%Y-%m-%d %H:%M:%S"))

detail = pd.read_excel(detail_file)

#测试用
#detail = detail.loc[detail.BD姓名=='']

print('明细数据条目数：',len(detail))

#过滤掉BD姓名为空的数据
filter_detail = detail.dropna(subset=['BD姓名'])
#下单时间取到日
filter_detail['下单时间']  = filter_detail['下单时间'].str.extract('(\d{4}[年]\d{1,2}[月]\d{1,2}[日])',expand=False)

#根据BD姓名分组聚合
group_detail = filter_detail.groupby('BD姓名')

print('BD总数：',len(group_detail))

result = []

#统计交易额
user_order_amount_map = group_detail['订单实际付款金额'].sum().to_dict();

#统计交易用户
user_enterpirse_amount_map = {}

for name,data in group_detail:
    sub_group = data.groupby(['采购商名称','供货商名称','下单时间'])
    
    count_map = {}
    
    for n1,d1 in sub_group:
        if n1[0] in count_map:
            count_map[n1[0]] = count_map[n1[0]] + 1
        else:
            count_map[n1[0]] = 1
    
    #1单数
    one_total_user = 0
    #2-3单数
    two_total_user = 0
    #4单及以上数
    three_total_user = 0
    #总数
    total_user = 0
    #交易户提成总金额
    total_user_amount =0
    
    for k,v in count_map.items():
        total_user = total_user + v
        
        if v == 1:
            one_total_user = one_total_user + 1
            total_user_amount = total_user_amount + 10
        elif v>=2 and v <=3:
            two_total_user = two_total_user + 1
            total_user_amount = total_user_amount + 20
        else:
            three_total_user = three_total_user + 1
            total_user_amount = total_user_amount + 40
            
    user_enterpirse_amount_map[name] = [one_total_user,two_total_user,three_total_user,total_user,total_user_amount]

#合并最终结果
result = []

for k,v in user_enterpirse_amount_map.items():
    item=[]
    
    item.append(k)
    
    item.extend(v)
    
    if k in user_order_amount_map:

        item.append(user_order_amount_map[k])
        item.append('{:.2f}'.format(user_order_amount_map[k]*0.005))
    else:
        item.append(0)
        item.append(0)
    
    result.append(item)

wb = Workbook(write_only=True)
ws = wb.create_sheet()

#excel表头
ws.append(['BD姓名','1单终端数据量','2-3单终端数量','4单及以上终端数量','总终端数量','交易户提成','实际付款总额','交易额提成'])

for item in result:
    ws.append(item)

wb.save(result_file)

end = datetime.datetime.now()

print('结束运行时间：'+end.strftime("%Y-%m-%d %H:%M:%S"))