AVG, occurence, vlook up, groups

Hello,

I would like to ask for help in determining the average of values in specific groups of data in a database. I processed the issue in excel in the attachment. This is a preview.

Thank you

Answer 1

[Image Analyst]

Try this:

data = readmatrix('Problem description.xlsx', 'Range', 'b3:c31')

data = 29×2

1 40 0 21 1 58 1 50 1 51 0 15 0 48 0 36 1 43 1 42

averages = grpstats(data(:, 2), data(:, 1), "mean")

averages = 2×1

29.2727 41.1667

counts = histcounts(data(:, 1))

counts = 1×2

11 18

[Marek Drliciak]

Thanks, the problem is that I need the average values from the "Value" attribute for the individual groups. Not just 1-0. In Excel, I used color to show the groups that start and end with the number 1. The green color defines a group with two numbers, blue with 3,...

[Image Analyst]

Then you need to identify each group in column 1 with a separate ID number which you can do with bwlabel in the Image Processing Toolbox.

data = readmatrix('Problem description.xlsx', 'Range', 'b3:c31')

data = 29×2

1 40 0 21 1 58 1 50 1 51 0 15 0 48 0 36 1 43 1 42

% Identify separate groups:
[groupIDs, numGroups] = bwlabel(data(:, 1))

groupIDs = 29×1

1 0 2 2 2 0 0 0 3 3

numGroups = 8

averages = grpstats(data(:, 2), groupIDs, "mean")

averages = 9×1

29.2727 40.0000 53.0000 43.3333 22.5000 48.5000 41.0000 27.5000 43.5000

counts = histcounts(groupIDs)

counts = 1×9

11 1 3 3 2 2 1 2 4

[Marek Drliciak]

Thank You very much. That was what I looking for.