Unstack - some new values are created

Hi all,

I'm working with a table which contains 3 variables, the first one is categorical (id_number), the second one is datetime (MM/dd/yyy HH:mm), and the last one is just numeric (let's say Var_3).

I need to unstack the table so that I get a new table with many colums as id_number and datetime, with values of Var_3, the code I wrote is:

load data.mat
whos TABLE
TABLE(1:5,:)
c = nnz(TABLE.Var_3 > 90)
tableNew = unstack(TABLE, 'Var_3', "id_number"); % Uploaded as dataNew.mat
tableNew(1:5,:)
cNew = nnz(tableNew.NA10 > 90)        % Test if there are values greater than 90 in the NA10 id_number

I obtain the table with the desired format but somehow some new values are created at row number 3485, with values over than about 90, which is completely wrong because those values do not exist in the original table. The normal range of Var_3 values are between -10 and 35 approximately (those are ambient temperatures indeed).

c = 0 and cNew = 115

Thank you guys!

[Siddharth Bhutiya]

When you unstack your table, if there are multiple rows in the table that have the same value for id_number and datetime, then all those values would be aggregated. The default for that is sum, so your values are being summed up and hence you are seeing values greater than 90. Depending on your data you might want to change the AggregationFunction to @mean or @unique.

>> t = table(["A";"A";"B";"B"],[3;4;3;4],[1;2;3;4]);

>> t = [t;t]

t =

  8×3 table

    Var1    Var2    Var3
    ____    ____    ____

    "A"      3       1  
    "A"      4       2  
    "B"      3       3  
    "B"      4       4  
    "A"      3       1  
    "A"      4       2  
    "B"      3       3  
    "B"      4       4  

>> unstack(t,'Var3','Var1') % This would sum up the values of Var3

ans =

  2×3 table

    Var2    A    B
    ____    _    _

     3      2    6
     4      4    8

>> unstack(t,'Var3','Var1','AggregationFunction',@mean) % This would take the mean of values of Var3

ans =

  2×3 table

    Var2    A    B
    ____    _    _

     3      1    3
     4      2    4