Error using convert2quaterly: The value of 'TT1' is invalid.

Hi there,

I am trying to convert a weekly time series into a quaterly one.

This is the code I am using:

TT = readtable('data.xlsx');
TT1 = convert2quarterly(TT,'Aggregation',["lastvalue" "sum"]);

data.xlsx is an excel file with two columns, the first one has the time and the second one the values (attached).

While trying to do so I get the following error:

The value of 'TT1' is invalid. Expected TT1 to be one of these types:

timetable

Instead its type was table.

I have tried different options but I am really struggling to get the operation done.

I am super graterful for any possible hint/advice/code suggestions.

Thank you so much.

Answer 1

[Star Strider]

The table argument has to be a timetable, so it must be converted after creating an appropriate datetime array for ‘date’.

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1127900/data.xlsx')

T = 939×2 table
         date         values
    ______________    ______

    {'2002-01-04'}    0.2188
    {'2002-01-11'}    0.1909
    {'2002-01-18'}    0.2563
    {'2002-01-25'}    0.2256
    {'2002-02-01'}    0.2081
    {'2002-02-08'}     0.247
    {'2002-02-15'}    0.1742
    {'2002-02-22'}     0.206
    {'2002-03-01'}    0.1669
    {'2002-03-08'}     0.187
    {'2002-03-15'}    0.1325
    {'2002-03-22'}    0.1258
    {'2002-03-29'}    0.0676
    {'2002-04-05'}     0.134
    {'2002-04-12'}    0.1584
    {'2002-04-19'}    0.1675

T.date = datetime(T.date);
TT = table2timetable(T);
TT1 = convert2quarterly(TT)

TT1 = 72×1 timetable
       date        values
    ___________    ______

    31-Mar-2002    0.0676
    30-Jun-2002    0.1112
    30-Sep-2002    0.2145
    31-Dec-2002    0.0919
    31-Mar-2003    0.1506
    30-Jun-2003    0.1177
    30-Sep-2003    0.0837
    31-Dec-2003    0.0356
    31-Mar-2004    0.0839
    30-Jun-2004     0.033
    30-Sep-2004    0.0594
    31-Dec-2004    0.0495
    31-Mar-2005    0.0717
    30-Jun-2005    0.0914
    30-Sep-2005    0.1035
    31-Dec-2005    0.0392

TT1 = convert2quarterly(TT,'Aggregation',["lastvalue" "sum"])

Error using TimeTablePeriodicityConversionUtilities.validateAggregationMethods
Aggregation methods must be scalars applied to all variables or vectors of the same length as the number of variables in TT.

Error in convert2quarterly (line 149)
aggregation = TimeTablePeriodicityConversionUtilities.validateAggregationMethods(aggregation, nVariables, "quarterly");

The 'Aggregation' does not work with ‘TT’. I have no experience with this function, so I have no idea what the problem may be.

.

[Leonardo Musati]

Thank you so much for your reply, it is very helpful.

[Star Strider]

As always, my pleasure!

Answer 2

[Simon Chan]

Your data has only one column besides the date. So you can only state one method to your data.

The following shows an example when "sum" and "lastvalue" are selected as the Aggregation method.

opts = detectImportOptions('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1127900/data.xlsx');
opts = setvartype(opts,'date','datetime');
TT = readtimetable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1127900/data.xlsx',opts);
TT1 = convert2quarterly(TT,'Aggregation',"sum")     % Use sum as the method

TT1 = 72×1 timetable
       date        values
    ___________    ______

    31-Mar-2002    2.4067
    30-Jun-2002    1.8909
    30-Sep-2002    2.6698
    31-Dec-2002    2.3818
    31-Mar-2003    1.9728
    30-Jun-2003    1.6916
    30-Sep-2003    1.1438
    31-Dec-2003    0.9237
    31-Mar-2004    1.0738
    30-Jun-2004    0.8648
    30-Sep-2004    0.7871
    31-Dec-2004    0.8055
    31-Mar-2005    0.8368
    30-Jun-2005    1.2639
    30-Sep-2005    1.1042
    31-Dec-2005    1.2724

TT2 = convert2quarterly(TT,'Aggregation',"lastvalue") % Use lastvalue as the method

TT2 = 72×1 timetable
       date        values
    ___________    ______

    31-Mar-2002    0.0676
    30-Jun-2002    0.1112
    30-Sep-2002    0.2145
    31-Dec-2002    0.0919
    31-Mar-2003    0.1506
    30-Jun-2003    0.1177
    30-Sep-2003    0.0837
    31-Dec-2003    0.0356
    31-Mar-2004    0.0839
    30-Jun-2004     0.033
    30-Sep-2004    0.0594
    31-Dec-2004    0.0495
    31-Mar-2005    0.0717
    30-Jun-2005    0.0914
    30-Sep-2005    0.1035
    31-Dec-2005    0.0392

[Leonardo Musati]

Hi, my issue has already been solved, but thank you anyway for your feedback.