Pandas读取excel合并单元格的正确姿势(openpyxl合并单元格拆分并填充内容)
2023-09-27 14:20:13 时间
问题介绍(ffill填充存在的问题)
在pandas读取excel经常会遇到合并单元格的问题。例如:
此时使用pandas读取到的内容为:
如果去百度,几乎所有人会说应该用如下代码:
df['班级'] = df['班级'].ffill()
这样看起来没问题,但是,该解决方案并不能适用于所有场景,甚至会造成数据错误。
例如:
对班级和备注填充后:
孙武空本来是数据缺失,现在被错误的标记成了挂科数据。
再例如:
对所有列填充后:
同样存在大量数据或错误数据。
正确填充方式
思路:① 使用openpyxl将合并单元格拆分,生成中间文件 ② 读取中间文件
第一步,使用如下工具类生成拆分单元格并生成中间文件:
import openpyxl
# 拆分所有的合并单元格,并赋予合并之前的值。
# 由于openpyxl并没有提供拆分并填充的方法,所以使用该方法进行完成
def unmerge_and_fill_cells(worksheet):
all_merged_cell_ranges = list(
worksheet.merged_cells.ranges
)
for merged_cell_range in all_merged_cell_ranges:
merged_cell = merged_cell_range.start_cell
worksheet.unmerge_cells(range_string=merged_cell_range.coord)
for row_index, col_index in merged_cell_range.cells:
cell = worksheet.cell(row=row_index, column=col_index)
cell.value = merged_cell.value
# 读取原始xlsx文件,拆分并填充单元格,然后生成中间临时文件。
def unmerge_cell(filename):
wb = openpyxl.load_workbook(filename)
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
unmerge_and_fill_cells(sheet)
filename = filename.replace(".xls", "_temp.xls")
wb.save(filename)
wb.close()
# openpyxl保存之后,再用pandas读取会存在公式无法读取到的情况,使用下面方式就可以了
# 如果你的excel不涉及公式,可以删除下面内容
# 原理为:使用windows打开excel,然后另存为一下
from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlApp.Visible = False
xlBook = xlApp.Workbooks.Open(str(Path(".").absolute() / filename)) # 这里必须填绝对路径
xlBook.Save()
xlBook.Close()
return filename
if __name__ == '__main__':
unmerge_cell("test.xlsx")
拆分后的sheet页如图:
然后再使用pandas读取中间文件即可:
import pandas as pd
df = pd.read_excel("test_temp.xlsx")
结果为:
相关文章
- 七周成为数据分析师—Excel实战篇
- excel之实验数据处理线性拟合
- python操作excel——openpyxl
- 读取和导出下载 excel 2003,2007 资料
- 【Python】excel
- Tp5.1使用导出Excel
- 使用pandas读取excel
- Excel宏被禁用解决办法
- Python自动化办公--Pandas玩转Excel数据分析【三】
- Python自动化办公--Pandas玩转Excel数据分析【二】
- Python自动化办公--Pandas玩转Excel【一】
- springboot实现EasyExcel对Excel读和写操作
- 如何把 excel 的数据导入到数据库里面去
- 基于Java实现Json文件转换为Excel文件
- .NET Core(C#) EPPlus读取Excel(.xlsx)文件的方法及示例代码
- Python 保存数据到Excel文件的方法(pandas、xlwt、openpyxl、xlsxwriter)
- Python xlwings pandas获取Excel中某列的数据的最大值和最小值
- Python pandas read_excel 读取xlsx文件报错:xlrd.biffh.XLRDError not supported 解决方法
- 利用excel与Pandas完成实现数据透视表
- Excel文件与DataSet之间的转化的探索与实现
- java用poi读取Excel表格中的数据
- jxl导出excel的问题
- python - xlwt和openpyxl和xlwtwriter写Excel文件示例