教你使用Pandas直接核算Excel中的快递费用

一、确定核算规则

二、根据核算规则编写代码，生成核算列

# -*- coding:utf-8 -*- 
import pandas as pd 
from math import ceil
import os

def account(adress,weight):
    
    if adress == \"湖南\":
        if weight <= 3:
            totel = 2.5
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*1
        else:
            totel = ceil(weight)*1
        return totel
            
    elif adress in [\"河北\",\"天津\",\"山西\",\"浙江\",\"江苏\",\"安徽\",\"福建\",\"山东\",\"江西\",\"广东\",\"广西\",\"河南\",\"湖北\",\"陕西\",\"四川\",\"重庆\",\"云南\",\"贵州\"]:
        if weight <= 3:
            totel = 2.5
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*1
        else:
            totel = ceil(weight)*2 
        return totel
    
    elif adress in [\"深圳\",\"北京\",\"上海\"]:
        if weight <= 3:
            totel = 3.3
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*1.5
        else:
            totel = ceil(weight)*2
        return totel
            
    elif adress in [\"海南\",\"辽宁\",\"黑龙江\",\"吉林\"]:
        if weight <= 3:
            totel = 2.5
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*2.5
        else:
            totel = ceil(weight)*3
        return totel
    elif adress in [\"内蒙古\",\"甘肃\",\"宁夏\",\"青海\"]:
        if  weight <= 1:
            totel = 9
        else:
            totel = 9 + ceil(weight-1)*6 
        return totel
    elif adress == \"新疆\":
        if  weight <= 1:
            totel = 15
        else:
            totel = 15 + ceil(weight-1)*12 
        return totel
    elif adress == \"西藏\":
        if  weight <= 1:
            totel = 16
        else:
            totel = 15 + ceil(weight-1)*18 
        return totel    
    else:
        print(\"你输入的省份不合法！！！\")
    
file_path = input(\"请输入文件路径：\")   
sheet_name = input(\"请输入工作簿名称：\")
pf = pd.read_excel(file_path,sheet_name=sheet_name)
#获取省份一列
pro = pf[\"省份\"].values.tolist()
#获取重量一列
wt = pf[\"重量\"].values.tolist()
#核算列
totel = []
for p,w in zip(pro,wt):
    print(p,w)
    totel.append(account(p,w))

pf[\"最新核算结果\"] = totel
file_name = os.path.basename(file_path)
pf.to_excel(os.path.join(os.path.dirname(file_path),os.path.basename(file_path).split(\".\")[0]+sheet_name+\"最新核算结果\"+\".xlsx\"))

三、输入账单，进行核算。

在脚本文件目录中执行pyinstaller -F hesuan.py 进行打包exe文件，如果为安装pyinstaller,使用pip install pyinstaller 安装。点击运行打包后的exe文件，输入文件的路径名和sheet名，就可以进行自动核算，脚本运行完成后会自动保存一个新的Excel文件。