CODEWITHSUNDEEP
pythondataframemultidimensional-arraydatasetpython-xarray
C Elise
C Elise

Reputation: 11

Converting 3D xarray dataset to dataframe

I have imported a xarray dataset like this and extracted the values at coordinates defined by zones from a csv file, and a time period defined by a date range (30 days of a (lon,lat) grid with some environmental values for every coordinates).

from xgrads import open_CtlDataset
ds_Snow = open_CtlDataset(path + 'file')
ds_Snow = ds_Snow.sel(lat = list(set(zones['lat'])), lon = list(set(zones['lon'])), 
time = period, method = 'nearest')

When i look for the information of ds_Snow, this is what I get :

Dimensions:  (lat: 12, lon: 12, time: 30)
Coordinates:
  * time     (time) datetime64[ns] 2000-09-01 2000-09-02 ... 2000-09-30
  * lat      (lat) float32 3.414e+06 3.414e+06 3.414e+06 ... 3.414e+06 3.414e+06
  * lon      (lon) float32 6.873e+05 6.873e+05 6.873e+05 ... 6.873e+05 6.873e+05
Data variables:
    spre     (time, lat, lon) float32 dask.array<chunksize=(1, 12, 12), meta=np.ndarray>
Attributes:
    title:    SnowModel
    undef:    -9999.0 type : <class 'xarray.core.dataset.Dataset'>

I would like to make it a dataframe, respecting the initial dimensions (time, lat, lon). So I did this :

df_Snow = ds_Snow.to_dataframe()

But here are the dimensions of the dataframe :

print(df_Snow)
lat       lon        time            
3414108.0 687311.625 2000-09-01   0.0
                     2000-09-02   0.0
                     2000-09-03   0.0
                     2000-09-04   0.0
                     2000-09-05   0.0
...                               ...
                     2000-09-26   0.0
                     2000-09-27   0.0
                     2000-09-28   0.0
                     2000-09-29   0.0
                     2000-09-30   0.0

[4320 rows x 1 columns]

It looks like all the data just got put in a single column. I have tried giving the dimensions orders as some documentation explained :

df_Snow = ds_Snow.to_dataframe(dim_order = ['time', 'lat', 'lon'])

But it does not change anything, and I can't seem to find an answer in forums or the documentation. I would like to know a way to keep the array configuration in the dataframe.

EDIT : I found a solution

Instead of converting the xarray, I chose to build my dataframe with pd.Series of each attributes like this :

ds_Snow = ds_Snow.sel(lat = list(set(station_list['lat_utm'])),lon = list(set(station_list['lon_utm'])), time = Ind_Run_ERA5_Land, method = 'nearest')
time = pd.Series(ds_Snow.coords["time"].values)
lon = pd.Series(ds_Snow.coords["lon"].values)
lat = pd.Series(ds_Snow.coords["lat"].values)
spre = pd.Series(ds_Snow['spre'].values[:,0,0])
frame = { 'spre': spre, 'time': time, 'lon' : lon,  'lat' : lat}
df_Snow = pd.DataFrame(frame)

Upvotes: 1

Views: 1662

Answers (1)

Matt Hall
Matt Hall

Reputation: 8112

This is the expected behaviour. From the docs:

The DataFrame is indexed by the Cartesian product of index coordinates (in the form of a pandas.MultiIndex). Other coordinates are included as columns in the DataFrame.

There is only one variable, spre, in the dataset. The other properties, the 'coordinates' have become the index. Since there were several coordinates (lat, lon, and time), the DataFrame has a hierarchical MultiIndex.

You can either get the index data through tools like get_level_values or, if you want to change how the DataFrame is indexed, you can use reset_index().

Upvotes: 1

Related Questions