Load multitemporal tabulary dataset

[adrienmichez]

I would like to load multitemporal dataset stored in a simple csv table. So every line represents a day of observation for one cell of a squared grid covering our study area. I guess this is also something that a lot of newbie users like me want to do with mikeio.

I tried to inspire from the work I've done with netCDF files from satellite imagery but did not work :)

Here is the table as well as the dfs2 grid if needed (but center coordinates are available in table).

I tried this:

import xarray
import mikeio
import pandas

df = pandas.read_csv("C:/DATA_TMP/IRM/IRM_withETP_wallonia_2001.csv", sep = ';')
df.head()

df = df.sort_values(by=['Y_L72'])
df['DATE'] = pandas.to_datetime(df['DATE'])
df = df.sort_values(by=['DATE'])

geometry = mikeio.Grid2D(x=df['X_L72'], y = df['Y_L72'], projection="Belge_Lambert_1972")

This last command didn't work :

NotImplementedError: values must be equidistant

But they do (5 km square grid) ;-)

So I made the grid with mikeZERO toolbox and load to be able to go to the next step:

geometry = mikeio.read('C:/DATA_TMP/IRM/grilleg2_rescale.dfs2')
                               
# didn't work either
da = mikeio.DataArray( data = df['PRECIP_QUANTITY(mm)'], geometry = geometry, 
                      time=pandas.to_datetime(df['DATE']),
                      dims=("time", "Y_L72","X_L72"))

But it also failed:

ValueError: Number of named dimensions does not equal data ndim

To sum-up I think I have to issues:
1) How to define the grid based on the table. The observations do are regularly spaced (5 km). Could it be du to the fact that the empty cells are not in the table?

once the geometry is OK

2) How to properly define the space and time dimensions as well as the attributes?? I tried with one variable (precipaitation quantity) but the table also contains other variables of interest.

[ecomodeller]

There are empty cells in this raster as you can see from this Colab Notebook where I took a look at your data

The definition of the Grid2d needs unique x, y values or a x0,dx,nx. See example in the learning material to the MIKE course

[adrienmichez]

Thanks! Indeed, the grid definition you propose is much more easier and logical! Have you an inch for the second issue?

[ecomodeller]

I have updated the notebook. The only thing left is the time axis and to create additional items (you should of course make your own QA), but I think you should be able to find out how by looking at the documentation and some of the notebooks in this repo 🙂

[adrienmichez]

Thank you very much! I followed your recommendations and managed to add the time dimension...

I put the snippet below. But sadly it is quite slow when I run the loop over the entire dataset (60 years * 365 days * 5 parameters).

I guess I have to fine tune the loop because this looks like a common issue with big pandas dataframes [1] [2]

Any suggestion to speed up is welcome :)

import pandas as pd
import matplotlib.pyplot as plt
import mikeio

df = pd.read_csv("C:/DATA_TMP/IRM/IRM_withETP_wallonia.csv", sep = ';')
df.head()

# reorganize df
df = df.sort_values(by=['Y_L72'])
df['DATE'] = pd.to_datetime(df['DATE'])
df = df.sort_values(by=['DATE'])

df['year'] =  pd.DatetimeIndex(df['DATE']).year
df = df[df['year']==2000]


# create empty grid
x = df['X_L72'].unique()
x.sort()
y = df['Y_L72'].unique()
y.sort()
xs = np.arange(x[0],x[-1],step=5000)
ys = np.arange(y[0],y[-1],step=5000)

# Start out with an empty array (filled with missing/delete values).
A = np.empty(shape=(len(df['DATE'].unique()),len(ys),len(xs))) # TO DO add a temporal axis (nt, ny, nx)
A[:] = np.nan

# And then fill in values in non-empty cells
for d,date in enumerate(df['DATE'].unique()):
    print(date)
    for i,y in enumerate(ys):
      for j,x in enumerate(xs):
        vals = df[(df.DATE==date) & (df.X_L72 == x) & (df.Y_L72 == y)]["PRECIP_QUANTITY(mm)"].values
        if len(vals) == 1:
            A[d,i,j]= vals[0]

# Take a look a the data to check that it looks right.
plt.pcolormesh(xs,ys,A[1,:,:])
plt.ylabel("Y_L72")
plt.xlabel("X_L72")
cbar = plt.colorbar()
cbar.set_label("PRECIP_QUANTITY(mm)")

# set crs
wkt = """
PROJCS["Belge_Lambert_1972",
    GEOGCS["GCS_Belge_1972",
        DATUM["Reseau_National_Belge_1972",
            SPHEROID["International_1924",6378388,297]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.017453292519943295]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["False_Easting",150000.01256],
    PARAMETER["False_Northing",5400088.4378],
    PARAMETER["Central_Meridian",4.367486666666666],
    PARAMETER["Standard_Parallel_1",49.8333339],
    PARAMETER["Standard_Parallel_2",51.16666733333333],
    PARAMETER["Latitude_Of_Origin",90],
    PARAMETER["Azimuth",0.008138472222222222],
    UNIT["Meter",1],
    AUTHORITY["EPSG","103300"]]
"""

# define grid
geometry = mikeio.Grid2D(x=xs, y=ys, projection=wkt)
geometry

# Create a dataarray for temperature
da = mikeio.DataArray(data=A, time=df['DATE'].unique(), geometry=geometry, item="TEMP_MAX")
da
da.plot()

# save
da.to_dfs("C:/DATA_TMP/IRM/PRECIP_QUANTITY_IRM.dfs2")```

[adrienmichez]

this way of looping already improved a lot

for d,date in enumerate(df['DATE'].unique()):
    print(date)
    df_light =  df[df['DATE']==date]
    for i,y in enumerate(ys):
      for j,x in enumerate(xs):
        vals = df_light[(df_light.DATE==date) & (df_light.X_L72 == x) & (df_light.Y_L72 == y)]["PRECIP_QUANTITY(mm)"].values
        if len(vals) == 1:
            A[d,i,j]= vals[0]