Best way to 'unmelt' Pandas output?

[trexfeathers]

Something either for the iris.pandas docstring examples, or actual Iris API, depending on how easy it is using Pandas API.

Sometimes data scientists find it helpful to specify a dimension of interest for a machine learning model. This should be 'pivoted'/'unmelted' into individual DataFrame columns for each dimension value (e.g. 33 columns for 33 height levels).

Can this be done by converting the fully melted DataFrame that iris.pandas.to_dataframe() produces? If that's not easy using Pandas API, what can we do within Iris to aid the conversion between dimensional and tabular data?

From @stevehadd and @hannahbrown7

[trexfeathers]

@stevehadd @hannahbrown7 @DPeterK @pp-mo @nathan962 @stephenworsley how's this:

Environment requirements

iris ==3.4
iris-sample-data
pandas

Code

>>> import iris
>>> from iris.pandas import as_data_frame
>>> import pandas as pd

# Activate the new n-dimensional Cube-to-DataFrame conversion.
>>> iris.FUTURE.pandas_ndim = True

# Configure Pandas display.
>>> pd.set_option("display.width", 256)
>>> pd.set_option("display.max_columns", 6)
>>> pd.set_option("display.max_rows", 12)
>>> pd.set_option("display.precision", 3)

>>> path = iris.sample_data_path('ostia_monthly.nc')
>>> cube = iris.load_cube(path)
>>> print(cube)

surface_temperature / (K)           (time: 54; latitude: 18; longitude: 432)
    Dimension coordinates:
        time                             x             -              -
        latitude                         -             x              -
        longitude                        -             -              x
    Auxiliary coordinates:
        forecast_reference_time          x             -              -
    Scalar coordinates:
        forecast_period             0 hours
    Cell methods:
        mean                        month, year
    Attributes:
        Conventions                 'CF-1.5'
        STASH                       m01s00i024

---

Here's the 'vanilla' multi-dimensional conversion:

>>> df = as_data_frame(cube)
>>> print(df)

                                        surface_temperature
time                latitude longitude                     
2006-04-16 00:00:00 -5.000   0.000                  301.659
                             0.833                  301.785
                             1.667                  301.821
                             2.500                  301.865
                             3.333                  301.927
...                                                     ...
2010-09-16 00:00:00  4.444   355.833                298.780
                             356.667                298.913
                             357.500                    NaN
                             358.333                    NaN
                             359.167                298.995

[419904 rows x 1 columns]

---

An index level can be converted to columns using pandas.DataFrame.unstack():

>>> interest_dim = "latitude"
>>> df_unstacked = df.unstack(level=interest_dim)
>>> print(df_unstacked)

                              surface_temperature                    ...                           
latitude                                   -5.000   -4.444   -3.889  ...    3.333    3.889    4.444
time                longitude                                        ...                           
2006-04-16 00:00:00 0.000                 301.659  301.673  301.646  ...  302.920  302.900  302.789
                    0.833                 301.785  301.745  301.566  ...  302.919  302.865  302.834
                    1.667                 301.821  301.735  301.628  ...  302.899  302.856  302.809
                    2.500                 301.865  301.818  301.689  ...  302.833  302.758  302.749
                    3.333                 301.927  301.900  301.755  ...  302.805  302.758  302.692
...                                           ...      ...      ...  ...      ...      ...      ...
2010-09-16 00:00:00 355.833               296.961  297.047  297.073  ...  299.506  299.070  298.780
                    356.667               296.884  296.953  297.014  ...  299.464  299.042  298.913
                    357.500               296.759  296.830  296.928  ...  299.427  299.112      NaN
                    358.333               296.749  296.790  296.865  ...  299.511  299.217      NaN
                    359.167               296.681  296.794  296.895  ...  299.594  299.339  298.995

[23328 rows x 18 columns]

---

If the row and column headers being MultiIndexes doesn't work for you, there are plenty of Pandas options for simplifying the format. For example:

>>> df_formatted = df_unstacked.copy(deep=True)
>>> col_index = df_formatted.columns.names.index(interest_dim)
>>> df_formatted.columns = ["lat_{:.1f}".format(c[col_index]) for c in df_formatted.columns]
>>> df_formatted.reset_index(inplace=True)
>>> print(df_formatted)

                      time  longitude  lat_-5.0  ...  lat_3.3  lat_3.9  lat_4.4
0      2006-04-16 00:00:00      0.000   301.659  ...  302.920  302.900  302.789
1      2006-04-16 00:00:00      0.833   301.785  ...  302.919  302.865  302.834
2      2006-04-16 00:00:00      1.667   301.821  ...  302.899  302.856  302.809
3      2006-04-16 00:00:00      2.500   301.865  ...  302.833  302.758  302.749
4      2006-04-16 00:00:00      3.333   301.927  ...  302.805  302.758  302.692
...                    ...        ...       ...  ...      ...      ...      ...
23323  2010-09-16 00:00:00    355.833   296.961  ...  299.506  299.070  298.780
23324  2010-09-16 00:00:00    356.667   296.884  ...  299.464  299.042  298.913
23325  2010-09-16 00:00:00    357.500   296.759  ...  299.427  299.112      NaN
23326  2010-09-16 00:00:00    358.333   296.749  ...  299.511  299.217      NaN
23327  2010-09-16 00:00:00    359.167   296.681  ...  299.594  299.339  298.995

[23328 rows x 20 columns]

[stevehadd]

that looks exactly like the sort of thing I was looking for @trexfeathers ! I'm definiitely going to try and update the data prep code use this and see whether there were any any gotchas I've forgotten about.

[trexfeathers]

Hi @stevehadd, did this work for you?