How to iterate through rows in excel spreadsheet and sum specific data from there?

I have a 3 column, 23829 row spreadsheet in excel. The top rows of the spreadsheet are below.

COUNTY  Total Shift  Total Work Time
1000  0  0
1000  205  300
1000  30  30
1000  60  345
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  18  468
1000  0  0
1000  60  487
1000  0  0
1000  0  0
1000  0  495
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  0  0
1000  120  570
1003  0  0
1003  0  450
1003  0  0
1003  0  0
1003  0  0
1003  195  495
1003  60  455
1003  0  0
1003  0  0
1003  15  255
1003  144  699
1003  0  0
1003  65  575
1003  15  540
1003  30  380
1003  83  603
1003  5  495
1003  30  300
1003  0  0
1003  0  380
1003  103  211
1003  0  0
1003  37  40
1003  0  0
1003  0  0
1003  0  0
1003  300  420
1003  0  0
1003  0  0
1003  0  0
1003  0  0
1003  0  0
1015  0  0
1015  120  675
1015  0  0
1015  40  280
1015  0  37
1015  0  375
1015  0  0
1015  0  0
1015  10  490
1015  0  0
1015  0  515
1015  140  680
1015  0  0
1015  0  0
1015  0  0
1015  0  0
1015  0  0
1015  0  0
1015  0  0
1015  240  660
1015  0  0
1015  0  0
1015  0  0
1015  0  0
1015  0  0
1073  0  0
1073  0  0
1073  0  480
1073  285  810
1073  0  0
1073  0  0
1073  0  0
1073  45  645
1073  141  606
1073  15  495
1073  0  0
1073  0  0
1073  5  5
1073  0  0
1073  0  490
1073  10  520
1073  0  0
1073  0  0
1073  85  175
1073  0  0
1073  0  0
1073  165  650
1073  0  0
1073  0  524
1073  0  0

I have many different counties (starting at FIPS code 1000). I want to add up all the numbers in "Total Shift" within each county, and add up all the numbers in "Total Work Time" for each county. Then, I need to divide the total work time by the total shift, for each county. How can I do this in MatLab?

Ex: Sum all "total shift" and "total work time" for COUNTY "1003" and then divide total shift/total work time. I want the results of the sums of each county in a table with columns: county, total shift, total work time, and fraction shift/work.

[dpb]

Use readtable to bring the data into a table and then findgroups and splitapply can do whatever processing you wish by county. There are some advantages if you convert the COUNTY codes into categorical, but if they're all integers, that is easy enough to work with as well.

Answer 1

[Albert Fan]

You can do something like this:

 data = readtable('test.xlsx');
total_shift_county1000 = data(data.COUNTY == 1000, 'TotalShift');
total_shift_county1000_sum = sum(table2array((total_shift_county1000)))

you can replace data.COUNTY == 1000 as any county number you wish

[Mili Shah]

Thank you, @Albert Fan - the issue is that I put this in a for loop, since I have to run it for many counties. However, I do not have data for each county number within my range.

So, the result table only gives me the last county the data was summed for. How can I generate a table with all the sums for each county?

This is my code so far.

for geoCode=1000:56000

      data=readtable('ShiftWorkerOutsideNorm.xlsx');

      shiftCounty = data(data.COUNTY == geoCode, 'TotalShift');
      shiftSum = sum(table2array((shiftCounty)));

      workCounty = data(data.COUNTY == geoCode, 'TotalWorkTime');
      workSum = sum(table2array((workCounty)));

      fractionShift = shiftSum/workSum;
      outTable = table(geoCode, shiftSum, workSum, fractionShift);

end

Out of all the numbers 1000 to 56000, I only have 330 counties. My result table only gives me the data for the last county number in the range of the for loop (e.g. 56000).

[Albert Fan]

I see. you have two problems. The first one is that you do not want to call readtable() inside the for loop since it will attempt to read the file at each iteration, which is unnecessary. The second one is that since you put outTable = table(geoCode, shiftSum, workSum, fractionShift); inside the for loop, the outTable variable will be overrided at each iteration. If you want to keep all of your results, you should create the outTable outside the for loop by sonething like outTable = table(), and update it by: outTable = [outTable;table(geoCode, shiftSum, workSum, fractionShift)]. However this will create a new issue that all geocodes will be stored in the table, you can avoid that by

if ~sum(data.COUNTY == geoCode) == 0
  % your calculations
end

[Albert Fan]

The final result may looks like this:

data=readtable('test.xlsx');
outTable = table()
for geoCode=1000:2000
  if ~sum(data.COUNTY == geoCode) == 0
      shiftCounty = data(data.COUNTY == geoCode, 'TotalShift');
      shiftSum = sum(table2array((shiftCounty)));
      workCounty = data(data.COUNTY == geoCode, 'TotalWorkTime');
      workSum = sum(table2array((workCounty)));
      fractionShift = shiftSum/workSum;
      outTable = [outTable;table(geoCode, shiftSum, workSum, fractionShift)];
  end
end

Answer 2

[dpb]

m=readtable('milli.dat');      
[g,County]=findgroups(m.COUNTY);
sums=splitapply(@(x,y) sum([x y]),m.Work,m.Shift,g);
s=table(County,sums(:,1), sums(:,2), sums(:,1)./sums(:,2), ...
        'VariableNames',{'County','ShiftSum','WorkSum','Ratio'})
s =
4×4 table
  County    ShiftSum    WorkSum    Ratio 
  ______    ________    _______    ______
  1000      2695         493       5.4665
  1003      6298        1082       5.8207
  1015      3712         550       6.7491
  1073      5400         751       7.1904
>>

I did change the column titles to 'Shift' and 'Work' to be valid ML variable names; names with spaces aren't recognizable.