ENH: The parameter merge_cells parameter in function to_excel should take a 2-tuple one for index and one for columns

[soumitrakp]

Currently pandas to_excel function has one parameter named merge_cells: if True it merges all cells in the excel sheet corresponding to the index and columns. However, if there are many levels of the index and many values for the higher levels, then as we scroll on the sheet, values of the higher levels in index become invisible. One way to keep the repeated values is to set merge_cells = False. However, in that case the column levels are also affected.

It would be great to have a way to control this aspect of index and columns separately. One way would be to let merge_cells to take a 2-tuple of which one value is for the index and the other for the columns.

[simonjayhawkins]

Thanks @soumitrakp for the report.

It would be great to have a way to control this aspect of index and columns separately. One way would be to let merge_cells to take a 2-tuple of which one value is for the index and the other for the columns.

another approach for allowing a boolean to apply to rows or columns only is to allow the use of 'index' or 'columns' as arguments.

from the current docs, https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.DataFrame.to_excel.html

merge_cells : bool, default True
Write MultiIndex and Hierarchical Rows as merged cells.

so the api could be

merge_cells : bool or {'index', 'columns'}, default True
Write MultiIndex and/or Hierarchical Rows as merged cells.

we do this currently for e.g. crosstab, https://pandas.pydata.org/pandas-docs/dev/reference/api/pandas.crosstab.html

normalize : bool, {‘all’, ‘index’, ‘columns’}, or {0,1}, default False
Normalize by dividing all values by the sum of values.

If passed ‘all’ or True, will normalize over all values.

If passed ‘index’ will normalize over each row.

If passed ‘columns’ will normalize over each column.

If margins is True, will also normalize margin values.