How to do Table Look-up?

Hello,

I am trying to reformat a table from 3 columns (Col-1 - Time, Col-2 - VariableName and Col-3 - Value/Data) to more of a map table/Matrix (Time as Rows, VariableNames as Columns) and for any missing data at a times step (for any variable) I want to have "-999" as a default value. I am not sure how do I do this as looping through takes long time to complete populating the matrix which is not a feasible solution.

I have attached images showing 2 tables (imported table with original data and the second is what I want to create from the original table). Please help me in processing this table.

Thanks in advance. :)

Answer 1

[Guillaume]

Matlab has you covered with unstack:

desired_table = unstack(Processed_Data, 'Value', 'Name')

You'll get NaN for missing values, which is a much better missing indicator than -999. But if you really really want -999 (why?), you can do this afterward:

desired_table = fillmissing(desired_table, 'Constant', -999);

[Logesh Velusamy]

Hi Guillaume,

Awesome, thanks a lot for your help.

These one line of code is simple and best.

[dpb]

Agreed. Was unaware of unstack.

Answer 2

[dpb]

If all observations are extant but just missing some timesteps, then

vars=unique(data(:,1),'stable');    % unique variable names in data
N=numel(vars);                      % how many variables there are
t=unique(data(:,2));                % and the time steps found
ttbl=array2timetable(reshape(data(:,3),N,[]).', ...
                    'RowTimes',seconds(t), ...
                    'VariableNames',vars);

Then use retime to fill in missing time intervals.

If there are missing observations at a given time, then the above won't work; you'll have to either fix that first or revert to a "find and destroy" method to account for those.

Instead of images, attach data files (smallish examples) for folks to use; nothing can be done with the images other than look.