FOR Loop _ Sum columns in Excel
7 ビュー (過去 30 日間)
古いコメントを表示
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
2 件のコメント
dpb
2019 年 8 月 6 日
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...
採用された回答
Star Strider
2019 年 8 月 7 日
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
3 件のコメント
Star Strider
2019 年 8 月 7 日
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
2019 年 8 月 7 日
編集済み: dpb
2019 年 8 月 7 日
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...
その他の回答 (1 件)
dpb
2019 年 8 月 7 日
編集済み: dpb
2019 年 8 月 7 日
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
>>
4 件のコメント
dpb
2019 年 8 月 8 日
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.
参考
カテゴリ
Help Center および File Exchange で Data Import from MATLAB についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!