Coverting a table into a matrix

Hello everyone,

I am attempting to do some basic data manipulation, but have had no luck so far (outside of manually doing it in excel).

I have a data table in this format:

2	R	N	0.174	120.434	120.5	-0.401	0	2.515
2	R	HA	0.277	4.425	4.34	-0.32	0	0.291
2	R	C	-1.734	174.083	176.3	-0.806	0	1.046
2	R	CA	-0.628	55.18	56.1	-0.486	0	0.914
2	R	CB	1.499	31.565	30.3	-0.39	0	1.191
2	R	HN	-0.205	7.779	8.27	-0.476	0	0.435
3	W	N	5.932	126.847	121.3	-0.642	0	2.605
3	W	HA	-0.209	4.058	4.66	-0.654	0	0.327
3	W	C	-1.17	174.989	176.519	-0.6	0	1.332
3	W	CA	0.596	57.577	57.345	-0.606	0	1.403
3	W	CB	-0.13	29.083	29.46	-0.412	0	1.452
3	W	HN	0.171	8.257	8.18	-0.156	0	0.503

I'm trying to convert this into a matrix like this:

	        N	C	CA	CB	HN
2R	        120.434	174.083	55.18	31.565	7.779
3W	        126.847	174.989	57.577	29.083	8.257
2R (err)	2.515	1.046	0.914	1.191	0.435
3W (err)	2.605	1.332	1.403	1.452	0.503

The data for the first column in my matrix comes from column 5 and column 9 from my data table. The letters on the top and side are simply the axis I'm trying to label. I know how to transpose my data one by one in excel (very paintstaking). What I'm trying to do is write a script that does all this for me.

One final thing to note, not all my sets have the same format (N,HA,C,CA,CB,HN,), there are variations throughout. So I'm trying to get it so that it searches for a specific phrase or letter within the 3rd column and uses the information in that row (from column 5 and 9) to make the matrix. I.E. Search for every row that has the letter 'N' in the 3rd column, and use the data from that rows 5th column (from my data table) and put that into the 1st column of the matrix.

In regards to context, I am attempting to create 2 matrices, and then take the difference between the two.

Diff= √(1/? ∑(?=1)▒((??−??)^2 )/(???^2))

With N being the number of variables being used (i.e. N,C,CA, so in our case 5), P being the data from the above matrix table I'm using (info coming from the first row), and E being the the data from the 2nd matrix I plan to make. dP is the error (row 3 in the above matrix that is labeled 2R (err)) and 'i' would be which iteration (i.e. which row) is being looked at. The above matrix is planned to be 330x5 (this is why I'm looking a script that will create the matrix, doing it by manually transposing in excel will take forever).

It also doesn't matter too much what the format of the matrix is. It could also be like this (if this is more intuitive):

	        N	C	CA	CB	HN
2R	        120.434	174.083	55.18	31.565	7.779
2R (err)	2.515	1.046	0.914	1.191	0.435
3W	        126.847	174.989	57.577	29.083	8.257
3W (err)	2.605	1.332	1.403	1.452	0.503

Please let me know if anything is unclear. My data is currently in excel in the format at the very top. So I'm thinking the easiest way would be to have a line that imports the data from excel, creates a matrix by using a word search through the row and then using that to take info from the appropriate column and create the matrix. I haven't been able to find anything on this though so any help would be greatly appreciated. Thank you!

Answer 1

[Karun Mathiazhagan]

Hello Sam,

As you have rightly noticed, it would be necessary to write a custom MATLAB script for this use case but it is very much possible to achieve this. The following are some useful functionalities that may help you get started with this:

1. To read the data table as such into MATLAB as a table (datatype), the function "readmatrix" could be used. The documentation page for the same is linked here and demonstrates different means to achieve this: https://www.mathworks.com/help/matlab/ref/readmatrix.html
2. To remove the additional columns, you could index into the imported matrix using subscripts. In case you have not already, you could look into the below documentation for different ways in which this could be achieved: https://www.mathworks.com/help/matlab/learn_matlab/array-indexing.html
3. To concatenate the first two columns into one, you could use the concatenation operators "[ ]".
4. It may also be useful to get acquainted with "table" operations. A good place to start would be the examples section of the following documentation page: https://www.mathworks.com/help/matlab/ref/table.html#d117e1340937

I hope this helps.

Best,

Karun

[Sam Mahdi]

Thanks for the tip! I've already come across a problem though. I've attempted to import my excel file and create a matrix out of it.

opts=detectImportOptions('Sparta_Predictions1.xlsx')
% preview('Sparta_Predictions1.xlsx',opts)
opts.Sheet='Sparta_Predictions'
opts.SelectedVariableNames=[1,2,3,5,9];
opts.DataRange='4:972';
M=readmatrix('Sparta_Predictions1.xlsx',opts)

This is being used on the same table in the original answer. The issue is I get this error

Undefined function or variable 'readmatrix'.

The only thing I can think of is I have the 2018b version of matlab, the wiki is the 2019a version. I don't know if that makes a difference, but otherwise I don't know why this isn't working.

I also looked into concatenation operators, but couldn't find anything that discussed how to combine variables (for display purposes, not to multiply them).

Finally, I've looked into reshaping the array (since all this will do is import the table and make it into a matrix), but I haven't found anything that enables you to format at it the way I want (sequentially organizing the column going row by row).