How to get cumulative sum in yearly buckets

6 views (last 30 days)
I have several years of data and temperature in one table. I want to calculate a cumulative temperature sum which I've done simply as A.CumulativeTemp= cumsum(A.Temp);
My question is, how can get a cumulative temp for each year, i.e. so it gives a cumulative temperature for the entire 2016, then starts again at zero in the same column and does the same for 2017 and so on?
Kind regards, Wendy
  2 Comments
Wendy Cameron
Wendy Cameron on 17 May 2018
Mostly my dates are in 1/1/2006 type format but I have made a year only column so there is just 2017 or 2016 that can be referred to. Wendy

Sign in to comment.

Accepted Answer

Akira Agata
Akira Agata on 17 May 2018
How about the following ?
% Read the data file and delete the answer column
opts = detectImportOptions('Reset Accum temp.xls');
T = readtable('Reset Accum temp.xls',opts);
T.AccumulatedTemperatureOfEachYear = []; % Remove the answer
% Apply cumsum function for each Year
T2 = varfun(@cumsum,T,'GroupingVariables','Year');
T = [T T2(:,'cumsum_Temperature')];
  2 Comments
Wendy Cameron
Wendy Cameron on 17 May 2018
Thanks that works. I am now wondering how it could be done accumulating the temperatures from 1st July to 30th June perhaps. Every answer seems to generate another question!
Kind regards, Wendy

Sign in to comment.

More Answers (3)

Ameer Hamza
Ameer Hamza on 17 May 2018
One way to do this is follow
[group, uniqueYears] = findgroups(A.years)
yearSum = splitapply(@sum, A.Temp, group)
yearSummary = [uniqueYears, group];
Or you can also use accumarray as follow:
yearSum = accumarray(A.years, A.Temp);
  1 Comment
Wendy Cameron
Wendy Cameron on 17 May 2018
I've attached a very cut down file to try to explain my question. I want to get a column like the third column in the attached. i.e. the accumulation temperature is shown for each day of the year but resets at a certain date and starts accumulating from zero again from that date (not necessarily the end of the year).
I hope this explains the question a bit better.

Sign in to comment.


Andrei Bobrov
Andrei Bobrov on 17 May 2018
data = readtable('Reset Accum_temp.xls','range','A2:B32');
[~,~,c] = unique(data.Year);
N = accumarray(c,data.Temperature);
T = data.Temperature;
lo = [0;diff(data.Year)]~=0;
T(lo) = T(lo) - N(1:end-1);
data.AccumulatedTemperatureOfEachYear = cumsum(T);
  1 Comment
Wendy Cameron
Wendy Cameron on 17 May 2018
Yes, well this certainly works. I could never have written it myself but I think I can see what you've done. Slowly I am learning. Thank you.

Sign in to comment.


Razvan Carbunescu
Razvan Carbunescu on 17 May 2018
If using R2018a and wanting the final sum each year only can use groupsummary to get this more directly:
>> T = readtable('Reset Accum_temp.xls','range','A2:B32');
>> GT = groupsummary(T,'Year','sum','Temperature')
GT =
3×3 table
Year GroupCount sum_Temperature
____ __________ _______________
2015 10 267
2016 10 166
2017 10 208
You can use the date directly also with groupsummary
>> T.Date = datetime(T.Year,1,1); % reconstruct full Date
>> GT = groupsummary(T,'Date','year','sum','Temperature')
GT =
3×3 table
year_Date GroupCount sum_Temperature
_________ __________ _______________
2015 10 267
2016 10 166
2017 10 208
  1 Comment
Wendy Cameron
Wendy Cameron on 18 May 2018
Thank you - this seems a very elegant solution and for my work I can see many applications. Unfortunately I don't have the 2018 version but hope to one day having seen this!
Regards, Wendy

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!