How to divide a column in table to other variables?

Hi all

I have a table called "TEMP" reporting daily minimum temprature(TMIN), daily maximum temprature(TMAX), and daily average temprature(TAVG) for a specific location in 2004. I want to devide the 'datatype' column into 3 columns of TMIN, TMAX, and TAVG. I will do it using the following code, but I would see 'NaN' values in the new table. Can anyone help me with this regard?

load ("TEMP1.mat");
unstackedTemp = unstack(TEMP,"value","datatype");

[Behrooz Daneshian]

Thank you so much!

Answer 1

[Stephen23]

You need to specify the "GROUPINGVARIABLE" option, otherwise "...unstack treats the remaining variables as grouping variables. Each unique combination of values in the grouping variables identifies a group of rows in S that is unstacked into a single row of U." So while you might think that every three lines of TAVG, TMAX, and TMIN form a "set" of values, take a look at the column/variable "attributes": are they the same for each set? (hint: no)

Solution: specify the grouping variable to specify which data get merged onto one line.

S = load('TEMP1.mat')

S = struct with fields:
    TEMP: [891×5 table]

T = S.TEMP

T = 891×5 table
             date              datatype           station           attributes    value
    _______________________    ________    _____________________    __________    _____

    {'2004-02-28T00:00:00'}    {'TAVG'}    {'GHCND:USR0000ABAN'}    {',,U,' }      47  
    {'2004-02-28T00:00:00'}    {'TMAX'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      61  
    {'2004-02-28T00:00:00'}    {'TMIN'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      31  
    {'2004-02-29T00:00:00'}    {'TAVG'}    {'GHCND:USR0000ABAN'}    {',,U,' }      55  
    {'2004-02-29T00:00:00'}    {'TMAX'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      66  
    {'2004-02-29T00:00:00'}    {'TMIN'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      42  
    {'2004-03-01T00:00:00'}    {'TAVG'}    {'GHCND:USR0000ABAN'}    {',,U,' }      63  
    {'2004-03-01T00:00:00'}    {'TMAX'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      69  
    {'2004-03-01T00:00:00'}    {'TMIN'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      57  
    {'2004-03-02T00:00:00'}    {'TAVG'}    {'GHCND:USR0000ABAN'}    {',,U,' }      56  
    {'2004-03-02T00:00:00'}    {'TMAX'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      63  
    {'2004-03-02T00:00:00'}    {'TMIN'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      53  
    {'2004-03-03T00:00:00'}    {'TAVG'}    {'GHCND:USR0000ABAN'}    {',,U,' }      63  
    {'2004-03-03T00:00:00'}    {'TMAX'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      74  
    {'2004-03-03T00:00:00'}    {'TMIN'}    {'GHCND:USR0000ABAN'}    {'H,,U,'}      55  
    {'2004-03-04T00:00:00'}    {'TAVG'}    {'GHCND:USR0000ABAN'}    {',,U,' }      67  

T = unstack(T,"value","datatype", "GroupingVariable","date")

T = 297×4 table
             date              TAVG    TMAX    TMIN
    _______________________    ____    ____    ____

    {'2004-02-28T00:00:00'}     47      61      31 
    {'2004-02-29T00:00:00'}     55      66      42 
    {'2004-03-01T00:00:00'}     63      69      57 
    {'2004-03-02T00:00:00'}     56      63      53 
    {'2004-03-03T00:00:00'}     63      74      55 
    {'2004-03-04T00:00:00'}     67      77      57 
    {'2004-03-05T00:00:00'}     67      73      58 
    {'2004-03-06T00:00:00'}     60      71      50 
    {'2004-03-07T00:00:00'}     54      69      43 
    {'2004-03-08T00:00:00'}     48      58      41 
    {'2004-03-09T00:00:00'}     46      59      37 
    {'2004-03-10T00:00:00'}     43      57      30 
    {'2004-03-11T00:00:00'}     51      69      29 
    {'2004-03-12T00:00:00'}     50      62      40 
    {'2004-03-13T00:00:00'}     52      65      38 
    {'2004-03-14T00:00:00'}     58      71      49 

As an aside, note that the "date" column/variable should probably be DATETIME, not text. This can be fixed using CONVERTVARS (even better: fix this when importing the data):

T = convertvars(T,'date','datetime')

T = 297×4 table
       date        TAVG    TMAX    TMIN
    ___________    ____    ____    ____

    28-Feb-2004     47      61      31 
    29-Feb-2004     55      66      42 
    01-Mar-2004     63      69      57 
    02-Mar-2004     56      63      53 
    03-Mar-2004     63      74      55 
    04-Mar-2004     67      77      57 
    05-Mar-2004     67      73      58 
    06-Mar-2004     60      71      50 
    07-Mar-2004     54      69      43 
    08-Mar-2004     48      58      41 
    09-Mar-2004     46      59      37 
    10-Mar-2004     43      57      30 
    11-Mar-2004     51      69      29 
    12-Mar-2004     50      62      40 
    13-Mar-2004     52      65      38 
    14-Mar-2004     58      71      49