resize and fill table

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!

Answer 1

[Voss]

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

[Sanley Guerrier]

Appreciate it.

So, the point is to convert the table into matrix before do any further work?

[Voss]

You can use a table or a matrix, whichever is more convenient. I showed both ways since I wasn't sure which is more convenient for you.

[Sanley Guerrier]

Excellent, thank you very much!

[Voss]

You're welcome!

Answer 2

[Image Analyst]

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

[Sanley Guerrier]

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.