python实现Excel文件读取的程序
前一段时间帮一个朋友用python写了一个读Excel程序操作的程序，具体要求为：读取两个Excel文件，根据其中某个特征的特征值对这两个文件进行取交集操作，生成三个Excel文件，第一个Excel文件为这两个文件的公共集，第二个Excel文件为第一个Excel文件除去公共项后的Excel文件，第三个Excel文件为第二个Excel文件除去公共项后的Excel文件。
最近在做一个销售价格预测的项目，数据量也是百万级别，在做这个项目的过程中又重新认识了一下python中大数据处理神器pandas模块，发现pandas对于表格类数据的处理效率非常高（以前对于pandas的理解只是停留在是一个模块包，和array、list、directory类型类似只是多了一些方法，所以不以为然，现在发现他对于表格处理内含的函数真的很多和很强大，对于大量数据的处理效率真的很高，特别适合数据挖掘领域中的特征处理），于是乎想着将这个Excel文件处理程序用pandas模块进行改进，结果效果和预期一样，之前用xlrd和xlsxwriter模块写的程序拿一个数据量30w+和一个数据量50w+的两张表格做测试耗时两个小时多（后文有介绍），现在换成pandas模块时间仅用不到五分钟（不可思议），主要耗时在与文件读取和写入，主要处理思想如下：
（1）将两表内要比较的列导出来并转换为set类型
（2）对两个set集合取交集，然后转换为list类型
（3）用pandas内置的方法将两表交集内的行找出来，并导入一张新表内。
（4）依次求出两表和其交集的差集，并导入到新表内。
总结：对于大量数据的处理，pandas就是一款神器，其功能强大，效率比自己写的for循环要高很多。
程序实现如下：点击下载
打包成单个可执行文件命令：installer -F MainInterface.py -p ExcelDealFunc.py --hidden-import ExcelDealFunc.py
# -*- coding: utf-8 -*-
import pandas as pd
import time
def exceldealfunc(filename1,filename2,filename3,filename4,filename5,str1,str2):
"""
函数名：exceldealfunc(filename1,filename2,filename3,filename4,filename5,num1,num2)
函数功能：执行表1表2取公共部分,并生成公共集表3,表1的去除公共集的表4,表2的去除公共集的表5
输入 1: filename1：读取表1的文件路径
输入 1: filename2：读取表2的文件路径
输入 1: filename3：写入表3的文件路径
输入 1: filename4：写入表4的文件路径
输入 1: filename5：写入表5的文件路径
输入 2: str1：表1要比较的列号名
输入 2: str2：表2要比较的列号名
输出 1: 无
其他说明：无
"""
start=time.time()
table1=pd.read_excel(filename1) # 读取表1
print("Time:%d s,Read Table1 Successful!"%(time.time()-start))
table2=pd.read_excel(filename2) # 读取表2
print("Time:%d s,Read Table2 Successful!"%(time.time()-start))
print()
set1=set(table1[str1]) # 将表1要比较的列转换为集合格式(集合1)
print("Time:%d s,File1 turn into a set Successful!"%(time.time()-start))
set2=set(table2[str2]) # 将表2要比较的列转换为集合格式(集合2)
print("Time:%d s,File2 turn into a set Successful!"%(time.time()-start))
print()
set3=set1 & set2 # 取集合1和集合2的交集set3
print("Time:%d s,set3 = set1 & set2 Successful!"%(time.time()-start))
list3=list(set3) # 将set3转换为列表的格式
print("Time:%d s,set3 turn to a list Successful!"%(time.time()-start))
table1[table1[str1].isin(list3)].to_excel(filename3,index=False,) # 将交集保存到表3
print("Time:%d s,Write to Table3 Successful!"%(time.time()-start))
print()
list4=list(set1 - set3) # 取集合1和交集的差集,并转换为列表格式
print("Time:%d s,(set1 - set3) turn to a list Successful!"%(time.time()-start))
table1[table1[str1].isin(list4)].to_excel(filename4,index=False,) # 将差集1保存到表4
print("Time:%d s,Write to Table4 Successful!"%(time.time()-start))
print()
list5=list(set2 - set3) # 取集合2和交集的差集,并转换为列表格式
print("Time:%d s,(set2 - set3) turn to a list Successful!"%(time.time()-start))
table2[table2[str2].isin(list5)].to_excel(filename5,index=False,) # 将差集2保存到表5
print("Time:%d s,Write to Table5 Successful!"%(time.time()-start))
print()
print("All task finish! Using Time:%d s."%(time.time()-start))
由于朋友所需要处理的数据量比较大，给我的是一个30w的数据集和一个50w的数据集小样本，这里记录一下我碰到的两个坑：
（1）xlwt模块保存的文件格式是xls，最多只能够写入65535行列256列，如果行数或列数过多就会报错。解决方法有两种，第一种是继续使用该模块，每65535行后或者256列后就新建一张sheet，将超过的数据保存到新sheet中；第二种方法是使用xlsxwriter模块，xlsxwriter模块保存的文件格式是xlsx，能够最多写入1048576行65535列，如果数据量还是过大只能用在该基础上再用第一种方法了。笔者用的是第二种。
（2）python列表中对列表直接操作速度会明显快于对列表索引操作，所以变成实现时尽量多用python内置列表操作（如a in list或者list=list1&list2等等），笔者一开始为了变成方便在其中用了列表索引操作，结果从晚上6点半运行到晚上11半都没出结果，第二天早上来实验室才发现运行完毕，将代码中对列表索引操作改成了对list操作后再次运行，结果只运行了两个小时。
程序界面说明：（后文附源码与GitHub链接）
这款软件只要选好两个Excel文件，以及对应要比较的列号（从0开始计数），然后设定好要存放生成的Excel文件地址与文件名然后直接点击开始就可以了。
重置按钮只是在运行完一次后激活开始按钮的作用。
笔者电脑配置如下：
测试了数据量分别为50w+和30w+的两张表，用时是2个小时左右。
下面直接pose出源代码：
GitHub下载地址：MyGitHub链接
文件1：GUI窗口
# -*- coding: utf-8 -*-
from tkinter import *
from tkinter.filedialog import askdirectory
from tkinter.filedialog import askopenfilename
from ExcelDealFunc import exceldealfunc
import os
class MyMainFace(object):
"""主界面类"""
def __init__(self):
"""
函数名：__init__(self)
函数功能：MyMainFace类的构造函数,界面组件均在此构造出的
输入 1: MyMainFace类的对象,自身,无需输入
输出 1: 无
其他说明：无
"""
self.root = Tk()
self.root.title('Excel处理程序')
# 请选择目标路径
Label(self.root,text = "请选择目标路径：",fg="red").grid(row = 0, column = 0,sticky="w")
Label(self.root,text = "表1目标路径:").grid(row = 1, column = 0,sticky="e")
self.path1 = StringVar()
Entry(self.root, width=60,textvariable = self.path1,state='readonly').grid(row = 1, column = 1,columnspan=3)
Button(self.root, text = "路径选择", command = self.selectPath1).grid(row = 1, column = 4)
self.num1 = StringVar()
Label(self.root,text = " 比较字段的ID").grid(row = 1, column = 5,sticky="e")
Entry(self.root, width=5,textvariable = self.num1).grid(row = 1, column = 6)
Label(self.root,text = "表2目标路径:").grid(row = 2, column = 0,sticky="e")
self.path2 = StringVar()
Entry(self.root,width=60,textvariable = self.path2,state='readonly').grid(row = 2, column = 1,columnspan=3)
Button(self.root, text = "路径选择", command = self.selectPath2).grid(row = 2, column = 4)
self.num2 = StringVar()
Label(self.root,text = " 比较字段的ID").grid(row = 2, column = 5,sticky="e")
Entry(self.root, width=5,textvariable = self.num2).grid(row = 2, column = 6)
# 请选择生成表格路径
Label(self.root,text = "请选择生成表格路径：",fg="red").grid(row = 4, column = 0,sticky="w")
Label(self.root,text = "表格存放路径：").grid(row = 5, column = 0,sticky="e")
self.path3 = StringVar()
Entry(self.root,width=60,textvariable = self.path3,state='readonly').grid(row = 5, column = 1,columnspan=3)
Button(self.root, text = "路径选择", command = self.selectPath3).grid(row = 5, column = 4)
# 请输入生成表格名称
Label(self.root,text = "请输入生成表格名称：",fg="red").grid(row = 6, column = 0,sticky="w")
Label(self.root,text = "表1表2的交集表的表名：").grid(row = 7, column = 0,sticky="e")
self.name1 = StringVar()
Entry(self.root, textvariable = self.name1).grid(row = 7, column = 1,sticky="w")
Label(self.root,text = "表1去除交集后的表名:").grid(row = 8, column = 0,sticky="e")
self.name2 = StringVar()
Entry(self.root, textvariable = self.name2).grid(row = 8, column = 1,sticky="w")
Label(self.root,text = "表2去除交集后的表名:").grid(row = 9, column = 0,sticky="e")
self.name3 = StringVar()
Entry(self.root, textvariable = self.name3).grid(row = 9, column = 1,sticky="w")
self.labeltxt=StringVar()
self.labeltxt.set(" ")
Label(self.root,textvariable = self.labeltxt,fg="red").grid(row = 7, column = 3)
Label(self.root,text = "以表1为参考",fg="red").grid(row = 7, column = 2)
self.var = StringVar()
self.var.set("开始")
self.button = Button(self.root,textvariable = self.var,command = self.start, width = 5)
self.button.grid(row = 8,column = 2,padx = 5)
self.var2 = StringVar()
self.var2.set("重置")
self.button2 = Button(self.root,textvariable = self.var2,command = self.reset, width = 5)
self.button2.grid(row = 9,column = 3,padx = 5)
# 创建一个背景色为白色的矩形
self.canvas = Canvas(self.root,width = 170,height = 26,bg = "white")
# 创建一个矩形外边框（距离左边,距离顶部,矩形宽度,矩形高度）,线型宽度，颜色
self.out_line = self.canvas.create_rectangle(2,2,180,27,width = 1,outline = "black")
self.canvas.grid(row = 8,column = 3,ipadx = 5)
self.root.mainloop()
def start(self):
"""
函数名：start(self)
函数功能：开始按钮的功能函数
输入 1: MyMainFace类的对象,自身
输出 1: 无
其他说明：无
"""
if self.path1.get():
filename1=self.path1.get()
else:
self.labeltxt.set("请选择好表1")
return
if self.path2.get():
filename2=self.path2.get()
else:
self.labeltxt.set("请选择好表2")
return
if self.path3.get():
filename=self.path3.get()
else:
filename="data"
if self.name1.get():
filename3=filename+"/"+self.name1.get()+".xlsx"
else:
filename3=filename+"/Table1_Table2.xlsx"
if self.name2.get():
filename4=filename+"/"+self.name2.get()+".xlsx"
else:
filename4=filename+"/Table1_del.xlsx"
if self.name3.get():
filename5=filename+"/"+self.name3.get()+".xlsx"
else:
filename5=filename+"/Table2_del.xlsx"
if self.num1.get():
num1=int(self.num1.get())
else:
self.labeltxt.set("请选择好num1")
return
if self.num2.get():
num2=int(self.num2.get())
else:
self.labeltxt.set("请选择好num2")
return
self.button.config(state="disable") # 关闭按钮1功能
self.root.withdraw()
os.system('cls')
print("正在运行中请稍等...")
exceldealfunc(filename1,filename2,filename3,filename4,filename5,num1,num2)
self.root.deiconify()
self.labeltxt.set("Finally")
def scheduleshow(self,i):
fill_line = self.canvas.create_rectangle(2,2,0,27,width = 0,fill = "blue")
self.canvas.coords(fill_line, (0, 0, 180*i, 30))
self.var.set(str(round(100*i,1))+"%")
self.root.update()
def reset(self):
"""
函数名：reset(self)
函数功能：重置按钮的功能函数
输入 1: MyMainFace类的对象,自身
输出 1: 无
其他说明：无
"""
self.button.config(state="active") # 激活按钮1
fill_line = self.canvas.create_rectangle(2,2,0,27,width = 0,fill = "white")
self.var.set("开始")
self.labeltxt.set(" ")
self.canvas.coords(fill_line, (0, 0, 181, 30))
self.root.update()
def selectPath1(self):
"""
函数名：selectPath1(self)
函数功能：选择路径1按钮的功能函数
输入 1: MyMainFace类的对象,自身
输出 1: 无
其他说明：无
"""
path_ = askopenfilename(filetypes = [('Excel', '*.xls*')])
self.path1.set(path_)
def selectPath2(self):
"""
函数名：selectPath2(self)
函数功能：选择路径2按钮的功能函数
输入 1: MyMainFace类的对象,自身
输出 1: 无
其他说明：无
"""
path_ = askopenfilename(filetypes = [('Excel', '*.xls*')])
self.path2.set(path_)
def selectPath3(self):
"""
函数名：selectPath3(self)
函数功能：选择路径3按钮的功能函数
输入 1: MyMainFace类的对象,自身
输出 1: 无
其他说明：无
"""
path_ = askdirectory()
self.path3.set(path_)
##############################程序入口#########################################
if __name__=="__main__":
MyMainFace()
文件2：Excel文件处理模块
# -*- coding: utf-8 -*-
import xlrd,xlsxwriter
import sys
def writeoneline(table_from,table_to,line,nrows):
"""
函数名：writeoneline(table_from,table_to,line,nrows)
函数功能：读取table_from的第line行数据写入到table_to的第nrows行
输入1: table_from：从该表内读取要写入的数据
输入2: table_to：向该表内添加一行数据
输入3: line：table_from内要读取数据的行数
输入4: nrows：table_to内要写入数据的行数
输出1: 无
其他说明：无
"""
write_data=table_from.row_values(line)
for i in range(len(write_data)):
table_to.write(nrows,i,write_data[i])
def readdata(filename,num):
"""
函数名：readdata(filename,num)
函数功能：通过文件路径,打开Excel文件,读取sheet
输入 1: filename：读取表的文件路径
输入 2: num：要比较的列号
输出 1: table：sheet表信息
输出 2: nrows：该Excel表的行数
输出 3: count：count要比较列的数据列表
其他说明：无
"""
data = xlrd.open_workbook(filename)
table = data.sheets()[0]
nrows = table.nrows
count=table.col_values(num)
return table,nrows,count
def exceldealfunc(filename1,filename2,filename3,filename4,filename5,num1,num2):
"""
函数名：exceldealfunc(filename1,filename2,filename3,filename4,filename5,num1,num2)
函数功能：执行表1表2取公共部分,并生成公共集表3,表1的去除公共集的表4,表2的去除公共集的表5
输入 1: filename1：读取表1的文件路径
输入 1: filename2：读取表2的文件路径
输入 1: filename3：写入表3的文件路径
输入 1: filename4：写入表4的文件路径
输入 1: filename5：写入表5的文件路径
输入 2: num1：表1要比较的列号
输入 2: num2：表2要比较的列号
输出 1: 无
其他说明：无
"""
table1,nrows1,count1=readdata(filename1,num1)
print("Read Table1 Successful!")
table2,nrows2,count2=readdata(filename2,num2)
print("Read Table2 Successful!")
data3 = xlsxwriter.Workbook(filename3) # 共同用户样本
table3 = data3.add_worksheet()
data4 = xlsxwriter.Workbook(filename4) # Table1处理后的样本Table4
table4 = data4.add_worksheet()
data5 = xlsxwriter.Workbook(filename5) # Table2处理后的样本Table5
table5 = data5.add_worksheet()
writeoneline(table2,table3,0,0)
writeoneline(table1,table4,0,0)
writeoneline(table2,table5,0,0)
nrows3=1
nrows4=1
nrows5=1
for i in range(1,nrows1):
if count1[i] in count2:
writeoneline(table1,table3,i,nrows3) # Table1写入共同用户样本Table3
nrows3+=1
else:
writeoneline(table1,table4,i,nrows4) # Table1写入处理后的样本Table4
nrows4+=1
if i%10000==0:
done=i/(nrows1+nrows2)
sys.stdout.write("\r[%s%s] %d%%" % ('█'*int(40*done),' '*(40-int(40*done)),int(100*done)))
sys.stdout.flush()
data3.close()
print("Write to Table3 Successful!")
data4.close()
print("Write to Table4 Successful!")
done=nrows2/(nrows1+nrows2)
sys.stdout.write("\r[%s%s] %d%%" % ('█'*int(40*done),' '*(40-int(40*done)),int(100*done)))
sys.stdout.flush()
for i in range(1,nrows2):
if count2[i] not in count1:
writeoneline(table2,table5,i,nrows5) # table2写入处理后的样本Table5
nrows5+=1
if i%10000==0:
done=(i+nrows2)/(nrows1+nrows2)
sys.stdout.write("\r[%s%s] %d%%" % ('█'*int(40*done),' '*(40-int(40*done)),int(100*done)))
sys.stdout.flush()
data5.close()
print("Write to Table5 Successful!")
5881
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
