BUG: to_csv extra header line with multiindex columns

[dsm054]

This seems strange to me, but I don't often use a MultiIndex so I might be missing something obvious.

>>> pd.__version__
'0.13.1-420-g6899ed6'
>>> df2 = pd.DataFrame([1], columns=pd.MultiIndex.from_arrays([[1],[2]]))
>>> df2
   1
   2
0  1

[1 rows x 1 columns]
>>> df2.columns
MultiIndex(levels=[[1], [2]],
           labels=[[0], [0]])
>>> print df2.to_csv()
,1
,2
,
0,1

Is there supposed to be that empty line at the end of the header? Compare

>>> print df2.to_csv(header=False)
0,1

[jreback]

yes, its for the row index-names (they are None here), in theory could could not print it as the read_csv will try both ways, but that's the 'original' format.

[dsm054]

Ah, okay.

[jreback]

@dsm054 I think its reasonable to do a PR which takes out the line and see if anything breaks....(obviously a tests which exactly is supposed to match won't), but I am talking about the read_csv should still work correctly.

and I guess its more in-line with what you'd except.

[meloun]

hi i have the same issue, any workarround how to not have this empty line there?

[jreback]

pandas will read this format
what version?

[meloun]

yes pandas will, but I need an output without this extra line (it's an input for other application)
see http://stackoverflow.com/questions/24372993/pandas-dataframe-with-2-rows-header-and-export-to-csv

[jreback]

you can use tupleize_cols=True to make the header in a single line

[ronanpaixao]

It appears this bugs still manifests itself if using to_excel:

>>> df = pd.DataFrame([[1,2,3],[4,5,6]], columns=pd.MultiIndex.from_tuples([('A',''),('B','C'),('B','D')]))
>>> df
   A  B
      C  D
0  1  2  3
1  4  5  6
>>> df.to_excel("out.xlsx")

This outputs the spreadsheet with an additional (and pretty much useless) blank line (3):

This is some workaround, but is really worse for cases like my example, where the upper row in the MultiIndex gets repeated (see df['B']).

[tsznxx]

A workaround to fix this is to save the headers and table contents separately.

writer = pandas.ExcelWriter("test.xlsx")
# writer headers as data frame
df.columns.to_frame().transpose().to_excel(writer,"test")
# writer table body without headers
df.to_excel(writer,"test",header=False,startrow=2))
writer.save()
writer.close()

This trick can also be used when saving pandas style to Excel, because pandas style doesn't support multiindex.

[jstefaniakk]

The solution given above by tsznxx worked for me, however a line:
df.columns.to_frame().transpose().to_excel(writer,"test")
generates two rows of data with names of the columns and when I had less than 3 rows of data in my dataframe, the unnecessary columns data remained printed into the excel. Instead, I propose to first write your empty dataframe to excel :
df.drop(df.index).to_excel(writer,"test")
and then follow it up with dataframe data without headers. So the whole example would look like:

writer = pandas.ExcelWriter("test.xlsx")
# write only column names
df.drop(df.index).to_excel(writer,"test")
# writer table body without headers
df.to_excel(writer,"test",header=False,startrow=2))
writer.save()
writer.close()