`pd.DataFrame.to_xarray()` and `xr.Dataset(<pd.DataFrame>)` are not equivalent

What is your issue?

Suppose we have a pandas DataFrame named df. df.to_xarray() and xr.Dataset(df) both cast the DataFrame to an xarray Dataset object, but they are not equivalent.

They handle multi-indexed DataFrames differently. Specifically:

df.to_xarray()

• Converts each level of the DataFrame MultIndex into a separate dimension for the resulting Dataset.
• Errors if the DataFrame has more than one row for any unique set of MultiIndex levels
• The resulting Dataset smoothly coverts back to the original pandas df via .to_dataframe()

xr.Dataset(df)

• Produces a dataset with a single, multi-indexed dimension whose coordinates are the levels of the pandas MultiIndex.
• Does not error if the DataFrame has for than one row for any unique set of MultiIndex levels.
• Converting back to pandas via to_dataframe() does not yield the original dataframe, because of #10538

Reprex

import xarray as xr
import pandas as pd

df_unique_idx = (
    pd.DataFrame(
        dict(
            x=[1, 2, 3, 1, 2, 3],
            y=["a", "a", "a", "b", "b", "b"],
            z=[5, 10, 15, 20, 25, 30],
        )
    )
    .set_index(["x", "y"])
    .sort_index()
)

ds_via_constructor = xr.Dataset(df_unique_idx)
print(ds_via_constructor)  # single dim_0 with coordinates x and y
# <xarray.Dataset> Size: 192B
# Dimensions:  (dim_0: 6)
# Coordinates:
#   * dim_0    (dim_0) object 48B MultiIndex
#   * x        (dim_0) int64 48B 1 2 3 1 2 3
#   * y        (dim_0) object 48B 'a' 'a' 'a' 'b' 'b' 'b'
# Data variables:
#     z        (dim_0) int64 48B 5 10 15 20 25 30

ds_via_to_xarray = df_unique_idx.to_xarray()
print(ds_via_to_xarray)  # x and y are dims
# <xarray.Dataset> Size: 88B
# Dimensions:  (x: 3, y: 2)
# Coordinates:
#   * x        (x) int64 24B 1 2 3
#   * y        (y) object 16B 'a' 'b'
# Data variables:
#     z        (x, y) int64 48B 5 20 10 25 15 30


round_trip_via_constructor = ds_via_constructor.to_dataframe()
print(
    round_trip_via_constructor.equals(df_unique_idx)
)  # False because of duplicated columns, see issue #10538
print(round_trip_via_constructor)
# False
#      x  y   z
# x y
# 1 a  1  a   5
#   b  1  b  20
# 2 a  2  a  10
#   b  2  b  25
# 3 a  3  a  15
#   b  3  b  30

round_trip_via_to_xarray = ds_via_to_xarray.to_dataframe()
print(round_trip_via_to_xarray.equals(df_unique_idx))  # True
# True

# create a multi-indexed pandas dataframe
# with a non-unique MultiIndex
df_non_unique_idx = pd.DataFrame(
    dict(
        x=[1, 2, 3, 1, 2, 3, 1],
        y=["a", "a", "a", "b", "b", "b", "a"],
        z=[5, 10, 15, 20, 25, 30, 35],
    )
).set_index(["x", "y"])

ds_via_constructor = xr.Dataset(df_non_unique_idx)  # succeeds

try:
    ds_via_to_xarray = df_non_unique_idx.to_xarray()  # errors
except ValueError as e:
    print(e)
# cannot convert a DataFrame with a non-unique MultiIndex into xarray

Thoughts

I don't think this is a bug per se, but it seems potentially surprising for the user that these two methods for converting DataFrame to Dataset behave so differently.

Start by running the pandas DataFrame and xarray Dataset conversion examples in the issue, including both unique and non-unique MultiIndexes. Compare the behavior of df.to_xarray(), xr.Dataset(df), and Dataset.to_dataframe(); the work is complete when the intended behavior is decided and the relevant round-trip inconsistency is resolved or clearly documented.