Split a Table at every nth row

I have a table that is approximately 96,000 lines long, i need to split it into tables that are approximately 28,000 lines long, how would i go about doing this?

[Stephen23]

Note that in general splitting up data makes it harder to work with.

MATLAB has plenty of functions and tools that identify and process groups of data within tables:

https://www.mathworks.com/help/matlab/matlab_prog/perform-calculations-by-group-in-table.html

https://www.mathworks.com/help/matlab/matlab_prog/split-data-into-groups-and-calculate-statistics.html

Answer 1

[the cyclist]

Here is one way:

% Make up a table
var1 = rand(96000,1);
var2 = rand(96000,1);
tbl = table(var1,var2);

% Define the smaller table size
smallSize = 28000;

% Break it up, storing each smaller table in a cell array
numberTables = ceil(height(tbl)/smallSize);

smallTables = cell(numberTables,1);
for nt = 1:numberTables-1
    indexToThisChunk = ((nt-1)*smallSize+1):(nt*smallSize);
    smallTables{nt} = tbl(indexToThisChunk,:);
end
smallTables{numberTables} = tbl((numberTables-1)*smallSize+1:end,:);

Answer 2

[Voss]

T = array2table(rand(96014,10)) % approximately 96000 rows in the table

T = 96014×10 table
      Var1        Var2        Var3        Var4        Var5        Var6        Var7        Var8       Var9       Var10  
    ________    ________    ________    ________    ________    ________    ________    ________    _______    ________

     0.18892    0.089271     0.88321     0.80645     0.11043      0.2568     0.26967     0.25484    0.45477      0.1718
     0.96556    0.043578     0.65871    0.053808     0.79238     0.93371     0.47394     0.33111    0.53997     0.69855
     0.44455     0.35608     0.20507     0.86167     0.45956     0.55613     0.17145     0.82845    0.96604     0.47359
     0.78605     0.67024     0.62304     0.99474     0.60555     0.87158     0.89374     0.81122    0.59305    0.076403
     0.73205     0.75425    0.098652     0.71518      0.8582     0.85426    0.081266     0.26992     0.1171     0.48139
     0.38181     0.48638      0.7665     0.43862     0.74424      0.3341     0.53427     0.82315    0.10296     0.70811
     0.82262     0.19081     0.32617     0.89847     0.86008     0.71494     0.76669     0.34935    0.81139     0.84729
     0.99334     0.35128     0.82732     0.43145     0.38879    0.078514     0.38516    0.010543    0.46569     0.67649
      0.8923     0.32302     0.78348     0.51972    0.058376     0.94041    0.026721     0.81799    0.90268    0.059318
     0.88259     0.24735     0.54698    0.026204    0.069982     0.95805     0.06013     0.67848     0.5047     0.78035
     0.29519     0.72573     0.09278     0.70563      0.4629     0.62191     0.11337     0.78986    0.98262     0.85354
     0.86889     0.87236     0.63646     0.45804     0.10876     0.84118     0.31049     0.70684    0.71617      0.9416
     0.45682     0.27314     0.75879    0.072619     0.78057     0.37275     0.32682     0.11941    0.15683     0.32215
     0.21714     0.21188     0.23398     0.12094     0.83434     0.83769     0.11437     0.24897    0.91117     0.45116
     0.17097     0.42333     0.38257     0.34116     0.91046     0.55847     0.46121     0.30671    0.82085     0.53366
    0.020299     0.29129     0.41523     0.62268     0.79487     0.20559     0.13821     0.42927    0.96562     0.68949

One approach: Split T into tables that are exactly 28000 rows long, plus another smaller table for the leftover rows at the end:

n_rows = 28000;

n_tables = ceil(size(T,1)/n_rows);

C = cell(n_tables,1);

for ii = 1:n_tables
    C{ii} = T((ii-1)*n_rows+1:min(ii*n_rows,end),:);
end

disp(C);

    {28000×10 table}
    {28000×10 table}
    {28000×10 table}
    {12014×10 table}

Another approach: Split T into (almost) equally-sized tables, each with as close to 28000 rows as you can get:

n_rows_target = 28000;

n_rows_T = size(T,1);

n_tables_min = floor(n_rows_T/n_rows_target);
n_tables_max = ceil(n_rows_T/n_rows_target);

n_rows_max = n_rows_T/n_tables_min;
n_rows_min = n_rows_T/n_tables_max;

if n_rows_max - n_rows_target > n_rows_target - n_rows_min
    n_rows = floor(n_rows_min);
    n_tables = n_tables_max;
else
    n_rows = floor(n_rows_max);
    n_tables = n_tables_min;
end

n_rows_extra = n_rows_T - n_rows*n_tables;

n_rows = n_rows * ones(1,n_tables);
n_rows(1:n_rows_extra) = n_rows(1:n_rows_extra) + 1;

start_idx = 1 + cumsum([0 n_rows]);
C = cell(n_tables,1);
for ii = 1:n_tables
    C{ii} = T(start_idx(ii):start_idx(ii+1)-1,:);
end

disp(C);

    {24004×10 table}
    {24004×10 table}
    {24003×10 table}
    {24003×10 table}

[Jacob Child]

For your first approach, how to i get it to output the table so that i can write it to an excel (.xls) sheet? im not seeing the tables in the workspace after i run it other than the original table

[Voss]

In both approaches, the tables are stored in the cell array C.

You can write each table to a different sheet of a xls file like this:

output_file = 'tables.xls';
for ii = 1:numel(C)
    writetable(C{ii},output_file,'Sheet',ii);
end