在我們使用excel的時(shí)候,一些數(shù)據(jù)的計(jì)算我們會(huì)用到excel自帶的函數(shù)來(lái)計(jì)算,但有些時(shí)候這些函數(shù)并不是那么好用,這時(shí)候我們可以換一種思路——使用pandas核算excel數(shù)據(jù)。那么怎么使用pandas直接核算excel中的快遞費(fèi)用呢,接下來(lái)的這篇文章帶你了解。
一、確定核算規(guī)則
二、根據(jù)核算規(guī)則編寫代碼,生成核算列
# -*- 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 ["內(nèi)蒙古","甘肅","寧夏","青海"]:
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("請(qǐng)輸入文件路徑:")
sheet_name = input("請(qǐng)輸入工作簿名稱:")
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["最新核算結(jié)果"] = 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+"最新核算結(jié)果"+".xlsx"))
三、輸入賬單,進(jìn)行核算。
在腳本文件目錄中執(zhí)行pyinstaller -F hesuan.py 進(jìn)行打包exe文件,如果為安裝pyinstaller,使用pip install pyinstaller 安裝。點(diǎn)擊運(yùn)行打包后的exe文件,輸入文件的路徑名和sheet名,就可以進(jìn)行自動(dòng)核算,腳本運(yùn)行完成后會(huì)自動(dòng)保存一個(gè)新的Excel文件。
到此這篇pandas核算excel數(shù)據(jù)的文章就介紹到這了,希望能給大家?guī)?lái)一個(gè)參考,也希望大家以后多多支持W3Cschool!