How do I repeat a number based on a criteria?

Hi all,

I got a table with monthly data (600000:12). In column 12, I have one value per year. (a year goes from Time 61 to 72). I want to repeat this value to all rows before that row, in that specific year. For example (in the figure): there is one element on row 6 and I want to repeat this in 1 to 5 (Time = 61 to 66). I want to repeat the value for BM on row 18 to all rows from 7 to 18. However, for some firm IDs, there is a value on row 5 (Time = 65) and I want to repeat this for row 1 to 6 (Time = 61 to 66). And I want to do this for the entire table, for every period (Time = 66, 78, 90,...).

Any help?

Best

[Eric Sofen]

If this data were in a timetable, you could use retime, specify the new times to be the same as the original times, and use the 'next' nearest-neighbor method.

Alternatively, you could extract the table data into an array and use interp1 similarly.

Both of these approaches assume that you want to fill all the contiguous preceeding NaNs with the value (e.g. fill 1-5 with the value at row 6). If you're completely missing data from any year, you'll end up filling 2 years worth of data with one value.

Answer 1

[Adam Danz]

Try out F = fillmissing(A,method) where method is set to "previous".

To apply that to your table, it will look something like,

T.BM = fillmissing(T.BM, 'previous');

You may also need to specify the EndValues property.