Save pandas pivot_table to include index and columns names

Question

I want to save a pandas pivot table for human reading, but DataFrame.to_csv doesn't include the DataFrame.columns.name. How can I do that?

Example:

For the following pivot table:

>>> import pandas as pd
>>> df = pd.DataFrame([[1, 2, 3], [6, 7, 8]])
>>> df.columns = list("ABC")
>>> df.index = list("XY")
>>> df
   A  B  C
X  1  2  3
Y  6  7  8
>>> p = pd.pivot_table(data=df, index="A", columns="B", values="C")

When viewing the pivot table, we have both the index name ("A"), and the columns name ("B").

>>> p
B    2    7
A
1  3.0  NaN
6  NaN  8.0

But when exporting as a csv we lose the columns name:

>>> p.to_csv("temp.csv")

===temp.csv===
A,2,7
1,3.0,
6,,8.0

How can I get some kind of human-readable output format which contains the whole of the pivot table, including the .columns.name ("B")?

Something like this would be fine:

B,2,7
A,,
1,3.0,
6,,8.0

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html — BENY, Mar 26 '19 at 14:41
@Wen-Ben That gets me the exact same thing as an `.xls`, not including the "B". — Cai, Mar 26 '19 at 14:46
How working `p.to_csv('temp.csv', index_label=[p.index.name, p.columns.name])` ? — jezrael, Mar 26 '19 at 14:50

jezrael · Accepted Answer · 2019-03-26T15:37:30.030

Yes, it is possible by append helper DataFrame, but reading file is a bit complicated:

p1 = pd.DataFrame(columns=p.columns, index=[p.index.name]).append(p)
p1.to_csv('temp.csv',index_label=p.columns.name)
B,2,7
A,,
1,3.0,
6,,8.0

#set first column to index
df = pd.read_csv('temp.csv', index_col=0)
#set columns and index names
df.columns.name = df.index.name
df.index.name = df.index[0]
#remove first row of data
df = df.iloc[1:]
print (df)
B    2    7
A          
1  3.0  NaN
6  NaN  8.0

Save pandas pivot_table to include index and columns names

1 Answers1