Efficiently copying values from one table two another in which unique values are columns

6 views (last 30 days)
Hi,
I have a file attached that contains two tables A and B. I would like to put the values from A.volatility into the right column in B. The right column would be the one that has the same number as A.days.
The tricky thing is that for some dates, there is a value for days missing. For instance, there is no combination of '1996-01-04' and 14 days in A. Such missing values should lead to NaN in B.
Right now, I am using two for loops. One that iterates through the Rows of A, one that iterates through the columns of B. That makes the code very slow as my real dataset is much much bigger and I think those two for loops cannot be the most efficient way.
load AB.mat
% Get vector of unique days in B
uniquedays = unique(B.days);
for i=1:size(A,1)
% Filter B for only rows that have same date as ith row in A
Row = B(B.date == A.date(i),:);
for j=2:size(A,1)
% Find position of fitting value that has same days
idx = (Row.days == uniquedays(j-1));
switch sum(idx)
case 0
B{i,j} = nan;
case 1
B{i,j} = Row.volatility(idx);
otherwise
error('blabla');
end
end
end

Accepted Answer

Mohammad Sami
Mohammad Sami on 30 Mar 2020
Edited: Mohammad Sami on 30 Mar 2020
C = unstack(B,'volatility','days');
The function unstack will do exactly what you are trying to do.
The first column would be all unique date, then there would be columns for every unique days in the table B, containing the value of volatility and NaN if there was no such combination.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!