Repository: pandas-dev/pandas
Pull request: ENH: Add option to only merge column header cells in `ExcelFormatter`.
Adds an option to pass `merge_cells="columns"` in `DataFrame.to_excel`. This allows only merging MultiIndex column header cells and prevents index cells to be merged.

- [x] closes #35384
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Problem statement:
ENH: The parameter merge_cells parameter in function to_excel should take a 2-tuple one for index and one for columns
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.

Hints from issue discussion:
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.

Commit message: Add entry in the whatsnew document

Patch to review:
diff --git a/doc/source/whatsnew/v3.0.0.rst b/doc/source/whatsnew/v3.0.0.rst
index 3d869bf31f372..a1811da0a8fc5 100644
--- a/doc/source/whatsnew/v3.0.0.rst
+++ b/doc/source/whatsnew/v3.0.0.rst
@@ -45,6 +45,7 @@ Other enhancements
 - :meth:`DataFrame.pivot_table` and :func:`pivot_table` now allow the passing of keyword arguments to ``aggfunc`` through ``**kwargs`` (:issue:`57884`)
 - :meth:`Series.cummin` and :meth:`Series.cummax` now supports :class:`CategoricalDtype` (:issue:`52335`)
 - :meth:`Series.plot` now correctly handle the ``ylabel`` parameter for pie charts, allowing for explicit control over the y-axis label (:issue:`58239`)
+- Add option to only merge MultiIndex column header header cells in :func:`DataFrame.to_excel` (:issue:`35384`)
 - Restore support for reading Stata 104-format and enable reading 103-format dta files (:issue:`58554`)
 - Support reading Stata 110-format (Stata 7) dta files (:issue:`47176`)
 
diff --git a/pandas/_typing.py b/pandas/_typing.py
index d90596878ba51..09a3f58d6ab7f 100644
--- a/pandas/_typing.py
+++ b/pandas/_typing.py
@@ -510,6 +510,7 @@ def closed(self) -> bool:
 
 # ExcelWriter
 ExcelWriterIfSheetExists = Literal["error", "new", "replace", "overlay"]
+ExcelWriterMergeCells = Union[bool, Literal["columns"]]
 
 # Offsets
 OffsetCalendar = Union[np.busdaycalendar, "AbstractHolidayCalendar"]
diff --git a/pandas/io/formats/excel.py b/pandas/io/formats/excel.py
index a98d9c175c2bd..7b968343b3b94 100644
--- a/pandas/io/formats/excel.py
+++ b/pandas/io/formats/excel.py
@@ -52,6 +52,7 @@
 
 if TYPE_CHECKING:
     from pandas._typing import (
+        ExcelWriterMergeCells,
         FilePath,
         IndexLabel,
         StorageOptions,
@@ -523,8 +524,11 @@ class ExcelFormatter:
         Column label for index column(s) if desired. If None is given, and
         `header` and `index` are True, then the index names are used. A
         sequence should be given if the DataFrame uses MultiIndex.
-    merge_cells : bool, default False
-        Format MultiIndex and Hierarchical Rows as merged cells.
+    merge_cells : bool or 'columns', default False
+        Format MultiIndex column headers and Hierarchical Rows as mergedcells
+        if True. Merge MultiIndex column headers only if 'columns'.
+        .. versionchanged:: :ref: 3.0.0
+            Added the 'columns' option.
     inf_rep : str, default `'inf'`
         representation for np.inf values (which aren't representable in Excel)
         A `'-'` sign will be added in front of -inf.
@@ -547,7 +551,7 @@ def __init__(
         header: Sequence[Hashable] | bool = True,
         index: bool = True,
         index_label: IndexLabel | None = None,
-        merge_cells: bool = False,
+        merge_cells: ExcelWriterMergeCells = False,
         inf_rep: str = "inf",
         style_converter: Callable | None = None,
     ) -> None:
@@ -580,6 +584,9 @@ def __init__(
         self.index = index
         self.index_label = index_label
         self.header = header
+
+        if (not isinstance(merge_cells, bool)) and (merge_cells != "columns"):
+            raise ValueError("Unexpected value for 'merge_cells'.")
         self.merge_cells = merge_cells
         self.inf_rep = inf_rep
 
@@ -614,7 +621,7 @@ def _format_header_mi(self) -> Iterable[ExcelCell]:
 
         columns = self.columns
         level_strs = columns._format_multi(
-            sparsify=self.merge_cells, include_names=False
+            sparsify=self.merge_cells in {True, "columns"}, include_names=False
         )
         level_lengths = get_level_lengths(level_strs)
         coloffset = 0
@@ -623,7 +630,7 @@ def _format_header_mi(self) -> Iterable[ExcelCell]:
         if self.index and isinstance(self.df.index, MultiIndex):
             coloffset = self.df.index.nlevels - 1
 
-        if self.merge_cells:
+        if self.merge_cells in {True, "columns"}:
             # Format multi-index as a merged cells.
             for lnum, name in enumerate(columns.names):
                 yield ExcelCell(
@@ -793,7 +800,9 @@ def _format_hierarchical_rows(self) -> Iterable[ExcelCell]:
             # with index names (blank if None) for
             # unambiguous round-trip, unless not merging,
             # in which case the names all go on one row Issue #11328
-            if isinstance(self.columns, MultiIndex) and self.merge_cells:
+            if isinstance(self.columns, MultiIndex) and (
+                self.merge_cells in {True, "columns"}
+            ):
                 self.rowcounter += 1
 
             # if index labels are not empty go ahead and dump
@@ -801,7 +810,7 @@ def _format_hierarchical_rows(self) -> Iterable[ExcelCell]:
                 for cidx, name in enumerate(index_labels):
                     yield ExcelCell(self.rowcounter - 1, cidx, name, None)
 
-            if self.merge_cells:
+            if self.merge_cells and self.merge_cells != "columns":
                 # Format hierarchical rows as merged cells.
                 level_strs = self.df.index._format_multi(
                     sparsify=True, include_names=False
diff --git a/pandas/io/formats/style.py b/pandas/io/formats/style.py
index a695c539977b3..6f4c2fa6c6eae 100644
--- a/pandas/io/formats/style.py
+++ b/pandas/io/formats/style.py
@@ -66,6 +66,7 @@
         Axis,
         AxisInt,
         Concatenate,
+        ExcelWriterMergeCells,
         FilePath,
         IndexLabel,
         IntervalClosedType,
@@ -551,7 +552,7 @@ def to_excel(
         startrow: int = 0,
         startcol: int = 0,
         engine: str | None = None,
-        merge_cells: bool = True,
+        merge_cells: ExcelWriterMergeCells = True,
         encoding: str | None = None,
         inf_rep: str = "inf",
         verbose: bool = True,
diff --git a/pandas/tests/io/excel/test_writers.py b/pandas/tests/io/excel/test_writers.py
index ad1f22224bc0d..482b331332462 100644
--- a/pandas/tests/io/excel/test_writers.py
+++ b/pandas/tests/io/excel/test_writers.py
@@ -49,7 +49,7 @@ def frame(float_frame):
     return float_frame[:10]
 
 
-@pytest.fixture(params=[True, False])
+@pytest.fixture(params=[True, False, "columns"])
 def merge_cells(request):
     return request.param
 


Code review comments:
diff_hunk: @@ -523,8 +524,11 @@ class ExcelFormatter:
         Column label for index column(s) if desired. If None is given, and
         `header` and `index` are True, then the index names are used. A
         sequence should be given if the DataFrame uses MultiIndex.
-    merge_cells : bool, default False
-        Format MultiIndex and Hierarchical Rows as merged cells.
+    merge_cells : bool or 'columns', default False
+        Format MultiIndex column headers and Hierarchical Rows as mergedcells
path: pandas/io/formats/excel.py
text: @user1:
```suggestion
        Format MultiIndex column headers and Hierarchical Rows as merged cells
```

diff_hunk: @@ -580,6 +584,9 @@ def __init__(
         self.index = index
         self.index_label = index_label
         self.header = header
+
+        if (not isinstance(merge_cells, bool)) and (merge_cells != "columns"):
+            raise ValueError("Unexpected value for 'merge_cells'.")
path: pandas/io/formats/excel.py
text: @user1:
```suggestion
        if not isinstance(merge_cells, bool) and merge_cells != "columns":
            raise ValueError(f"Unexpected value for {merge_cells=}.)
```

diff_hunk: @@ -523,8 +524,11 @@ class ExcelFormatter:
         Column label for index column(s) if desired. If None is given, and
         `header` and `index` are True, then the index names are used. A
         sequence should be given if the DataFrame uses MultiIndex.
-    merge_cells : bool, default False
-        Format MultiIndex and Hierarchical Rows as merged cells.
+    merge_cells : bool or 'columns', default False
+        Format MultiIndex column headers and Hierarchical Rows as mergedcells
+        if True. Merge MultiIndex column headers only if 'columns'.
+        .. versionchanged:: :ref: 3.0.0
path: pandas/io/formats/excel.py
text: @user1:
```suggestion
        .. versionchanged:: 3.0.0
```