Coverting a table into a matrix

์กฐํšŒ ์ˆ˜: 13 (์ตœ๊ทผ 30์ผ)
Sam Mahdi
Sam Mahdi 2019๋…„ 5์›” 23์ผ
๋Œ“๊ธ€: Sam Mahdi 2019๋…„ 5์›” 30์ผ
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!

๋‹ต๋ณ€ (1๊ฐœ)

Karun Mathiazhagan
Karun Mathiazhagan 2019๋…„ 5์›” 30์ผ
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
  ๋Œ“๊ธ€ ์ˆ˜: 1
Sam Mahdi
Sam Mahdi 2019๋…„ 5์›” 30์ผ
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).

๋Œ“๊ธ€์„ ๋‹ฌ๋ ค๋ฉด ๋กœ๊ทธ์ธํ•˜์‹ญ์‹œ์˜ค.

์นดํ…Œ๊ณ ๋ฆฌ

Help Center ๋ฐ File Exchange์—์„œ Data Import from MATLAB์— ๋Œ€ํ•ด ์ž์„ธํžˆ ์•Œ์•„๋ณด๊ธฐ

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by