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

cell arrayrepelem

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?

Best Answer

  • 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!