MATLAB Answers

How to calculate count of matching rows and average of specified column of all matching rows

5 views (last 30 days)
Mekala balaji
Mekala balaji on 9 Jun 2018
Commented: Image Analyst on 9 Jun 2018
Hi,
I have cell array matrix as below:
Day Name Score Status
2018-01-02 23:04:45 VAH 23 Success
2018-04-05 13:44:15 BHT 23 Success
2018-05-02 14:34:56 HDA07H 12 Success
2018-05-12 09:34:56 PHA07H 3 Failure
2018-05-12 09:34:56 HJA07H 34 Failure
2018-05-23 09:23:46 HOA07H 47 Success
I want to count the total count of "Success" cases and the average score of success cases in column3
Day Name Score Status
2018-01-02 23:04:45 VAH 23 Success
2018-04-05 13:44:15 BHT 23 Success
2018-05-02 14:34:56 HDA07H 12 Success
2018-05-23 09:23:46 HOA07H 47 Success
My desired output is:
SuccesCount AvgSuccessScore
4 26.25
I use below code: do in 4 steps, but can I get it one step?
Index= strfind(input(:,4), 'Success');
Index = find(not(cellfun('isempty', Index)));
AvgSuccessScore=mean(input(Index,3))
SuccesCount=size(Index,1);

  1 Comment

Paolo
Paolo on 9 Jun 2018
Are two steps acceptable?
SuccessCount = nnz(strcmp(cellA(:,4), 'Success'));
AvgSuccessScore=mean(cell2mat(cellA(strcmp(cellA(:,4),'Success'),3)));

Sign in to comment.

Answers (1)

Image Analyst
Image Analyst on 9 Jun 2018
I believe so. First, don't use a cell array, use a table. Cell array is not the best form for this data - table is much better. Then simply call grpstats() if you have the Statistics and Machine Learning Toolbox.

  2 Comments

Image Analyst
Image Analyst on 9 Jun 2018
See how grpstats() works with your antique (pre-table) version. The function was there back then and worked somehow. Maybe just use cell2mat() to extract the numerical parts into a matrix.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by