How to group values based on week number

Hey guys,

I got this code at the moment, If you run this code (gave the excel file as well), the output got a column called 'Week'. I want to be able to group the values in 'Verify12VInput' if they have the same weeks. Can anyone please please help me how to do this base on what I have?

%--FIND NaN ROWS THEN DELETE ENTIRE ROW
PCBAllDataSet = readtable('testfile8.csv');
%imports the only columns needed
PCBRequiredCol = PCBAllDataSet(:,{'PcbTestID','TestTime','PcbID','Verify12VInput','VerifyCurrentDraw','Rail5V'});
%remove row that contains NaN value
PCBRemoveNaNRow = rmmissing(PCBRequiredCol);

%--REMOVE ROW DUPLICATIONS
%create another table for keyset and valueset
%duplications will be removed as the table is made
newTable = containers.Map('KeyType','char','ValueType','any');

%forloop to assign key and value set on every row
for i = 1:size(PCBRemoveNaNRow,1)
    %key for hashMap to partner with valueSet
    keyID = char(table2cell(PCBRemoveNaNRow(i,3)));
    %current(i) row, 2nd column to end column
    valueSet = PCBRemoveNaNRow(i,2:end);
    %setting value pairs
    newTable(keyID) = valueSet;
end

%shows values of non duplicated newTable (Map -> Table)
voltsInput = newTable.values;
allDataSet = [];
for j = 1:numel(voltsInput)
allDataSet = [allDataSet; voltsInput{j}];
end

%%%--sorted dates and created week column
tsort = sortrows(allDataSet);
sortedTimeSet = sortrows(allDataSet);
weeklyNum = table(week(tsort.TestTime));
%new table with weekly number
newTableWeekly = [allDataSet weeklyNum];
%renames new table column
newTableWeekly.Properties.VariableNames{'Var1'} = 'Week'

OUTCOME AT THE MOMENT:

        TestTime          PcbID       Verify12VInput    VerifyCurrentDraw    Rail5V    Week
    ________________    __________    ______________    _________________    ______    ____

    03/07/2018 10:42    'G3000042'        11.965             0.3226          4.9396     26 
    27/06/2018 10:20    'G3000044'        11.962             0.3321          4.9425     27 
    20/07/2018 10:48    'G3000045'        11.969             0.3212          4.9555     28 
    10/07/2018 11:14    'G3001377'        11.968             0.3041          4.9851     28 
    10/07/2018 11:06    'G3002743'         11.97              0.288          4.9917     28 
    20/07/2018 10:50    'G3002744'        11.971             0.2976          4.9695     28 
    10/07/2018 11:04    'G3002745'         11.97             0.2876          4.9655     28 
    10/07/2018 10:52    'G3002746'         11.97             0.2879          4.9689     28 
    10/07/2018 10:57    'G3002747'        11.969             0.3003          4.9856     29 
    10/07/2018 11:10    'G3002749'        11.969             0.2891          4.9678     29 

EXPECTED OUTCOME (Hoping to see this outcome :( ):

something like this (for now, it doesn't matter if they were stored as table, array or etc. All I want is to be able to gorup the numbers and plot afterwards :(

26 = [11.965]

27 = [11.962]

28 = [11.969 11.968 11.968 11.97 11.971 11.97 11.97 ]

29 = [11.969 11.969]

The reason why I want to group the data based on weekly number is that, I want to get the mean value for each week then plot it afterwards, but I couldn't even group them.

someone please help :(

[Andrea Del Rosario]

Here is the testfile excel used to run the code

Answer 1

[Adam Danz]

The solution below lists the unique week numbers in unqWeekNum. It then loops through each unique week number and stores all Verify12VInput that belong to the week number in a cell array "data". So, data{h} is associated with unqWeekNum(h). Then you can perform statistics on the cell array as I showed by applying mean() to each element of the cell array.

% List unique week numbers
unqWeekNum = unique(newTableWeekly.Week);
% Loop through each week and store data in cell array
data = cell(length(unqWeekNum),1); 
for k = 1:length(unqWeekNum)
    data{k} = newTableWeekly.Verify12VInput(newTableWeekly.Week == unqWeekNum(k)); 
end
% calculate mean of each group
dataMean = cellfun(@mean, data); 

dataMean(h) is the mean of data from week number unqWeekNum(h).

[Andrea Del Rosario]

You are a legend !!!!! Thank you so much!

Just wondering, as I got this as result

{[ 11.9649]}

{[ 11.9622]}

{6×1 double}

{2×1 double}

is there a way to view the values inside the {6×1 double} and {2×1 double} ?

[madhan ravi]

celldisp() to view the contents of the cell

[Adam Danz]

Happy to help.

As madhan ravi pointed out, celldisp(data) will display the vectors stored in the cell array.

You can use cellfun() in lots of ways to apply functions to each element of the array. Here are some examples.

%how many numbers are in each cell element?
cellfun(@numel, data)
%Multiply all numbers by 6
cellfun(@(x) x*6, data, 'UniformOutput', false)
%subtract the mean of each group 
cellfun(@(x) x-mean(x), data, 'UniformOutput', false)

.