Sum up daily values to monthly values in a table of different company identifiers

14 views (last 30 days)
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
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)?

Sign in to comment.

Accepted Answer

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

Categories

Find more on Dates and Time in Help Center and File Exchange

Products


Release

R2018b

Community Treasure Hunt

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

Start Hunting!