Align multiple DataFrames to a Dataset

An example notebook that reads data and convert it to xarray Dataset with aligned time and space axis.

The data used in this notebook is attached with the GitHub repository of motrainer. They can be found via this link.

Import libraries and set paths¶

In [1]:

import pandas as pd
import xarray as xr
import matplotlib.pyplot as plt # only for plots
from pathlib import Path

import pandas as pd import xarray as xr import matplotlib.pyplot as plt # only for plots from pathlib import Path

In [2]:

data_dir = "./example_data/dssat_s1/"
nc_file_path = "./example2_data.nc"
zarr_file_path = "./example2_data.zarr"

data_dir = "./example_data/dssat_s1/" nc_file_path = "./example2_data.nc" zarr_file_path = "./example2_data.zarr"

Read the data and explore it¶

In [3]:

pickle_files_path = list(Path(data_dir).rglob('*.pkl'))
pickle_files_path

pickle_files_path = list(Path(data_dir).rglob('*.pkl')) pickle_files_path

Out[3]:

[PosixPath('example_data/dssat_s1/DSSAT/brabant_LAI.pkl'),
 PosixPath('example_data/dssat_s1/DSSAT/brabant_CWAD.pkl'),
 PosixPath('example_data/dssat_s1/DSSAT/brabant_SWTD.pkl'),
 PosixPath('example_data/dssat_s1/DSSAT/brabant_SWTD6.pkl'),
 PosixPath('example_data/dssat_s1/Sentinel-1/Amp_CR_New.pkl')]

In [4]:

for file in pickle_files_path:
    df = pd.read_pickle(file)  # read file as pandas dataframe
    print(file.stem)  # check file name
    print(df.index.dtype)  # check index type
    print(len(df.index))  # check length of index
    print(len(df.columns))  # check length of columns
    print("********")

for file in pickle_files_path: df = pd.read_pickle(file) # read file as pandas dataframe print(file.stem) # check file name print(df.index.dtype) # check index type print(len(df.index)) # check length of index print(len(df.columns)) # check length of columns print("********")

brabant_LAI
datetime64[ns]
148
1283
********
brabant_CWAD
datetime64[ns]
148
1283
********
brabant_SWTD
datetime64[ns]
274
1283
********
brabant_SWTD6
datetime64[ns]
274
1283
********
Amp_CR_New
object
60
21927
********

Convert data¶

As seen above, the type of index of data are not consitent among different files e.g. "object" and "datetime64[ns]". Below, we convert it to the same type.

In [5]:

# Read the data
ds_list = []
for file in pickle_files_path:
    # read files and extract filename
    df = pd.read_pickle(file)
    var_name = file.stem

    # Check the dtype of the index. If it's not datetime64[ns], convert it.
    if df.index.dtype != "datetime64[ns]":
        df.index = pd.to_datetime(df.index)

    # convert dataframe to dataset
    ds = xr.Dataset({var_name: (["time", "space"], df.values)},
                     coords={"time": df.index, "space": df.columns})
    ds_list.append(ds)

# Create one dataset
dataset = xr.concat(ds_list, dim="time")

# Add attribute (metadata)
dataset.attrs['source'] = 'data source'
dataset.attrs['license'] = 'data license'

# Read the data ds_list = [] for file in pickle_files_path: # read files and extract filename df = pd.read_pickle(file) var_name = file.stem # Check the dtype of the index. If it's not datetime64[ns], convert it. if df.index.dtype != "datetime64[ns]": df.index = pd.to_datetime(df.index) # convert dataframe to dataset ds = xr.Dataset({var_name: (["time", "space"], df.values)}, coords={"time": df.index, "space": df.columns}) ds_list.append(ds) # Create one dataset dataset = xr.concat(ds_list, dim="time") # Add attribute (metadata) dataset.attrs['source'] = 'data source' dataset.attrs['license'] = 'data license'

Inspect output and store it¶

In [6]:

dataset

dataset

Out[6]:

<xarray.Dataset>
Dimensions:        (space: 21927, time: 904)
Coordinates:
  * space          (space) int64 1526157 1526199 1526200 ... 2311808 2311820
  * time           (time) datetime64[ns] 2017-05-06 2017-05-07 ... 2017-12-27
Data variables:
    brabant_LAI    (time, space) float64 0.0 0.0 0.0 0.0 0.0 ... nan nan nan nan
    brabant_CWAD   (time, space) float64 nan nan nan nan nan ... nan nan nan nan
    brabant_SWTD   (time, space) float64 nan nan nan nan nan ... nan nan nan nan
    brabant_SWTD6  (time, space) float64 nan nan nan nan nan ... nan nan nan nan
    Amp_CR_New     (time, space) float64 nan nan nan nan ... 0.445 0.3501 0.3075
Attributes:
    source:   data source
    license:  data license

xarray.Dataset

• Dimensions:
  • space: 21927
  • time: 904
• Coordinates: (2)
  • space
    (space)
    int64
    1526157 1526199 ... 2311808 2311820

    array([1526157, 1526199, 1526200, ..., 2311765, 2311808, 2311820])

  • time
    (time)
    datetime64[ns]
    2017-05-06 ... 2017-12-27

    array(['2017-05-06T00:00:00.000000000', '2017-05-07T00:00:00.000000000',
           '2017-05-08T00:00:00.000000000', ..., '2017-12-15T00:00:00.000000000',
           '2017-12-21T00:00:00.000000000', '2017-12-27T00:00:00.000000000'],
          dtype='datetime64[ns]')

• Data variables: (5)
  • brabant_LAI
    (time, space)
    float64
    0.0 0.0 0.0 0.0 ... nan nan nan nan

    array([[ 0.,  0.,  0., ..., nan, nan, nan],
           [ 0.,  0.,  0., ..., nan, nan, nan],
           [ 0.,  0.,  0., ..., nan, nan, nan],
           ...,
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan]])

  • brabant_CWAD
    (time, space)
    float64
    nan nan nan nan ... nan nan nan nan

    array([[nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           ...,
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan]])

  • brabant_SWTD
    (time, space)
    float64
    nan nan nan nan ... nan nan nan nan

    array([[nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           ...,
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan]])

  • brabant_SWTD6
    (time, space)
    float64
    nan nan nan nan ... nan nan nan nan

    array([[nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           ...,
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan],
           [nan, nan, nan, ..., nan, nan, nan]])

  • Amp_CR_New
    (time, space)
    float64
    nan nan nan ... 0.445 0.3501 0.3075

    array([[       nan,        nan,        nan, ...,        nan,        nan,
                   nan],
           [       nan,        nan,        nan, ...,        nan,        nan,
                   nan],
           [       nan,        nan,        nan, ...,        nan,        nan,
                   nan],
           ...,
           [0.14172222, 0.2719292 , 0.24513262, ..., 0.3101958 , 0.28541936,
            0.22427617],
           [0.20507831, 0.32489699, 0.31356163, ..., 0.35787517, 0.32660959,
            0.29032968],
           [0.19860778, 0.27824248, 0.27584513, ..., 0.4449813 , 0.35008485,
            0.30750795]])

• Indexes: (2)
  • space
    PandasIndex

    PandasIndex(Int64Index([1526157, 1526199, 1526200, 1526234, 1526249, 1526374, 1526375,
                1526429, 1526436, 1526442,
                ...
                2311568, 2311595, 2311608, 2311669, 2311688, 2311706, 2311743,
                2311765, 2311808, 2311820],
               dtype='int64', name='space', length=21927))

  • time
    PandasIndex

    PandasIndex(DatetimeIndex(['2017-05-06', '2017-05-07', '2017-05-08', '2017-05-09',
                   '2017-05-10', '2017-05-11', '2017-05-12', '2017-05-13',
                   '2017-05-14', '2017-05-15',
                   ...
                   '2017-11-03', '2017-11-09', '2017-11-15', '2017-11-21',
                   '2017-11-27', '2017-12-03', '2017-12-09', '2017-12-15',
                   '2017-12-21', '2017-12-27'],
                  dtype='datetime64[ns]', name='time', length=904, freq=None))

• Attributes: (2)

  source :

  data source

  license :

  data license

In [7]:

# Time series of one variable at one location
Amp_CR_New = dataset.Amp_CR_New.isel(space=0)
Amp_CR_New.plot()

# Time series of one variable at one location Amp_CR_New = dataset.Amp_CR_New.isel(space=0) Amp_CR_New.plot()

Out[7]:

[<matplotlib.lines.Line2D at 0x7f28a87059d0>]

In [8]:

# Save data in netcdf format
dataset.to_netcdf(nc_file_path)

# Save data in netcdf format dataset.to_netcdf(nc_file_path)

In [9]:

# For large dataset, chunk the data and save data in zarr format
dataset.chunk({'space':1000})
dataset.to_zarr(zarr_file_path)

# For large dataset, chunk the data and save data in zarr format dataset.chunk({'space':1000}) dataset.to_zarr(zarr_file_path)

Out[9]:

<xarray.backends.zarr.ZarrStore at 0x7f28909af8b0>