利用Python pandas对Excel进行合并的方法示例

时间：2020-12-02 数据分析查看: 1038

前言

在网上找了很多Python处理Excel的方法和代码，都不是很尽人意，所以自己综合网上各位大佬的方法，自己进行了优化，具体的代码如下。

博主也是新手一枚，代码肯定有很多需要优化的地方，欢迎各位大佬提出建议~

代码我自己已经用了一段时间，可以直接拿去用

主要功能

按行合并，即保留固定的表头（如前几行），实现多个Excel相同格式相同名字的表单按纵轴合并；
按列合并。即保留固定的首列，实现多个Excel相同格式相同名字的表单按横轴合并；
表单集成，实现不同Excel中相同sheet的集成（即不汇总，仅集成到同一个新的Excel中）。此处的代码稍微改一下即可实现不同Excel中所有sheet的集成；
自动检测所需合并的sheet名称是否出现在所有的目标文件中，如果不是则予以提示；
sheet选择、表头选择、功能选择界面实现可视化；
合并后进行简单的缺省值处理、格式处理
解决MacOS系统下文件目录中出现.DS_Store隐藏文件导致程序出错的bug。

用到的库

pandas 、tkinter 、 pathlib、os 、 xlrd

代码

import pandas as pd
import tkinter as tk
from tkinter import filedialog
import pathlib
import os
import xlrd

# 选择文件夹对话框，窗口交互，打开选择窗口
filedirectory = filedialog.askdirectory()
p1 = pathlib.Path(filedirectory) # 该部分主要为了获取目标路径下的文件名
print(p1)
bookname1 = os.listdir(p1) # 返回目标文件夹下的所有文件名
if ".DS_Store" in bookname1:
 bookname1.remove('.DS_Store')

# 删除文件名里的.xlsx
bookname = []
for n in bookname1:
 n1 = list(n) # 把字符变成列表
 for i in range(5): # 因为去除的是.XSLX，5个字符，可以根据实际需要修改
  n1.pop() # 依次删除最后一个元素
 n2 = ''.join(n1) # 把列表变成字符
 bookname.append(n2)

excles = p1.rglob('*.xlsx') # 类似于os.work，能够返回目标路径下的文件路径，并且可以添加条件
excelarr = []
for eachexcel in excles:
 excelarr.append(eachexcel) # 创建目标路径下特定文件名的列表

# 建立一个交互窗口
windows = tk.Tk()
windows.title('请提供如下信息') # 设置文本框的标题
windows.geometry('1000x300') # 设置界面的大小
# tk.Label(windows, text='你好！this is Tkinter', bg='green', font=('Arial', 12), width=30, height=2)
# 说明：bg为背景，font为字体，width为长，height为高，这里的长和高是字符的长和高，比如height=2,就是标签有2个字符这么高)
tk.Label(windows, text='请输入想要合并的sheet名称：').grid(row=0, column=0) # label用来显示不可编辑的文本和图标（提示性文字）
tk.Label(windows, text='请输入想要确定的表头行数：').grid(row=1, column=0)
tk.Label(windows, text='请输入您想实现的功能，1为按行合并sheet，2为按列合并sheet，3为sheet汇总：').grid(row=2, column=0)
# Listbox(dict={}) # 创建可选下拉框
e1 = tk.Entry(windows) # 创建输入框
e2 = tk.Entry(windows) # 创建第2个输入框
e3 = tk.Entry(windows)
e1.grid(row=0, column=1, padx=10, pady=5)
e2.grid(row=1, column=1, padx=10, pady=5)
e3.grid(row=2, column=1, padx=10, pady=5)
tk.Button(windows, text='点击继续', width=10, command=windows.quit) \
   .grid(row=4, column=1, sticky=tk.E, padx=10, pady=5) # sticky表示方位，NSWE为上下左右
tk.mainloop() # 结束循环
# 为变量赋值
word = str(e1.get())
number = int(e2.get()) - 1
choice = int(e3.get())

# 检查想要处理的sheet是否在所有的目标文件中
file_list = os.listdir(p1)
file_list.remove('.DS_Store') # 移除Mac系统自动生成的文件
for file in file_list: # 循环遍历列出所有文件名称
 file_name = os.path.join(p1, file) # 因os.listdir工具返回的是目标文件夹里文件的名字，然而打开文件需要文件路径+名字，故通过此工具获取完整的文件名
 workbook = xlrd.open_workbook(file_name) # 打开遍历的文件
 if word in workbook.sheet_names():
  continue
 else:
  print(str(file) + '中不存在想要合并的sheet')

if choice == 2:
 p1 = pd.ExcelFile(excelarr[0]) # 读取获取到的第一个文件名对应的文件
 mergedata1 = p1.parse(header=number, sheet_name=word).iloc[:, 0] # 设置索引为第一行，如果为index_col，则索引为第一列 iloc为取特定的列
 p2 = []
 for i in range(0, len(excelarr)):
  tmp = pd.ExcelFile(excelarr[i]) # 读取文件
  currentdata = tmp.parse(header=number, sheet_name=word).iloc[:, [1, 2, 3, 4]] # 读取特定的列
  mergedata1 = pd.concat([mergedata1, currentdata], axis=1) # concat连接函数，唯一必须的参数是参与连接的对象的列表或字典。axis=1，表示可以按照纵轴来合并
  n = currentdata.shape[1] # 返回Dataframe的行数，1为返回列数
  p2.append(n) # 获取读取文件的行数列表
 name_list = []
 i = 0
 # 获取应插入的文件名的列表
 for a1 in bookname:
  for x in range(p2[i]):
   name_list.append(a1)
  i = i + 1
 name_list.insert(0, '来自表格')
 mergedata1.loc[-1] = name_list # 按行插入

if choice == 1:
 p1 = pd.ExcelFile(excelarr[0]) # 读取获取到的第一个文件名对应的文件
 mergedata1 = p1.parse(header=number, sheet_name=word) # 设置索引为第一行，如果为index_col，则索引为第一列
 p2 = []
 p2.append(mergedata1.shape[0])
 for i in range(1, len(excelarr)):
  tmp = pd.ExcelFile(excelarr[i]) # 读取文件
  currentdata = tmp.parse(header=number, sheet_name=word) # 读取特定的表
  mergedata1 = pd.concat([mergedata1, currentdata]) # concat连接函数，唯一必须的参数是参与连接的对象的列表或字典。axis=1，表示可以按照纵轴来合并
  n = currentdata.shape[0] # 返回Dataframe的行数，1为返回列数
  p2.append(n) # 获取读取文件的行数列表
 name_list = []
 i = 0
 # 获取应插入的文件名的列表
 for a1 in bookname:
  for x in range(p2[i]):
   name_list.append(a1)
  i = i + 1
 mergedata1.insert(0, '来自表格', name_list) # 插入第一列，作为表格数据来源的注释

if choice == 3:
 i = 0
 fname = tk.filedialog.asksaveasfilename(title=u'保存文件', filetypes=[("excel", ".xlsx")])
 # fideialog的一个方法，可以实现数据储存是要保存的名字
 writerExcel = pd.ExcelWriter(fname+'.xlsx') # 写入到一个新的Excel，并且命名为上一步骤确认的名字
 for name in bookname:
  p1 = pd.ExcelFile(excelarr[i])
  mergedata1 = p1.parse(header=number, sheet_name=word) # 设置索引为第一行，如果为index_col，则索引为第一列
  # mergedata1.dropna(thresh=4, inplace=True) # 必须使用inplace才可以使数据库记住删除的单元格
  # mergedata1.dropna(axis='columns', how='all') # 删除全是缺失值的列
  mergedata1.fillna('0') # 将所有缺失值填充为0
  mergedata1.to_excel(writerExcel, sheet_name=name, index=False) # 新建一个sheet储存信息
  i = i + 1

if choice != 3:
 # 数据清理
 mergedata1.dropna(thresh=4, inplace=True) # 必须使用inplace才可以使数据库记住删除的单元格
 mergedata1.dropna(axis='columns', how='all') # 删除全是缺失值的列
 mergedata1.fillna('0') # 将所有缺失值填充为0
 # 保存至excel
 fname = tk.filedialog.asksaveasfilename(title=u'保存文件',
           filetypes=[("excel", ".xlsx")]) # fideialog的一个方法，可以实现数据储存是要保存的名字
 writerExcel = pd.ExcelWriter(fname + '.xlsx') # 写入到一个新的Excel，并且命名为上一步骤确认的名字
 mergedata1.to_excel(writerExcel, sheet_name='汇总表', index=False) # 将之前汇总的farmdate数据通过to excel写入到Excel中

# 设置格式
sheetname = writerExcel.sheets
workbook = writerExcel.book
for sheets in sheetname:
 worksheet = writerExcel.sheets[sheets]
 format1 = workbook.add_format({'num_format': '###,##0.00', })
 # 通过xlsxwriter模块命名format1的格式，对于数字内容，每三位进行一个分隔符，并且保留两位小数。#.00%为保留两位小数的百分数.border为边框。最后为文本换行和居中
 # format2 = workbook.add_format({'bold': True, 'italic': True}) # 加粗、斜体
 worksheet.set_column('A:ZZ', 16, format1) # 将上述定义的格式应用到具体的单元格
 # worksheet.set_row(0, 16, format2) # 将特定格式用于表头

writerExcel.save() # 保存Excel
print('success')

总结

到此这篇关于利用Python pandas对Excel进行合并的文章就介绍到这了,更多相关Python pandas对Excel合并内容请搜索python博客以前的文章或继续浏览下面的相关文章希望大家以后多多支持python博客！

pandas |

展开全文