FOR Loop _ Sum columns in 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 

[dpb]

Show what

whos BPAQ

returns and

BPAQ.Properties.VariableNames

It's not clear to me what you're actually trying to sum and what you're trying to describe about the sum being repetitive.

Show us a small example of what you have and what you're trying to compute...

[Ted Dang]

I've attached BPAQ table.

I have to compute the sum from bps_1 to bps_29 in each row. There're 8 participants so I have to repeat this 8 times. That's why I try For Loop.

I expect the output returned will be the sum for 8 participants.

I'm a new beginner so bear this with me!

Answer 1

[Star Strider]

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:

RowSum =
   116
   116
   116
    70
   116
    60
    41
   116

Tot =
   751

[Ted Dang]

Thank you so much @Star Strider. It's much easier and simplier.

I also have another question related to the name of columns in table.

Is it easier if we call them: 5,6,7...33 rather than bps_1, bps_2..., bps_9 or A,B,C...AA,AI...?

Actually I have to work with an original excel file (more than 3000 columns) but I don't know how to handle this big file so I break it into smaller files like BPAQ. In this case, should I stick to this numeric rule?

[Star Strider]

As always, my pleasure.

I used the column numbers because that is what the xlsread function creates with your file. You might find the readtable functon (R2013b and later) easier to work with, since it will allow you to choose the columns by name (as created by readtable), making them easier to work with. You can then extract the selected data to a matrix, or calculate them in the table using the information in the documentation section on Access Data in a Table. (It might be easier to extract them and work with them using the table2array function. That is your decision.)

[dpb]

You're right on track to get rid of the sequentially-named variables in lieu of an array -- see the Answer I posted that does it after the fact by finding where they are in the imported table.

Alternatively, given the larger file, you could(should) create a SpreadsheetImportOptions object and modify it to treat the bps_N variables as an array on import.

See

doc detectimportoptions

for more info, examples...

Answer 2

[dpb]

As one alternative solution to S S, there's the readtable route...

t=readtable('BPAQ.xlsx');                                         % read .xlsx file to table, t
ix=find(ismember(t.Properties.VariableNames,{'bps_1','bps_29'})); % find location of given variables
t.bps=t{:,ix(1):ix(2)};                                           % replace named variables bps_n w/ array bps
t(:,ix(1):ix(2))=[];                                              % remove the now superfluous named ones
t.bpsmean=mean(t.bps,2,'omitnan');                                % compute row means excluding NaN

leaves you with...

>> t
t =
  8×7 table
    sudscreen_studyid    redcap_event_name    date_bpaq    bps_score    q_bpaq_complete         bps         bpsmean
    _________________    _________________    _________    _________    _______________    _____________    _______
    1                    'baseline1_arm_1'    NaN          NaN          2                  [1x29 double]         4 
    2                    'baseline1_arm_1'    NaN          NaN          2                  [1x29 double]         4 
    3                    'baseline1_arm_1'    NaN          NaN          2                  [1x29 double]         4 
    4                    'baseline1_arm_1'    NaN           70          2                  [1x29 double]    2.5926 
    5                    'baseline1_arm_1'    NaN          NaN          0                  [1x29 double]         4 
    6                    'baseline1_arm_1'    NaN          NaN          2                  [1x29 double]    2.2222 
    7                    'baseline1_arm_1'    NaN          NaN          2                  [1x29 double]    1.5185 
    8                    'baseline1_arm_1'    NaN          NaN          0                  [1x29 double]         4 
>> 

[Ted Dang]

@dpb: Thank you so much!!!!!!! I think I can definitely use your solution as well, especially when I have to keep some other information of the table like event name, screen id... Let's me take a look and dive into it because it's somewhat complicated and I want to understand the underlying logic why you come up with this.

[dpb]

"...logic why you come up with this."

Precisely because almost every time you have variables with sequentially-generated suffixes for names or the like, in MATLAB they invariably should be an array instead. Doing that makes subsequently operating on either the array or subsets of the array essentially painless as computing the row means here illustrates.

The only difficulty arises in programmatically creating the array from the named variables in the first place -- here, since the original post didn't indicate this was just a very small subset of a much larger dataset, I hardcoded the first/last names in the lookup to locate their position in the table.

"In anger" it would be better to find that size dynamically as well, and if the size is potentially variable, almost mandatory. This is simple enough in principle as well as long as there is a way to uniquely identify which variables are those which belong to the array set--here a regular expression of bps_digits would suffice.

[Ted Dang]

There's a lot of gem in your comments, I'm still working on my function and this table. I might come back and forth and look at your comments again because you touched on lot of things I need to work on. For now, I can understand how to work with sequentially-named variables which I was struggling for a while. and I'm still looking at the documentation to understand some details in your code.

Thank you so much!

[dpb]

You're welcome, certainly! Trying to teach as well as "just answer" is what I enjoy most...and so I'll typically try to push learners to do more than just enough to get by, but to begin to use the power inherent in Matlab...that takes some effort, granted, but is effort well repaid.

To see what's going on above, you can take pieces of the indexing expressions out and just evaluate them on their own -- then much of the mystery will disappear.