Create tables based on partial match in variable names in other table (2024)

ans = 8×8 table

absd1234xs absd5678xs aood9101xs aood1123xs adad1234xs aiid5678xs adsd9101xs addd1123xs __________ __________ __________ __________ __________ __________ __________ __________ 1 2 3 4 5 6 8 1 3 2 2 312 1 2 1 5 4 2 1 2 1 1 2 6 5.6667 2 0 104 -1.6667 -2 -2.3333 9 7.1667 2 -1 103 -3.6667 -4.5 -5.3333 11.5 8.6667 2 -2 102 -5.6667 -7 -8.3333 14 10.167 2 -3 101 -7.6667 -9.5 -11.333 16.5 11.667 2 -4 100 -9.6667 -12 -14.333 19

T_new = 1×4 cell array

{10000×2 table} {10000×2 table} {10000×2 table} {10000×2 table}

ans = 8×2 table

absd1234xs adad1234xs __________ __________ 1 5 3 1 4 1 5.6667 -1.6667 7.1667 -3.6667 8.6667 -5.6667 10.167 -7.6667 11.667 -9.6667

ans = 8×2 table

absd5678xs aiid5678xs __________ __________ 2 6 2 2 2 1 2 -2 2 -4.5 2 -7 2 -9.5 2 -12

Thank you so much for the answer, exactly what i was looking for.

Kind regards

You're welcome!

I have a small thought. The output now is a 1x4 cell, is it possible to get 4 matrices as the output from the for loop above? I tried with cell2mat but I get an error each time...

More specifically, I tried with:

at the end of the for loop you presented above. In addition, is it possible to connect the matchVals with the "new" table names?

Thank you

is it possible to get 4 matrices as the output from the for loop above?

Can you do this, creating dynamically named variables? Yes.

I know it is not optimal. However, I have a similar table with a significant number of columns (>500) in which I want to compare two rows simultanously which gives me more than 250 combinations in total. Furthermore, by this comparison I can visualize the data set and thereafter perform necessary mathematical operations...

"is it possible to get 4 matrices as the output ... [and] connect the matchVals with the "new" table names?"

Maybe use a struct array:

Thank you for the reply. It is very close to what I want to get. Is it possible to get 4 separate structs/tables as direct output from the loop itself? The structs/tables should be based on matchVals = {'1234', '5678','9101','1123'} and each contain their data values according to your comment above?

The reason for my question is that I have a huge file with hundreds of columns that I will need to process several times. Hence, in terms of time-effectiveness, I believe it is best to do it within the loop.

Thank u again

"Is it possible to get 4 separate structs/tables as direct output from the loop itself?"

Yes, my original answer showed how to get 4 separate tables - all contained in a cell array - and my most recent comment showed how to get 4 separate structs - all contained in a struct array.

If you are under the impression that a few indexing operations, e.g., having to do S(1) instead of S1, is going to have an impact on the time it takes the code to run, don't worry about that, it won't.

In fact, it may be that the most efficient approach is what was stated at the end of my original answer, which is to avoid splitting apart the table at all, and operate on it directly (thus avoiding making a copy of the whole thing):

I see. I think I explained my dilemma insufficiently. Your first example creates 4 separate tables perfectly as you say (in a cell array). And your second example in a struct array.

I understand that computational effort won't be significantly affected by indexing operations.

I think I'm doing the process a bit more difficult than it should be. I'm not so common working with cells since I'm quite a beginner. But probably it is easier to do the mathematical operation just within the cell according to your latest suggestion. I guess it is not any more difficult than normal table operation in a loop (what i saw on mathworks at least), as well as plotting. However, I tried to add the values within the same "matchvals" and could not make it to work. Hence, my effort to divide the cell within the loop.

A last question, in order to add the values within two columns in the table "T" that corresond to the same matchvals- is it something according to the code below. This should correspond to four new columns from the original eigh(since I'm adding the values of the sum from two columns). I get the error "uncrecognized table vairable name "properties")

Thank you

T.Properties is a TableProperties object, essentially a scalar struct, so it doesn't make sense to try to index its columns with T.Properties(:,idx).

You can sum all the columns of T with variable names matching each element of matchVals, by doing sum(T{:,idx},2) (here I'm storing the sums in the struct array and also in new columns of T - two different options):

thank you. This is preferrable compared to working with tons of tables. I even managed to include the matching variables as headings in the corresponding plots. Not the easiest to do include legends for the columns in each plot haha, but I will manage

I changed the cellstr to strings but cannot manage to identify the patters. Do you maybe know how i could do this within my loop attached above?

My four table outputs are of type: 10 000×0 empty table (i.e. the rows are empty which they should not be)

I attached the file I'm working with. I want to create four new columns based on the coloring of the variables (and the matches are based on the strings in bold)