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

convert dateMATLABmonthsum up values each month

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 18828200×3 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!

Best Answer

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.

Related Solutions

MATLAB: Daily average of several years data

If you can turn your data into a table, use the groupsummary function. Assuming you want to average days from each year, you could do this:

dailyAvg = groupsummary(dataTbl,["monthVar","dayVar"],"mean","sst")

If you actually convert your date into a datetime variable, you could also do this:

groupsummary(dataTbl,"date","dayofyear","mean","sst")

Here's the full code using the snippet of data you provided above.

data = [1	1	1982	13.4691935954243
2	1	1982	13.4287935963273
3	1	1982	13.4183935965598
4	1	1982	13.9135935854912
5	1	1982	13.9483935847134
6	1	1982	13.9511935846508
7	1	1982	13.9471935847402
8	1	1982	14.0023935835064
9	1	1982	13.9711935842037
10	1	1982	13.8475935869664];
dataTbl = table(data);
dataTbl = splitvars(dataTbl,"data","NewVariableNames",["day","month","year","sst"])
% option one - group by month then day.
dailyAvg1 = groupsummary(dataTbl,["month","day"],"mean","sst")
% option two - use the groupbin "dayofyear" on datetime variable "date"
dataTbl.date = datetime(fliplr(data(:,1:3)));
dailyAvg2 = groupsummary(dataTbl,"date","dayofyear","mean","sst")

MATLAB: Accumarray with tall arrays

You can use findgroups and splitapply to do this, like so:

% Make some example data
tt = tall(table(datetime(2017, 03, randi([1 31], 100, 1)), ...
    rand(100, 1), 'VariableNames', {'Date', 'Value'}));
% group by date
g = findgroups(tt.Date);
% Call splitapply to find the sum of values on each unique date
sumAndDate = splitapply(@(v, d) {sum(v), d(1)}, tt.Value, tt.Date, g)

Note that because findgroups for tall arrays doesn't support the second output argument, I've concocted a slightly unusual function for the splitapply stage which returns both the sum and the datetime corresponding to that group.

Best Answer

Related Solutions

MATLAB: Daily average of several years data

MATLAB: Accumarray with tall arrays

Related Question