Sum up daily values to monthly values in a table of different company identifiers
14 views (last 30 days)
Show older comments
Christian Sturm
on 10 Aug 2019
Commented: Christian Sturm
on 11 Aug 2019
Hello all,
I have a table of unique company identifiers (column 1, 'PERMCO') and their corresponding day (column 2,'DATE') and the values for the specific days I want to sum for each month (column 3,'VALUE').
It looks like this and has the size 18828200x3 with around 2000 different company identifiers (PERMCO):
PERMCO DATE VALUE
2584 20030214 4
2584 20030215 2
2584 20030226 2
2584 20030227 1
2584 20030228 1
2584 20030303 2
2584 20030305 5
2584 20030330 4
etc.
I want to sum up VALUE for each month, keep the last day in the month and store it all in a new table/matrix so that it looks like this:
2584 20030228 10
2584 20030330 11
etc.
I couldn't find a solution that works for this purpose. Thanks a lot for your help!
1 Comment
Guillaume
on 10 Aug 2019
There are many easy ways to do this. Is the date stored as a number or is it properly stored as a datetime (which makes it so much easier)?
Accepted Answer
Guillaume
on 10 Aug 2019
It's trivial to do any number of ways. But first, your date must be stored as a proper datetime. If it's stored as you show:
yourtable.DATE = datetime(yourtable.DATE, 'ConvertFrom', 'yyyymmdd');
Once that's done, the easiest way:
result = groupsummary(yourtable, {'PERMCO', 'DATE'}, {'none', 'month'}, 'sum')
It can also be done with findgroups and splitapply:
[monthid, actualmonth] = discretize(yourtable.DATE, 'month');
[group, PERMCO, monthnum] = findgroups(yourtable.PERMCO, monthid);
sum_VALUE = splitapply(@sum, yourtable.VALUE, group);
MONTH = actualmonth(monthnum);
result = table(PERMCO, MONTH, sum_VALUE);
It could also be done using rowfun or varfun.
More Answers (0)
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!