I have a table in an excel that contains many merged cells.
Given Excel Table When I open it with python, I want to be able to turn it into a dataframe while maintaining data integrity. It should look like this:
I've searched through and referred to a few of the different solutions here:
How to read merged cells in python using openpyxl?
How to read merged Excel cells with NaN into Pandas DataFrame
But none have worked.
Understand that fillna will fill up even cells that were originally not merged. I also read a little about merged_cell but am still unable to make it work.
import openpyxl
from openpyxl.utils import range_boundaries
from openpyxl import Workbook
from openpyxl import load_workbook
# wb = Workbook()
wb = openpyxl.load_workbook('file')
sheet = wb.active
mcells = sheet.merged_cells.ranges
# mcells gives a list of merged cells
# I want to go through the list of merged cells and use sheet.unmerge_cells() to unmerge them and fill the cells accordingly.
# This way I can ensure cells that are not merged will remain blank.
for k,v in enumerate(mcells):
print(v)
# How do I continue from here?
Is there a way to do convert the merged cells accurately?
