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

Question

Mili Shah 2018-7-27

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/412455-how-to-iterate-through-rows-in-excel-spreadsheet-and-sum-specific-data-from-there

编辑： dpb 2018-7-28

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

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2018-7-27

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 2018-7-27

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/412455-how-to-iterate-through-rows-in-excel-spreadsheet-and-sum-specific-data-from-there#answer_330685

在 MATLAB Online 中打开

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

3 个评论
显示 1更早的评论隐藏 1更早的评论

Albert Fan 2018-7-27

在 MATLAB Online 中打开

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 2018-7-27

在 MATLAB Online 中打开

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 2018-7-28

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/412455-how-to-iterate-through-rows-in-excel-spreadsheet-and-sum-specific-data-from-there#answer_330713

编辑：dpb 2018-7-28

在 MATLAB Online 中打开

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
>>