Modified Outerjoin function code

1 visualizzazione (ultimi 30 giorni)
Jules
Jules il 28 Mar 2016
Risposto: Teja Muppirala il 28 Mar 2016
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:

Risposte (1)

Teja Muppirala
Teja Muppirala il 28 Mar 2016
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.

Categorie

Scopri di più su Dates and Time in Help Center e File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by