Read exel with merged cells and write to another excel

Read exel with merged cells and write to another excel - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Read exel with merged cells and write to another excel (/thread-20422.html)

Read exel with merged cells and write to another excel - SriMekala - Aug-10-2019

Hi,
I read the excel sheet which has "merged cells", and I want to write to another file retaining original merged cells.

My input excel is below:

Group	Name	Date	    Item	  Value
Group1	VGT	   2016-06-06	Power	  0
	    BHA	   2016-06-07	Voltage	  1
Group2	BNA	   2016-06-08	Volume	  1
	    KAL	   2016-06-09	AAA	      0
	    PAR	   2016-06-10	ANM	      0
Group3	VGT	2016-06-11	    Power	  1

I read and write another file, but can not retain merged cells.

row 2 is empty, row4~row5 again empty (but need to be merged with row3 ie, Group2)

Group	Name	Date	            Item	Value
Group1	VGT	   2016-06-06 00:00:00	Power	 0
empty	BHA	   2016-06-07 00:00:00	Voltage	 1
Group2	BNA	   2016-06-08 00:00:00	Volume	 1
empty	KAL	   2016-06-09 00:00:00	AAA	     0
empty	PAR	   2016-06-10 00:00:00	ANM	     0
Group3	VGT	   2016-06-11 00:00:00	Power	 1

How to retain the oroginal merged cells,

I sue below code:

#%%
import pandas as pd
infile='D:\\mergedinput.xlsx'
df=pd.read_excel(infile)

but the output is as below: