how to recreate excel table in matlab

3 views (last 30 days)
I have a problem. I have attached an excel file to make you understand what I have to do:
I have to get in matlab a table/structure equal to the one present in this attached excel, in the sheet called "coord_lambda". I need this arrangement because then I have to do other calculations with these values.
this sheet is made up of coordinates and lambda values ​​(each lambda value is associated with two coordinates) which are found in two other sheets ("coord" and "lambda"). the coordinates are divided by colors where the orange ones must be entered from row 3c, while the yellow ones must be entered in the first and second column starting from row 4 (A4-B4). the lambda values ​​("lambda" sheet) instead must be entered in the table alternately (blue and green colors) as in the "coord_lambda" sheet: in column order A-I-B-J-C-K-D-L-E-M-F-N-G-O-H-P. column A of "lambda" must be inserted from cell C4 in "coord_lambda" and so on.
is it possible to do this? Can anyone help me?

Accepted Answer

dpb
dpb on 16 Nov 2022
Edited: dpb on 18 Nov 2022
C=readmatrix('esempio.xlsx','Sheet','coord'); % get inputs...
L=readmatrix('esempio.xlsx','Sheet','lambda');
N=size(L,2)/2; % figure output array size
M=size(C,1);
CL=nan(M-N+1,2*N+2); % preallocate output array
CL(1,3:end)=reshape(C(1:N,1:2),1,[]); % first row coordinates from beginning coordinates
CL(2:end,1:2)=C(N+1:end,1:2); % then fill in first two columns of coordinates
CL(2:end,3:2:end)=L(:,1:N); % Left, then
CL(2:end,4:2:end)=L(:,N+1:end); % right of field data alternating columns
vNames=["lam_L"+(1:N); "lam_R"+(1:N)]; % build variable names for table
vNames=["coord"+[1:2] vNames(:).']; % produce "L/R" sequence for lambda
tCL=array2table(CL,'VariableNames',vNames); % resulting table matching spreadsheet layout
produces close facsimile...
>> [head(tCL);tail(tCL)]
ans =
16×18 table
coord1 coord2 lam_L1 lam_R1 lam_L2 lam_R2 lam_L3 lam_R3 lam_L4 lam_R4 lam_L5 lam_R5 lam_L6 lam_R6 lam_L7 lam_R7 lam_L8 lam_R8
______ ______ ________ _______ ________ _______ ________ _______ ________ _______ ________ _______ ________ _______ ________ _______ ________ _______
NaN NaN 176.01 176.05 176.1 176.15 176.17 176.15 176.1 176.05 38.862 38.872 38.866 38.846 38.814 38.783 38.776 38.786
175.94 38.587 0.20216 1.5551 0.20165 1.5511 0.1996 1.5354 0.19608 1.5083 0.19424 1.4942 0.19462 1.4971 0.19777 1.5213 0.20027 1.5405
176 38.541 0.22834 1.7565 0.22789 1.753 0.22608 1.7391 0.22298 1.7152 0.22137 1.7028 0.2217 1.7054 0.22447 1.7267 0.22667 1.7437
176.06 38.496 0.26292 2.0225 0.26253 2.0195 0.26096 2.0074 0.25828 1.9867 0.25689 1.9761 0.25717 1.9782 0.25956 1.9966 0.26147 2.0113
176.13 38.463 0.29631 2.2793 0.29597 2.2767 0.29458 2.266 0.2922 2.2477 0.29097 2.2382 0.29122 2.2402 0.29333 2.2564 0.29503 2.2694
176.23 38.417 0.35379 2.7215 0.3535 2.7193 0.35234 2.7103 0.35035 2.695 0.34933 2.6872 0.34954 2.6888 0.3513 2.7023 0.35272 2.7132
176.34 38.375 0.42231 3.2485 0.42207 3.2467 0.42109 3.2392 0.41943 3.2264 0.41858 3.2198 0.41875 3.2212 0.42023 3.2325 0.42141 3.2416
176.46 38.351 0.48789 3.753 0.48768 3.7514 0.48684 3.7449 0.4854 3.7338 0.48466 3.7282 0.48481 3.7293 0.48609 3.7391 0.48711 3.747
176.28 39.139 0.28385 2.1835 0.28349 2.1807 0.28204 2.1695 0.27956 2.1504 0.27827 2.1406 0.27854 2.1426 0.28074 2.1596 0.28251 2.1732
176.2 39.104 0.22561 1.7354 0.22515 1.7319 0.22332 1.7178 0.22017 1.6936 0.21854 1.6811 0.21888 1.6837 0.22168 1.7052 0.22392 1.7224
176.1 39.065 0.16536 1.272 0.16474 1.2672 0.16223 1.2479 0.15787 1.2144 0.15559 1.1968 0.15606 1.2004 0.15996 1.2305 0.16305 1.2542
176.04 39.002 0.10834 0.83341 0.10739 0.82609 0.1035 0.79615 0.096525 0.7425 0.092746 0.71343 0.093529 0.71945 0.099913 0.76856 0.10478 0.80601
175.95 38.918 0.063126 0.48558 0.061479 0.47291 0.054395 0.41842 0.039544 0.30419 0.029129 0.22407 0.031534 0.24257 0.047215 0.36319 0.056796 0.43689
175.89 38.829 0.089325 0.68712 0.088169 0.67822 0.083384 0.64141 0.074551 0.57347 0.069589 0.5353 0.070629 0.5433 0.078888 0.60683 0.08497 0.65361
175.87 38.75 0.12844 0.98801 0.12764 0.98185 0.12438 0.95679 0.11864 0.91264 0.11559 0.88915 0.11622 0.89399 0.12142 0.93396 0.12545 0.96501
175.9 38.657 0.1653 1.2715 0.16467 1.2667 0.16216 1.2474 0.1578 1.2139 0.15552 1.1963 0.15599 1.1999 0.1599 1.23 0.16298 1.2537
>>
  2 Comments
dpb
dpb on 17 Nov 2022
Of course it does... :)
Glad to help...

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!