Python操作Excel
1.读取文件
1.1 pandas直接读取
1.1.1 读取excel
字典形式导入
src1 = {
'file': r'd:\user\医药销售09月薪资考核数据1010.xlsx',
'sht': 'Sheet1',
'cols': 'AE,AF,I,J,K,Z',
'names': ['客户名称', 'yj账号', 'yj网点', '上期逾期期末未回款金额', '工资月份', '大区'],
'colsorder': ['工资月份', '大区', '客户名称', 'yj账号', 'yj网点', '上期逾期期末未回款金额']
}
src1['data'] = pd.read_excel(src1['file'], sheet_name=src1['sht'], header=None, skiprows=1, usecols=src1['cols'], names=src1['names'])
1.1.2 读取csv
类似于导入excel，需注意encoding = ‘’utf-8’ 或 encoding = ‘’gbk’
df = pd.read_csv(file, encoding='utf-8', usecols=cols, low_memory=False)
1.1.3 读取文件夹
os.chdir(r'd:\user\桌面\BP-js12.7')
extension = 'csv'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
#combine all files in the list
js = pd.concat([pd.read_csv(f, encoding='gbk', low_memory=False) for f in all_filenames ])
1.2 模块式读取，主程序调用
思路：现有2张表，读入过程用函数封装，以模块形式调用。
具体做法：
①建立2个py文件，分别定义函数get_jsdf， get_bcmddf；
②将这2个文件（模块）放入同一文件夹《hkhsm》下，分别命名为importm_***。
》 导入jsdf，命名为importm_js.py
import os
import pandas as pd
import numpy as np
def get_jsdf(jsdir):
files = getfiles(jsdir, '.csv')
print(files)
file = files[0]
df = getdf(file)
for f in files[1:]:
newdf = getdf(f)
df = pd.concat([df, newdf])
return df
def getdf(file):
print(file)
df = pd.read_csv(file, encoding='gbk', low_memory=False)
df = df[df['大区'].isin(['ws大区', 'ky部', 'Tx']) & ~df['地区名称'].isin(['kyab区', 'kycd区'])]
#一些数据处理过程
df['yj账号'] = pd.to_numeric(df['yj账号'], errors='coerce').fillna(df['js账号'])
df['yj账号'] = df['yj账号'].astype(np.int64)
df['未收(未付)-本位币'] = df['未收(未付)-本位币'].apply(lambda x: 0.0 if x < 0.0 else x)
return df
def getfiles(path, extension):
list = os.listdir(path)
result = []
for i in range(0, len(list)):
temp = os.path.join(path, list[i])
if os.path.isfile(temp) and os.path.splitext(temp)[1] == extension:
result.append(temp)
return result
if __name__ == '__main__':
jsdir = r'd:\user\桌面\hkhs原始数据1\BP-js'
df = get_jsdf(jsdir)
# df = pd.read_pickle('./js.pkl')
print(df.shape)
print(df)
print(df.info())
》 导入bcmd，命名为importm_bcmd.py
import pandas as pd
import os
def get_bcmddf(path, shortname):
file = os.path.join(path, shortname)
md = pd.read_excel(file, usecols='A')
return md
if __name__ == '__main__':
bcmd = get_bcmddf(r'd:\user\桌面\货款回收原始数据1', '补充名单.xlsx')
print(bcmd.info())
print(bcmd)
》建立一个关联py，命名为import_.py，将3张表导入
from .importm_js import get_jsdf
from .importm_bcmd import get_bcmddf
》在主程序调用模块
import hkhsm.import_ as im
》在主程序确定路径
》在主程序导入
# 货款回收目录
mydir = r'd:\user\桌面\hkhs原始数据1'
# 结算bp（csv文件夹）
jsdir = os.path.join(mydir, 'BP-js')
jsdf = im.get_jsdf(jsdir)
# bcmd（excel文件）
bcmd = im.get_bcmddf(mydir, 'bcmd.xlsx')
# 2.导出文件
## xlwings导出，可自动打开
```python
book = xw.Book()
book.sheets(1).range('A1').options(index=False).value = df
1.3 tkiner选择读取
单个文件
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
file_path = filedialog.askopenfilename()
获取多个文件
- filedialog.askopenfilenames()，返回一个包含多个文件路径的元组（tuple）。通过迭代来对每个文件操作，以实现批处理。
- 批量读一个文件内容，然后写入另外一个文件中
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
file_path = filedialog.askopenfilenames()
for f in file_path:
fo = f.split('.')[0]+'.csv'
with open(fo,'w') as foo:
with open(f,'r') as fn:
fn.readline()
for line in fn.readlines():
li = line.strip().split()
foo.write('%f,%f\n'%(float(li[1]),float(li[0])))
print(li)
1.4 pd创建dataframe
df = pd.DataFrame(columns=['数量', '金额'], index=['配送费', '1-20日', '20日后日均', '仓库操作费', '小计'])
df['数量'] = [ps_num, beforetwenty_num, aftertwenty_num, warehouse_operation_num, sum]
df['金额'] = [ps_bill, beforetwenty_bill, aftertwenty_bill, warehouse_operation_bill, Sum_bill]
2.写入文件
2.1 pandas写入工作簿多个工作表
xlsx = pd.ExcelWriter(r"d:\user\桌面\yy.xlsx")
table6.to_excel(xlsx, sheet_name='当期数据明细', index=False)
table1.to_excel(xlsx, sheet_name='明细1', index=False)
table3.to_excel(xlsx, sheet_name='明细2', index=False)
table5.to_excel(xlsx, sheet_name='明细3', index=False)
xlsx.close()
2.2 xlwings写入
2.2.1 写入已有工作簿多个sheet
s3 = table
s3 = pd.DataFrame(s3)
wd = xw.Book(r'E:\模板.xlsm')
sht = wb.sheets('py明细')
sht = wb.sheets('第一')
sht.range('A1').value = s3
wb.save()
2.2.2 单个写入
book = xw.Book()
book.sheets(1).range('A1').options(index=False).value = table
2.2.3 思路：带格式–创建模板，复制模板写入
bill_dir = './在途账单'
template = './Template.xlsx'
dest = os.path.join(os.path.dirname(template), "综合物流.xlsx")
shutil.copyfile(template, dest)
sht = xw.Book(dest).sheets('Sheet1')
#精确到写入某个单元格
count = 0
start_row = 2 # 从0开始
start_column = 2 # 从0开始
sht[start_row - 2, start_column + count*2].value = dq
sht[start_row - 1, start_column + count*2].value = "数量"
sht[start_row - 1, start_column + count*2 + 1].value = '金额'
3.常用数据清洗与处理
3.1 df查看信息
print(df)
print(df.shape)
print(df.info())
print(df.head())
3.2 列数据筛选（导入时）
3.3 列数据处理
3.3.1 df挑选要展示的列
cols2 = ['地区', 'yj账号', 'lxr']
dfcjb2 = dfcjb[cols2]
3.3.2 修改df列名
df.rename(columns={'kh卡号': 'yj账号'}, inplace=True)
3.3.3 df增加一列
#工资一列填为9月
s1['工资'] = '9月'
#增加一列为空
s1['当期回款金额'] = None
3.3.4 split()分列/map()，list[:]分列（取某几位）
#将转至组织中'/'之前的内容填入新增的一列工号1
s1['工号1'] = s1['转至组织'].astype(str).apply(lambda x: x.split('/')[0])
s2 = s1[s1['日期'].map(lambda x: x[-2:]) <= '20']
# 取结算账号前3位
sxty['结算3'] = [x[:3] for x in (sxty['结算账号'])]
3.3.5 函数（def，lambda）–A\B列作为条件，填充C列
例子：如果【用户类型】=专席且【转办状态】已转线 和任务分发，则【归属】填为坐席；
否则，归属=原用户类型。
方法一
def function(a, b):
if a == '专席' and b in ['已转线', '任务分发']:
return '坐席'
elif a == '专席' and b not in ['已转线', '任务分发']:
return '专席'
else:
return '坐席'
s1['归属'] = s1.apply(lambda x: function(x.用户类型, x.转办状态), axis=1)
方法二
df['归属'] = df.apply(lambda x: '坐席' if (x['用户类型'] == '专席') and (x['转办状态'] in ['已转线', '任务分发']) else x['用户类型'], axis=1)
3.3.6 函数（lambda）–小数转换为百分比
data1['比率1'] = data1['比率1'].apply(lambda x: format(x, '.2%'))
3.3.7 数字转为十位文本格式
# data['yj账号'] = '0' * (10-len(data['yj账号'])) + data['yj账号']
for i in data1.index:
if len(data1.at[i,'yj账号']) < 10:
data1.at[i, 'yj账号'] = '0' * (10 - len(data1.at[i,'yj账号'])) + data1.at[i, 'yj账号']
3.3.8 numpy/df-文本转为int类型
df['yj账号'] = df['yj账号'].astype(np.int64)
df['yj账号'] = int(df['yj账号'])
3.3.9 .loc()–A列值=B列值
sxdl.loc[:, '结算账号'] = sxdl.loc[:, '客户卡号']
3.3.10 如果A列为特定值，在B列填为特定值
# 列结算账号 为'0000000003'的，列结算地区 填为安徽区
sxty.loc[sxty.结算账号 == '0000000003', '结算地区'] = '安徽区'
3.4 行数据筛选
方法一 筛选出df：A列某值和B列某值
data1 = data1[(data1['工资月份'] == '10月') & (data1['大区'] == 'ws')]
方法二 筛选出df：A列某些值和不要B列某些值
df = df[df['大区'].isin(['ws', 'ky部', 'TS']) & ~df['地区名称'].isin(['kyhx区', 'kygm区'])]
3.4.1 去重
customer = customer.drop_duplicates('月结账号', keep='first')
3.4.2 ceil() —0.5向上进制
# 0.2进位0.5,1.6进位2.0
import math
def NumTrans(x):
return (math.ceil(x/0.5))*0.5
print(NumTrans(5.792))
3.4.3 思路：四舍五入 2378进制
# a.10KG以下的以0.1KG为制度，第二位及以后的小数采用四舍五入进位（1KG以下按照1KG计算）；
# b.10-100KG以0.5KG为进制，采用2378制（2退3进7退8进），第二位及以后小数采用四舍五入进位；
# c.100KG以上，以1KG为制度，四舍五入取整。最后保留一位小数。
def XL(l):
if l < 10:
return round((l*10)+0.5)/10
elif l < 100:
return round(((l*10)+0.5)/10*2, 0)/2
else:
return round(((l*10)+0.5)/10, 0)
3.4.3 lambda-将＜0值填为0
df['未收(未付)-本位币'] = df['未收(未付)-本位币'].apply(lambda x: 0.0 if x < 0.0 else x)
3.4.4 df s2筛选后，s3为s1剩余部分
#文件s1 筛选：应付金额大于0，增值费用为运费
#分为s2(每月1-20日) s3（20日后）
s1 = s0[(s0['应付金额'] >= 0) & (s0['增值费用'] == '运费')]
s2 = s1[s1['日期'].map(lambda x: x[-2:]) <= '20']
s3 = s1[(~s1.isin(s2))]
s3 = s3[~s3['日期'].fillna('null').isin(['null'])]
3.5 空值处理
3.5.1 空值填为0
cc['sdcw'] = cc['sdcw'].fillna(0)
table2['未回款'] = table2['未回款'].apply(lambda x: 0.0 if numpy.isnan(x) else x)
3.5.2 若为空值则填为入另一列对应的值
df['yj账号'] = pd.to_numeric(df['yj账号'], errors='coerce').fillna(df['js账号'])
4.数据透视&分组
4.1.1 pd.pivot
s2 = pd.pivot_table(s1, index=['考核', '跟进区', '客户名称', 'js账号', 'js网点'], values=['应收应付-CNY', '已核销金额-CNY', '未收(未付)-CNY'], aggfunc=np.sum)
s2 = s2.reset_index()
4.1.2 group by
dq_groups = bp3.groupby(['地区'])
ys = dq_groups['应收(应付)-本位币'].sum()/10000
ye = dq_groups['未收(未付)-本位币'].sum()/10000
pt_dq = pd.DataFrame({'应收金额': ys, '应收余额': ye})
例子：
方法一：
方法二：
4.1.3 思路：添加小计
4.1.4 思路：数据透视变形
5.df之间
5.1 pd.merge 匹配
bp1 = pd.merge(bpdf, lxr1, on=['yj账号'], how='left', validate="m:1")
table2 = pd.merge(s4, s5, on=['yj账号', 'yj网点'], how='left', validate="m:1")
table6 = data1.merge(customer, how='left', left_on='yj账号', right_on='yj号', validate='many_to_one')
5.1.1 思路：A表匹，匹不到用B表
#表sxdl与表wddm1匹配，匹配不到的填为-1
sxdl = pd.merge(sxdl, wddm1, how='left', left_on='所属区域代码', right_on='区部代码', validate='many_to_one')
sxdl['区部名称'] = sxdl['区部名称'].fillna(-1)
print(sxdl.shape)
#遍历，匹配不到的使用表wddm2匹
for i in sxdl.index:
if sxdl.at[i, '区部名称'] == -1:
wd3 = sxdl.at[i, '网点']
dfwddm = wddm2[wddm2['网点'] == wd3]
if dfwddm.shape[0] > 0:
sxdl.at[i, '区部名称'] = dfwddm.iat[0, 1]
sxdl.at[i, '大区名称'] = dfwddm.iat[0, 2]
5.2 pd.concat()纵向合并两df
jsdf = jsdf
ywdf = ywdf
bpdf = pd.concat([jsdf, ywdf])
5.3 两表根据关联列，A表中剔除/保留含B表列
jsdf = jsdf[~jsdf['月结账号'].isin(tcmd['子账号'].values)]
jsdf = jsdf[jsdf['月结账号'].isin(tcmd['子账号'].values)]
6 python链接Mysql
import pandas as pd
from sqlalchemy import create_engine
# lyhk_sql_sentence 用于存储语句，见下方
from lyhk_sql_sentence import *
# 链接MySQL 用户名root 密码1111 网络3306 数据库p&mrecovery
def get_engine():
engine = create_engine('mysql+pymysql://root:1111@localhost:3306/p&mrecovery')
return engine
# 将读取的dataframe写入mysql
def to_sql(excel_name, table_name):
engine = get_engine()
excel_name.to_sql(table_name, engine, index=False, if_exists='replace')
# 将mysql中处理好数据写回df
def read_sql(sql_sen):
engine = get_engine()
table = pd.read_sql_query(sql_sen, engine)
return table
if __name__ == '__main__':
to_sql(jcb, 'jcb')
to_sql(jsyw, 'jsyw')
mx = read_sql(sql_mx())
lyhk_sql_sentence.py
def sql_mx():
mx ='''
SELECT
`jszh`,`yjzh`
FROM jsyw
GROUP BY
`jszh`, `yjzh`
'''
return mx
没什么用的小技巧
运行计时
#导入库后插入
import time
time_start = time.time()
#末尾插入
time_end = time.time()
print('Time cost = %fs' % (time_end - time_start))
程序内进度条
import time
def ProgressBar(num):
"""
:param num: 传入进度条总量值
:return:
"""
total = 20
if num == 0:
print('传入参数不可为0，仅接受int类型')
elif type(num) != int:
print('传入参数仅接受int类型')
else:
for item in range(num + 1):
test_num = int((item / num) * 100) # 当前百分比
step = int(test_num / (100 / total)) # 一个‘#’的在100% 内的占比
now = r"[ %s%s ]%s" % ("#" * step, ' ' * (total - step), str(test_num))
sys.stdout.write("\r%s%%" % now)
sys.stdout.flush()
time.sleep(0.1)
----------------
ProgressBar(100)
4515
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
