How to merge cells within a table

I have a data table in which I am expanding with calcualtions. I want to have a column that merges the sequential even with its subsequent odd.

I want column 6 to have the bordered cells merged with a result of a calculation inside. Is this possible? I've seen 2 column headers merged into one with 2 separate columns underneath (see Temperature in image below). I would assume something similar could be done. Thanks in advance for any help.

Answer 1

[Guillaume]

No it's not possible to merge cells by row or even by columns. What you're seeing in the temperature column is not the merging of two different columns but the display of a 2 column matrix stored in the single temperature variable.

Note that you can easily perform your calculation on pair of rows with rowfun. However if you want the result back into your original table you'll have it to duplicate it for each pair, e.g.:

%demo table
t = table(datetime(2017, 1, 5, 'Format', 'dd/MM/yyyy HH:mm:ss') + hours(0:19)', ...
          rand(20, 1)*7000, [randi([50 70], 20, 1), randi([25 40], 20, 1)], ...
          'VariableNames', {'Date', 'Load', 'Temperature'})

%calculate mean load per pair of rows
pairedload = rowfun(@mean, [t, table(repelem(1:height(t)/2, 2)', 'VariableNames', {'Group'})], ...
                    'InputVariables', 'Load', 'GroupingVariables', 'Group')

%to reinsert into original table
t.MeanPairedLoad = repelem(pairedload.Var3, 2)

[David Stolnis]

Thank you Guillaume. I do have it duplicated currently. My goal was to reduce formatting after exporting to excel. Wishful thinking...

Answer 2

[Peter Perkins]

David, this is certainly possible, but you have not said what you want done with the other variables in the table. Guillaume has provided a solution that merged each odd/even pair into a variable that's half the height of the original, and then broadcasts that out into the original as duplicate pairs of values. It sounds like that's not what you want. What do you want as the result?

[David Stolnis]

Peter, I tried to be as explicit as possible with using images to illustrate my desired outcome, but let me clarify. Rowfun does not help me. The colored boxes in my first image have a value that is the same for each row contained in that box. Currently I just duplicate the value across both cells but I would prefer just one cell that spans the 2 rows.

I do not want my other variables to change. This is simply a formatting issue for that single column. I want the colored squares in my first image to merge into one. The other columns should not change. Where the red box in column 60 has 2 cells, I want only one cell, and so on for each color. Therefore column 60 would have half the number of cells as the previous columns in the table. I am not sure this is in fact possible.

See how excel does it? That's what I want.

[Guillaume]

No, it's really not possible.

On the back end, tables require that all variables (columns) have the same number of rows. So you could not even store a variable that span multiple rows.

Since the back end does not allow it, the only way this could work would be to do this at the display level, merging rows of a variable that are identical. However, this is not something that is supported by the variable editor.

You would really need a new type of class or seriously complicate the current table class in order to support this, plus some improvement to the variable editor. The new class, you could implement at the m level (as table currently is) but the variable editor would needs modification of matlab executable.

[David Stolnis]

Right, I had a feeling it was going to be way past my ability.

It would really be great automate this formatting process and eliminate the need to export to excel to finish my tables. I suppose MATLAB cant beat Microsoft at everything haha.

[Guillaume]

Note: the following is just my opinion, I don't work for Mathworks, don't know what their plans are, and I certainly don't speak for them.

I don't think what you want fits the concept of tables, which to me match very much the database design where all rows and columns have the same size.

What you want is more like a grid with cells of varying sizes. With that design you can't really have a concept of rows or columns as a particular cell may span several rows or columns. While it may make for some fancy display it would also make manipulating the data a lot harder. As a result, I don't see this being implemented. Certainly not as part of the table design.

[Peter Perkins]

Perhaps I've misunderstood the question. It is possible as long as you create a variable with two columns in the output table, or a variable that is a cell array, each cell of which contains a two-element vector. Maybe that's not what you're looking for, but David, you still haven't shown a concrete example.

[Guillaume]

My understanding is that David wants some cells of the table to span more than one row or column. Similar to the rowspan or colspan of an html table or the merge cell feature of Excel.

That's the way I interpret the 1st illustration in the question. Column 60 would have half the number of elements as the other columns, with 1st element shared between row 1 and 2 of the other columns, 2nd element shared between row 3 and 4, etc.

[Peter Perkins]

OK, I get it. That's not possible.