Extract values from double with for loop and calculate sum

Dear matlab-ers!

I have a double, called 'file' here, consisting of two columns and 36 rows. In the second column I have values from 0-17 randomly distributed and not occuring the same number of times. In the first column there is a 0 or 1 assigned to every value in the second column. I now want to look at every value in the second column from 0-17 and separately, for each occurence of that number, sum the respective value in column 1. Later I want to calculate the mean of each sum.

Say the value 5 occurs 4 times in column 2 and in column 1 the respective values are 1,1,0,1.

I'm not sure how to extract the numbers, what strcuture to put the numbers into and how to add them up.

This is a fraction of my code. I hope I could get my idea across and hope you can help me. I appreciate any tips!

An unexperienced beginner.

number = 0:17; %18 different values in column 2
row = 1:36;  %36 rows

C = cell(20,17);  %

for i = number
    for ii = row
        if file(ii,2) == i; %looking at 0-17, if number found, extract value in same row of first column
           C{i} = C{i} + file(ii,1); %trying to add up the values
        end
   %... adjust counters after loop

Answer 1

[Adam Danz]

There's no need for loops. I'll walk you through my solution.

I have a double, called 'file' here, consisting of two columns and 36 rows. In the second column I have values from 0-17 randomly distributed and not occuring the same number of times. In the first column there is a 0 or 1 assigned to every value in the second column.

%Generate data based on description
file = [randi(2, 36, 1)-1, randi(18, 36, 1)-1];

I now want to look at every value in the second column from 0-17 and separately, for each occurence of that number, sum the respective value in column 1.

% Create groups based on the 2nd column of 'file'
[group, groupID] =  findgroups(file(:,2)); 
% apply the "SUM" function to column 1 of 'file' within each group defined in column 2.
groupSum = splitapply(@sum, file(:,1), group); 

% Display the results in a table 
table(groupID, groupSum)

Later I want to calculate the mean of each sum.

I'm not sure what this means. The mean of a single number is just that single number. However, you can replace the "SUM" function with any other function. Here are some examples.

% Calculate the mean of column 1 within each group
groupSum = splitapply(@mean, file(:,1), group); 

% Determine the number of members of each group
groupSum = splitapply(@length, file(:,1), group); 

% apply a more advanced function where I take the sum and multiply by 3
groupSum = splitapply(@(x) sum(x)/3, file(:,1), group); 

[Pauli]

This is exactly what I was looking for, thank you!

Also, I meant the mean of column 1 within each group, so before taking the sum of course :)

Maybe you can help me with this one too: If I have two data sets in a table now (so 4 columns) and get two tables as output.. Is there a way that I can merge those to display the data next to each other in one table?

As the single tables all have different lengths I can't simply merge them, so I tried using the outerjoin function but it seems to put Nan's in random places.

Or maybe I have to fill up the tables with Nan's before I merge them? For example, if there is no occurence of the value 3:

0 3

1 4

2 1

3 nan

4 2

[Adam Danz]

If you'd like to represent every possible group ID in the output table, construct your tables this way. Groups that do not have a paired value will show a NaN value.

% Create table with *all* groups 0:17; fill 2nd column wiht NaNs
t = table((0:17)', nan(18,1), 'VariableNames', {'groupID', 'groupSum'});

% match the "groupID" with the correct row of your table
[~, groupIdx] = ismember(groupID, t.groupID);

% Insert the group sums into the correct row of your table.
t.groupSum(groupIdx) = groupSum; 

Now all of your tables will have the same number of rows and it will be much easier to merge tables.

If you're vertically merging (concatenating) and the tables have the same header names:

grandTable = [table1; table2];

If you're horizontally concatenating, the header names must be unique for each column

grandTable = [table1, table2];

If you're trying to horizontally concatenate with duplicate column names, you either need to rename the variable names so that all of them are unique or just but the concatenated data into a cell array instead of a table.

[Pauli]

Thank you!

Answer 2

[TADA]

Can also use accumarray to solve this one

n = 10;
xMax = 7;

% generate the matrix as: [boolean, x, 1]
% x is the column of values you wanted (between 0 and xMax)
% boolean is the flag 0/1
% the third column will be used to count the occurences of each value to
% calculate the mean boolean value of each
file = [randi(2, n, 1)-1, randi(xMax, n, 1), ones(n, 1)];

% turn x column into a list of valid subscripts
idx = file(:,2)+1;

accumulatedFlags = accumarray(idx, file(:,1));
nOccurences = accumarray(idx, file(:,3));
uniqueValues = unique(file(:,2));
uniqueIdx = uniqueValues+1;

% [sum, x, mean]
out = [accumulatedFlags(uniqueIdx), uniqueValues, accumulatedFlags(uniqueIdx)./nOccurences(uniqueIdx)];

[Pauli]

Got it now, but thank you!