What is the fastest way to add a row at the bottom of a table?

Hi all,

In my program, I create an empty table with, lets say, 70 columns. Lets call it 'Collection_Table'.

During a tipical run the program has to add a one row table, 'Row_Table', to the bottom of 'Collection_Table'.

In a tipical run it happens few tens of thousend times (~20000 and more).

It turns that it takes A LOT OF TIME. I tryied the following methods:

1.

Collection_Table(end+1,:) = Row_Table; 

2.

Collection_Table = [Collection_Table; Row_Table];

3.

I created a very large 'Cellection_Table' with zero, or empty, entires to eliminate the problem of enlarging the table during run.

Then, I used a counter 'Counter' to track the right entry to the table:

Counter = Counter + 1;
Collection_Talbe(Counter,:) = Row_Table; 

In all three methods the time it takes to add 'Row_Table' data was long! Actually it is the major time consuming action in the program.

Is there a way to be more efficient? Am I missing something? Or I have to accept that working with the tables is time consuming?

Thanks,

Alon

[Akira Agata]

If you know a maximum number of rows, say N, of Collection_Talbe after running your program, how about pre-alocating a N-by-70 empty table ?

To pre-alocate a table, the following example will be some help.

https://www.mathworks.com/help/matlab/ref/table.html#mw_9cdbdca8-7493-4866-9e8d-c2ddb22e7a1f

Answer 1

[Guillaume]

"Or I have to accept that working with the tables is time consuming?"

You have to accept that growing anything (tables, matrices, etc.) one row at a time is time consuming. For efficiency data is stored continuously in memory. Since the memory after your current matrix/table may already be in use, when you add a row, matlab has to:

• allocate a new block of memory somewhere with enough room for the current data + the new row
• copy over the current data into that new block
• add the new row at the end

Do that a few 20,000 times and it adds up to a lot of memory allocations and data copies.

The way to avoid is not to grow things but tell matlab from the start what the final size is going to be by preallocating your matrix/table/whatever, and then filling it one row at a time. If for some reason, there's no way to know beforehand the final size, then preallocate a too large table/whatever, fill it up and trim the extra at the end.

[Alon Rozen]

Thanks Guillaume,

In example number 3 I did just that. As I explained, I created a large table before entering the data and then just entered the new data at the right location. Still, it takes a lot of time.

Alon

[Guillaume]

Ah, ok, I missed that you did preallocate the table in example 3.

Then, yes you're seeing the overhead of the table. One downside of a table compared to a matrix is that the variables (columns) are stored in separate arrays. So assigning to a row means assigning to different arrays which means a lot of indexing and lookup in the background.

In that case, I would recommend that you temporarily store your data in a matrix (if it's homegenous) or a cell array (if it's heterogenous) and convert to a table when you've finished the storage.

[Alon Rozen]

I did many tests now - with a pre-created table filled with zeros and empty cells and with a table that just grow whenever a new one row table is added to it. Creating a full size table didn't reduce the time much. It is the inserting of data that takes so much time.

So I have to live with that?

Alon

[Guillaume]

Yes, the insertion of data takes a long time. I've just done some tests and the performance for a 20,000x70 table is indeed abysmall. tables are not well suited for filling up row by row (column by column should be fast on the other hand). As I said, use a matrix or cell array for filling up and convert it into a table at the end. It wil be several orders of magnitude faster.

[Alon Rozen]

Thanks Guillaume,

I think that you are rigjt - have similar results on my tests.

As for working with cell arrays (matricies are not an option since not all data are numbers) - I tested that and the last stage - using 'cellfun' to convert each field to a column before adding it to a table is even more time consuming.

Alon

[Guillaume]

You can convert the cell array into a table using cell2table. It will be a lot more efficient than cellfun. It's near instantaneous for me with a 20,000 x 70 cell array.

[Alon Rozen]

Hi,

I tried but I can't get it write.

The problem is the complicated data I use. If I have to keep it in a cell array, each cell will host a struct who its fields are either number, matrix or logical. The table I get is not a table in which the fiieds turns to colulmns and each stores the data from all structs in all cell. I don't see a way around this.

Alon

[Guillaume]

I'm a bit confused. Whichever way you create the table, at some point you must convert these structures into something that can be put into a table. So how did you do it originally?

Perhaps this is that conversion that is actually the bottleneck.

I suggest that you give an example of the data you have. If everything is a structure, perhaps concatenating the lot into a single structure array instead of a cell array is the way to go. You can then convert that structure array into a table with struct2table.

Answer 2

[KSSV]

T = array2table(rand(3)) ;
T1 = array2table(rand(1,3)) ;
T = [T ; T1]

[Alon Rozen]

Thanks KSSV,

The problem is that many of the table's columns contain data which is not numbers. In fact, I have, numbers, matricies, and cells in different columns. So I can't use fast matrix operations to add a new row.

Alon

Answer 3

[Charles Lee]

My solution is to set all variables as 'char' before merging.

1. detectImportOptions() to find the variables with type 'double'
2. setvartype() to set the variables as 'char'
3. Just merge

%% one example→merge Sapfluxnet_info & St_md :
opts = detectImportOptions('Sapflux_St_md.csv','NumHeaderLines',0); 
% Just give a hint in case it can't get the variablenames
opts = setvartype(opts,{'st_age','st_basal_area','st_density','st_height'},'char');          
%'st_age','st_basal_area','st_density','st_height' → 'double' type variables
Sapfluxnet_info = readtable('Sapflux_St_md.csv',opts); 

opts = detectImportOptions('St_md_name.csv','NumHeaderLines',0); 
opts = setvartype(opts,{'st_age','st_basal_area','st_density','st_height'},'char');
St_md = readtable('St_md_name.csv',opts);
Sapfluxnet_info = [Sapfluxnet_info;St_md];