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

1 次查看（过去 30 天）

显示更早的评论

Thorben Green 2017-8-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/353390-dynamic-approach-to-create-a-vector-out-of-on-an-array-based-on-2-criteria-in-another-array

编辑： Stephen23 2017-8-20

采纳的回答： Stephen23

在 MATLAB Online 中打开

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Stephen23 2017-8-20

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/353390-dynamic-approach-to-create-a-vector-out-of-on-an-array-based-on-2-criteria-in-another-array#answer_278595

编辑：Stephen23 2017-8-20

在 MATLAB Online 中打开

>> 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