How can I increase this code's efficiency?

Hello. I'm currently studying Matlab on an online platform. However, I'm stuck at this specific exercise:

"The attached Distances.xlsx file contains a spreadsheet with the pairwise distances in miles of the top 100 US cities by population. A preview of the spreadsheet is shown below. The first row and first column contain the city names using the following format: city name comma space capitalized state abbreviation, e.g., Nashville, TN. Note that the very first cell of the spresheet, A1, is blank.

Write a function called get_distance that accepts two character vector inputs representing the names of two cities. The function returns the distance between them as an output argument called distance. For example, the call get_distance('Seattle, WA','Miami, FL') should return 3723. If one or both of the specified cities are not in the file, the function returns -1."

Preview of the first five cities of Distances.xlsx

My code is as follows:

function distance = get_distance(x,y)
[num txt raw] = xlsread('Distances.xlsx');
[~, col_labels] = xlsread('Distances.xlsx',1,'B1:LY1');
find_col = find(strcmp([col_labels(:)], x));
[~, row_labels] = xlsread('Distances.xlsx',1,'A2:A337');
find_row = find(strcmp([row_labels(:)], y));

if isempty(find_col) || isempty(find_row)
    distance = -1;
else
    distance = cell2mat(raw(find_row + 1,find_col + 1));
end
    

I've tried the code above with the example stated above, and with multiple other examples using Matlab and it all checks out. However, when I input the code into the website, it states that the time runs out, and that I should check for infinite loops or that I should improve my code's efficiency. The online course in itself covered only preallocation very briefly so I currently have very limited idea on the subject. Can anyone recommend a way to improve my code's efficiency? Any response will be appreciated. Thank you!

[Aritra Das]

Thanks a lot.

Answer 1

[Alex Mcaulley]

There are many options, but the main goal is to reduce the number of calls to xlsread (it is really slow). One option:

function distance = get_distance(x,y)
    [~,~,raw] = xlsread('Distances.xlsx');
    col_labels = raw(1,:);
    row_labels = raw(:,1);
    try
        distance = raw{contains(row_labels,y),contains(col_labels,x)};
    catch
        distance = -1;
    end
end

[Jonathan Macuroy]

Thank you very much, it worked! And it's also my first encounter with 'try' and 'catch' so thanks for that also. :)

[shashwat patnaik]

doesnt work for me

[shashwat patnaik]

giving me -1 everytime

Answer 2

[Jiaqi Liu]

function distance = get_distance(x,y)
[~,~,raws] = xlsread('Distances.xlsx');
col_name = raws(1,:);
row_name = raws(:,1);
col_num = find(strcmp(col_name,y));
row_num = find(strcmp(row_name,x));
if (col_num & row_num) == 1
    distance = cell2mat(raws(row_num,col_num));
else
    distance = -1;
end

Answer 3

[Ahmed Mamdouh]

function result=get_distance(ct1,ct2)
 [~,~,x] = xlsread('Distances.xlsx');
sizr=size(x,1);sizc=size(x,2);
for i=1:sizr
    if(ct1==string(x{1,i}))
        nr=i;
        break;
    elseif (ct1~=string(x{1,i}))
        nr=-1;
    end
end
for t=1:sizc
    if (ct2==string(x{t,1}))
        nc=t;
        break;
    elseif (ct1~=string(x{1,t}))
        nc=-1;
    end
end

if (nr==-1 || nc==-1)
    result=-1;
end
if (nr~=-1 && nc~=-1)
    result=x{nc,nr};
end
end

Answer 4

[Pavel Radko]

Well, Matlab suggested to use readtable function instead of xlsread when I started to solve this task. So I learned how to use it here.

"ReadRowNames" is set to "true" because the default is "false". With "ReadRowNames" set to "true" I can search through row names.

"PreserveVariableNames" is set to "true" to prevent changes of column(variables) names and keep originals. Because MATLAB will change them by default ( we get "Abilene_TX" instead of "Abilene, TX").

T(A,B) - outputs table on the crossing row named "A" and column(variable name) named "B". This will output a table, but we need only value of that crossing - so use "table2array" functuon.

ismember(A,T.Properties.VariableNames) - checks "A" in variable names(column names) of T and return "true" if found. The same made with "B". If any or both wont be found in variable names then "distance" is set to -1 by "else" statement.

And yes, All Tests Passed

function distance = get_distance (A,B)
T = readtable("Distances.xlsx", "ReadRowNames", true, "PreserveVariableNames", true);
if ismember(A,T.Properties.VariableNames) && ismember(B,T.Properties.VariableNames)
distance = table2array(T(A,B));
else 
    distance = -1
end
end