pandas to hdmf dynamic table

[jonahpearl]

Hi all,

I have an analysis that spits out some data in a pandas DataFrame, and I'd like to be able to store it in my NWB file. This particular data is interval-based, so my plan was to use a TimeIntervals object. I see in the tutorial that I could add all the columns I want, then add row by row. But this table will have tens of thousands of rows, so I'd rather add by column. Problem is, it doesn't seem like that's possible? Eg

x = nwb.epoch.TimeIntervals(name='test',description='test', id=np.arange(5))
x.add_column(name='test', description='test', data=[np.arange(5)])

gives
ValueError: column must have the same number of rows as 'id'.

I'm a bit at a loss for how to simply "drag and drop" columns from the dataframe into the TimeIntervals object. Any suggestions are welcome...I will resort to looping row by row if I need to, but it just feels wrong. Thank you!

[oruebel]

It sounds like you already have all the column data available when you create the table. In that case you can just supply the columns to the constructor via the columns and colnames argument (see https://pynwb.readthedocs.io/en/stable/pynwb.epoch.html?highlight=TimeIntervals#pynwb.epoch.TimeIntervals). An example for how to construct a DynamicTable from existing data is shown in the HDMF docs here https://hdmf.readthedocs.io/en/stable/tutorials/plot_dynamictable_howto.html#initializing-columns . TimeIntervals inherits from DynamicTable so the process is the same.

Alternatively, if the column structure of your Pandas dataframe includes the required columns for TimeIntervals then you may be able to simply use the from_dataframe method to create the table (see https://hdmf.readthedocs.io/en/stable/tutorials/plot_dynamictable_howto.html#converting-the-table-from-a-pandas-dataframe). Even if that doesn't work directly for TimeIntervals, you may still be able to use the from_dataframe approach to create a generic DynamicTable to create the columns you need to then copy them to the TimeIntervals.

[jonahpearl]

Hi @oruebel -- thanks for this info last year. Sorry I was slow to reply, I ended up just solving this another way. I was going back to do some re-factoring and came across this question again. It appears to me like the issue still persists. No rush on solving this for me but seems like something worth understanding (or let me know if there's something simple I'm doing wrong). Here is an MRE:

from hdmf.common import VectorData, DynamicTable
import pynwb as nwb

col1 = VectorData(
    name='col1',
    description='column #1',
    data=[1, 2],
)
col2 = VectorData(
    name='col2',
    description='column #2',
    data=['a', 'b'],
)

# works fine
table = DynamicTable(
    name='test',
    description='test',
    columns=[col1, col2],
)

# raises ValueError
nwb.epoch.TimeIntervals(
    name='test', 
    description='test',
    columns=[col1, col2],
    colnames=['col1', 'col2']
    )

Thanks!

[oruebel]

Thanks for preparing this simple example! The issue is that in TimeIntervals, the columns start_time and stop_time are required. In the example, custom columns (which include data for 2 rows) are specified, but the required start_time and stop_time columns are missing. As a result TimeIntervals is adding the required start_time and stop_time columns in the constructor. However, since there is no data for those columns, we end up with a mismatch in length between the custom columns ['col1', 'col2'] and the start_time and stop_time columns. If you also specify the corresponding data for the start_time and stop_time columns, then the example works. Here the update example.

from hdmf.common import VectorData
import pynwb as nwb

col1 = VectorData(
    name='col1',
    description='column #1',
    data=[1, 2],
)
col2 = VectorData(
    name='col2',
    description='column #2',
    data=['a', 'b'],
)

start_col = VectorData(
    name='start_time',
    description='start times',
    data=[0.1, 0.2],
)

stop_col = VectorData(
    name='stop_time',
    description='stop times',
    data=[0.3, 0.4],
)

ti = nwb.epoch.TimeIntervals(
    name='test',
    description='test',
    columns=[start_col, stop_col, col1, col2],
    colnames=['start_time', 'stop_time', 'col1', 'col2']
    )

print(ti)