index columns with missing values

[bjhardcastle]

Hi NWB community!

Is there a way to deal with columns in TimeIntervals tables which are intended as an index into an array - and should therefore be an integer datatype - but which also contain missing values?
Using np.nan for those missing values converts the whole column to floats, which then makes indexing awkward and doesn't clearly convey to a user that the values are meant to be used as indices.

Two things I've tested that haven't worked completely:

1. using the pandas datatype Int64

   • this works insofar as a DataFrame can hold integers and null values in a column - partial success!
   • writing to hdmf via pynwb fails due to the unsupported type of the null values
   • writing to hdf via pandas fails for the same reason

2. using the pyarrow backend for a DataFrame

   • as above, the DataFrame handles integers and null values nicely, but writing to hdf5 ultimately fails

I haven't tried adding row-by-row to the table, in order to mix types across the column, but it looks like a column must have a single datatype when written to hdf5.

Does anyone know a way to achieve this? Is it something that could be supported by hdmf when writing/reading?

[bendichter]

This is a constraint of HDF5 and Zarr, whose Datasets must have the same underlying data type. Columns of DynamicTables are Datasets in these backends, so they have the same constraint. If it's an integer column, all values must be an integer. You are right that float has the special values NaN, Inf and -Inf that can be used for this purpose, but ints do not. There are three solutions I'd seen for this type of thing, none of which are perfect:

1. Use the value -1. This will require you to use a signed int type, not an unsigned int type, but a value of -1 in a context that should clearly only support positive values can sometimes indicate a missing value
2. You could make the value indexed, which would put each value into a list. The missing values could be a list with a length of 0. So instead of [3, 4, 5, None, 1] you'd have [[3],[4],[5],[],[1]]
3. Use float, as you suggested and use NaN as the filler value. This can sometimes increase the size of the data, but it may not be that bad depending on the size of the Dataset.

[CodyCBakerPhD]

@bendichter summarizes quite well - here are some extra comments for each of their points

1. Keep in mind that negative indexing can, for some people, some libraries, and some programming languages mean 'positive index relative from the end rather than the start' - as such I would not recommend this approach

2. This is the most elegant, but also probably the hardest to pull off. I'd say give it a shot and if you can get it to work, great, if not go to option 3

3. Easiest option, though as you say perhaps awkward if someone only looked at the data type of the column - two things you can do to help communicate the meaning of the column, and even an explanation of the float casting weirdness - is to (a) name the column in a way that indicates they are indices, as well as (b) add a rich description to that column describing how the column values are to be interpreted and used

[oruebel]

Re 2., I agree this is the most elegant approach. These two section show examples of how this works in Python:

• https://hdmf.readthedocs.io/en/stable/tutorials/plot_dynamictable_tutorial.html#adding-ragged-array-columns
• https://hdmf.readthedocs.io/en/stable/tutorials/plot_dynamictable_howto.html#ragged-array-columns

If you need to reference ranges in a TimeSeries then the TimeSeriesReferenceVectorData type will be useful.