How to get cumulative sum in yearly buckets

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

[KSSV]

Do you have dates/ years in the data?

[Wendy Cameron]

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

Answer 1

[Akira Agata]

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')];

[Andrei Bobrov]

+1.

[Wendy Cameron]

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

Answer 2

[Ameer Hamza]

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);

[Wendy Cameron]

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.

Answer 3

[Andrei Bobrov]

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);

[Wendy Cameron]

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.

Answer 4

[Razvan Carbunescu]

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

[Wendy Cameron]

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