2

I have two tables, say, price (5 rows, 1 column) and pricedate (6 rows, 1 column), they don't have anything in common. I want to obtain full outer join, so that my new table ABC has 30 rows, basically, every row in pricedate has all prices.

How to do that? Can I use full outer join or something else?

JenZ
  • 129
  • 1
  • 1
  • 9
  • Wouldn't that be a cross product rather than an outer join? If there was nothing in common, would an outer join not just be 11 rows, and have `NULL` for the other tables columns? – Dan Jun 25 '15 at 14:57
  • Yes. Exactly! It is something like "“Cartisian Product” of two tables as our DBA people say. But how to do that in Matlab? – JenZ Jun 25 '15 at 15:59

2 Answers2

0

My answer covers two cases:

  1. P (prices) and PD (pricedates) are tables
  2. P (prices) and PD (pricedates) are arrays

Here is the code:

% generate some sample data
price      = [1;2;3;4;5];
pricedate1 = [11;12;13;14;15;16];
pricedate2 = pricedate1+10;
pricedate3 = pricedate2+10;

% create sample table
P  = table(price);
PD = table(pricedate1,pricedate2,pricedate3);

% this is the code if P and PD are tables
TMP = repmat(P{:,1},1,size(PD,1))';
T = [repmat(PD,size(P,1),1),table(TMP(:),'VariableNames',{'price'})]

% create sample arrays
P  = price;
PD = [pricedate1,pricedate2,pricedate3];

% this is the code if P and PD are arrays
TMP = repmat(P,1,size(PD,1))'
T = [repmat(PD,size(P,1),1), TMP(:)]

It is possible to write it in a single line to to eliminate the TMP-variable.

For data of type table:

T = [repmat(PD,size(P,1),1),table(subsref(repmat(P{:,1},1,size(PD,1))',struct('type','()','subs',{{':'}})),'VariableNames',{'price'})]

For data of type array:

T = [repmat(PD,size(P,1),1),subsref(repmat(P,1,size(PD,1))',struct('type','()','subs',{{':'}}))];

I admit, that the single line looks quite cryptic.

Matt
  • 12,848
  • 2
  • 31
  • 53
  • Yes! This is the answer!. – JenZ Jun 25 '15 at 17:50
  • But got an error when trying the single line: T = [repmat(PD,size(P,1),1),table(subsref(repmat(P{:,1},1,size(PD,1))',struct('type','()','subs',{{':'}})),'VariableNames',{'price'})] Cell contents reference from a non-cell array object. – JenZ Jun 25 '15 at 18:00
  • @JenZ: It works in my test-script. Are you trying the single line for the `table`-type where `P` and `PD` are a table? Just putting it to the bottom of my test-script is not correct. It needs to be inserted in the middle before `P` and `PD` are overwritten as arrays. – Matt Jun 25 '15 at 18:13
  • I see. I will try it later. But for now, my computer got re-imaged and I lost Matlab. I have to fix a Excel issue on a network drive for now. – JenZ Jun 26 '15 at 12:35
0

Since it looks like you are trying to do a Cartesian Product, i suggest using the allcomb function from the FileExchange, http://www.mathworks.com/matlabcentral/fileexchange/10064-allcomb

since your data is also in (matlab) tables (hopefully), you can do:

function Tallcomb = allcomb_2tables(T1, T2)
    %USe allcomb to create the cartesian product of the indexes
    idxAB = allcomb(1:height(T1),1:height(T2));

    % Now horzcat the multi-broadcasted tables:
    Tallcomb = horzcat(T1(idxAB(:,1),:), T2(idxAB(:,2),:) );

end
alexandre iolov
  • 582
  • 3
  • 11