How to create an increasing sequence that resets when a cell array of strings changes?

I am finding it difficult to articulate what I want to accomplish in the question title, so let me use some examples first to show what I am starting with. I have a table consisting of two year columns (Year and End) and an ID column of strings. Here is how I generated my table:

t = table();
t.Year = randi([2011 2015],10,1);
t.ID = cellstr(char([65:74])');
t.End = randi([2011 2015],10,1);

So I am starting with one row per ID. Basically, I want to repeat each ID for each year in between its start (Year) and End. Let's consider the following example:

Year | ID | End

2012 | 'B' | 2014

If ID 'B' starts in 2012 and doesn't end until 2014, that means I want to repeat the 'B' row two times so that there is an entry for 2013 and 2014. Here is the solution that I have created to repeat the appropriate rows the appropriate number of times:

t.Reps = max(t.End - t.Year + 1,0);
a = repelem(t,t.Reps,1);

Note that when Year is greater than End, those rows will be removed (which is what I want).

Now here is the issue that I would like to solve. The IDs are repeated the appropriate number of times, but I would like to update the Year column to reflect that each time a row is repeated, it represents the next year.

In my example above, 'B' would currently be repeated three times and shown as:

Year | ID | End

2012 | 'B' | 2014

2012 | 'B' | 2014

2012 | 'B' | 2014

So, for the example of 'B' I would like it to look like:

Year | ID | End

2012 | 'B' | 2014

2013 | 'B' | 2014

2014 | 'B' | 2014

How can I do this for all of the IDs?

Answer 1

[Geoff Hayes]

Bryan - once you have calculated t.Reps, you could do the following

% current row index
rowIdx = 1;
while true
    if t.Reps(rowIdx) == 0
        % delete a row (no need to increment rowIdx)
        t(rowIdx,:) = [];
    else
        if t.Reps(rowIdx) > 1
            % insert rows
            rowToRepeat = t(rowIdx,:);
            reps = t.Reps(rowIdx);
            year = t.Year(rowIdx);
            for m = 1:reps-1
                % change the year for the new row
                rowToRepeat.Year(1) = m + year;
                % insert the new row via concatenation
                t = [t(1:rowIdx,:) ;  rowToRepeat ; t(rowIdx+1:end,:)];
                rowIdx = rowIdx + 1;
            end
        end
        % go to the next row
        rowIdx = rowIdx + 1;
    end

      % if our new rowIdx is greater than the number of rows in the table,
      % then we are don!
      if rowIdx > length(t.Reps)
          break;
      end
  end

I don't have much experience with tables so there may be more efficient ways of doing the above. {Perhaps someone else will provide an answer too!

[Bryan Frost]

Thanks for the response! This answer works well for my question. I especially like the use of concatenation to insert a new row.

As an FYI, I ended up finding another solution that uses logical indexing. It goes like this...

t = repelem(t,t.Reps,1);
[~,last] = unique(t.ID,'legacy'); %last row for each ID
adjusted = last - t.Reps(last) + 1; %last original row before repelem

clear last

rep2idx = adjusted(t.Reps(adjusted) > 1) + 1;
rep3idx = adjusted(t.Reps(adjusted) > 2) + 2;
rep4idx = adjusted(t.Reps(adjusted) > 3) + 3;
rep5idx = adjusted(t.Reps(adjusted) > 4) + 4;

clear adjusted

t.Year(rep2idx) = t.Year(rep2idx) + 1;
t.Year(rep3idx) = t.Year(rep3idx) + 2;
t.Year(rep4idx) = t.Year(rep4idx) + 3;
t.Year(rep5idx) = t.Year(rep5idx) + 4;

clear rep2idx rep3idx rep4idx rep5idx