Modified Outerjoin function code

1 次查看（过去 30 天）
Jules
Jules 2016-3-28
回答： Teja Muppirala 2016-3-28
I have a Table(A) containing:
  1. Company ID (ID)
  2. Event A Date (DateA)
  3. other irrelevant variables.
I have a Table(B) with:
  1. Company ID (ID)
  2. Event B Date (DateB)
  3. other irrelevant variables.
A company can have several events (A or B).
I would like a code to add to Table(B) all the DateA that are 10 days before OR after Date B for a given company (red column in example).
There is the function outerjoin which I think can be used, but I'm not sure how to include the +/- 10 days factor.
Please find attach below an example.
Thank you for your help!
Example:

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

回答（1 个）

Teja Muppirala
Teja Muppirala 2016-3-28
This requires a bit more creativity, but this is how I would do it.
%% 1. Making the data (see my comment at the end)
A = table;
A.ID = [1;1;1;2;3];
A.DateA = datetime({'01-Jan-11';
'31-Jan-06';
'20-Jan-15';
'01-Jan-12';
'14-Jun-01'},'InputFormat','dd-MMM-yy','Local','en');
A.Irrelevant = [9;8;7;6;5];
B = table;
B.ID = [1;1;2;4];
B.DateB = datetime({'28-Dec-10';
'14-Jan-07';
'03-Jan-12';
'14-Mar-07'},'InputFormat','dd-MMM-yy','Local','en');
B.Irrelevant = [0; 1; 2; 3];
%% 2. Do the joining by checking day by day -10 to +10
B.order = (1:size(B,1))'; % Add this so we can unsort the dates back to the original order later
Acopy = A;
J = {};
for n = -10:10
Acopy.DateB = A.DateA + n; %Shift the date by "n"
J{end+1} = innerjoin(Acopy,B,...
'Keys',{'ID' 'DateB'},...
'RightVariables',{'ID' 'DateB' 'order'},...
'LeftVariables',{'DateA'});
end
J = cat(1,J{:}); % Accumulate the results
B_out = outerjoin(B,J,'mergekeys',true);
%% 3. Maybe not necessary, but we'll undo any sorting to put the dates in the original order
[~,ord] = sort(B_out.order);
B_out = B_out(ord,:);
B_out.order = [] % Remove the "order" column that we added
This gives out:
B_out =
ID DateB Irrelevant DateA
__ __________ __________ __________
1 2010/12/28 0 2011/01/01
1 2007/01/14 1 NaT
2 2012/01/03 2 2012/01/01
4 2007/03/14 3 NaT
By the way, instead of pasting an image of your data, it would generally be easier to help out if you included the raw numbers (or the code needed to generate them) as text so people can copy-paste.

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by