Covert a nested DataFrame 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.

This notebook generates the example dataset ./example1_data.zarr/ for the following example notebooks:

• Prallely training sklearn models with dask-ml
• Prallely training DNN with Tensorflow

Import libraries and set paths¶

In [1]:

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

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

In [2]:

pickle_file_path = "./example_data/example_data.pickle"
nc_file_path = "./example1_data.nc"
zarr_file_path = "./example1_data.zarr"

pickle_file_path = "./example_data/example_data.pickle" nc_file_path = "./example1_data.nc" zarr_file_path = "./example1_data.zarr"

Read the data and explore it¶

In [3]:

# Read the data
df_all_gpi = pd.read_pickle(pickle_file_path)

# Read the data df_all_gpi = pd.read_pickle(pickle_file_path)

In [4]:

df_all_gpi

df_all_gpi

Out[4]:

	lat	lon	data
1	56.125	11.375	sig slop curv ...
2	46.125	6.625	sig slop curv ...
3	53.375	6.125	sig slop curv ...
4	49.375	12.375	sig slop curv ...
5	44.375	0.625	sig slop curv ...

In [5]:

df_all_gpi.iloc[3]["data"]

df_all_gpi.iloc[3]["data"]

Out[5]:

	sig	slop	curv	TG1	TG2	TG3	WG1	WG2	WG3	BIOMA1	BIOMA2
datetime_doy
2007-01-02	-8.774847	-0.118061	-0.001871	282.495667	277.571790	280.432019	0.353169	0.297954	0.316928	0.055779	0.064610
2007-01-03	-8.737255	-0.116761	-0.001753	283.059404	278.609833	279.851678	0.224477	0.336283	0.303121	0.057188	0.007182
2007-01-03	-8.791911	-0.118357	-0.002037	284.386143	278.075722	285.383157	0.378645	0.250349	0.335715	0.062280	0.043909
2007-01-05	-7.962205	-0.118063	-0.002072	276.947048	277.841682	277.941320	0.305945	0.332280	0.315607	0.052877	0.017596
2007-01-06	-8.607216	-0.118727	-0.002048	276.458553	282.783491	277.956962	0.380480	0.364697	0.280530	0.051309	0.034444
...	...	...	...	...	...	...	...	...	...	...	...
2019-12-30	-8.824627	-0.119621	-0.000872	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2019-12-31	-8.578708	-0.121446	-0.001059	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2019-12-31	-8.731547	-0.119538	-0.000887	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2020-01-01	-7.358630	-0.122284	-0.000725	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2020-01-01	-9.165778	-0.123732	-0.000753	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

7995 rows × 11 columns

Convert data¶

As seen above, the "datetime_doy" values are not unique. While it's possible to have non-unique index values, it's generally not recommended. Having a non-unique index can make certain operations and data manipulation more complex, or even incorrect. These values shows two observation at one day. To avoid duplication, we add a hal-hour shift.

In [6]:

# Function to make timestamps unique by adding half an hour
def make_timestamps_unique(df):
    seen_timestamps = set()
    new_index = []

    for timestamp in df.index:
        if timestamp not in seen_timestamps:
            new_index.append(timestamp)
            seen_timestamps.add(timestamp)
        else:
            # Timestamp is a duplicate, add half an hour
            while timestamp in seen_timestamps:
                timestamp += pd.Timedelta(minutes=30)
            new_index.append(timestamp)
            seen_timestamps.add(timestamp)
    
    df.index = new_index
    df.index.name = "time"
    return df

# Function to make timestamps unique by adding half an hour def make_timestamps_unique(df): seen_timestamps = set() new_index = [] for timestamp in df.index: if timestamp not in seen_timestamps: new_index.append(timestamp) seen_timestamps.add(timestamp) else: # Timestamp is a duplicate, add half an hour while timestamp in seen_timestamps: timestamp += pd.Timedelta(minutes=30) new_index.append(timestamp) seen_timestamps.add(timestamp) df.index = new_index df.index.name = "time" return df

In [7]:

ds_list = []
for index, row in df_all_gpi.iterrows():
    
    # Filter the nested DataFrame based on location
    df = df_all_gpi.iloc[index-1]["data"]

    # Make timestamps unique
    df = make_timestamps_unique(df)

    # convert dataframe to dataset
    ds = xr.Dataset(df, coords={'latitude': row["lat"], 'longitude': row["lon"]})
    ds_list.append(ds)

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

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

ds_list = [] for index, row in df_all_gpi.iterrows(): # Filter the nested DataFrame based on location df = df_all_gpi.iloc[index-1]["data"] # Make timestamps unique df = make_timestamps_unique(df) # convert dataframe to dataset ds = xr.Dataset(df, coords={'latitude': row["lat"], 'longitude': row["lon"]}) ds_list.append(ds) # Create one dataset dataset = xr.concat(ds_list, dim="space") # Add attribute (metadata) dataset.attrs['source'] = 'data source' dataset.attrs['license'] = 'data license'

Inspect output and store it¶

In [8]:

dataset

dataset

Out[8]:

<xarray.Dataset>
Dimensions:    (time: 8506, space: 5)
Coordinates:
  * time       (time) datetime64[ns] 2007-01-02 ... 2020-01-01T01:00:00
    latitude   (space) float64 56.12 46.12 53.38 49.38 44.38
    longitude  (space) float64 11.38 6.625 6.125 12.38 0.625
Dimensions without coordinates: space
Data variables:
    sig        (space, time) float64 -9.49 -8.494 -9.069 ... -8.071 -8.237
    slop       (space, time) float64 -0.1208 -0.1178 -0.121 ... -0.1144 -0.1191
    curv       (space, time) float64 -0.001396 -0.001464 ... -0.0006173
    TG1        (space, time) float64 280.0 270.4 285.5 277.4 ... nan nan nan nan
    TG2        (space, time) float64 274.8 278.4 280.6 283.7 ... nan nan nan nan
    TG3        (space, time) float64 280.9 279.7 278.0 278.0 ... nan nan nan nan
    WG1        (space, time) float64 0.3249 0.2798 0.2773 0.2867 ... nan nan nan
    WG2        (space, time) float64 0.3408 0.2902 0.3373 0.2709 ... nan nan nan
    WG3        (space, time) float64 0.3123 0.2916 0.2891 0.3538 ... nan nan nan
    BIOMA1     (space, time) float64 0.07079 0.05532 0.04846 ... nan nan nan
    BIOMA2     (space, time) float64 0.04366 0.0462 0.03821 ... nan nan nan
Attributes:
    source:   data source
    license:  data license

xarray.Dataset

• Dimensions:
  • time: 8506
  • space: 5
• Coordinates: (3)
  • time
    (time)
    datetime64[ns]
    2007-01-02 ... 2020-01-01T01:00:00

    array(['2007-01-02T00:00:00.000000000', '2007-01-03T00:00:00.000000000',
           '2007-01-03T00:30:00.000000000', ..., '2020-01-01T00:00:00.000000000',
           '2020-01-01T00:30:00.000000000', '2020-01-01T01:00:00.000000000'],
          dtype='datetime64[ns]')

  • latitude
    (space)
    float64
    56.12 46.12 53.38 49.38 44.38

    array([56.125, 46.125, 53.375, 49.375, 44.375])

  • longitude
    (space)
    float64
    11.38 6.625 6.125 12.38 0.625

    array([11.375,  6.625,  6.125, 12.375,  0.625])

• Data variables: (11)
  • sig
    (space, time)
    float64
    -9.49 -8.494 ... -8.071 -8.237

    array([[-9.48985745, -8.49424829, -9.06917897, ..., -8.87350746,
            -8.55944322,         nan],
           [-9.02591086, -8.79194073, -8.24172421, ..., -8.65639331,
            -7.39492538, -8.74562845],
           [-9.29394782, -8.87621932, -8.96752995, ..., -8.2149011 ,
            -9.08360639,         nan],
           [-8.77484736, -8.73725534, -8.79191062, ..., -7.35863039,
            -9.16577772,         nan],
           [-8.24819331, -8.8397918 , -9.10849921, ..., -8.06904894,
            -8.07118479, -8.23718386]])

  • slop
    (space, time)
    float64
    -0.1208 -0.1178 ... -0.1144 -0.1191

    array([[-0.12082425, -0.11783219, -0.12098574, ..., -0.11359581,
            -0.11493786,         nan],
           [-0.11527791, -0.11782626, -0.11479579, ..., -0.12395586,
            -0.12741118, -0.12636578],
           [-0.11198183, -0.11500686, -0.11297782, ..., -0.11395541,
            -0.1149019 ,         nan],
           [-0.11806147, -0.11676077, -0.11835654, ..., -0.1222841 ,
            -0.1237319 ,         nan],
           [-0.11225743, -0.1164341 , -0.11506161, ..., -0.11670426,
            -0.114442  , -0.11907956]])

  • curv
    (space, time)
    float64
    -0.001396 -0.001464 ... -0.0006173

    array([[-0.00139633, -0.00146407, -0.00155133, ..., -0.00147185,
            -0.00133919,         nan],
           [-0.00192987, -0.00172127, -0.00155783, ..., -0.00127703,
            -0.0010599 , -0.00104103],
           [-0.00175796, -0.00185267, -0.0018512 , ..., -0.00093651,
            -0.00089887,         nan],
           [-0.0018711 , -0.00175281, -0.00203733, ..., -0.00072492,
            -0.00075293,         nan],
           [-0.00105648, -0.00112672, -0.0011477 , ..., -0.00039547,
            -0.00081939, -0.00061725]])

  • TG1
    (space, time)
    float64
    280.0 270.4 285.5 ... nan nan nan

    array([[280.02112326, 270.43043758, 285.49255157, ...,          nan,
                     nan,          nan],
           [273.95243199, 279.10472356, 275.67491766, ...,          nan,
                     nan,          nan],
           [282.79039682, 285.25875506, 286.80482212, ...,          nan,
                     nan,          nan],
           [282.49566693, 283.05940391, 284.38614309, ...,          nan,
                     nan,          nan],
           [272.8261471 , 280.37911393, 283.709762  , ...,          nan,
                     nan,          nan]])

  • TG2
    (space, time)
    float64
    274.8 278.4 280.6 ... nan nan nan

    array([[274.84709093, 278.41673812, 280.57289091, ...,          nan,
                     nan,          nan],
           [278.42437536, 283.31915753, 278.51042955, ...,          nan,
                     nan,          nan],
           [279.21544423, 279.57413371, 284.76804529, ...,          nan,
                     nan,          nan],
           [277.57179007, 278.6098334 , 278.07572192, ...,          nan,
                     nan,          nan],
           [278.54961327, 283.62250125, 286.60718795, ...,          nan,
                     nan,          nan]])

  • TG3
    (space, time)
    float64
    280.9 279.7 278.0 ... nan nan nan

    array([[280.94967399, 279.73701754, 277.95140294, ...,          nan,
                     nan,          nan],
           [275.7377006 , 278.96455422, 283.03484774, ...,          nan,
                     nan,          nan],
           [277.36359601, 279.51634776, 281.45332132, ...,          nan,
                     nan,          nan],
           [280.43201923, 279.85167842, 285.38315711, ...,          nan,
                     nan,          nan],
           [280.93250698, 277.34480673, 276.23607919, ...,          nan,
                     nan,          nan]])

  • WG1
    (space, time)
    float64
    0.3249 0.2798 0.2773 ... nan nan

    array([[0.32492937, 0.27983456, 0.27729615, ...,        nan,        nan,
                   nan],
           [0.28419109, 0.29665461, 0.26357131, ...,        nan,        nan,
                   nan],
           [0.28657149, 0.27418057, 0.32130955, ...,        nan,        nan,
                   nan],
           [0.35316888, 0.22447731, 0.37864479, ...,        nan,        nan,
                   nan],
           [0.32013111, 0.32989414, 0.37377789, ...,        nan,        nan,
                   nan]])

  • WG2
    (space, time)
    float64
    0.3408 0.2902 0.3373 ... nan nan

    array([[0.34075338, 0.29023887, 0.33732519, ...,        nan,        nan,
                   nan],
           [0.29739347, 0.31481947, 0.32647732, ...,        nan,        nan,
                   nan],
           [0.31802497, 0.35188802, 0.27310549, ...,        nan,        nan,
                   nan],
           [0.29795442, 0.33628332, 0.25034937, ...,        nan,        nan,
                   nan],
           [0.34499846, 0.3227702 , 0.34632033, ...,        nan,        nan,
                   nan]])

  • WG3
    (space, time)
    float64
    0.3123 0.2916 0.2891 ... nan nan

    array([[0.31225323, 0.29158948, 0.28909846, ...,        nan,        nan,
                   nan],
           [0.31240911, 0.28942008, 0.28934139, ...,        nan,        nan,
                   nan],
           [0.31223442, 0.32122713, 0.25863411, ...,        nan,        nan,
                   nan],
           [0.31692828, 0.30312131, 0.33571518, ...,        nan,        nan,
                   nan],
           [0.34937695, 0.33768108, 0.3156577 , ...,        nan,        nan,
                   nan]])

  • BIOMA1
    (space, time)
    float64
    0.07079 0.05532 0.04846 ... nan nan

    array([[0.07078687, 0.05531632, 0.04845626, ...,        nan,        nan,
                   nan],
           [0.06313461, 0.06849046, 0.06948795, ...,        nan,        nan,
                   nan],
           [0.10973646, 0.09282028, 0.10490551, ...,        nan,        nan,
                   nan],
           [0.05577876, 0.05718846, 0.06228029, ...,        nan,        nan,
                   nan],
           [0.06641911, 0.07987195, 0.07466871, ...,        nan,        nan,
                   nan]])

  • BIOMA2
    (space, time)
    float64
    0.04366 0.0462 0.03821 ... nan nan

    array([[0.04365577, 0.04620425, 0.03821127, ...,        nan,        nan,
                   nan],
           [0.05440495, 0.03978223, 0.00253587, ...,        nan,        nan,
                   nan],
           [0.11810849, 0.13832198, 0.1520621 , ...,        nan,        nan,
                   nan],
           [0.06460992, 0.00718201, 0.04390921, ...,        nan,        nan,
                   nan],
           [0.04191187, 0.04335878, 0.08054077, ...,        nan,        nan,
                   nan]])

• Indexes: (1)
  • time
    PandasIndex

    PandasIndex(DatetimeIndex(['2007-01-02 00:00:00', '2007-01-03 00:00:00',
                   '2007-01-03 00:30:00', '2007-01-05 00:00:00',
                   '2007-01-06 00:00:00', '2007-01-08 00:00:00',
                   '2007-01-08 00:30:00', '2007-01-09 00:00:00',
                   '2007-01-10 00:00:00', '2007-01-11 00:00:00',
                   ...
                   '2019-12-29 01:00:00', '2019-12-30 00:00:00',
                   '2019-12-30 00:30:00', '2019-12-30 01:00:00',
                   '2019-12-30 01:30:00', '2019-12-31 00:00:00',
                   '2019-12-31 00:30:00', '2020-01-01 00:00:00',
                   '2020-01-01 00:30:00', '2020-01-01 01:00:00'],
                  dtype='datetime64[ns]', name='time', length=8506, freq=None))

• Attributes: (2)

  source :

  data source

  license :

  data license

In [9]:

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

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

Out[9]:

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

In [10]:

# map of one variable at one time
time = "2007-01-02T00:00:00.000000000"
sig = dataset.sig.sel(time=time)
lons, lats = xr.broadcast(sig.longitude, sig.latitude)
plt.figure(figsize=(8, 6))
plt.scatter(lons, lats, c=sig, cmap="viridis", marker="o", s=10)
plt.colorbar(label="sig") 
plt.xlabel("longitude")
plt.ylabel("latitude")
plt.title(f"Plot of sig at time {time}")
plt.grid(True)
plt.show()

# map of one variable at one time time = "2007-01-02T00:00:00.000000000" sig = dataset.sig.sel(time=time) lons, lats = xr.broadcast(sig.longitude, sig.latitude) plt.figure(figsize=(8, 6)) plt.scatter(lons, lats, c=sig, cmap="viridis", marker="o", s=10) plt.colorbar(label="sig") plt.xlabel("longitude") plt.ylabel("latitude") plt.title(f"Plot of sig at time {time}") plt.grid(True) plt.show()

In [11]:

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

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

In [12]:

# 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[12]:

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