how read and write merged cells in excel

how read and write merged cells in 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: how read and write merged cells in excel (/thread-20372.html)

how read and write merged cells in excel - SriMekala - Aug-07-2019

Hi,
I have below data in which some of the cells are merged,

This data is saved in excel temp.xlsx, I want to read and write to another workbook detail.xlsx

Group   Name   Rank
Group1  ABC     2
        BGA     5
        HJK     10
Group2  PLK     4
        UJK     5
        VBA     50
        YZ      23
        YU_20   3

I tried below code but did not work, give an error:
KeyError: 'Group'

import pandas as pd

excel = pd.read_excel('D:\pivotdata.xlsx',sheetname='merge',header=1)
excel['Group']=excel['Group'].fillna(method='ffill')

RE: how read and write merged cells in excel - scidam - Aug-07-2019

Suppose you have a data frame with multiindex, e.g.

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
tuples = list(zip(*arrays))
df = pd.DataFrame(pd.np.random.randn(8), index=index)

Then if you call df.reset_index(), you get almost what you want:

Output: 	first 	second 	0
0 	bar 	one 	0.602861
1 	bar 	two 	1.843366
2 	baz 	one 	0.425292
3 	baz 	two 	-1.067120
4 	foo 	one 	1.012777
5 	foo 	two 	0.607141
6 	qux 	one 	0.582811
7 	qux 	two 	2.501628

Something like df.reset_index().to_excel('...') should give you desired result.