sum of consecutive months value in an array

1 view (last 30 days)
Hello: I have an array where the first, second and third columns show year, month and values. I need to sum the values of consecutive months. The matrix X is as below
1988 6 223.12
1994 7 1033.0
1994 8 464.67
1995 9 251.33
1997 6 121.41
1997 8 624.58
1999 9 221.60
2001 7 782.04
The desired output:
1988 223.12
1994 1497.67
1995 251.33
1997 121.41
1997 624.58
1999 221.60
2001 782.04
I have tried like this:
dt = datetime([X(:,1:2), repmat([0 0 0 0],length(X),1)]);
Monthgroup = cumsum([1; months(diff(datetime([X(:,1:2), repmat([0 0 0 0],length(X),1)]))) == 1]);
where same year with a different month, example, 1994 in this case, will suppose to show a common index but I am getting some error. Based on this common index, could I use accumarray function? Any help will be appreciated. Thanks,
  2 Comments
jonas
jonas on 11 Sep 2018
I guess it needs to work for any number of consecutive months?
Poulomi Ganguli
Poulomi Ganguli on 11 Sep 2018
yes but for the same year not for different years.

Sign in to comment.

Accepted Answer

Andrei Bobrov
Andrei Bobrov on 11 Sep 2018
Edited: Andrei Bobrov on 11 Sep 2018
a = [1988 6 223.12
1994 7 1033.0
1994 8 464.67
1995 9 251.33
1997 6 121.41
1997 8 624.58
1999 9 221.60
2001 7 782.04 ];
b = datetime(a(:,1),a(:,2),1);
lo = [true; b(1:end-1) + calmonths(1) ~= b(2:end)];
out = [a(lo,1), accumarray(cumsum(lo),a(:,3))];
or
b2 = a(:,1:2)*[12;1];
lo2 = [true;diff(b2) ~= 1];
out2 = [a(lo2,1), accumarray(cumsum(lo2),a(:,3))];
  6 Comments
Rik
Rik on 11 Sep 2018
If you want to merge them, use either of the methods Andrei posted, if not, use the adapted code I posted in my comment. Which is the correct version for you depends on your application.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!