resize and fill table

12 views (last 30 days)
Sanley Guerrier
Sanley Guerrier on 17 Jul 2023
Commented: Voss on 17 Jul 2023
Hello, How can I resize the table, I mean fill out missing values in column A so that the pattern repeat itself. For example, 0 1 2 3 0 1 2 3.........
And then replace the corresponding rows to these values by NaN in column B and C. After resizind it, the table will have 97 rows it currently has 86 rows.
Thank you!

Sign in to answer this question.

Accepted Answer

Voss
Voss on 17 Jul 2023
Edited: Voss on 17 Jul 2023
Ran in:
Here's one way, using a table:
t = readtable('file.xlsx');
disp(t);
QHR A B ___ _____ _____ 0 -6.67 -6.01 1 -6.73 -6.03 2 -6.71 -6.05 3 -6.73 -6.03 0 -6.8 -6.01 1 -6.86 -6.14 2 -6.97 -6.27 3 -7.13 -6.36 1 -7.13 -6.46 2 -7.09 -6.44 3 -7.12 -6.52 0 -7.12 -6.49 1 -7.09 -6.57 2 -7.09 -6.55 3 -7.07 -6.51 1 -7.02 -6.52 2 -6.93 -6.48 3 -6.86 -6.41 0 -6.82 -6.43 1 -6.82 -6.43 2 -6.79 -6.33 3 -6.78 -6.36 1 -6.78 -6.3 2 -6.74 -6.35 3 -6.75 -6.33 0 -6.7 -6.25 1 -6.67 -6.28 2 -6.58 -6.22 3 -6.54 -6.18 1 -6.48 -6.15 2 -6.33 -6.04 0 -6.25 -5.94 1 -6.15 -5.92 2 -6.02 -5.76 3 -5.93 -5.73 0 -5.57 -5.47 1 -5.43 -5.28 2 -5.08 -5.16 3 -4.81 -4.92 0 -4.68 -4.73 1 -4.52 -4.55 2 -4.47 -4.5 3 -4.42 -4.42 1 -4.21 -4.27 2 -4.01 -4.11 3 -4.03 -4.08 0 -4.06 -4.02 1 -4.05 -4.03 2 -4.01 -4.04 3 -3.98 -3.93 1 -4.05 -3.99 2 -3.87 -3.91 3 -3.88 -3.85 0 -3.97 -3.82 1 -3.91 -3.86 2 -4.03 -3.91 3 -4.05 -3.93 1 -4.31 -4.07 2 -4.23 -4.05 3 -4.25 -4.12 0 -4.29 -4.1 1 -4.29 -4.15 2 -4.23 -4.15 3 -4.25 -4.1 1 -4.25 -4.09 2 -4.13 -4.03 3 -4.1 -4.02 0 -4.06 -3.94 1 -4.12 -3.97 2 -4.03 -3.96 0 -3.96 -3.97 1 -3.92 -3.84 2 -3.95 -3.91 3 -3.99 -3.84 0 -3.95 -3.82 1 -3.93 -3.84 2 -3.87 -3.89 3 -3.84 -3.85 1 -3.85 -3.77 2 -3.78 -3.72 3 -3.81 -3.72 0 -3.76 -3.71 1 -3.77 -3.74 2 -3.74 -3.7 3 -3.69 -3.72
[N,M] = size(t);
ii = 2;
single_row_table = array2table(NaN(1,M),'VariableNames',t.Properties.VariableNames);
while ii <= N
if t{ii,1}-t{ii-1,1} ~= 1 && (t{ii,1} ~= 0 || t{ii-1,1} ~= 3)
single_row_table{1,1} = mod(t{ii-1,1}+1,4);
t = [t(1:ii-1,:); ...
single_row_table; ...
t(ii:end,:)];
N = N+1;
end
ii = ii+1;
end
disp(t);
QHR A B ___ _____ _____ 0 -6.67 -6.01 1 -6.73 -6.03 2 -6.71 -6.05 3 -6.73 -6.03 0 -6.8 -6.01 1 -6.86 -6.14 2 -6.97 -6.27 3 -7.13 -6.36 0 NaN NaN 1 -7.13 -6.46 2 -7.09 -6.44 3 -7.12 -6.52 0 -7.12 -6.49 1 -7.09 -6.57 2 -7.09 -6.55 3 -7.07 -6.51 0 NaN NaN 1 -7.02 -6.52 2 -6.93 -6.48 3 -6.86 -6.41 0 -6.82 -6.43 1 -6.82 -6.43 2 -6.79 -6.33 3 -6.78 -6.36 0 NaN NaN 1 -6.78 -6.3 2 -6.74 -6.35 3 -6.75 -6.33 0 -6.7 -6.25 1 -6.67 -6.28 2 -6.58 -6.22 3 -6.54 -6.18 0 NaN NaN 1 -6.48 -6.15 2 -6.33 -6.04 3 NaN NaN 0 -6.25 -5.94 1 -6.15 -5.92 2 -6.02 -5.76 3 -5.93 -5.73 0 -5.57 -5.47 1 -5.43 -5.28 2 -5.08 -5.16 3 -4.81 -4.92 0 -4.68 -4.73 1 -4.52 -4.55 2 -4.47 -4.5 3 -4.42 -4.42 0 NaN NaN 1 -4.21 -4.27 2 -4.01 -4.11 3 -4.03 -4.08 0 -4.06 -4.02 1 -4.05 -4.03 2 -4.01 -4.04 3 -3.98 -3.93 0 NaN NaN 1 -4.05 -3.99 2 -3.87 -3.91 3 -3.88 -3.85 0 -3.97 -3.82 1 -3.91 -3.86 2 -4.03 -3.91 3 -4.05 -3.93 0 NaN NaN 1 -4.31 -4.07 2 -4.23 -4.05 3 -4.25 -4.12 0 -4.29 -4.1 1 -4.29 -4.15 2 -4.23 -4.15 3 -4.25 -4.1 0 NaN NaN 1 -4.25 -4.09 2 -4.13 -4.03 3 -4.1 -4.02 0 -4.06 -3.94 1 -4.12 -3.97 2 -4.03 -3.96 3 NaN NaN 0 -3.96 -3.97 1 -3.92 -3.84 2 -3.95 -3.91 3 -3.99 -3.84 0 -3.95 -3.82 1 -3.93 -3.84 2 -3.87 -3.89 3 -3.84 -3.85 0 NaN NaN 1 -3.85 -3.77 2 -3.78 -3.72 3 -3.81 -3.72 0 -3.76 -3.71 1 -3.77 -3.74 2 -3.74 -3.7 3 -3.69 -3.72
Here's the same method, but using a matrix:
m = readmatrix('file.xlsx');
disp(m);
0 -6.6700 -6.0100 1.0000 -6.7300 -6.0300 2.0000 -6.7100 -6.0500 3.0000 -6.7300 -6.0300 0 -6.8000 -6.0100 1.0000 -6.8600 -6.1400 2.0000 -6.9700 -6.2700 3.0000 -7.1300 -6.3600 1.0000 -7.1300 -6.4600 2.0000 -7.0900 -6.4400 3.0000 -7.1200 -6.5200 0 -7.1200 -6.4900 1.0000 -7.0900 -6.5700 2.0000 -7.0900 -6.5500 3.0000 -7.0700 -6.5100 1.0000 -7.0200 -6.5200 2.0000 -6.9300 -6.4800 3.0000 -6.8600 -6.4100 0 -6.8200 -6.4300 1.0000 -6.8200 -6.4300 2.0000 -6.7900 -6.3300 3.0000 -6.7800 -6.3600 1.0000 -6.7800 -6.3000 2.0000 -6.7400 -6.3500 3.0000 -6.7500 -6.3300 0 -6.7000 -6.2500 1.0000 -6.6700 -6.2800 2.0000 -6.5800 -6.2200 3.0000 -6.5400 -6.1800 1.0000 -6.4800 -6.1500 2.0000 -6.3300 -6.0400 0 -6.2500 -5.9400 1.0000 -6.1500 -5.9200 2.0000 -6.0200 -5.7600 3.0000 -5.9300 -5.7300 0 -5.5700 -5.4700 1.0000 -5.4300 -5.2800 2.0000 -5.0800 -5.1600 3.0000 -4.8100 -4.9200 0 -4.6800 -4.7300 1.0000 -4.5200 -4.5500 2.0000 -4.4700 -4.5000 3.0000 -4.4200 -4.4200 1.0000 -4.2100 -4.2700 2.0000 -4.0100 -4.1100 3.0000 -4.0300 -4.0800 0 -4.0600 -4.0200 1.0000 -4.0500 -4.0300 2.0000 -4.0100 -4.0400 3.0000 -3.9800 -3.9300 1.0000 -4.0500 -3.9900 2.0000 -3.8700 -3.9100 3.0000 -3.8800 -3.8500 0 -3.9700 -3.8200 1.0000 -3.9100 -3.8600 2.0000 -4.0300 -3.9100 3.0000 -4.0500 -3.9300 1.0000 -4.3100 -4.0700 2.0000 -4.2300 -4.0500 3.0000 -4.2500 -4.1200 0 -4.2900 -4.1000 1.0000 -4.2900 -4.1500 2.0000 -4.2300 -4.1500 3.0000 -4.2500 -4.1000 1.0000 -4.2500 -4.0900 2.0000 -4.1300 -4.0300 3.0000 -4.1000 -4.0200 0 -4.0600 -3.9400 1.0000 -4.1200 -3.9700 2.0000 -4.0300 -3.9600 0 -3.9600 -3.9700 1.0000 -3.9200 -3.8400 2.0000 -3.9500 -3.9100 3.0000 -3.9900 -3.8400 0 -3.9500 -3.8200 1.0000 -3.9300 -3.8400 2.0000 -3.8700 -3.8900 3.0000 -3.8400 -3.8500 1.0000 -3.8500 -3.7700 2.0000 -3.7800 -3.7200 3.0000 -3.8100 -3.7200 0 -3.7600 -3.7100 1.0000 -3.7700 -3.7400 2.0000 -3.7400 -3.7000 3.0000 -3.6900 -3.7200
[N,M] = size(m);
ii = 2;
while ii <= N
if m(ii,1)-m(ii-1,1) ~= 1 && (m(ii,1) ~= 0 || m(ii-1,1) ~= 3)
m = [m(1:ii-1,:); ...
mod(m(ii-1,1)+1,4) NaN(1,M-1); ...
m(ii:end,:)];
N = N+1;
end
ii = ii+1;
end
disp(m);
0 -6.6700 -6.0100 1.0000 -6.7300 -6.0300 2.0000 -6.7100 -6.0500 3.0000 -6.7300 -6.0300 0 -6.8000 -6.0100 1.0000 -6.8600 -6.1400 2.0000 -6.9700 -6.2700 3.0000 -7.1300 -6.3600 0 NaN NaN 1.0000 -7.1300 -6.4600 2.0000 -7.0900 -6.4400 3.0000 -7.1200 -6.5200 0 -7.1200 -6.4900 1.0000 -7.0900 -6.5700 2.0000 -7.0900 -6.5500 3.0000 -7.0700 -6.5100 0 NaN NaN 1.0000 -7.0200 -6.5200 2.0000 -6.9300 -6.4800 3.0000 -6.8600 -6.4100 0 -6.8200 -6.4300 1.0000 -6.8200 -6.4300 2.0000 -6.7900 -6.3300 3.0000 -6.7800 -6.3600 0 NaN NaN 1.0000 -6.7800 -6.3000 2.0000 -6.7400 -6.3500 3.0000 -6.7500 -6.3300 0 -6.7000 -6.2500 1.0000 -6.6700 -6.2800 2.0000 -6.5800 -6.2200 3.0000 -6.5400 -6.1800 0 NaN NaN 1.0000 -6.4800 -6.1500 2.0000 -6.3300 -6.0400 3.0000 NaN NaN 0 -6.2500 -5.9400 1.0000 -6.1500 -5.9200 2.0000 -6.0200 -5.7600 3.0000 -5.9300 -5.7300 0 -5.5700 -5.4700 1.0000 -5.4300 -5.2800 2.0000 -5.0800 -5.1600 3.0000 -4.8100 -4.9200 0 -4.6800 -4.7300 1.0000 -4.5200 -4.5500 2.0000 -4.4700 -4.5000 3.0000 -4.4200 -4.4200 0 NaN NaN 1.0000 -4.2100 -4.2700 2.0000 -4.0100 -4.1100 3.0000 -4.0300 -4.0800 0 -4.0600 -4.0200 1.0000 -4.0500 -4.0300 2.0000 -4.0100 -4.0400 3.0000 -3.9800 -3.9300 0 NaN NaN 1.0000 -4.0500 -3.9900 2.0000 -3.8700 -3.9100 3.0000 -3.8800 -3.8500 0 -3.9700 -3.8200 1.0000 -3.9100 -3.8600 2.0000 -4.0300 -3.9100 3.0000 -4.0500 -3.9300 0 NaN NaN 1.0000 -4.3100 -4.0700 2.0000 -4.2300 -4.0500 3.0000 -4.2500 -4.1200 0 -4.2900 -4.1000 1.0000 -4.2900 -4.1500 2.0000 -4.2300 -4.1500 3.0000 -4.2500 -4.1000 0 NaN NaN 1.0000 -4.2500 -4.0900 2.0000 -4.1300 -4.0300 3.0000 -4.1000 -4.0200 0 -4.0600 -3.9400 1.0000 -4.1200 -3.9700 2.0000 -4.0300 -3.9600 3.0000 NaN NaN 0 -3.9600 -3.9700 1.0000 -3.9200 -3.8400 2.0000 -3.9500 -3.9100 3.0000 -3.9900 -3.8400 0 -3.9500 -3.8200 1.0000 -3.9300 -3.8400 2.0000 -3.8700 -3.8900 3.0000 -3.8400 -3.8500 0 NaN NaN 1.0000 -3.8500 -3.7700 2.0000 -3.7800 -3.7200 3.0000 -3.8100 -3.7200 0 -3.7600 -3.7100 1.0000 -3.7700 -3.7400 2.0000 -3.7400 -3.7000 3.0000 -3.6900 -3.7200
  4 Comments
Show 2 older comments
Sanley Guerrier
Sanley Guerrier on 17 Jul 2023
Excellent, thank you very much!
Voss
Voss on 17 Jul 2023
You're welcome!

Sign in to comment.

More Answers (1)

Image Analyst
Image Analyst on 17 Jul 2023
Ran in:
Does this work for you?
data = readmatrix('file.xlsx');
% Note: sometimes 0's are missing from colum 1 for some reason.
% Is this the way it's supposed to be???
[rows, columns] = size(data)
rows = 85
columns = 3
finalRow = 97;
data(end : finalRow, 2:3) = nan;
% Fill up tail of column 1 with 0;1;2;3;0;1;2;3; etc.
for row = rows+1 : finalRow
data(row, 1) = mod(row+2, 4);
end
% Optional: Convert from array to table
t = table(data(:, 1), data(:, 2), data(:, 3), 'VariableNames', {'QHR', 'A', 'B'})
t = 97×3 table
QHR A B ___ _____ _____ 0 -6.67 -6.01 1 -6.73 -6.03 2 -6.71 -6.05 3 -6.73 -6.03 0 -6.8 -6.01 1 -6.86 -6.14 2 -6.97 -6.27 3 -7.13 -6.36 1 -7.13 -6.46 2 -7.09 -6.44 3 -7.12 -6.52 0 -7.12 -6.49 1 -7.09 -6.57 2 -7.09 -6.55 3 -7.07 -6.51 1 -7.02 -6.52
  1 Comment
Sanley Guerrier
Sanley Guerrier on 17 Jul 2023
Thank you,
The main point is to fill in the missing number from column 1 so the pattern continue as 0 1 2 3 0 1 2 3 0 1 2 3 until the end.

Sign in to comment.

Categories

Find more on Cell Arrays in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!