Export pandas.DataFrame.column.name attribute during pd.to_excel() export

Question

a = np.array(["foo", "foo", "foo", "foo", "bar", "bar",
              "bar", "bar", "foo", "foo", "foo"], dtype=object)
b = np.array(["one", "one", "one", "two", "one", "one",
              "one", "two", "two", "two", "one"], dtype=object)
data = {'A': a, 'B': b}
df = pd.DataFrame(data)
df['C'] = 1
table = pd.pivot_table(df, values='C',index=['A'], columns=['B'], aggfunc="count")
table.to_excel("table.xlsx")
print('Name: ',table.columns.name)
print()
print('Table: ')
print(table)


Name:  B

Table: 
B    one  two
A            
bar    3    1
foo    4    3

This is my code so far. My goal is to create several pivot tables each with data that looks similar to this example and export them to excel. I would like to, if possible keep the DataFrame.columns.name attribute during the export. The current export looks like this:

A	one	two
bar	3	1
foo	4	3

and if possible I would like an export that looks like this:

B	one	two
A
bar	3	1
foo	4	3

Hunt3r5o4 · Accepted Answer · 2025-11-14 19:54:49Z

You can keep the column name during the export, but you need to tell Excel explicitly to write an extra header row. Pandas does not output the DataFrame.columns.name by default when writing to Excel, so you have to inject it yourself before calling toExcel.

The simplest way is to reindex the columns with a new top level that contains the existing name. That gives you a two-level header where the first level is your column name and the second level is the actual pivot columns. Excel will then show the name above the header just like in your screenshot.

Example:

print('convert columns to multi index with name')
table.columns = pd.MultiIndex.from_product(
    [[table.columns.name], table.columns]
)
writer = pd.ExcelWriter('table.xlsx', engine='xlsxwriter')
table.to_excel(writer)
writer.save()

After this the exported sheet shows the name B above the one and two columns, matching the layout you want.

This approach works for any number of pivot tables because you can apply the same two-line conversion to each table before exporting.

Collectives™ on Stack Overflow

Export pandas.DataFrame.column.name attribute during pd.to_excel() export

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related