Table referencing using cell

Hi all,

I have two tables:

Tickers

Tickers nbr

________ ___

{'A2M' } 1

{'AAN' } 2

{'AAP' } 3

{'ABB' } 4

{'ABC' } 5

{'ABS' } 6

{'ADP' } 7

{'AEO' } 8

{'AFG1'} 9

{'AGL' } 10

Data

Date Code Open High Low Close Volume

___________ _______ _____ _____ _____ _____ ________

31-Mar-2015 {'A2M'} 0.555 0.595 0.53 0.565 4.816294

01-Apr-2015 {'A2M'} 0.575 0.58 0.555 0.565 4.37666

02-Apr-2015 {'A2M'} 0.56 0.565 0.535 0.555 2.77964

07-Apr-2015 {'A2M'} 0.545 0.55 0.54 0.545 0.392179

08-Apr-2015 {'A2M'} 0.545 0.545 0.53 0.54 0.668446

09-Apr-2015 {'A2M'} 0.54 0.54 0.532 0.535 0.706846

10-Apr-2015 {'A2M'} 0.53 0.535 0.53 0.535 0.175104

13-Apr-2015 {'A2M'} 0.535 0.54 0.53 0.54 0.565808

14-Apr-2015 {'A2M'} 0.54 0.54 0.535 0.54 0.371393

15-Apr-2015 {'A2M'} 0.54 0.55 0.54 0.54 0.925749

I need to create a new column in data called 'CodeRef' that brings the nbr column from the Tickers table into the corresponding code in the data table.

I've tried find, ismember - but for some reason I am missing something.

Answer 1

[Voss]

% some (simplified) tables like yours:
data = table({'a';'b';'c';'c';'a';'b';'b'}, ...
    [3;4;6;8;3;5;4],'VariableNames',{'Code','Open'})

data = 7×2 table
    Code     Open
    _____    ____

    {'a'}     3  
    {'b'}     4  
    {'c'}     6  
    {'c'}     8  
    {'a'}     3  
    {'b'}     5  
    {'b'}     4  

Tickers = table({'a';'b';'c'},[1;2;3],'VariableNames',{'Tickers','nbr'})

Tickers = 3×2 table
    Tickers    nbr
    _______    ___

     {'a'}      1 
     {'b'}      2 
     {'c'}      3 

[ism,idx] = ismember(data.Code,Tickers.Tickers);
data.CodeRef = NaN(size(data,1),1); % in case any data.Code is missing from Tickers.Tickers, its CodeRef will be NaN
data.CodeRef(ism) = Tickers.nbr(idx(ism))

data = 7×3 table
    Code     Open    CodeRef
    _____    ____    _______

    {'a'}     3         1   
    {'b'}     4         2   
    {'c'}     6         3   
    {'c'}     8         3   
    {'a'}     3         1   
    {'b'}     5         2   
    {'b'}     4         2   

Answer 2

[Stephen23]

The simple MATLAB approach is to use the JOIN() family:

data = table({'a';'b';'c';'c';'a';'b';'b'}, [3;4;6;8;3;5;4],'VariableNames',{'Code','Open'})

data = 7×2 table
    Code     Open
    _____    ____

    {'a'}     3  
    {'b'}     4  
    {'c'}     6  
    {'c'}     8  
    {'a'}     3  
    {'b'}     5  
    {'b'}     4  

Tickers = table({'a';'b';'c'},[1;2;3],'VariableNames',{'Tickers','nbr'})

Tickers = 3×2 table
    Tickers    nbr
    _______    ___

     {'a'}      1 
     {'b'}      2 
     {'c'}      3 

out = join(data,renamevars(Tickers,'Tickers','Code'))

out = 7×3 table
    Code     Open    nbr
    _____    ____    ___

    {'a'}     3       1 
    {'b'}     4       2 
    {'c'}     6       3 
    {'c'}     8       3 
    {'a'}     3       1 
    {'b'}     5       2 
    {'b'}     4       2