Bottom Page

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')
Quote
#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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Read exel with merged cells and write to another excel SriMekala 0 405 Aug-10-2019, 07:14 AM
Last Post: SriMekala
  Python write result of VAR to excel file wissam1974 8 685 Jul-13-2019, 01:09 PM
Last Post: wissam1974
  Two dataframes merged Ecniv 10 695 Jun-16-2019, 09:10 PM
Last Post: Ecniv
  Python read Password protected excel and convert to Pandas DataFrame FORTITUDE 2 4,409 Aug-30-2018, 01:08 PM
Last Post: FORTITUDE
  Pandas - Write to Exisitng Excel File - Sorted List dj99 4 2,697 Jul-29-2018, 07:56 AM
Last Post: dj99
  How do I write a line of code into an excel spreadsheet using Python code? Emerogork 2 856 Feb-07-2018, 06:54 PM
Last Post: snippsat
  Write data into existing Excel (xlsx) file with multiple sheets BNB 1 9,855 Jun-01-2017, 04:22 PM
Last Post: Larz60+

Forum Jump:


Users browsing this thread: 1 Guest(s)