2

(My first post, very excited! Happy Wednesday!)

When I use pd.MultiIndex.from_tuples and pd.ExcelWriter to create an Excel file, the column index looks like the following:

enter image description here

col_index2 is being merged, but not col_index1.

It seems pd.MultiIndex can automatically merge column index horizontally but not vertically

Is there a way to fix this? I must have overlooked something obvious

Also, does anyone know why there is an empty row after the column index?

Any help is truly appreciated! Thank you guys so much!

import pandas as pd
tuples = [('col_index1', 'col_index1'), ('col_index2', 'col_index3'), ('col_index2', 'col_index4')]
col_index = pd.MultiIndex.from_tuples(tuples)
row_index = ['row_index1']
df = pd.DataFrame(index = row_index, columns = col_index)
with pd.ExcelWriter('test.xlsx') as writer:
    df.to_excel(writer)
ti7
  • 16,375
  • 6
  • 40
  • 68
EggHead
  • 23
  • 4

1 Answers1

0

Perhaps you can clean up the tuples before making them headers to remove duplicates

Fast ordered list reduction: How do you remove duplicates from a list whilst preserving order?

tuples = [('col_index1', 'col_index1'), ('col_index2', 'col_index3'), ('col_index2', 'col_index4')]
tuples = [tuple(dict.fromkeys(t).keys()) for t in tuples]
# [('col_index1',), ('col_index2', 'col_index3'), ('col_index2', 'col_index4')]
ti7
  • 16,375
  • 6
  • 40
  • 68
  • This doesn't seem to work in my quick test. When change ('col_index1', 'col_index1') to ('col_index1',), it actually becomes ('col_index1', 'col_index4'). But thank you for your answer! – EggHead Feb 09 '21 at 23:03
  • 1
    This is late but allow me to accept this answer. Once again, many thanks! – EggHead Apr 05 '22 at 13:35