Repository: pydata/xarray

Issue:
Duplicate values on unstack
### What happened?

I unstacked a dataset and got values I didn't expect. It turns out that, when unstacking, my dataset had multiple values for the same index. This is clearly a case of user error, but it silently passed. 

### What did you expect to happen?

A warning or error would be raised to say, "this isn't going to work".

### Minimal Complete Verifiable Example

```Python
import datetime as dt
import xarray as xr


ds = xr.DataArray(
    [[1, 2, 3], [4, 5, 6]],
    dims=("lat", "time"),
    coords={"lat": [-60, 60], "time": [dt.datetime(2010, 1, d) for d in range(1, 4)]},
    name="test",
).to_dataset()

ds = (
    ds.assign_coords(
        {
            "month": ds["time"].dt.month,
            "year": ds["time"].dt.year,
        }
    )
    .set_index(time=["month", "year"])
)
ds = ds.unstack("time")

# the output only has 2 values, which isn't what I expected
ds["test"].data
```


### MVCE confirmation

- [X] Minimal example — the example is as focused as reasonably possible to demonstrate the underlying issue in xarray.
- [X] Complete example — the example is self-contained, including all data and the text of any traceback.
- [X] Verifiable example — the example copy & pastes into an IPython prompt or [Binder notebook](https://mybinder.org/v2/gh/pydata/xarray/main?urlpath=lab/tree/doc/examples/blank_template.ipynb), returning the result.
- [X] New issue — a search of GitHub Issues suggests this is not a duplicate.

### Relevant log output

_No response_

### Anything else we need to know?

It's not clear to me where the error is. It might just be that this particular order of operations leads to a case that isn't otherwise caught. Looking at intermediate output, I thought the error was in unstack but maybe it's more complex than that...

### Environment

<details>

INSTALLED VERSIONS
------------------
commit: e678a1d7884a3c24dba22d41b2eef5d7fe5258e7
python: 3.8.13 | packaged by conda-forge | (default, Mar 25 2022, 06:04:14) 
[Clang 12.0.1 ]
python-bits: 64
OS: Darwin
OS-release: 21.5.0
machine: arm64
processor: arm
byteorder: little
LC_ALL: None
LANG: en_AU.UTF-8
LOCALE: ('en_AU', 'UTF-8')
libhdf5: 1.12.2
libnetcdf: 4.8.1

xarray: 0.1.dev4312+ge678a1d.d20220928
pandas: 1.5.0
numpy: 1.22.4
scipy: 1.9.1
netCDF4: 1.6.1
pydap: installed
h5netcdf: 1.0.2
h5py: 3.7.0
Nio: None
zarr: 2.13.2
cftime: 1.6.2
nc_time_axis: 1.4.1
PseudoNetCDF: 3.2.2
rasterio: 1.3.1
cfgrib: 0.9.10.1
iris: 3.3.0
bottleneck: 1.3.5
dask: 2022.9.1
distributed: 2022.9.1
matplotlib: 3.6.0
cartopy: 0.21.0
seaborn: 0.12.0
numbagg: 0.2.1
fsspec: 2022.8.2
cupy: None
pint: 0.19.2
sparse: 0.13.0
flox: 0.5.9
numpy_groupies: 0.9.19
setuptools: 65.4.0
pip: 22.2.2
conda: None
pytest: 7.1.3
IPython: 8.5.0
sphinx: None

</details>


Discussion:
znicholls: Looks super nice, thanks!

Patch:
diff --git a/doc/whats-new.rst b/doc/whats-new.rst
index ed0b1c30..50eece5f 100644
--- a/doc/whats-new.rst
+++ b/doc/whats-new.rst
@@ -74,6 +74,8 @@ Bug fixes
   lead to integer overflow or unsafe conversion from floating point to integer
   values (:issue:`8542`, :pull:`8575`).  By `Spencer Clark
   <https://github.com/spencerkclark>`_.
+- Raise an error when unstacking a MultiIndex that has duplicates as this would lead
+  to silent data loss (:issue:`7104`, :pull:`8737`). By `Mathias Hauser <https://github.com/mathause>`_.
 
 Documentation
 ~~~~~~~~~~~~~
diff --git a/xarray/core/indexes.py b/xarray/core/indexes.py
index 1697762f..e71c4a6f 100644
--- a/xarray/core/indexes.py
+++ b/xarray/core/indexes.py
@@ -1017,6 +1017,13 @@ class PandasMultiIndex(PandasIndex):
     def unstack(self) -> tuple[dict[Hashable, Index], pd.MultiIndex]:
         clean_index = remove_unused_levels_categories(self.index)
 
+        if not clean_index.is_unique:
+            raise ValueError(
+                "Cannot unstack MultiIndex containing duplicates. Make sure entries "
+                f"are unique, e.g., by  calling ``.drop_duplicates('{self.dim}')``, "
+                "before unstacking."
+            )
+
         new_indexes: dict[Hashable, Index] = {}
         for name, lev in zip(clean_index.names, clean_index.levels):
             idx = PandasIndex(


Test patch:
diff --git a/xarray/tests/test_dataarray.py b/xarray/tests/test_dataarray.py
index d898d3a3..2829fd7d 100644
--- a/xarray/tests/test_dataarray.py
+++ b/xarray/tests/test_dataarray.py
@@ -2532,6 +2532,15 @@ class TestDataArray:
         actual = DataArray(s, dims="z").unstack("z")
         assert_identical(expected, actual)
 
+    def test_unstack_requires_unique(self) -> None:
+        df = pd.DataFrame({"foo": range(2), "x": ["a", "a"], "y": [0, 0]})
+        s = df.set_index(["x", "y"])["foo"]
+
+        with pytest.raises(
+            ValueError, match="Cannot unstack MultiIndex containing duplicates"
+        ):
+            DataArray(s, dims="z").unstack("z")
+
     @pytest.mark.filterwarnings("error")
     def test_unstack_roundtrip_integer_array(self) -> None:
         arr = xr.DataArray(
diff --git a/xarray/tests/test_dataset.py b/xarray/tests/test_dataset.py
index 267a5ca6..ae7d87bb 100644
--- a/xarray/tests/test_dataset.py
+++ b/xarray/tests/test_dataset.py
@@ -3764,6 +3764,14 @@ class TestDataset:
         with pytest.raises(ValueError, match=r".*do not have exactly one multi-index"):
             ds.unstack("x")
 
+        ds = Dataset({"da": [1, 2]}, coords={"y": ("x", [1, 1]), "z": ("x", [0, 0])})
+        ds = ds.set_index(x=("y", "z"))
+
+        with pytest.raises(
+            ValueError, match="Cannot unstack MultiIndex containing duplicates"
+        ):
+            ds.unstack("x")
+
     def test_unstack_fill_value(self) -> None:
         ds = xr.Dataset(
             {"var": (("x",), np.arange(6)), "other_var": (("x",), np.arange(3, 9))},
diff --git a/xarray/tests/test_indexes.py b/xarray/tests/test_indexes.py
index 866c2ef7..3ee7f045 100644
--- a/xarray/tests/test_indexes.py
+++ b/xarray/tests/test_indexes.py
@@ -452,6 +452,15 @@ class TestPandasMultiIndex:
         assert new_indexes["two"].equals(PandasIndex([1, 2, 3], "two"))
         assert new_pd_idx.equals(pd_midx)
 
+    def test_unstack_requires_unique(self) -> None:
+        pd_midx = pd.MultiIndex.from_product([["a", "a"], [1, 2]], names=["one", "two"])
+        index = PandasMultiIndex(pd_midx, "x")
+
+        with pytest.raises(
+            ValueError, match="Cannot unstack MultiIndex containing duplicates"
+        ):
+            index.unstack()
+
     def test_create_variables(self) -> None:
         foo_data = np.array([0, 0, 1], dtype="int64")
         bar_data = np.array([1.1, 1.2, 1.3], dtype="float64")