Multidimensional xarray to dataframe to excel-CodePudding

I have a three dimensional xarray in python built as

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

data_np = np.array([ 
                [ [1, 2, 0, 8], [3, 4, 11, 2], [5, 6, 43, 90] ],
                [ [7, 8, 2, 66], [9, 10, 31, 21], [11, 12, 56, 45] ] 
                ])

dims = ['patient', 'parameter', 'day']

coords = {'patient':['Joe','Bill'], 'parameter':['a', 'b', 'c'], 'day':[0, 1, 2, 3]}

data = xr.DataArray(data_np, dims = dims, coords = coords )

I want to export it to an excel sheet with this structure:

Desired output

Using Pandas data frames I can get the excel sheet with all the variables (dims) branching along the same axis:

df = data.to_dataframe('value')
df = df.transpose()

with pd.ExcelWriter("main.xlsx") as writer: 
    df.to_excel(writer)

Obtained output:

How can I obtained the desired structure?

CodePudding user response：

You can use unstack() method to arrange the data as you want:

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

data_np = np.array([ 
                [ [1, 2, 0, 8], [3, 4, 11, 2], [5, 6, 43, 90] ],
                [ [7, 8, 2, 66], [9, 10, 31, 21], [11, 12, 56, 45] ] 
                ])

dims = ['patient', 'parameter', 'day']

coords = {'patient':['Joe','Bill'], 'parameter':['a', 'b', 'c'], 'day':[0, 1, 2, 3]}

data = xr.DataArray(data_np, dims = dims, coords = coords )

df = data.to_series().unstack(level=[1,2])
with pd.ExcelWriter("main.xlsx") as writer: 
    df.to_excel(writer)

Or you can avoid using xarray and use pd.MultiIndex to create DataFrame directly from NumPy array:

import numpy as np
import pandas as pd

data_np = np.array([ 
                [ [1, 2, 0, 8], [3, 4, 11, 2], [5, 6, 43, 90] ],
                [ [7, 8, 2, 66], [9, 10, 31, 21], [11, 12, 56, 45] ] 
                ])

dims = ['patient', 'parameter', 'day']

coords = {'patient':['Joe','Bill'], 'parameter':['a', 'b', 'c'], 'day':[0, 1, 2, 3]}

mi = pd.MultiIndex.from_product([coords[dims[i]] for i in range(3)], names=dims)
df = pd.Series(data_np.flatten(), index=mi).unstack(level=[1,2])

with pd.ExcelWriter("main.xlsx") as writer: 
    df.to_excel(writer)

CodePudding user response：

Use unstack:

data.to_dataframe('value').unstack(level=[1, 2]).droplevel(level=0, axis=1) \
    .rename_axis(index=None, columns=['Parameter', 'Patient / Day'])