Repository: pandas-dev/pandas (python)

Issue:
BUG: .groupby() produces MultiIndex with NaN in levels instead of encoded in codes
### Pandas version checks

- [X] I have checked that this issue has not already been reported.

- [X] I have confirmed this bug exists on the [latest version](https://pandas.pydata.org/docs/whatsnew/index.html) of pandas.

- [ ] I have confirmed this bug exists on the [main branch](https://pandas.pydata.org/docs/dev/getting_started/install.html#installing-the-development-version-of-pandas) of pandas.


### Reproducible Example

```python
import pandas as pd

df = pd.DataFrame({"A": [1, 2, 3, 4], "B": [1, float("nan"), 2, float("nan")], "C": [2, 4, 6, 8]})
df_grouped = df.groupby(["A", "B"], dropna=False).sum()

index = df_grouped.index
pd.testing.assert_index_equal(index, pd.MultiIndex.from_frame(index.to_frame()))
```


### Issue Description

The snippet above results in an AssertionError, because NaN in the MultiIndex is encoded in levels when returned from `.groupby()`, but in codes when returned from `pd.MultiIndex.from_frame()`.

```
AssertionError: Index are different

Index values are different (50.0 %)
[left]:  MultiIndex([(1, 1.0),
            (2, nan),
            (3, 2.0),
            (4, nan)],
           names=['A', 'B'])
[right]: MultiIndex([(1, 1.0),
            (2, nan),
            (3, 2.0),
            (4, nan)],
           names=['A', 'B'])
```

See https://github.com/pandas-dev/pandas/blob/435762115d960a7a22d9fab738181a8b1908efa0/pandas/core/groupby/grouper.py#L767-L779


### Expected Behavior

I expect the assertion to pass.

### Installed Versions

<details>

INSTALLED VERSIONS
------------------
commit           : 0f437949513225922d851e9581723d82120684a6
python           : 3.11.4.final.0
python-bits      : 64
OS               : Windows
OS-release       : 10
Version          : 10.0.19044
machine          : AMD64
processor        : Intel64 Family 6 Model 158 Stepping 13, GenuineIntel
byteorder        : little
LC_ALL           : None
LANG             : None
LOCALE           : English_United Kingdom.1252

pandas           : 2.0.3
numpy            : 1.25.1
pytz             : 2023.3
dateutil         : 2.8.2
setuptools       : 68.0.0
pip              : 23.2
Cython           : None
pytest           : 7.4.0
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : None
pandas_datareader: None
bs4              : None
bottleneck       : None
brotli           : None
fastparquet      : None
fsspec           : None
gcsfs            : None
matplotlib       : 3.7.2
numba            : None
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pyreadstat       : None
pyxlsb           : None
s3fs             : None
scipy            : None
snappy           : None
sqlalchemy       : 2.0.19
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
zstandard        : None
tzdata           : 2023.3
qtpy             : None
pyqt5            : None

Process finished with exit code 0


</details>


Pull request:
Added a test for the groupby MultiIndex with codes
- [ ] closes #54347
- [ ] [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
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Patch:
diff --git a/ci/code_checks.sh b/ci/code_checks.sh
index 424171cee7..0fa61a2465 100755
--- a/ci/code_checks.sh
+++ b/ci/code_checks.sh
@@ -162,7 +162,7 @@ if [[ -z "$CHECK" || "$CHECK" == "docstrings" ]]; then
         -i "pandas.Series.ne SA01" \
         -i "pandas.Series.pad PR01,SA01" \
         -i "pandas.Series.plot PR02" \
-        -i "pandas.Series.pop RT03,SA01" \
+        -i "pandas.Series.pop SA01" \
         -i "pandas.Series.prod RT03" \
         -i "pandas.Series.product RT03" \
         -i "pandas.Series.reorder_levels RT03,SA01" \
@@ -260,7 +260,6 @@ if [[ -z "$CHECK" || "$CHECK" == "docstrings" ]]; then
         -i "pandas.Timestamp.to_period PR01,SA01" \
         -i "pandas.Timestamp.today SA01" \
         -i "pandas.Timestamp.toordinal SA01" \
-        -i "pandas.Timestamp.tz SA01" \
         -i "pandas.Timestamp.tz_localize SA01" \
         -i "pandas.Timestamp.tzinfo GL08" \
         -i "pandas.Timestamp.tzname SA01" \
diff --git a/doc/source/whatsnew/v3.0.0.rst b/doc/source/whatsnew/v3.0.0.rst
index f7039021ff..ec7b8b375a 100644
--- a/doc/source/whatsnew/v3.0.0.rst
+++ b/doc/source/whatsnew/v3.0.0.rst
@@ -39,6 +39,7 @@ Other enhancements
 - Users can globally disable any ``PerformanceWarning`` by setting the option ``mode.performance_warnings`` to ``False`` (:issue:`56920`)
 - :meth:`Styler.format_index_names` can now be used to format the index and column names (:issue:`48936` and :issue:`47489`)
 - :class:`.errors.DtypeWarning` improved to include column names when mixed data types are detected (:issue:`58174`)
+- :func:`DataFrame.to_excel` argument ``merge_cells`` now accepts a value of ``"columns"`` to only merge :class:`MultiIndex` column header header cells (:issue:`35384`)
 - :meth:`DataFrame.corrwith` now accepts ``min_periods`` as optional arguments, as in :meth:`DataFrame.corr` and :meth:`Series.corr` (:issue:`9490`)
 - :meth:`DataFrame.cummin`, :meth:`DataFrame.cummax`, :meth:`DataFrame.cumprod` and :meth:`DataFrame.cumsum` methods now have a ``numeric_only`` parameter (:issue:`53072`)
 - :meth:`DataFrame.fillna` and :meth:`Series.fillna` can now accept ``value=None``; for non-object dtype the corresponding NA value will be used (:issue:`57723`)
diff --git a/pandas/_libs/tslibs/timestamps.pyx b/pandas/_libs/tslibs/timestamps.pyx
index 93715c907d..628527bd4f 100644
--- a/pandas/_libs/tslibs/timestamps.pyx
+++ b/pandas/_libs/tslibs/timestamps.pyx
@@ -2368,6 +2368,17 @@ timedelta}, default 'raise'
         """
         Alias for tzinfo.
 
+        The `tz` property provides a simple and direct way to retrieve the timezone
+        information of a `Timestamp` object. It is particularly useful when working
+        with time series data that includes timezone information, allowing for easy
+        access and manipulation of the timezone context.
+
+        See Also
+        --------
+        Timestamp.tzinfo : Returns the timezone information of the Timestamp.
+        Timestamp.tz_convert : Convert timezone-aware Timestamp to another time zone.
+        Timestamp.tz_localize : Localize the Timestamp to a timezone.
+
         Examples
         --------
         >>> ts = pd.Timestamp(1584226800, unit='s', tz='Europe/Stockholm')
diff --git a/pandas/_typing.py b/pandas/_typing.py
index d90596878b..09a3f58d6a 100644
--- a/pandas/_typing.py
+++ b/pandas/_typing.py
@@ -510,6 +510,7 @@ ToStataByteorder = Literal[">", "<", "little", "big"]
 
 # ExcelWriter
 ExcelWriterIfSheetExists = Literal["error", "new", "replace", "overlay"]
+ExcelWriterMergeCells = Union[bool, Literal["columns"]]
 
 # Offsets
 OffsetCalendar = Union[np.busdaycalendar, "AbstractHolidayCalendar"]
diff --git a/pandas/core/arrays/sparse/accessor.py b/pandas/core/arrays/sparse/accessor.py
index 6a1c25711a..39a70fba9a 100644
--- a/pandas/core/arrays/sparse/accessor.py
+++ b/pandas/core/arrays/sparse/accessor.py
@@ -98,8 +98,8 @@ class SparseAccessor(BaseAccessor, PandasDelegate):
         ...     ([3.0, 1.0, 2.0], ([1, 0, 0], [0, 2, 3])), shape=(3, 4)
         ... )
         >>> A
-        <3x4 sparse matrix of type '<class 'numpy.float64'>'
-        with 3 stored elements in COOrdinate format>
+        <COOrdinate sparse matrix of dtype 'float64'
+            with 3 stored elements and shape (3, 4)>
 
         >>> A.todense()
         matrix([[0., 0., 1., 2.],
@@ -186,8 +186,8 @@ class SparseAccessor(BaseAccessor, PandasDelegate):
         ...     row_levels=["A", "B"], column_levels=["C", "D"], sort_labels=True
         ... )
         >>> A
-        <3x4 sparse matrix of type '<class 'numpy.float64'>'
-        with 3 stored elements in COOrdinate format>
+        <COOrdinate sparse matrix of dtype 'float64'
+            with 3 stored elements and shape (3, 4)>
         >>> A.todense()
         matrix([[0., 0., 1., 3.],
         [3., 0., 0., 0.],
@@ -380,8 +380,8 @@ class SparseFrameAccessor(BaseAccessor, PandasDelegate):
         --------
         >>> df = pd.DataFrame({"A": pd.arrays.SparseArray([0, 1, 0, 1])})
         >>> df.sparse.to_coo()
-        <4x1 sparse matrix of type '<class 'numpy.int64'>'
-                with 2 stored elements in COOrdinate format>
+        <COOrdinate sparse matrix of dtype 'int64'
+            with 2 stored elements and shape (4, 1)>
         """
         import_optional_dependency("scipy")
         from scipy.sparse import coo_matrix
diff --git a/pandas/core/series.py b/pandas/core/series.py
index ee34240366..0a67ad7e57 100644
--- a/pandas/core/series.py
+++ b/pandas/core/series.py
@@ -5020,7 +5020,8 @@ class Series(base.IndexOpsMixin, NDFrame):  # type: ignore[misc]
 
         Returns
         -------
-        Value that is popped from series.
+        scalar
+            Value that is popped from series.
 
         Examples
         --------
diff --git a/pandas/io/formats/excel.py b/pandas/io/formats/excel.py
index a98d9c175c..52b5755558 100644
--- a/pandas/io/formats/excel.py
+++ b/pandas/io/formats/excel.py
@@ -52,6 +52,7 @@ from pandas.io.formats.printing import pprint_thing
 
 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 merged cells
+        if True. Merge MultiIndex column headers only if 'columns'.
+        .. versionchanged:: 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 @@ class ExcelFormatter:
         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 @@ class ExcelFormatter:
         self.index = index
         self.index_label = index_label
         self.header = header
+
+        if not isinstance(merge_cells, bool) and merge_cells != "columns":
+            raise ValueError(f"Unexpected value for {merge_cells=}.")
         self.merge_cells = merge_cells
         self.inf_rep = inf_rep
 
@@ -614,7 +621,7 @@ class ExcelFormatter:
 
         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 @@ class ExcelFormatter:
         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 @@ class ExcelFormatter:
             # 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 @@ class ExcelFormatter:
                 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 a695c53997..6f4c2fa6c6 100644
--- a/pandas/io/formats/style.py
+++ b/pandas/io/formats/style.py
@@ -66,6 +66,7 @@ if TYPE_CHECKING:
         Axis,
         AxisInt,
         Concatenate,
+        ExcelWriterMergeCells,
         FilePath,
         IndexLabel,
         IntervalClosedType,
@@ -551,7 +552,7 @@ class Styler(StylerRenderer):
         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/io/formats/style_render.py b/pandas/io/formats/style_render.py
index 19a3563f43..ec718f2a12 100644
--- a/pandas/io/formats/style_render.py
+++ b/pandas/io/formats/style_render.py
@@ -1052,7 +1052,7 @@ class StylerRenderer:
         When using a ``formatter`` string the dtypes must be compatible, otherwise a
         `ValueError` will be raised.
 
-        When instantiating a Styler, default formatting can be applied be setting the
+        When instantiating a Styler, default formatting can be applied by setting the
         ``pandas.options``:
 
           - ``styler.format.formatter``: default None.


Test patch:
diff --git a/pandas/tests/groupby/test_groupby.py b/pandas/tests/groupby/test_groupby.py
index 3506fb55e0..13fb9cfc4c 100644
--- a/pandas/tests/groupby/test_groupby.py
+++ b/pandas/tests/groupby/test_groupby.py
@@ -2985,3 +2985,14 @@ def test_groupby_agg_namedagg_with_duplicate_columns():
     )
 
     tm.assert_frame_equal(result, expected)
+
+
+def test_groupby_multi_index_codes():
+    # GH#54347
+    df = DataFrame(
+        {"A": [1, 2, 3, 4], "B": [1, float("nan"), 2, float("nan")], "C": [2, 4, 6, 8]}
+    )
+    df_grouped = df.groupby(["A", "B"], dropna=False).sum()
+
+    index = df_grouped.index
+    tm.assert_index_equal(index, MultiIndex.from_frame(index.to_frame()))
diff --git a/pandas/tests/io/excel/test_writers.py b/pandas/tests/io/excel/test_writers.py
index ad1f22224b..482b331332 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