left outerjoin without changing initial order in the output

조회 수: 40(최근 30일)
i want to make outerjoin in such way to have output C keeping the same order as the key variable, precisely my key variable is dates when i apply outerjoin i see that the output C is sorted ascending way because the dates are detected as numbers example
dates=[05012000 02032000 31012000 15092007]';
ret1=[100 55 66 23]';
tab1=table(dates,ret1);
clear dates
%========
dates=[05012000 08012000 31122000]';
ret2=[2 8 7]';
tab2=table(dates,ret2);
%====
[C] = outerjoin(tab1,tab2,'Type','left');
%result
dates_tab1 dates_tab2
*2032000* 55 NaN NaN
5012000 100 5012000 2
15092007 23 NaN NaN
*31012000* 66 NaN NaN
you can see that in the output we have 31012000 in the bottom wich is not convenient cause i want to have the same sorting dates us in my initial vector dates such that
dates_tab1 dates_tab2
5012000 100 5012000 2
*31012000* 66 NaN NaN
*2032000* 55 NaN NaN
15092007 23 NaN NaN

채택된 답변

Guillaume
Guillaume 2017년 2월 9일
The problem is your definition of the order of numbers is not the mathematical standard one. Matlab does not let you redefine the order of numbers 3101 is always greater than 1509, so the proper solution is not to use numbers to store your date but a proper date type such as datetime
%in R2016b:
tab1.dates = datetime(compose('%08d', tab1.dates), 'InputFormat', 'ddMMyyyy');
tab2.dates = datetime(compose('%08d', tab2.dates), 'InputFormat', 'ddMMyyyy');
C = outerjoin(tab1, tab2, 'Type', 'left')
Or
%any version with outerjoin
todatetime = @(d) datetime(mod(d, 1e4), floor(mod(d, 1e6)/1e4), floor(d/1e6));
tab1.dates = todatetime(tab1.dates)
tab2.dates = todatetime(tab2.dates)
C = outerjoin(tab1, tab2, 'Type', 'left')
  댓글 수: 2
wgourlay
wgourlay 2018년 7월 30일
I'm also trying to use outerjoin without changing the initial order of my data, however my keys are alphanumeric strings. If I modify the original example as follows:
keys1={'b1' 'b2' 'c1' 'a1'}';
keys2={'d1' 'b1' 'a1'}';
tab1=table(keys1, 'VariableNames', {'Key1'});
tab2=table(keys2, 'VariableNames', {'Key1'});
[C, ia, ib] = outerjoin(tab1,tab2)
What I get is the following:
C =
Key1_tab1 Key1_tab2
_________ _________
'a1' 'a1'
'b1' 'b1'
'b2' ''
'c1' ''
'' 'd1'
What I would like to see instead is the following (note these rows occur in the same order that they do in the original data sets):
C =
Key1_tab1 Key1_tab2
_________ _________
'' 'd1'
'b1' 'b1'
'b2' ''
'c1' ''
'a1' 'a1'
I've managed to create a complex block of code that reorders things again based on the ia and ib indexes that are returned from the outerjoin command, but it requires a for loop, two more rounds of sorting, and a few calls to find. I'm hoping there is a simpler solution, something like the 'stable' option used for the unique function, i.e. outerjoin(tab1,tab2,'stable'). Is anyone aware of such a thing?

댓글을 달려면 로그인하십시오.

추가 답변(0개)

태그

제품

Community Treasure Hunt

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

Start Hunting!

Translated by