高发低退检查

准备工作

导入包

pip install openpyxl -i https://pypi.doubanio.com/simple
pip install pandas -i https://pypi.doubanio.com/simple

引入依赖

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string

原始数据的字段

仓库名称	客户名称	商品代码	单价	单据类型	日期	数量

数据读入

	filePath1 = './src/超级原始数据精修.xlsx'
    # 加载工作簿
    wb = load_workbook(filePath1)
    # 获取sheet页,修改第一个sheet页面为
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    #销售明细
    df0 = pd.read_excel(filePath1, sheet_name='销售明细')

复杂情况

发货可能调价

退货也会调价

退货不是一次将货物退完

配退货价格明细生成

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook

def creat_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)


if __name__ == '__main__':
    filePath1 = './src/超级原始数据.xlsx'
    # 加载工作簿
    wb = load_workbook(filePath1)
    # 获取sheet页,修改第一个sheet页面为
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    #销售明细
    df0 = pd.read_excel(filePath1, sheet_name='销售明细')
    #获取配退货数据
    df1 = df0[df0['单据类型'] == '商店配货单'].copy()
    df2 = df0[df0['单据类型'] == '商店退货单'].copy()
    #获取配退价格
    index_list = ['客户名称', '商品代码', '单价']
    value_list = ['数量']
    df12 = df1.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df22 = df2.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df12 = df12.reset_index()
    df22 = df22.reset_index()
    #获取进货价格总和
    index_list = ['客户名称', '商品代码']
    value_list = ['单价']
    df13 = df12.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df23 = df22.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df13 = df13.reset_index()
    df23 = df23.reset_index()
    #获取进退货数量
    df12['配货价格'] = df12.pop('单价')
    df12['配货数量'] = df12.pop('数量')
    df22['退货价格'] = df22.pop('单价')
    df22['退货数量'] = df22.pop('数量')
    df32 = pd.merge(df12, df22, how='right')
    # 获取进退货价格总和连接
    df13['进货价格总和'] = df13.pop('单价')
    df23['退货价格总和'] = df23.pop('单价')
    df41 = pd.merge(df13, df23, how='right')
    # 将生成的工作表导入到程序中
    filePath2 = "./src/配退货价格明细.xlsx"
    creat_excel(filePath2)
    result_sheet = pd.ExcelWriter(filePath2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet
    # df1将0转变为空
    df12 = df12.replace(0, '')
    df12.to_excel(result_sheet, "配货价格", index=False, na_rep=0, inf_rep=0)
    df22 = df22.replace(0, '')
    df22.to_excel(result_sheet, "退货价格", index=False, na_rep=0, inf_rep=0)
    df13.to_excel(result_sheet, "配货价格总和", index=False, na_rep=0, inf_rep=0)
    df23.to_excel(result_sheet, "退货价格总和", index=False, na_rep=0, inf_rep=0)
    df32.to_excel(result_sheet, "退货配货价格数量", index=False, na_rep=0, inf_rep=0)
    # 获取进退货价格总和连接
    df41.to_excel(result_sheet, "进退货价格总和连接", index=False, na_rep=0, inf_rep=0)
    # 这步不能省，否则不生成文件
    result_sheet.save()

仅生成进退货价格总和连接

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook

def creat_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)


if __name__ == '__main__':
    filePath1 = './src/超级原始数据.xlsx'
    # 加载工作簿
    wb = load_workbook(filePath1)
    # 获取sheet页,修改第一个sheet页面为
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    #销售明细
    df0 = pd.read_excel(filePath1, sheet_name='销售明细')
    #获取配退货数据
    df1 = df0[df0['单据类型'] == '商店配货单'].copy()
    df2 = df0[df0['单据类型'] == '商店退货单'].copy()
    #获取配退价格
    index_list = ['客户名称', '商品代码', '单价']
    value_list = ['数量']
    df12 = df1.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df22 = df2.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df12 = df12.reset_index()
    df22 = df22.reset_index()
    #获取进货价格总和
    index_list = ['客户名称', '商品代码']
    value_list = ['单价']
    df13 = df12.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df23 = df22.pivot_table(index=index_list, values=value_list, aggfunc='sum').copy()
    df13 = df13.reset_index()
    df23 = df23.reset_index()
    # 获取进退货价格总和连接
    df13['进货价格总和'] = df13.pop('单价')
    df23['退货价格总和'] = df23.pop('单价')
    df41 = pd.merge(df13, df23, how='right')
    # 将生成的工作表导入到程序中
    filePath2 = "./src/配退货价格.xlsx"
    creat_excel(filePath2)
    result_sheet = pd.ExcelWriter(filePath2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet
    df41.to_excel(result_sheet, "进退货价格总和连接", index=False, na_rep=0, inf_rep=0)
    # 这步不能省，否则不生成文件
    result_sheet.save()

进退货价格总和与退货工作表连接

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook

def creat_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)

def sheet_name_deal(file_path):
    wb = load_workbook(file_path)
    # 获取sheet页,修改第一个sheet页面名为销售明细
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    wb.save(file_path)


if __name__ == '__main__':
    file_path1 = './src/退货明细.xlsx'
    file_path2 = './src/配退货价格.xlsx'
    sheet_name_deal(file_path1)
    sheet_name_deal(file_path2)
    # 读入数据
    df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    df2 = pd.read_excel(file_path2, sheet_name='销售明细')
    #连接两表
    df3 = pd.merge(df1,df2,how='left')
    file_path3 = './src/高发低退检查.xlsx'
    df3.to_excel(file_path3)

计算高发低退

逻辑
已知信息
退货日期，退货商品，退货价格，关联进货单
发货商品码，发货日期，发货价格
如何判断高发低退
当前小于退货日期的最大发货日期
判断此时的发货日期的商品价格是否与退货日期的商品价格符合
如果不符合，在行末状态标注为高发低退或者低发高退。
如果正常退货，在行末标记为状态正常

示例代码

先精修数据，去除没有进货单，或者退货的数据

原始字段

仓库名称	客户名称	商品代码	单据类型	日期	单价	数量	进货价格总和	退货价格总和	差异

处理后字段

仓库名称	客户名称	商品代码	单据类型	日期	单价	数量	进货价格总和	退货价格总和	差异	单据状态

林京美高发低退检查

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook

def creat_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)

def sheet_name_deal(file_path):
    wb = load_workbook(file_path)
    # 获取sheet页,修改第一个sheet页面名为销售明细
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    wb.save(file_path)


if __name__ == '__main__':
    #数据必须预先处理，去除无进货，无退货的情况
    file_path1 = './src/林京美高发低退检查.xlsx'
    sheet_name_deal(file_path1)
    # 读入数据
    df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    #list存储商品是高发低退还是低发高退
    state_list = []
    #遍历
    commodity_order_sum = 0
    return_order_sum = 0
    group_list_issue = []
    group_list_return = []
    for row in df1.itertuples():
        #print(row)
        if getattr(row, '单据类型') == '合计':
            commodity_order_sum = commodity_order_sum+1
            for return_item in group_list_return:
                #获取退货价格
                return_value = return_item[6]
                #获取日期
                return_date = str(return_item[5])
                #进货价格
                issue_value = 0
                #进货日期
                issue_date = ''
                print(return_date)
                #print('发货日期' + issue_date + '退货日期' + return_date)
                for issue_item in reversed(group_list_issue):
                    #获取进货价格
                    issue_value = issue_item[6]
                    issue_date = str(issue_item[5])
                    if issue_date <= return_date:
                        print('发货日期'+issue_date+'退货日期'+return_date)
                        break
                if issue_value == return_value:
                    state_list.append('正常状态')
                elif issue_value < return_value:
                    state_list.append('低发高退')
                elif issue_value > return_value:
                    state_list.append('高发低退')
            for i in range(0, commodity_order_sum-return_order_sum):
                state_list.append('正常状态')
            #重置
            commodity_order_sum = 0
            return_order_sum = 0
            group_list_issue.clear()
            group_list_return.clear()
        elif getattr(row, '单据类型') == '商店退货单':
            return_order_sum = return_order_sum+1
            commodity_order_sum = commodity_order_sum + 1
            group_list_return.append(list(row))
        elif getattr(row, '单据类型') == '商店配货单':
            commodity_order_sum = commodity_order_sum + 1
            group_list_issue.append(list(row))
    file_path2 = './src/林京美处理结果.xlsx'
    df1['单据状态'] = pd.Series(state_list)
    creat_excel(file_path2)
    df1.to_excel(file_path2)

去除正常状态的商品

原始字段

仓库名称	客户名称	商品代码	单据类型	日期	单价	数量	进货价格总和	退货价格总和	差异	单据状态

处理字段

仓库名称	客户名称	商品代码	单据类型	日期	单价	数量	进货价格总和	退货价格总和	差异	单据状态	删除判断

保留异常单据

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import os
from openpyxl import Workbook


def create_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)


def sheet_name_deal(file_path):
    wb = load_workbook(file_path)
    # 获取sheet页,修改第一个sheet页面名为销售明细
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    wb.save(file_path)


# 修改元素为删除
def process(x):
    return "保留"

if __name__ == '__main__':
    file_path1 = './src/林京美处理结果.xlsx'
    sheet_name_deal(file_path1)
    # 读入数据
    df1 = pd.read_excel(file_path1, sheet_name='销售明细')
    # 投影获取，有异常的商品
    df2 = df1[(df1['单据状态'] == '高发低退') | (df1['单据状态'] == '低发高退')]
    #获取有异常的商品,唯一化
    column_list = ['客户名称', '商品代码', '单据状态']
    df3 = pd.DataFrame(df2, columns=column_list)
    df3['单据状态'] = df3['单据状态'].apply(process)
    df3['删除判断'] = df3.pop('单据状态')
    #连接
    df4 = pd.merge(df1, df3, how='left')
    # 删除需要删除的商品
    df4 = df4[df4['删除判断'] == '保留']
    file_path2 = './src/林京美最终处理结果.xlsx'
    create_excel(file_path2)
    df4.to_excel(file_path2, index=False)
    # 连接
    # 筛选出有异常的退货单