How do I add rows to a table in a for loop?

I want to figure out how to take data from a list of tab-delimited .txt files of indeterminate length with formats like this:

Test Number Result

First test 1

First test 8

First test 3

First test 4

Test Number Result

Second test 201

Second test 208

Second test 203

Second test 204

And produce a table like this:

First 1 8 3 4

Second 201 208 203 204

filePattern = fullfile(myFolder, '*.txt'); % Change to whatever pattern you need.
theFiles = dir(filePattern);
for k = 1 : length(theFiles)
        baseFileName = theFiles(k).name;
        fullFileName = fullfile(theFiles(k).folder, baseFileName);
        usableTable = readtable (fullFileName); 
        choppedTable = usableTable(:,["Test","Result"])
        punyTable = choppedTable (1, "Test")
        punyTable
        SecondChopTable = choppedTable(:, "Result")
        LongRow = rows2vars(SecondChopTable)
        NewRow = [punyTable, LongRow]
        NewRow.OriginalVariableNames = []
        NewRow = vertcat(NewRow{i,:});
end

My problem is that, try as I might, I can't get NewRow to add rows onto the end of a table.

[Stephen23]

Is that one data file or two? It would be much easier to answer this, if you simply uploaded a sample data file or two.

[Alister]

I have these files to work with (Sandbox1, Sandbox2, Sandbox3) and am trying to get the output seen in Result.txt.

This is my code, and it keeps producing the tables I want, but not in the order I want, and I don't know how to fix this.

addpath '\\myfiles.campus.edu\alister\Sandbox' %adds where the files are
myFolder = '\\myfiles.campus.edu\alister\Sandbox' % creates new folder

filePattern = fullfile(myFolder, '*.txt'); % Change to whatever pattern you need.
theFiles = dir(filePattern);
FileLength = length(theFiles)
for k = 1 : length(theFiles)
    baseFileName = theFiles(k).name;
    fullFileName = fullfile(theFiles(k).folder, baseFileName);
    usableTable = readtable (fullFileName);  %some kind of wonky BS that I don't understand. What I think is happening here is that this is 
    % somehow turning this into a real table function (whatever this means)
    % that Matlab can then act upon.
    choppedTable = usableTable(:,["Test","Result"])
    punyTable = choppedTable (1, "Test")
    punyTable
    SecondChopTable = choppedTable(:, "Result")
    LongRow = rows2vars(SecondChopTable)
    NewRow = [punyTable, LongRow]
    NewRow.OriginalVariableNames = [] %can use removevars in Matlab 2018+
    for k = 1:H
        T1 = table()
        T1 = vertcat(T1,NewRow);
    end
end

[Stephen23]

"addpath '\\myfiles.campus.edu\alister\Sandbox' %adds where the files are"

Do not add folders of data files to the MATLAB search path. The search path should be for code only.

"myFolder = '\\myfiles.campus.edu\alister\Sandbox' % creates new folder"

That line of code does not create any folder.

"some kind of wonky BS that I don't understand. What I think is happening here is that this is somehow turning this into a real table function (whatever this means)"

The READTABLE documentation explains what it does. The phrase "real table function" exists only on this thread.

Answer 1

[Star Strider]

The vertcat function needs to know what ‘NewRow’ is to be concatenated to.

Try something like this —

T1 = array2table(magic(5))

T1 = 5×5 table
    Var1    Var2    Var3    Var4    Var5
    ____    ____    ____    ____    ____

     17      24       1       8      15 
     23       5       7      14      16 
      4       6      13      20      22 
     10      12      19      21       3 
     11      18      25       2       9 

for k = 1:3
    NewRow = array2table(rand(1,5));
    T1 = vertcat(T1,NewRow);
end
T1

T1 = 8×5 table
     Var1       Var2       Var3        Var4       Var5  
    _______    _______    _______    ________    _______

         17         24          1           8         15
         23          5          7          14         16
          4          6         13          20         22
         10         12         19          21          3
         11         18         25           2          9
    0.93963      0.769    0.84968      0.7547    0.33558
    0.33767    0.33738    0.78089     0.54578    0.54718
    0.65906    0.82833    0.59296    0.076004    0.46649

.

[Alister]

I tried that; it didn't seem to work. In an above comment, I have attached the files that I am trying to work on.

[Star Strider]

I first thought that unstack would work here, so I tried that first, and if the ‘Number’ entries differed, it still might be worth considering.

However, the desired result is simply a gropued and transposed concatenation of the original tables, so that is what I did —

T{1,:} = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1272080/Sandbox1.txt');
T{2,:} = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1272085/Sandbox2.txt');
T{3,:} = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1272090/Sandbox3.txt');

Cat = T{1};
for k = 2:numel(T)
    Cat = vertcat(Cat,T{k});
end
Cat

Cat = 69×3 table
      Test        Number     Result
    _________    ________    ______

    {'First'}    {'test'}       1  
    {'First'}    {'test'}       8  
    {'First'}    {'test'}       3  
    {'First'}    {'test'}       4  
    {'First'}    {'test'}       9  
    {'First'}    {'test'}       2  
    {'First'}    {'test'}       7  
    {'First'}    {'test'}       6  
    {'First'}    {'test'}      10  
    {'First'}    {'test'}      18  
    {'First'}    {'test'}      13  
    {'First'}    {'test'}      14  
    {'First'}    {'test'}      19  
    {'First'}    {'test'}      12  
    {'First'}    {'test'}      17  
    {'First'}    {'test'}      16  

[G,ID] = findgroups(Cat.Test);                              % Identify Groups By 'Test'

Tally = accumarray(G,Cat.Result,[],@(x){x});                % Accumulate Results, Return As Cell Array
Out = cellfun(@(x)transpose(x),Tally, 'Unif',0);            % Transpose Individual Results
Out = array2table(cell2mat(Out),'RowNames',ID)              % Create Output Table

Out = 3×23 table
              Var1    Var2    Var3    Var4    Var5    Var6    Var7    Var8    Var9    Var10    Var11    Var12    Var13    Var14    Var15    Var16    Var17    Var18    Var19    Var20    Var21    Var22    Var23
              ____    ____    ____    ____    ____    ____    ____    ____    ____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____

    First       1       8       3       4       9       2       7       6       10      18       13       14       19       12       17       16       11       21       28       23       24       29       22 
    Second    201     208     203     204     209     202     207     206     2010    2018     2013     2014     2019     2012     2017     2016     2011     2021     2028     2023     2024     2029     2022 
    third     301     308     303     304     309     302     307     306     3010    3018     3013     3014     3019     3012     3017     3016     3011     3021     3028     3023     3024     3029     3022 

DR = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1272095/Result.txt')

DR = 3×24 table
       Var1       Var2    Var3    Var4    Var5    Var6    Var7    Var8    Var9    Var10    Var11    Var12    Var13    Var14    Var15    Var16    Var17    Var18    Var19    Var20    Var21    Var22    Var23    Var24
    __________    ____    ____    ____    ____    ____    ____    ____    ____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____    _____

    {'First' }      1       8       3       4       9       2       7       6       10       18       13       14       19       12       17       16       11       21       28       23       24       29       22 
    {'Second'}    201     208     203     204     209     202     207     206     2010     2018     2013     2014     2019     2012     2017     2016     2011     2021     2028     2023     2024     2029     2022 
    {'Third' }    301     308     303     304     309     302     307     306     3010     3018     3013     3014     3019     3012     3017     3016     3011     3021     3028     3023     3024     3029     3022 

The ‘ID’ cell array could be used either as the first variable (use addvars for that) or as row names. I chose row names here.

This could even be simplified further, however I sense that it’s a ‘proxy problem’ for a different set of tables (and possibly a similar but different problem), so I left it as it is.

.

[Alister]

How would you do this if you had to grab files from a directory?

[Star Strider]

I can’t demonstrate that easily here, however the general guidance is in: Import or Export a Sequence of Files

I would import all of them to a cell array in a loop, as I did here manually (since the directory structure here is a bit more complex than would usually be encountered) and then use the approach I used here to process the data to produce the desired final result.

.

Answer 2

[Stephen23]

"... it keeps producing the tables I want, but not in the order I want, and I don't know how to fix this."

My guess (in lieu of your explanation) is that your filenames are numbered and you expect the file to be returned in numeric order. In that case you will need to sort the filenames alphanumerically. One way is to download my FEX submission NATSORTFILES, which was written to sort filenames into alphanumeric order:

https://www.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort

In most cases it is very simple to include in your code:

P = '.'; % absolute/relative path to where the files are saved.
S = dir(fullfile(P,'*.txt'));
S = natsortfiles(S); % download here: https://www.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort
for k = 1:numel(S)
    F = fullfile(S(k).folder,S(k).name);
    T = readtable(F);
    S(k).data = T(:,["Test","Result"]);
end
T = vertcat(S.data);
[U,~,X] = unique(T.Test,'stable');
H = @(a) {num2cell(a.')};
C = splitapply(H,T.Result,X);
C = [U,vertcat(C{:})]

C = 3×24 cell array
    {'First' }    {[  1]}    {[  8]}    {[  3]}    {[  4]}    {[  9]}    {[  2]}    {[  7]}    {[  6]}    {[  10]}    {[  18]}    {[  13]}    {[  14]}    {[  19]}    {[  12]}    {[  17]}    {[  16]}    {[  11]}    {[  21]}    {[  28]}    {[  23]}    {[  24]}    {[  29]}    {[  22]}
    {'Second'}    {[201]}    {[208]}    {[203]}    {[204]}    {[209]}    {[202]}    {[207]}    {[206]}    {[2010]}    {[2018]}    {[2013]}    {[2014]}    {[2019]}    {[2012]}    {[2017]}    {[2016]}    {[2011]}    {[2021]}    {[2028]}    {[2023]}    {[2024]}    {[2029]}    {[2022]}
    {'third' }    {[301]}    {[308]}    {[303]}    {[304]}    {[309]}    {[302]}    {[307]}    {[306]}    {[3010]}    {[3018]}    {[3013]}    {[3014]}    {[3019]}    {[3012]}    {[3017]}    {[3016]}    {[3011]}    {[3021]}    {[3028]}    {[3023]}    {[3024]}    {[3029]}    {[3022]}

T = cell2table(C)

T = 3×24 table
        C1        C2     C3     C4     C5     C6     C7     C8     C9     C10     C11     C12     C13     C14     C15     C16     C17     C18     C19     C20     C21     C22     C23     C24 
    __________    ___    ___    ___    ___    ___    ___    ___    ___    ____    ____    ____    ____    ____    ____    ____    ____    ____    ____    ____    ____    ____    ____    ____

    {'First' }      1      8      3      4      9      2      7      6      10      18      13      14      19      12      17      16      11      21      28      23      24      29      22
    {'Second'}    201    208    203    204    209    202    207    206    2010    2018    2013    2014    2019    2012    2017    2016    2011    2021    2028    2023    2024    2029    2022
    {'third' }    301    308    303    304    309    302    307    306    3010    3018    3013    3014    3019    3012    3017    3016    3011    3021    3028    3023    3024    3029    3022

Your description is unclear about what data type you require the output to be, so I showed a few options for you.