`Dataset.to_dataframe()` and `DataArray.to_dataframe()` convert multi-index levels both to pandas `MultiIndex` and to columns

[dylanhmorris]

Overview

When to_dataframe() is called on an xarray Dataset with a multi-dimensional index along a given dimension, the index coordinates are translated both:

• into levels of a pandas MultiIndex for the dataframe
• into individual columns of the dataframe.

Is this expected and intended behavior?

Main reprex

import numpy as np
import pandas as pd
import xarray as xr

data_dict = dict(x=[1, 2, 1, 2, 1], y=["a", "a", "b", "b", "b"], z=[5, 10, 15, 20, 25])
data_dict_w_dims = {k: ("my_dim", v) for k, v in data_dict.items()}

# create a dataset multi-indexed along "my_dim" by "x" and "y" 
xr_dat = xr.Dataset(data_dict_w_dims).set_coords(["x", "y"]).set_xindex(["x", "y"])

print(xr_dat)
# <xarray.Dataset> Size: 140B
# Dimensions:  (my_dim: 5)
# Coordinates:
#   * my_dim   (my_dim) object 40B MultiIndex
#   * x        (my_dim) int64 40B 1 2 1 2 1
#   * y        (my_dim) <U1 20B 'a' 'a' 'b' 'b' 'b'
# Data variables:
#     z        (my_dim) int64 40B 5 10 15 20 25

print(xr_dat.to_dataframe()) # x and y present both as columns and as multi-index
#       z  x  y
# x y
# 1 a   5  1  a
# 2 a  10  2  a
# 1 b  15  1  b
# 2 b  20  2  b
# 1 b  25  1  b

Cause

I believe the key line is here in the _to_dataframe() internal method:

xarray/xarray/core/dataset.py

Lines 7092 to 7095 in 699d895

	def _to_dataframe(self, ordered_dims: Mapping[Any, int]):
	from xarray.core.extension_array import PandasExtensionArray
	columns_in_order = [k for k in self.variables if k not in self.dims]

The constituent IndexArrays of the multi-index are present in self.variables (and not in self.dims), so they become columns:

"x" in xr_dat.dims
# False
"x" in xr_dat.variables
# True
xr_dat.variables["x"]
# <xarray.IndexVariable 'my_dim' (my_dim: 5)> Size: 40B
# [5 values with dtype=int64]

This has consequences for pandas -> xarray -> pandas conversion

Because of this, converting a MultiIndex-ed pandas dataframe to an xarray Dataset via the xr.Dataset() constructor and then converting back to pandas via .to_dataframe() will not give back the original dataframe.

Reprex

# create a multi-indexed pandas dataframe
pd_df = pd.DataFrame(
   data_dict   
).set_index(["x", "y"])

print(pd_df) # multi-indexed-df with one column
#       z
# x y
# 1 a   5
# 2 a  10
# 1 b  15
# 2 b  20
# 1 b  25

# Conversion to xarray is as expected:
xr_from_pd = xr.Dataset(pd_df)
print(xr_from_pd)
# <xarray.Dataset> Size: 160B
# Dimensions:  (dim_0: 5)
# Coordinates:
#   * dim_0    (dim_0) object 40B MultiIndex
#   * x        (dim_0) int64 40B 1 2 1 2 1
#   * y        (dim_0) object 40B 'a' 'a' 'b' 'b' 'b'
# Data variables:
#     z        (dim_0) int64 40B 5 10 15 20 25

# Converting back to pandas df via `to_dataframe()` yields a df multi-indexed by 
# x and y that also contains `x` and `y` as columns:

print(xr_from_pd.to_dataframe()) # x and y as multi-index and as columns
#      x  y   z
# x y
# 1 a  1  a   5
# 2 a  2  a  10
# 1 b  1  b  15
# 2 b  2  b  20
# 1 b  1  b  25

Thoughts

• If this behavior is not intended, the flagged line in _to_dataframe() should be changed to determine column names in a way that ignores IndexVariables that form part of a multi-index.
• It might be important not just to filter to data variables, because one might want coordinates to become columns when they are not going to be part of the pandas MultiIndex, e.g.

# similar dataset with x and y as coordinates but not as a multi-index
dat_no_multiindex = xr.Dataset(
    data_dict_w_dims
).set_coords(["x", "y"])

# potentially intended behavior?
print(dat_no_multiindex.to_dataframe())
#        x  y   z
# my_dim
# 0       1  a   5
# 1       2  a  10
# 2       1  b  15
# 3       2  b  20
# 4       1  b  25

[dylanhmorris]

Addendum, inspired by the discussion in #10581

The test here passes despite the issue noted above:

xarray/xarray/tests/test_pandas_to_xarray.py

Lines 159 to 176 in 85b5a16

	def test_to_xarray_with_multiindex(self, df):
	from xarray import Dataset
	# MultiIndex
	df.index = MultiIndex.from_product([["a"], range(4)], names=["one", "two"])
	result = df.to_xarray()
	assert result.sizes["one"] == 1
	assert result.sizes["two"] == 4
	assert len(result.coords) == 2
	assert len(result.data_vars) == 8
	tm.assert_almost_equal(list(result.coords.keys()), ["one", "two"])
	assert isinstance(result, Dataset)
	result = result.to_dataframe()
	expected = df.copy()
	expected["f"] = expected["f"].astype(object)
	expected.columns.name = None
	tm.assert_frame_equal(result, expected)

This occurs becausedf.to_xarray() is not equivalent to xr.Dataset(df). Both operations produce an xarray Dataset from a pandas DataFrame, but 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. That Dataset smoothly coverts back to pandas without producing redundancy.
• xr.Dataset(df) produces a dataset with a single, multi-indexed dimension whose coordinates are the levels of the pandas MultiIndex. That Dataset converts back to pandas with duplication.

[dylanhmorris]

Thoughts on this from maintainers? I would be happy to contribute a PR if this is not intended behavior.

[dcherian]

i believe we only want to preserve the multi-index and no duplicate data. A PR would be very welcome!

[dylanhmorris]

While working on this I have found that the same behavior occurs with individual DataArrays as well. This occurs DataArray.to_dataframe() first converts the DataArray to a Dataset: and then calls the _to_dataframe() internal method (so the fix for Dataset._to_dataframe() should also successfully address the DataArray behavior)

xarray/xarray/core/dataarray.py

Lines 3953 to 3963 in 699d895

	# dataframe with columns in the wrong order, for reasons I have not
	# been able to debug (possibly a pandas bug?).
	unique_name = "__unique_name_identifier_z98xfz98xugfg73ho__"
	ds = self._to_dataset_whole(name=unique_name)
	if dim_order is None:
	ordered_dims = dict(zip(self.dims, self.shape, strict=True))
	else:
	ordered_dims = ds._normalize_dim_order(dim_order=dim_order)
	df = ds._to_dataframe(ordered_dims)

New (currently failing) test I've added for DataArray

        arr_np = np.random.randn(4, 3)
        arr_multi_single = DataArray(
            arr_np.flatten(), [("MI", mindex_single)], dims="MI", name="test"
        )
        actual_df = arr_multi_single.to_dataframe()
        expected_df = arr_multi_single.to_series().to_frame()
        assert expected_df.equals(actual_df)

[dylanhmorris]

@dcherian I have opened #10671