MATLAB: FOR Loop _ Sum columns in Excel

#forloop #sum #columns #excel

I'm trying to calculate the sum of few columns in an excel file. Each row is each participant so the Sum will be repetitve Row times (7), BPAQ is the filename

When I hit run this script, the output is not the Sum but two new variables in the workspace with only one number inside.

Can someone help me figure this out and Am I using the right funtion to sum columns in table?

Thank you so much.

I've also attached the screenshot

for i=1:1:7
score_BPAQ = sum(BPAQ{i,["bps_1","bps_29"]});
end

Best Answer

Try this:

[D,S] = xlsread('BPAQ.xlsx');
BPAQ = D(:,5:33);
BPAQ(isnan(BPAQ)) = 0;                              % Set ‘NaN’ Values To 0
RowSum = sum(BPAQ,2)                                % Sum For Each PArticipant
Tot = sum(RowSum)                                   % Sum For All Participants

producing:

Related Solutions

MATLAB: Troubleshooting Import big excel file

>> t=readtable('export.csv');
>> whos t
  Name       Size              Bytes  Class    Attributes
  t         45x255            270679  table              
>> t(1:10,1:10)
ans =
  10×10 table
    ID       event_name        date_contacts    name    phone1    phone1_type    phone1_msg    phone2    phone2_type    phone2_msg
    __    _________________    _____________    ____    ______    ___________    __________    ______    ___________    __________
    1     'baseline1_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    1     'baseline2_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    1     'baseline3_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    1     'baseline4_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    1     'baseline5_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    1     'baseline6_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    2     'baseline1_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    2     'baseline2_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    2     'baseline3_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
    2     'baseline4_arm_1'    NaN              ''      ''        NaN            NaN           ''        NaN            NaN       
>>

Didn't seem to have any trouble here.

The files isn't all that big...it's mostly empty fields.

>> d=dir('export.csv')
d = 
  struct with fields:
       name: 'export.csv'
     folder: 'C:\Users\Duane\Documents\MATLAB\Work'
       date: '13-Aug-2019 14:47:19'
      bytes: 17449
      isdir: 0
    datenum: 7.3765e+05
>>

It's only 17k on disk...

MATLAB: Populating matrix with data from a table

Since have expanded somewhat, will add additional Answer to bring to forefront -- and illustrate couple other things...

Above we ended up with something like --

opt=detectImportOptions('ii.csv');                              % create base import options object
opt.VariableTypes=strrep(opt.VariableTypes,'char','double');    % all are double except time
opt.PreserveVariableNames='true';                               % and keep original symbol/name
opt.VariableNames(1)={'Date'};                                  % fix Date column name
opt=setvaropts(opt,'Date',"DatetimeFormat",'dd-MMM-uuuu');      % and a more concise display format

Now, since it is a set of time data, use the timetable object instead of just the straight table -- this gets you the date info essentially "for free" in conjunction with the data selection.

ttII=readtimetable('ii.csv',opt);                               % 't' or 'tt' prefix to remind of class

Now you can do addressing however wish for whatever purpose -- remember that "()" addressing on the table will return another table but "{}" or "dot" addressing the data.

With a plain table, though, the Date and data aren't the same type/class so you can't concatenate them--that's the advantage of the timetable; the date comes along for the ride...

So you can do things like--

>> ix=randperm(width(ttII),10)          % pick a random set of columns/symbols
ix =
        512.00       1168.00        899.00        177.00        286.00        463.00        739.00        975.00        159.00        734.00
>> format bank, format compact          % since are stock prices; more legible display format
>> ttII(:,ix)                           % retrieve those -- note result is another table w/ Date
ans =
  3×10 timetable
       Date        AGGLN    EMGSNO    SONIPL    CBGLN    ROGSW     JPRLN    FWBIM    PFCLN    NG_LN    ATLIM
    ___________    _____    ______    ______    _____    ______    _____    _____    _____    _____    _____
    14-Feb-2000    0.50      NaN       NaN      4.25     103.46    25.31     NaN      NaN     1.69     3.22 
    15-Feb-2000    0.50      NaN       NaN      4.15     102.14    25.55     NaN      NaN     1.66     3.24 
    16-Feb-2000    0.49      NaN       NaN      4.05     102.62    25.63     NaN      NaN     1.72     3.33 
>> ttII{:,ix}                           % retrieve as array -- free of the date
ans =
          0.50           NaN           NaN          4.25        103.46         25.31           NaN           NaN          1.69          3.22
          0.50           NaN           NaN          4.15        102.14         25.55           NaN           NaN          1.66          3.24
          0.49           NaN           NaN          4.05        102.62         25.63           NaN           NaN          1.72          3.33
>>

Read up on tables and timetables -- there is a lot of processing power packaged to go with them -- particularly varfun, rowfun and friends as well as time selection with timerange

Don't sell MATLAB short...takes some time and effort to learn about syntax, but it's extremely powerful when do.

ADDENDUM:

However, you'll undoubtedly run into memory and perhaps performance issues if you try to load years of data for this many variables. I suggest to make the selection of variables needed at the time you read the data and only read in those really need.

You can do this by manipulating the 'SelectedVariableNames' property in the import options object. You could have ended up with the above table by

 opt.SelectedVariableNames=opt.SelectedVariableNames(ix);

to have gotten the randomized selection or by any other chosen pattern.

Best Answer

Related Solutions

MATLAB: Troubleshooting Import big excel file

MATLAB: Populating matrix with data from a table

Related Question