Empty index from loop

Hi everyone,

I currently have an n x 6 numeric matrix called meta. where site = meta(:,1), datenum = meta(:,2), variables = meta(3:10). There are numerous datenums that are identical as a consequence of looking at multiple sites over the same time period. I would like to sum each variable individually in accordance to the datenum. Below is the code that I have so far. I'm attempting to make a loop (see code below), however it keeps returning an empty index when I run the date_index=find(dates==i) line. I know that the dates will definitely match i (the unique dates), so confused as to why it's empty.

Equally, perhaps my loop within the loop needs tweaking? I'm very new to Matlab, so any help would be much appreciated. Also happy to provide further explanation if needed. Thank you in advance!

% Plot 1: Computing summary of sites by datenum
meta=xlsread('Incidents_and_HeadcountsUpdated.xlsx',4,'A2:L2123');
dates=(meta(:,2))
unique_dates=unique(dates)
%Sum of each individual variable per unique datenum
for i = 1:length(unique_dates),
    date_index = find(dates==i);
    for j = meta(3:10), %Columns for each incident type (rip, wind, tcur and tcut, total inc) and headcount (beach,water,total)
        data_sums(i,1)=i;
        data_sums(i,j-1) = sum(meta(index,j));
    end
        
end

[Guillaume]

Which version of matlab are you using (there's a field on the right of the question for you to enter that)?

In any recent version of matlab, what you're asking is trivially easy to do (just one line) provided you can have the date as datetime. Ideally, the data would be imported as a table (with readtable). As it looks like your excel table has headers (since you're currently skipping the first row), you could even use that header to name the table variables, making the whole code clearer.

[Ffion Mitchell]

Hi there Guillaume,

Sorry, I missed that. I'm using R2018b. One line sounds amazing! I can easily import the data as a table, and convert into datetime if necessary.

Answer 1

[Guillaume]

Importing your data as a table (which should automatically import dates as datetime):

meta = readtable('Incidents_and_HeadcountsUpdated.xlsx', 'Sheet', 4, 'Range', 'A:L'); %I'm assuming row 1 is header. Range may not even be needed
meta = groupsummary(meta, 2, 'sum')  %instead of 2 you could use the actual name of the 2nd variable.

Instead of groupsummary, you could also use varfun:

meta = varfun(@sum, meta, 'GroupingVariables', 2) %instead of 2 you could use the actual name of the 2nd variable.

Both of the above assume that the dates you want to group are actually identical. If they differ by a small fraction and you want to bin them you can use groupsummary for that as well, but better would be to convert to timetable and retime it.

[Ffion Mitchell]

Worked perfectly! Thank you so much for you help.

Answer 2

[Bob Thompson]

find(dates==i)

This is not looking for the actual dates, because i is just an index integer, i.e. on the first loop you are looking for dates==1, and the second loop dates==2. Based on what I think you're trying to do you need to index the unique_dates, rather than just include the index.

find(dates==unique_dates(i))

[Ffion Mitchell]

Thank you so much! This definitely helps me move forward, although now it is telling me that the Matrix Dimensions must agree. I have set them both into horizontal arrays (see below) whereby i = 1x712 double and m = 1x21222 double. The lengths were always going to be different naturally. Should I transpose them into vertical arrays? Perhaps that is the issue. Thank you for your help!

for i = 1:length(unique_dates),
    m = 1:length(dates),
    date_index = find(dates(m)==unique_dates(i));

[Guillaume]

For reference, if you were going to use a loop iterating over the unique dates, this is how it should be done. Note that I'm not doing the iteration over the columns because that loop is really pointless

unique_dates = unique(meta(:, 2));
data_sum = [unique_dates, zeros(numel(unique_dates), size(meta, 2)-2);  %Always preallocate arrays. Don't grow them in the loop!
for row = 1:numel(unique_dates)
    data_sum(row, 2:end) = sum(meta(meta(:, 2) == unique_date, 3:end));
end

But a faster way would have been to use accumarray:

[unique_dates, ~, destrow] = unique(meta(:, 2));
destination = [repmat(destrow, size(meta, 2)-2, 1), repelem(3:size(meta, 2)-2, numel(unique_dates))'];
data_sum = [unique_dates, accumarray(destrow, reshape(meta(:, 3:end), [], 1]));