How to get score of each combination of pairs in each tool

Hi,

I have score of a different combination of recipe pairs in each tool, now I give available recipes and want to get the score of each possible combination of recipes in each tool,

Score_data:

Tool  Pair  Score
Tool1  AB  40
Tool1  AS  37
Tool1  AT  11
Tool1  SA  23
Tool1  ST  37
Tool1  TA  39
Tool1  TB  17
Tool1  BA  39
Tool2  BA  23
Tool2  BS  40
Tool2  BT  39
Tool2  SA  36
Tool2  ST  33
Tool2  AS  10
Tool3  AB  11
Tool3  AS  21
Tool3  AT  26
Tool3  BA  38
Tool3  BS  12
Tool3  BT  39
Tool3  ST  21
Tool3  TA  0
Tool4  AB  23
Tool4  BT  40
Tool4  SA  0
Tool4  TB  11
Tool4  AT  40
Tool4  BA  0
Tool4  BS  10

AB means: A-->B pair,AS means A-->S pair etc.

Now I want to get possible score of given recipes in each tool,

New_recipes:
A
B
S
T
D
N

My desired output: If any pair's score not available then it is NA in the desired output.

            AB AS AT AD AN BA BS BT BD BN SA SB ST SD SN TA TB TD TN
   Tool1    40 37 11 NA NA 39 NA NA NA NA 23 NA 37 NA NA 39 17 NA NA
   Tool2    NA 10 NA NA NA 23 40 39 NA NA NA NA 33 NA NA NA 11 NA NA
   Tool3    11 21 26 NA NA 38 12 39 NA NA 36 NA 21 NA NA 0  NA NA NA
   Tool4    23 NA 40 NA NA 0  10 40 NA NA 0  NA NA NA NA NA NA NA NA

Many thnaks in advance,

Answer 1

[Guillaume]

Very easily done if your data is in a table, using unstack:

toolscore = readtable('scoredata.txt');  %after saving your example in a text file
unstack(toolscore, 'Score', 'Pair')

[Mekala balaji]

Sir,

I want to find the score for new recipes:

New_recipes:
A
B
S
T
D
N

The different pairs of above recipes are:

AB AS AT AD AN BA BS BT BD BN SA SB ST SD SN TA TB TD TN

Now, from score data, want to find the score for each pair of new recipes in each tool, my desired output is for new recipe pairs,

[Guillaume]

The code in my answer produces the following output:

ans =

    4×11 table

       Tool      AB     AS     AT     BA    BS     BT     SA     ST     TA     TB 
      _______    ___    ___    ___    __    ___    ___    ___    ___    ___    ___ 
      'Tool1'     40     37     11    39    NaN    NaN     23     37     39     17
      'Tool2'    NaN     10    NaN    23     40     39     36     33    NaN    NaN
      'Tool3'     11     21     26    38     12     39    NaN     21      0    NaN
      'Tool4'     23    NaN     40     0     10     40      0    NaN    NaN     11

Isn't it what you wanted?

[Mekala balaji]

Not Sir,
my inputs are:
1. score data
2. current recipes
here, the new recipes are:

    New_recipes:
    A
    B
    S
    T
    D
    N

the possible combinations out of above New-recipes are:

AB AS AT AD AN BA BS BT BD BN SA SB ST SD SN TA TB TS TD TN DA DB DS DT DN NA NB NS NT ND

then get the score of each of the above pair in each tool (from score-data).

First, need to get the recipe pairs from New-recipes, then get the scores in each tool,

my desired-output: attached

[Guillaume]

So the problem is that the output is missing the pairs that never appear in the input? One way to solve that:

toolscore = readtable('scoredata.txt');  %after saving your example in a text file
pairscore = unstack(toolscore, 'Score', 'Pair');
%create list of all pairs, using ndgrid
[half1, half2] = ndgrid('ABSTDN');
allpairs = cellstr([half2(~eye(size(half2))), half1(~eye(size(half1)))]);  %the eye is to remove pairs with identical values such as AA, BB, ...
%create table for all pairs, full of nan:
fullpairscore = [pairscore(:, 1), array2table(nan(height(pairscore), numel(allpairs)), 'VariableNames', allpairs)];
%fill full table with known pairs:
fullpairscore(:, pairscore.Properties.VariableNames) = pairscore

[Mekala balaji]

I followed your code and tried, but some error,

my code:

close all
clear all
clc
[~,~,toolscore]=xlsread('scoreInput.xlsx');
% toolscore = readtable('scoredata.txt');  
pairscore = unstack(toolscore, 'Score', 'Pair');
[half1, half2] = ndgrid('ABSTDN');
allpairs = cellstr([half2(~eye(size(half2))), half1(~eye(size(half1)))]);  %the eye is to remove pairs with identical values such as AA, BB, ...
fullpairscore = [pairscore(:, 1), array2table(nan(height(pairscore), numel(allpairs)), 'VariableNames', allpairs)];
fullpairscore(:, pairscore.Properties.VariableNames) = pairscore;

Error:
Undefined function 'unstack' for input arguments of type 'cell'.

Error in crossToolScore (line 6)
pairscore = unstack(toolscore, 'Score', 'Pair');

[Guillaume]

Well, yes, you commented out the first line, which reads the file into a table. As I wrote in the first line of my answer:

"Very easily done if your data is in a table using unstack"

Get rid of the outdated xlsread and use readtable instead, as per my answer.

[Mekala balaji]

Sir,

but my scoreInput file will be either in xlsx or csv format,

do I need to preprocess the data such as read data first (from xlsx or csv), then form a table