Joining a table using a column of list

Hi all, I have two tables as shown:

Table A (size mxn):

VarA VarB .....

------ -------

3 [1, 5, 7]

167 [2, 6, 9, 11]

......

Table B (size axb):

VarC VarD .....

------ -------

1 "X"

2 "B"

5 "E"

6 "F"

7 "G"

9 "I"

11 "R"

...

I would like to make use of Table B to join a new column to Table A like below:

New Table A:

VarA VarB VarE .....

------ ------- -------

3 [1, 5, 7] ["X", "E", "G"]

167 [2, 6, 9, 11] ["B", "F", "I", "R"]

......

Any help is much appreciated.

[Adam Danz]

Where's the few lines of code that produce the example tables?

[Walter Roberson]

Does order matter? Alternately, will VarB always be in increasing order? Will VarC always be in increasing order?

[Douglas Chiang]

For VarB, you mean the order inside the array like [1,5,7]? Yes

For VarC, it is actually corresponds to the numbers in VarB and VarD shows their "Names", and the value increase down the column.

Answer 1

[Mohammad Sami]

Try this

if true
  tabA.VarE = cellfun(@(X)tabB.VarD(ismember(tabB.VarC,X)),tabA.VarB,'UniformOutput',false);
end

[Douglas Chiang]

It returns an error:

Error using cellfun

Input #2 expected to be a cell array, was categorical instead.

[Adam Danz]

That's exactly why I asked for the code that produces the example tables.

[Douglas Chiang]

I import these tables from csv files that I received. They come in that way :(

[Adam Danz]

That doesn't prevent you from writing some code that reproduces the tables or uploading a mat-file containing the tables. This is called a minimal working example (MWE) and it greatly reduces the amount of time it takes to answer a question and it eliminates the need for volunteers to make guesses about the inputs.

Sometimes people don't want to invest time into creating a MWE but that often results in volunteers wasting their time in developing a solution that doesn't work. Questions with MWEs are usually answered more quickly and with less discussion than questions without MWEs.

[Mohammad Sami]

You will need to convert the categorical array to cellstr, then convert it double.

tabA.VarB = cellstr(tabA.VarB);
tabA.VarB = regexprep(tabA.VarB,'[\[\]]','');
tabA.VarB = cellfun(@(x)str2double(strsplit(x,',')),tabA.VarB,'UniformOutput',false);
tabA.VarE = cellfun(@(X)tabB.VarD(ismember(tabB.VarC,X)),tabA.VarB,'UniformOutput',false);

[Douglas Chiang]

That do the job, thanks a lot.