CODEWITHSUNDEEP
arraysmatlabreplacevectorization
Andrew Makarskiy
Andrew Makarskiy

Reputation: 53

How to replace certain elements of table column with corresponding values of another array of different size in Matlab?

I have a categorical table variable alloptions.Exp (96399 rows) having such values as:

'Mar1 15'
'Mar1 15'
'Mar1 15'
'Mar1 15'
'Mar 15'
'Mar 15'
'Mar 15'
'Apr1 15'
'Apr2 15'
'Apr 15'
'Apr4 15'
'May1 15'
'May2 15'
'May 15'
'May4 15' ....

and I have a 119x2 Expiration_Table array having these values:

'Mar1 15'   '06-Mar-2015'
'Mar2 15'   '13-Mar-2015'
'Mar 15'    '20-Mar-2015'
'Mar5 15'   '31-Mar-2015'
'Apr1 15'   '02-Apr-2015'
'Apr2 15'   '10-Apr-2015'
'Apr 15'    '17-Apr-2015'
'Apr4 15'   '24-Apr-2015'
'May1 15'   '01-May-2015'
.....

What I need is to create another table variable alloptions.ExpDate having the corresponding values from Expiration_Table(:,2), that is from the second column.

Is there a nicer way to do it by not using loops?

Upvotes: 4

Views: 798

Answers (3)

Matthew Gunn
Matthew Gunn

Reputation: 4519

Yes! In database language, you're asking for a table join. if your data is in "table" variable types in MATLAB, you can join two tables on a common variable. If you want all the rows in table1 to make into your new table, you want a left outer join. In R2015b the syntax is:

new_table = outerjoin(my_table, expiration_table,'Type','left');

I believe earlier versions of MATLAB may have different syntax. Several comments:

  1. This code assumes my_table and expiration_table have a variable with the same name, and the variable with the common name is the variable you want to match on. If not, you can specify 'LeftKey' etc...
  2. Be aware that if expiration_table has multiple rows with the same values in question, you'll get a row in your new table FOR EACH POSSIBLE PAIRING!
  3. It also assumes the common variable in each table are comparable. If you have different strings/weird formatting, you may need to convert to something comparable like a datenum (eg. x = datenum(char(date_categorical)))

Example of outer join. Staring with two tables t and t2

 t.d                     t2.d        t2.n
 ___                     _____       ____
'jan'                    'feb'        2
'feb'                    'asdf'       0
'mar'                    'feb'        2.1
                         'mar'        3

outerjoin(t, t2,'Type','left') would return:

 d_t     d_t2      n 
_____    _____    ___
'feb'    'feb'      2
'feb'    'feb'    2.1
'jan'    ''       NaN
'mar'    'mar'      3

Upvotes: 0

Luis Mendo
Luis Mendo

Reputation: 112659

How about this?

[v, w] = ismember(alloptions, Expiration_Table(:,1));
result = Expiration_Table(w(v),2);

In your example, with

alloptions = {'Mar1 15'
              'Mar1 15'
              'Mar1 15'
              'Mar1 15'
              'Mar 15'
              'Mar 15'
              'Mar 15'
              'Apr1 15'
              'Apr2 15'
              'Apr 15'
              'Apr4 15'
              'May1 15'
              'May2 15'
              'May 15'
              'May4 15'};
Expiration_Table = {'Mar1 15'   '06-Mar-2015'
                    'Mar2 15'   '13-Mar-2015'
                    'Mar 15'    '20-Mar-2015'
                    'Mar5 15'   '31-Mar-2015'
                    'Apr1 15'   '02-Apr-2015'
                    'Apr2 15'   '10-Apr-2015'
                    'Apr 15'    '17-Apr-2015'
                    'Apr4 15'   '24-Apr-2015'
                    'May1 15'   '01-May-2015'}

the result is

result = 
    '06-Mar-2015'
    '06-Mar-2015'
    '06-Mar-2015'
    '06-Mar-2015'
    '20-Mar-2015'
    '20-Mar-2015'
    '20-Mar-2015'
    '02-Apr-2015'
    '10-Apr-2015'
    '17-Apr-2015'
    '24-Apr-2015'
    '01-May-2015'

This also works if alloptions is categorical, such as running alloptions = nominal(alloptions); after the above definition.

Note that

  • If a value in alloptions matches several rows in Expiration_Table the first one is picked.
  • Values of alloptions that don't have a matching row in Expiration_Table are ignored. If you prefer to set them to a predefined value such as an empty string, use the following modified code:

Modified code to set values not found to a predefined value:

[v, w] = ismember(alloptions, Expiration_Table(:,1));
result = repmat({''}, numel(alloptions), 1); %// initiallize to predefined value
result(v) = Expiration_Table(w(v),2);

which gives

result = 
    '06-Mar-2015'
    '06-Mar-2015'
    '06-Mar-2015'
    '06-Mar-2015'
    '20-Mar-2015'
    '20-Mar-2015'
    '20-Mar-2015'
    '02-Apr-2015'
    '10-Apr-2015'
    '17-Apr-2015'
    '24-Apr-2015'
    '01-May-2015'
    ''
    ''
    ''

Upvotes: 5

Mehrdad Nazmdar
Mehrdad Nazmdar

Reputation: 192

No. There is not any nicer way to do so as names may not be unique. Consider the following text for the Expiration_Table for example:

'Mar1 15'   '06-Mar-2015'
'Mar1 15'   '13-Mar-2015'
'Mar 15'    '20-Mar-2015'
...

What should happen if we have repeated elements in the first column with different corresponding values in the second column.

Or What should program do if it can not find any equal name in the first column?

Upvotes: 0

Related Questions