How to make pd.MultiIndex merge column index vertically?

user_15177889 0
Viewed 116

(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)
pandas dataframe python
1 Answers

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')]
user_45410450
Related