Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 how read and write merged cells in excel
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)
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(, index=index)
Then if you call df.reset_index(), you get almost what you want:

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.

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Read exel with merged cells and write to another excel SriMekala 0 126 Aug-10-2019, 07:14 AM
Last Post: SriMekala
  Python write result of VAR to excel file wissam1974 8 397 Jul-13-2019, 01:09 PM
Last Post: wissam1974
  Two dataframes merged Ecniv 10 554 Jun-16-2019, 09:10 PM
Last Post: Ecniv
  Python QtableWidget get text of all cells and headers to dataframe Mady 3 1,694 Dec-15-2018, 06:46 PM
Last Post: Axel_Erfurt
  Copy raw data in excel to another new excel file keerthiprashanth 5 603 Oct-20-2018, 10:13 AM
Last Post: volcano63
  Python read Password protected excel and convert to Pandas DataFrame FORTITUDE 2 3,589 Aug-30-2018, 01:08 PM
  Pandas - Write to Exisitng Excel File - Sorted List dj99 4 2,243 Jul-29-2018, 07:56 AM
Last Post: dj99
  Converting days to years in loop while computing values across grid cells Lightning1800 2 601 May-15-2018, 08:44 PM
Last Post: Lightning1800
  How do I write a line of code into an excel spreadsheet using Python code? Emerogork 2 804 Feb-07-2018, 06:54 PM
Last Post: snippsat
  Write data into existing Excel (xlsx) file with multiple sheets BNB 1 9,040 Jun-01-2017, 04:22 PM
Last Post: Larz60+

Forum Jump:

Users browsing this thread: 1 Guest(s)