Dynamic approach to create a vector out of on an array based on 2 criteria in another array

Hi guys,

Again, I am struggling on a rather simple problem I guess. But I just cannot find an efficient solution. Thanks for your help in advance:

Given those two data matrices

Test=[734871,0.00821917808219178;734871,0.00821917808219178;734871,0.00821917808219178;734871,0.00821917808219178;734872,0.0465753424657534;734872,0.0465753424657534;734872,0.0700000000000000;734874,0.0410958904109589;734874,0.0410958904109589;734874,0.0700000000000000]

and

InterestRates=  [0,1.00000000000000,2.00000000000000,3.00000000000000,3.99999999999999,5,6.00000000000001;734871,0.00149221053152049,0.00149252127808058,0.00149283196575199,0.00149314259454174,0.00149345316445687,0.00149376367550442;734872,0.00179283647186252,0.00179296334795502,0.00179309021293526,0.00179321706681056,0.00179334390958821,0.00179347074127556;734873,0.00175878491156134,0.00175893200621115,0.00175907907647424,0.00175922612236268,0.00175937314388858,0.00175952014106402;734874,0.00171879426874853,0.00171897937923391,0.00171916444062407,0.00171934945293958,0.00171953441620097,0.00171971933042880;734877,0.00101765625895641,0.00101860328333341,0.00101954970373857,0.00102049552039589,0.00102144073352936,0.00102238534336297;734878,-0.00164751459768531,-0.00164356727085485,-0.00163962281819655,-0.00163568123862644,-0.00163174253106054,-0.00162780669441486;734879,0.00169155025995175,0.00169167062539390,0.00169179102411180,0.00169191145609191,0.00169203192132066,0.00169215241978451;734880,0.00169748599884064,0.00169759864693397,0.00169771133156046,0.00169782405270544,0.00169793681035427,0.00169804960449228;734881,0.00169572651238145,0.00169583699273224,0.00169594750453311,0.00169605804777128,0.00169616862243397,0.00169627922850843;734887,0.00165212953618841,0.00165225465518583,0.00165237979610366,0.00165250495893135,0.00165263014365836,0.00165275535027416;734888,0.00161844806796075,0.00161857928974519,0.00161871053673167,0.00161884180890785,0.00161897310626137,0.00161910442877988]

who look like this basically

I want to "find the values out of matrix InterestRates that correspond to the matrix Test".

In detail, the first column of Test are dates and the second column are days (divided by 365). matrix InterestRates is a matrix in which the first row are dates, the first column are days (not divided by 365 though) and the values inside are interest rates.

So InterestRates(2,3) corresponds to to the interest (0.0015) for the first date (734871) and the second day.

Now what I want MATLAB to do is:

Go thru matrix Test and add a column filled with the corresponding interest rate from matrix InterestRate that meet the date criteria and day criteria.

For the first row of Test this would be the InterestRate(2,4) (Note that 0.0082*365=3)

I tried to use for loops but the execution takes around 45 min (my dataset includes 3 Mio observations), so I guess the efficiency level is super low. I know it will probably work with some logical indexing / ismember type of function but I just don't know how to to it (or rather, I just don't know how to make it dynamic and apply this for different dates and different days.

Here is a buggy version of my for loop:

% for dd=1:length(Test(:,1))
%     for ddr=1:length(InterestRates(:,1))
%         for mm=1:length(InterestRates(1,:))
%             if Test(dd,1)==InterestRates(ddr,1) && Data(dd,2)==InterestRates(1,mm)
%         
%         
%         Test(dd,3)=rate(ddr+1,mm+1);
%         
%              end
%         end
%     end 
% end

Thanks for your support. I really appreciate it.

Answer 1

[Stephen23]

>> Tnew = round(bsxfun(@times,Test,[1,365]));
>> [~,idx] = ismember(Tnew(:,1), round(InterestRates(:,1)));
>> [~,idy] = ismember(Tnew(:,2), round(InterestRates(1,:)).');
>> idz = idy>0; % exclude days not in InteresteRates
>> ndx = sub2ind(size(InterestRates),idx(idz),idy(idz));
>> InterestRates(ndx)
ans =
       0.00149283196575199
       0.00149283196575199
       0.00149283196575199
       0.00149283196575199

[Thorben Green]

Thanks for your quick answer!

I tried your code (modified to my actual data), so I replaced "Test" with "Data", "InterestRates" with "rates", and the corresponding Rows/Columns:

Tnew = round(bsxfun(@times,Data,[1,365]));
[~,idx] = ismember(Tnew(:,2), round(rate(:,1)));
[~,idy] = ismember(Tnew(:,5), round(rate(1,:)).');
idz = idy>0; % exclude days not in InteresteRates
ndx = sub2ind(size(rate),idx(idz),idy(idz));
rate(ndx)

but this yields

Error using bsxfun Non-singleton dimensions of the two input arrays must match each other.

[Thorben Green]

I am wondering about that since the inputs I delivered do not have the same dimensions

[Stephen23]

Apparently you actually use different columns that what you described. If Data has atleast five columns, how do you expect to multiply it by something that has only two columns?:

 bsxfun(@times,Data,[1,365])

In any case the bsxfun call is not really required, so lets get rid of it. You could simply do this:

Tnew = Data(:,[1,5]); % pick the date and day columns
Tnew(:,2) = Tnew(:,2)*365;

Which matches the columns that you are apparently using, and then use the rest of the code that I gave you (without changing my original indexing).

[Thorben Green]

the size is

>> size(Data)

ans =

       3166586          20

Applying this

Tnew = Data;
Tnew(:,2) = Tnew(:,2)*365;
[~,idx] = ismember(Tnew(:,2), round(rate(:,1)));
[~,idy] = ismember(Tnew(:,5), round(rate(1,:)).');
idz = idy>0; % exclude days not in InteresteRates
ndx = sub2ind(size(rate),idx(idz),idy(idz));
rate(ndx)

yields

Error using sub2ind (line 43) Out of range subscript.

Sorry If I was unclear at some point. I really appreciate your help a lot. Thanks!

[Thorben Green]

ah sorry, I did not see the "don't change indices". Let me try

[Thorben Green]

Tnew = Data(:,[1,5]); % pick the date and day columns
  Tnew(:,2) = Tnew(:,2)*365;
  [~,idx] = ismember(Tnew(:,1), round(rate(:,1)));
  [~,idy] = ismember(Tnew(:,2), round(rate(1,:)).');
  idz = idy>0; % exclude days not in InteresteRates
  ndx = sub2ind(size(rate),idx(idz),idy(idz));
  rate(ndx)

gives the same error.

This is the rate size ans =

        1071          93

[Stephen23]

Tell me what these display:

nnz(idx==0)
nnz(idy==0)
numel(idx)
numel(idy)

You can probably fix it with

idz = idx>0 & idy>0

[Thorben Green]

>> nnz(idx==0) nnz(idy==0) numel(idx) numel(idy)

ans =

     3166586

ans =

      760364

ans =

     3166586

ans =

     3166586

When using

idz = idx>0 & idy>0

and changing the output to

newrates=rate(ndx);

my result is an empty matrix (newrates =[]).

[Stephen23]

And what about

idz = idx>0 & idy>0;
nnz(idz)

??

[Thorben Green]

I changed the input a little bit (FilteredData instead of Data), but that should not alter the results. New sizes are

size(FilteredData)

ans =

      704120          20

>> size(rate)

ans =

        1072          94

Executing (where all the indices are finally right, sorry again)

Tnew = FilteredData(:,[2,5]); % pick the date and day columns
Tnew(:,2) = Tnew(:,2)*365;
[~,idx] = ismember(Tnew(:,1), round(rate(:,1)));
[~,idy] = ismember(Tnew(:,2), round(rate(1,:)).');
idz = idx>0 & idy>0;
nnz(idz);
ndx = sub2ind(size(rate),idx(idz),idy(idz));
newrates=rate(ndx);

gives me a interest rate vector of size

>> size(newrates)

ans =

      630436           1

I can see that ndx has the same length as the results.

Thinking about it, I guessed that maybe there are not interest rates for every day but that is not true. Same for the second criteria "days". So there must be a rate for all dates in the Data (FilteredData) matrix and for all possible maturities (days).

      isequal(unique(FilteredData(:,2)),rate(2:end,1))

ans =

    logical

     1

     isequal(unique(FilteredData(:,5))'*365,rate(1,2:end))

ans =

    logical

     1

[Jan]

@Stephen: I find the ">>" at the start of the code lines confusing. But I was surprised, that this does not impede to run your code by copy&paste in the command window.

[Thorben Green]

I removed the "round" and now it is working perfectly.

THANKS again!

[Stephen23]

Actually there should be round at this step:

Tnew(:,2) = round(Tnew(:,2)*365);

Read these to know why round will make your code more robust:

https://www.mathworks.com/matlabcentral/answers/57444-faq-why-is-0-3-0-2-0-1-not-equal-to-zero

https://www.mathworks.com/matlabcentral/answers/41536-why-is-2-24-10-22-4-not-equal-to-0

https://www.mathworks.com/matlabcentral/answers/316889-why-two-equal-numbers-are-not-equal

etc, etc, you also will find plenty of explanations on the interweb.