MATLAB: How do you change an n-by-2 array, where the 2 columns are datenum in 1 and associated value in 2, into a table where the rows are the years and the columns are months with the table populated by the associated values

arraydatenumtable

How do you change an n-by-2 array, where the 2 columns are datenum in 1 and associated value in 2, into a table where the rows are the years and the columns are months with the table populated by the associated values?

Best Answer

It may be possible to do this with the new timetable type introduced in R2016b, but I'm not familiar enough with it to know how.
Using regular table it's not particularly difficult, put the year and month in two different columns, then simply unstack the table:
m = [datenum(repelem(2000:2006, 12), repmat(1:12, 1, 7), 1); 1:7*12].' %example matrix

md = datetime(m(:, 1), 'ConvertFrom', 'datenum'); %convert datenum to datetime for convenience

t = table(year(md), month(md), m(:, 2), 'VariableNames', {'Year', 'Month', 'Value'});
newt = unstack(t, 'Value', 'Month', 'NewDataVariableNames', month(datetime(0,1:12,1), 'shortname')); %the NewDataVariableNames works as long as all months are present
Alternatively, using accumaray:
m = [datenum(repelem(2000:2006, 12), repmat(1:12, 1, 7), 1); 1:7*12].' %example matrix
md = datetime(m(:, 1), 'ConvertFrom', 'datenum'); %convert datenum to datetime for convenience
[years, ~, yearrows] = unique(year(md));
[months, ~, monthrows] = unique(month(md));
newm = accumarray([yearrows monthrows], m(:, 2))
Related Question