How can I do the cumulative?

Hello everyone,

I should calculate the cumulative of daily data.

Then I should do a monthly cumulative and finally, the yearly average.

Can anyone help me please?

Thank you.

[Wan Ji]

Hi, Pul

I'd like to know which column needs the cumulative calculation and how to deal with the NaN number

So that I can help you

[Pul]

Hello Wan,

Yes, sorry. I need to take into account "Time" and "Var5".

Regarding NaNs, I don't want they become zero, otherwise my calculation would be altered.

Thank you very much for your help.

[Wan Ji]

@Pul

Hi Pul,

I think Star Strider has offered good answer, you can do following his way.

Wan Ji

[Pul]

Yes, thank you!

Answer 1

[Star Strider]

The retime function is your friend foir this type of problem —

LD = load('giulia_TT[1].mat');
giulia_TT = LD.giulia_TT

giulia_TT = 136440×6 timetable
            Time            Var1    Var2    Var3     Var4    Var5    Var6 
    ____________________    ____    ____    _____    ____    ____    _____

    10-Dec-1997 09:00:00    310      1        -27     43     NaN     712.9
    10-Dec-1997 10:00:00    300      1      -28.4     47     NaN     712.7
    10-Dec-1997 11:00:00    260      4      -30.3     48     NaN     712.8
    10-Dec-1997 12:00:00    240      4      -32.4     49     NaN     712.6
    10-Dec-1997 13:00:00    220      4      -34.5     50     NaN     712.3
    10-Dec-1997 14:00:00    180      4      -31.4     52     NaN     712.3
    10-Dec-1997 15:00:00    220      1      -31.3     53     NaN     712.1
    10-Dec-1997 16:00:00    200      2      -29.9     53     NaN       712
    10-Dec-1997 17:00:00    180      3      -29.4     54     NaN     711.7
    10-Dec-1997 18:00:00    200      2      -29.1     54     NaN     711.5
    10-Dec-1997 19:00:00    210      3      -28.9     54     NaN     711.4
    10-Dec-1997 20:00:00    180      3      -28.7     54     NaN     711.4
    10-Dec-1997 21:00:00    210      1      -28.3     54     NaN     711.3
    10-Dec-1997 22:00:00    250      2      -28.1     55     NaN     711.3
    10-Dec-1997 23:00:00    270      3      -28.2     55     NaN     711.4
    11-Dec-1997 00:00:00    270      3      -27.9     55     NaN     711.3

RTds_giulia_TT = retime(giulia_TT,'daily', @(x)sum(x,'omitnan'))            % Daily Sum

RTds_giulia_TT = 7722×6 timetable
       Time        Var1    Var2     Var3     Var4    Var5    Var6 
    ___________    ____    ____    ______    ____    ____    _____

    10-Dec-1997    3430     38     -445.9     775     0      10680
    11-Dec-1997    4970     88     -708.9    1304     0      17079
    12-Dec-1997    4770    190     -718.3    1265     0      17107
    13-Dec-1997    4820    257     -593.6    1329     0      17118
    14-Dec-1997    5130    223     -625.3    1276     0      17148
    15-Dec-1997    6160    122     -610.4    1231     0      17301
    16-Dec-1997    5330    282     -612.3    1254     0      17229
    17-Dec-1997    4840    220     -631.3    1295     0      17226
    18-Dec-1997    5210    171     -638.5    1294     0      17181
    19-Dec-1997    5500    192     -662.9    1233     0      17175
    20-Dec-1997    5900    149     -629.9    1242     0      17164
    21-Dec-1997    5020    153     -615.8    1273     0      17345
    22-Dec-1997    5500    331     -555.8    1315     0      17371
    23-Dec-1997    5020    274     -579.6    1306     0      17275
    24-Dec-1997    5090    220     -541.7    1338     0      17406
    25-Dec-1997    5390    325     -542.8    1293     0      17477

RTms_giulia_TT = retime(giulia_TT,'monthly', @(x)sum(x,'omitnan'))          % Daily Sum

RTms_giulia_TT = 254×6 timetable
       Time           Var1       Var2     Var3      Var4     Var5        Var6   
    ___________    __________    ____    _______    _____    _____    __________

    01-Dec-1997    1.1479e+05    4878     -12777    28071        0     3.732e+05
    01-Jan-1998    1.6505e+05    4790     -20623    38684        0    5.3087e+05
    01-Feb-1998    1.5418e+05    7953     -22694    35043        0    4.7948e+05
    01-Mar-1998    1.5895e+05    8202     -16416    53622        0    5.0993e+05
    01-Apr-1998         19780    1272    -1792.9     7235        0         64240
    01-May-1998             0       0          0        0        0             0
    01-Jun-1998             0       0          0        0        0             0
    01-Jul-1998             0       0          0        0        0             0
    01-Aug-1998             0       0          0        0        0             0
    01-Sep-1998             0       0          0        0        0             0
    01-Oct-1998             0       0          0        0        0             0
    01-Nov-1998         15670     735    -2075.2     4977     1151         52008
    01-Dec-1998    1.5947e+05    6713     -18695    51606    12188    5.3271e+05
    01-Jan-1999    1.4317e+05    6176     -17875    55303    13262    5.3282e+05
    01-Feb-1999    1.4437e+05    6296     -26016    40696    12270    4.8046e+05
    01-Mar-1999    1.5873e+05    7751     -38321    34910    14902    5.2862e+05

RTym_giulia_TT = retime(giulia_TT,'yearly', @(x)mean(x,'omitnan'))          % Yearly Average

RTym_giulia_TT = 23×6 timetable
       Time         Var1      Var2      Var3       Var4      Var5      Var6 
    ___________    ______    ______    _______    ______    ______    ______

    01-Jan-1997    221.18    9.3988    -24.618    54.087       NaN    719.08
    01-Jan-1998    221.12     9.739    -31.197     62.76    16.347    712.16
    01-Jan-1999    205.71     9.868    -38.546    60.084    27.833    709.86
    01-Jan-2000    206.22    10.858    -40.587     58.32    42.214    712.57
    01-Jan-2001    209.39    9.7574    -40.086    57.345    49.112    711.97
    01-Jan-2002    209.49     11.56    -46.325    50.188    41.987    714.63
    01-Jan-2003     204.8    13.667    -45.886    51.518     58.09     713.5
    01-Jan-2004    216.18    10.512    -44.052    53.394    68.275    697.74
    01-Jan-2005    212.52    11.821    -41.702     55.45     76.23    689.32
    01-Jan-2006    209.99    10.875    -40.048    57.513    98.428    721.18
    01-Jan-2007    210.15    9.7131    -39.308    58.263    96.873    747.83
    01-Jan-2008    215.82    11.908    -40.737    56.545    73.186    711.08
    01-Jan-2009    216.33    12.638    -41.397    56.284    78.298    711.96
    01-Jan-2010    226.65    11.077     -47.59    49.562    75.023    710.05
    01-Jan-2011     213.5    12.444     -44.61    54.425    90.797    713.36
    01-Jan-2012    218.74    11.087    -41.803    56.673    101.93    713.02

Experiment to get different results.

.

[Pul]

Perfect, thank you!

load('giulia_TT');
GIULIA_YEARLY_N=retime(giulia_TT,'yearly','mean');
N=GIULIA_YEARLY_N.Var5*3.5

If I do in this way, how can I do differences between years, considering "N" instead of Var5?

For istance 1998- (minus)1999, 1999-(minus)2000, 2000-(minus)2001.

Because I'd like to plot them, then.

Thank you.

[Star Strider]

As always, my pleasure!

I am not certain that I understand what you want.

Perhaps:

LD = load('giulia_TT[1].mat');
giulia_TT = LD.giulia_TT;
GIULIA_YEARLY_N = retime(giulia_TT,'yearly', @(x)mean(x,'omitnan'));        % Yearly Average
N=GIULIA_YEARLY_N.Var5*3.5;
SelIdx = year(GIULIA_YEARLY_N.Time) >= 1998;                                % Logical Mask Vectror
N_Sel = N(SelIdx);
N_Sel_Dif = -diff(N_Sel);
Years = 1998+(1:numel(N_Sel_Dif));

figure
plot(Years, N_Sel_Dif)
grid
xlim([min(Years) max(Years)])
xlabel('Years')
ylabel('Yearly N-Differences')
set(gca, 'XTick',Years)

Make appropriate changes to get the result you want.

.

[Pul]

@Star Strider

Yes, perfect,you got it exactly what I meant.

You're always so helpful.

I have the last question, I promise. What if I want to do in this way :

15 Dec 1998 until 7 Dec 1999, 7 Dec 1999-30 Nov 2000, 30 Nov 2000 -13 Nov 2001, 13 Nov 2001-30 Dec 2002,30 Dec 2002-4 Jan 2004,4 Jan 2004-22 Nov 2004,22 Nov 2004-24 Nov 2005, 24 Nov 2005-15 Dec 2006,15 Dec 2006-22 Jan 2008, 22 Jan 2008-9 Jan 2011/3 , 1 Jan 2010-16 Nov 2011, 16 Nov 2011-9 Jan 2013/2 , 9 Jan 2013-5 Jan 2014, 5 Jan 2014-31 Jan 2015, 31 Jan 2015-25 Jan 2018/3 ?

I mean, instead of considering one year, I' d like to do a more precise calculation.

Thank you very much.

[Star Strider]

As always, my pleasure!

I am not certain what you want to do.

One way to select the dates you want to work with is:

LD = load('giulia_TT[1].mat');
giulia_TT = LD.giulia_TT;
GIULIA_YEARLY_N = retime(giulia_TT,'yearly', @(x)mean(x,'omitnan'));        % Yearly Average
N=GIULIA_YEARLY_N.Var5*3.5;
limits_mtx = datetime({'15 Dec 1998', '07 Dec 1999'; '07 Dec 1999', '30 Nov 2000'}, 'InputFormat','dd MMM yyyy')

limits_mtx = 2×2 datetime array
   15-Dec-1998   07-Dec-1999
   07-Dec-1999   30-Nov-2000

for k = 1:size(limits_mtx,1)
    IdxRng = (giulia_TT.Time>=limits_mtx(k,1)) & (giulia_TT.Time<limits_mtx(k,2));                                % Logical Mask Vectror
    GIULIA_SELECT{k,:} = giulia_TT(IdxRng,:);
end

GIULIA_SELECT

GIULIA_SELECT = 2×1 cell array
    {4368×6 timetable}
    {5386×6 timetable}

Expand the ‘limits_mtx’ to include all the entries, and remember to add leading zeros to single-digit dates that do not have them. Then, process them as you wish.

.

[Pul]

@Star Strider Wow, thanks.

Basically, I should obtain the same trend you can see in the graph you created in the previous comment, but not considering years from 1 Jan 1998-1 Jan 1999; 1 Jan 1999-1Jan 2000...(so 365 days,as you can see in the table) but considering them from 15 Dec 1998 until 7 Dec 1999 (eg 357 days), 7 Dec 1999-30 Nov 2000 (eg 359 days) and so on.

The problem is that for these periods I should divide the Var5 for the missing year, so 22 Jan 2008-9 Jan 2011 divided 3 (because 3 years are missing) ,16 Nov 2011-9 Jan 2013 (divided 2) and 31 Jan 2015-25 Jan 2018 (divided 3).

The other problem is that now I can't use the variable 5 so, before doing the previous step, I should multiply x 3.5 the values of Var5.

I don't know if it's clear, maybe I wasn't good at explaining it.

LD = load('giulia_TT.mat');
giulia_TT = LD.giulia_TT;
GIULIA_YEARLY_N = retime(giulia_TT,'yearly', @(x)mean(x,'omitnan'));        % Yearly Average
N=GIULIA_YEARLY_N.Var5*3.5;
limits_mtx = datetime({'15 Dec 1998', '07 Dec 1999'; '07 Dec 1999', '30 Nov 2000';'30 Nov 2000','13 Nov 2001';'13 Nov 2001','30 Dec 2002';'30 Dec 2002','4 Jan 2004';'4 Jan 2004','22 Nov 2004';'22 Nov 2004','24 Nov 2005';'24 Nov 2005','15 Dec 2006';'15 Dec 2006','22 Jan 2008'; '22 Jan 2008','9 Jan 2011';'1 Jan 2010','16 Nov 2011';'6 Nov 2011-9 Jan 2013';'9 Jan 2013','5 Jan 2014';'5 Jan 2014','31 Jan 2015';'31 Jan 2015','25 Jan 2018';}, 'InputFormat','dd MMM yyyy')

Error using vertcat
Dimensions of arrays being concatenated are not consistent.

for k = 1:size(limits_mtx,1)
    IdxRng = (giulia_TT.Time>=limits_mtx(k,1)) & (giulia_TT.Time<limits_mtx(k,2));                                % Logical Mask Vectror
    GIULIA_SELECT{k,:} = giulia_TT(IdxRng,:);
end
GIULIA_SELECT

[Star Strider]

I demonstrated a way of selecting the relevant dates (you will need to add the other date ranges to ‘limits_mtx’) however I still do not know what you want to do.

The retime function will likely not work as you want it to with the extracted date segments, since it will calculate by calendar months or years, not the entire range in the ‘GIULIA_SELECT’ segments. However now that you have them isloated, you can calculate the aggregated values yourself. (You can still use retime to reliably calculate the daily values, just not the monthly or yearly ones, because the data are for incomplete months and years.)

If you want to multiply or divide specific variables by specific constants in specific ‘GIULIA_SELECT’ segments (although the reason is not obvious to me), you can of course do that directly in each of the ‘GIULIA_SELECT’ segments. Then, do the other calculations.

.

[Pul]

Yes, I added all the data in limits_mtx but I got the error you can see in the previous comment.

Okay,thank you.

[Star Strider]

The problem was:

% limits_mtx = datetime({'15 Dec 1998', '07 Dec 1999'; '07 Dec 1999', '30 Nov 2000';'30 Nov 2000','13 Nov 2001';'13 Nov 2001','30 Dec 2002';'30 Dec 2002','4 Jan 2004';'4 Jan 2004','22 Nov 2004';'22 Nov 2004','24 Nov 2005';'24 Nov 2005','15 Dec 2006';'15 Dec 2006','22 Jan 2008'; '22 Jan 2008','9 Jan 2011';'1 Jan 2010','16 Nov 2011';'6 Nov 2011-9 Jan 2013';'9 Jan 2013','5 Jan 2014';'5 Jan 2014','31 Jan 2015';'31 Jan 2015','25 Jan 2018';}, 'InputFormat','dd MMM yyyy')
%                                                                                                                                                                                                                                                                                                                                                       ↑ ← HERE

I corrected that typographical error and now the array works:

LD = load('giulia_TT[1][1].mat');
giulia_TT = LD.giulia_TT;
limits_mtx = datetime({'15 Dec 1998', '07 Dec 1999'; '07 Dec 1999', '30 Nov 2000';'30 Nov 2000','13 Nov 2001';'13 Nov 2001','30 Dec 2002';'30 Dec 2002','4 Jan 2004';'4 Jan 2004','22 Nov 2004';'22 Nov 2004','24 Nov 2005';'24 Nov 2005','15 Dec 2006';'15 Dec 2006','22 Jan 2008'; '22 Jan 2008','9 Jan 2011';'1 Jan 2010','16 Nov 2011';'6 Nov 2011','9 Jan 2013';'9 Jan 2013','5 Jan 2014';'5 Jan 2014','31 Jan 2015';'31 Jan 2015','25 Jan 2018';}, 'InputFormat','dd MMM yyyy')

limits_mtx = 15×2 datetime array
   15-Dec-1998   07-Dec-1999
   07-Dec-1999   30-Nov-2000
   30-Nov-2000   13-Nov-2001
   13-Nov-2001   30-Dec-2002
   30-Dec-2002   04-Jan-2004
   04-Jan-2004   22-Nov-2004
   22-Nov-2004   24-Nov-2005
   24-Nov-2005   15-Dec-2006
   15-Dec-2006   22-Jan-2008
   22-Jan-2008   09-Jan-2011
   01-Jan-2010   16-Nov-2011
   06-Nov-2011   09-Jan-2013
   09-Jan-2013   05-Jan-2014
   05-Jan-2014   31-Jan-2015
   31-Jan-2015   25-Jan-2018

The rest should work as well, now.

.

[Pul]

Thank you!

[Star Strider]

As always, my pleasure!

.