Python Forum
how read and write merged cells in excel
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how read and write merged cells in excel
#1
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')
Reply
#2
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Write from dictionary to excel divon 3 3,472 Jun-11-2023, 10:37 AM
Last Post: Larz60+
Smile How to further boost the data read write speed using pandas tjk9501 1 1,230 Nov-14-2022, 01:46 PM
Last Post: jefsummers
  Split excel file and write output at specific row and set sheet position DSCA 0 1,960 May-12-2022, 07:29 PM
Last Post: DSCA
  [Pandas] Write data to Excel with dot decimals manonB 1 5,774 May-05-2021, 05:28 PM
Last Post: ibreeden
  Python - Merge existing cells of Excel file created with xlsxwriter manonB 0 3,686 Mar-10-2021, 02:17 PM
Last Post: manonB
  Python read Excel cell data validation anantpatil 0 4,106 Jan-31-2020, 04:57 PM
Last Post: anantpatil
  Read exel with merged cells and write to another excel SriMekala 0 2,938 Aug-10-2019, 07:14 AM
Last Post: SriMekala
  Python write result of VAR to excel file wissam1974 8 8,523 Jul-13-2019, 01:09 PM
Last Post: wissam1974
  Two dataframes merged Ecniv 10 5,167 Jun-16-2019, 09:10 PM
Last Post: Ecniv
  Python read Password protected excel and convert to Pandas DataFrame FORTITUDE 2 17,023 Aug-30-2018, 01:08 PM
Last Post: FORTITUDE

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020