CODEWITHSUNDEEP
pythonexcelpython-2.7pandasexport-to-excel
Juan David
Juan David

Reputation: 2797

Pandas dataframe with multindex to excel

I'm trying to get this

enter image description here

But I'm getting this:

enter image description here

Even without content, I'm getting a vertical multindex. This is a MWE of what I'm doing 

from pandas import DataFrame, MultiIndex, ExcelWriter, Series
import numpy.random as rd

FILENAME = 'SampleFile.xlsx'
writer = ExcelWriter(FILENAME, engine='xlsxwriter')
multindex_headers = [['A', 'B', 'C', 'D'],
                     ['A1', 'B2','C3', 'D4']]

index = MultiIndex.from_tuples( list(zip(*multindex_headers)),  names=['l1','l2'])
sampleDF = DataFrame(index=index)
sample_info = [rd.randn(4).tolist()]*5

for sample_data in sample_info:
    sampleDF = sampleDF.append(DataFrame(sample_data, index=index))

sampleDF.to_excel(writer, sheet_name='case')
writer.save()

EDIT:

I've implemented a workaround with transpose() but I'd like to know if there is a formal approach to this issue:

from pandas import DataFrame, MultiIndex, ExcelWriter, Series
import numpy.random as rd

FILENAME = 'SampleFile.xlsx'
writer = ExcelWriter(FILENAME, engine='xlsxwriter')
multindex_headers = [['A', 'B', 'C', 'D'],
                     ['A1', 'B2','C3', 'D4']]

index = MultiIndex.from_tuples( list(zip(*multindex_headers)))
sampleDF = DataFrame(index=index)
sampleDF = sampleDF.transpose()
sample_info = [rd.randn(4).tolist()]*5

for sample_data in sample_info:
     sampleDF = sampleDF.append(DataFrame(sample_data, index=index).transpose())

#sampleDF.to_csv('samplecsv.csv')
sampleDF.to_excel(writer, sheet_name='case')
writer.save()

Upvotes: 1

Views: 368

Answers (1)

Juan David
Juan David

Reputation: 2797

 from pandas import DataFrame, MultiIndex, ExcelWriter, Series
import numpy.random as rd

FILENAME = 'SampleFile.xlsx'
writer = ExcelWriter(FILENAME, engine='xlsxwriter')
multindex_headers = [['A', 'B', 'C', 'D'],
                     ['A1', 'B2','C3', 'D4']]

index = MultiIndex.from_tuples( list(zip(*multindex_headers)))
sampleDF = DataFrame(index=index)
sampleDF = sampleDF.transpose()
sample_info = [rd.randn(4).tolist()]*5

for sample_data in sample_info:
     sampleDF = sampleDF.append(DataFrame(sample_data, index=index).transpose())

#sampleDF.to_csv('samplecsv.csv')
sampleDF.to_excel(writer, sheet_name='case')
writer.save()

Taken from my edit

Upvotes: 1

Related Questions