Monthly to annual totals

Hello,

I am hoping to convert monthly totals to annual totals using a loop. However, instead of using the calendar year (Jan-Dec) I want to use the water year which runs from October to September. I have a number of time series organised in a structure (468 rows, 1 column) starting from Oct 1960 and ending in Sept 1999.

I have attempted to sum up each 12 consecutive rows (1:12, 13:24 and so on) to get the annual totals but my code (below) is not doing the job (I am not sure what exactly it is doing) and I was hoping I could get some help fixing it. Many thanks!

for k=1:length(varnames)
    p=0;
    for i=1:39
        for months=1:12
            p=p+1;
            annual_flow.(varnames{k})(i,1)=flow_monthly.(varnames{k})(p,1);
        end
    end
end

Answer 1

[ag]

Hi Anna,

To convert monthly totals to annual totals based on a water year (October to September), you will need to sum every 12 consecutive months starting from October. The current code seems to be assigning monthly values to the annual flow, instead of summing them.

Below is the modified code snippet for the required logic:

for k = 1:length(varnames)
    p = 0;
    for i = 1:39
        % Initialize the sum for the current water year
        annual_sum = 0;
        
        % Sum the 12 months of the current water year
        for months = 1:12
            p = p + 1;
            annual_sum = annual_sum + flow_monthly.(varnames{k})(p, 1);
        end
        
        % Store the sum in the annual_flow structure
        annual_flow.(varnames{k})(i, 1) = annual_sum;
    end
end

Hope this helps!

Answer 2

[Star Strider]

This is straightforward with the accumarray function —

Y = 1960:1999;
M = 1:12;
Data = [[repmat(M(:), numel(Y), 1) repelem(Y(:), numel(M), 1)] randi(100, 480, 5)]      % Create Data

Data = 480×7

1 1960 8 18 78 82 19 2 1960 8 64 81 31 3 3 1960 4 98 56 46 99 4 1960 46 63 19 72 65 5 1960 21 56 28 1 14 6 1960 22 22 57 32 14 7 1960 13 39 46 65 35 8 1960 91 40 48 65 93 9 1960 22 24 10 44 52 10 1960 37 11 21 54 66

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

idx = reshape(ones(12,1) * (1:numel(Y)+1), [], 1);
idx = idx([4:end-12 end-2:end]);
disp(idx)

     1
     1
     1
     1
     1
     1
     1
     1
     1
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     3
     3
     3
     3
     3
     3
     3
     3
     3
     3
     3
     3
     4
     4
     4
     4
     4
     4
     4
     4
     4
     4
     4
     4
     5
     5
     5
     5
     5
     5
     5
     5
     5
     5
     5
     5
     6
     6
     6
     6
     6
     6
     6
     6
     6
     6
     6
     6
     7
     7
     7
     7
     7
     7
     7
     7
     7
     7
     7
     7
     8
     8
     8
     8
     8
     8
     8
     8
     8
     8
     8
     8
     9
     9
     9
     9
     9
     9
     9
     9
     9
     9
     9
     9
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
    11
    11
    11
    11
    11
    11
    11
    11
    11
    11
    11
    11
    12
    12
    12
    12
    12
    12
    12
    12
    12
    12
    12
    12
    13
    13
    13
    13
    13
    13
    13
    13
    13
    13
    13
    13
    14
    14
    14
    14
    14
    14
    14
    14
    14
    14
    14
    14
    15
    15
    15
    15
    15
    15
    15
    15
    15
    15
    15
    15
    16
    16
    16
    16
    16
    16
    16
    16
    16
    16
    16
    16
    17
    17
    17
    17
    17
    17
    17
    17
    17
    17
    17
    17
    18
    18
    18
    18
    18
    18
    18
    18
    18
    18
    18
    18
    19
    19
    19
    19
    19
    19
    19
    19
    19
    19
    19
    19
    20
    20
    20
    20
    20
    20
    20
    20
    20
    20
    20
    20
    21
    21
    21
    21
    21
    21
    21
    21
    21
    21
    21
    21
    22
    22
    22
    22
    22
    22
    22
    22
    22
    22
    22
    22
    23
    23
    23
    23
    23
    23
    23
    23
    23
    23
    23
    23
    24
    24
    24
    24
    24
    24
    24
    24
    24
    24
    24
    24
    25
    25
    25
    25
    25
    25
    25
    25
    25
    25
    25
    25
    26
    26
    26
    26
    26
    26
    26
    26
    26
    26
    26
    26
    27
    27
    27
    27
    27
    27
    27
    27
    27
    27
    27
    27
    28
    28
    28
    28
    28
    28
    28
    28
    28
    28
    28
    28
    29
    29
    29
    29
    29
    29
    29
    29
    29
    29
    29
    29
    30
    30
    30
    30
    30
    30
    30
    30
    30
    30
    30
    30
    31
    31
    31
    31
    31
    31
    31
    31
    31
    31
    31
    31
    32
    32
    32
    32
    32
    32
    32
    32
    32
    32
    32
    32
    33
    33
    33
    33
    33
    33
    33
    33
    33
    33
    33
    33
    34
    34
    34
    34
    34
    34
    34
    34
    34
    34
    34
    34
    35
    35
    35
    35
    35
    35
    35
    35
    35
    35
    35
    35
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    37
    37
    37
    37
    37
    37
    37
    37
    37
    37
    37
    37
    38
    38
    38
    38
    38
    38
    38
    38
    38
    38
    38
    38
    39
    39
    39
    39
    39
    39
    39
    39
    39
    39
    39
    39
    40
    40
    40
    40
    40
    40
    40
    40
    40
    40
    40
    40
    41
    41
    41

DataYr = accumarray(idx, (1:numel(idx)), [], @(x) {Data(x,:)});                         % Demonstrate Function Results (Delete Later If Desired)

disp(DataYr{1})

           1        1960           8          18          78          82          19
           2        1960           8          64          81          31           3
           3        1960           4          98          56          46          99
           4        1960          46          63          19          72          65
           5        1960          21          56          28           1          14
           6        1960          22          22          57          32          14
           7        1960          13          39          46          65          35
           8        1960          91          40          48          65          93
           9        1960          22          24          10          44          52

disp(DataYr{2})

          10        1960          37          11          21          54          66
          11        1960          46          98          64          98          45
          12        1960          56          48          56          88          74
           1        1961           1          59          52          45          67
           2        1961          11          86          76          37          18
           3        1961           4          76          77          77          49
           4        1961          77          77          15           7          85
           5        1961          37          64          77           7          96
           6        1961          57          19          92          67           9
           7        1961          58          47          94          29          66
           8        1961          46          52          34          81          14
           9        1961          79          83          24          72          27

disp(DataYr{end-1})

          10        1998          57          89          33          25          81
          11        1998          20          95          39          67          25
          12        1998           1          21          84          73          43
           1        1999          75          64          43          83          59
           2        1999          66          64          69          13          71
           3        1999          35          89          87          32           2
           4        1999          52          81          63          63          91
           5        1999          89          76          66          96          23
           6        1999          80          62          19          12          68
           7        1999          60          40          23          11          97
           8        1999          12          30          97          67          41
           9        1999          46          37          54          65          98

DataYr{end}

ans = 3×7

10 1999 51 43 7 79 69 11 1999 82 32 86 85 17 12 1999 91 36 5 98 13

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

DataYrSum = accumarray(idx, (1:numel(idx)), [], @(x) {sum(Data(x,3:end))});             % Calculate Annual Sum

Result = [[Y(:); Y(end)+1] cell2mat(DataYrSum)];
disp(Result)

        1960         235         424         423         438         394
        1961         509         720         682         662         616
        1962         654         595         696         629         452
        1963         714         747         513         549         548
        1964         684         334         593         617         652
        1965         794         432         550         508         551
        1966         601         635         518         566         720
        1967         531         398         517         745         657
        1968         650         586         627         595         499
        1969         562         609         398         695         478
        1970         455         684         471         699         621
        1971         645         791         437         534         559
        1972         500         591         649         655         562
        1973         519         580         586         754         589
        1974         671         383         752         648         596
        1975         738         527         519         443         652
        1976         572         633         577         540         526
        1977         790         411         636         473         593
        1978         725         582         699         603         763
        1979         552         624         665         594         794
        1980         660         660         593         603         550
        1981         616         604         388         504         646
        1982         660         645         643         456         649
        1983         730         538         743         546         640
        1984         583         496         523         465         788
        1985         453         565         615         619         682
        1986         629         728         576         596         635
        1987         559         505         544         639         823
        1988         481         601         544         590         686
        1989         703         553         681         602         812
        1990         439         460         544         677         633
        1991         787         595         488         621         559
        1992         508         668         685         709         384
        1993         666         603         569         483         758
        1994         634         752         499         639         695
        1995         624         677         674         676         549
        1996         580         684         405         754         486
        1997         524         455         478         710         533
        1998         760         586         363         699         510
        1999         593         748         677         607         699
        2000         224         111          98         262          99

The accumarray function existed in the 2012 versions of MATLAB. With it, no explicit loops are necessary.

The actual code (after importing the file, or creating the data as I did here) begins with creating the ‘idx’ vector.

.