How to do Table Look-up?
1 view (last 30 days)
Show older comments
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. :)
0 Comments
Accepted Answer
Guillaume
on 21 Jan 2020
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);
2 Comments
More Answers (1)
dpb
on 21 Jan 2020
Edited: dpb
on 21 Jan 2020
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.
0 Comments
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!