pandas dataframe with 2-rows header and export to csv

Question

I have a dataframe

df = pd.DataFrame(columns = ["AA", "BB", "CC"])
df.loc[0]= ["a", "b", "c1"]
df.loc[1]= ["a", "b", "c2"]
df.loc[2]= ["a", "b", "c3"]

I need to add secod row to header

df.columns = pd.MultiIndex.from_tuples(zip(df.columns, ["DD", "EE", "FF"]))

my df is now

  AA BB  CC
  DD EE  FF
0  a  b  c1
1  a  b  c2
2  a  b  c3

but when I write this dataframe to csv file

df.to_csv("test.csv", index = False)

I get one more row than expected

AA,BB,CC
DD,EE,FF
,,
a,b,c1
a,b,c2
a,b,c3

Answer 1

I think this is a bug in to_csv. If you're looking for workarounds then here's a couple.

To read back in this csv specify the header rows*:

In [11]: csv = "AA,BB,CC
DD,EE,FF
,,
a,b,c1
a,b,c2
a,b,c3"

In [12]: pd.read_csv(StringIO(csv), header=[0, 1])
Out[12]:
  AA BB  CC
  DD EE  FF
0  a  b  c1
1  a  b  c2
2  a  b  c3

*strangely this seems to ignore the blank lines.

To write out you could write the header first and then append:

with open('test.csv', 'w') as f:
    f.write('\n'.join([','.join(h) for h in zip(*df.columns)]) + '\n')
df.to_csv('test.csv', mode='a', index=False, header=False)

Note the to_csv part for MultiIndex column here:

In [21]: '\n'.join([','.join(h) for h in zip(*df.columns)]) + '\n'
Out[21]: 'AA,BB,CC\nDD,EE,FF\n'

Answer 2

It's an ugly hack, but if you needed something to work Right Now(tm), you could write it out in two parts:

>>> pd.DataFrame(df.columns.tolist()).T.to_csv("noblankrows.csv", mode="w", header=False, index=False)
>>> df.to_csv("noblankrows.csv", mode="a", header=False, index=False)
>>> !cat noblankrows.csv
AA,BB,CC
DD,EE,FF
a,b,c1
a,b,c2
a,b,c3