# How can I increase this code's efficiency?

2 views (last 30 days)
Jonathan Macuroy on 9 Apr 2019
Edited: Pavel Radko on 15 Aug 2020
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 on 8 May 2020
Thanks a lot.

Alex Mcaulley on 9 Apr 2019
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)
col_labels = raw(1,:);
row_labels = raw(:,1);
try
distance = raw{contains(row_labels,y),contains(col_labels,x)};
catch
distance = -1;
end
end
##### 3 CommentsShow 1 older commentHide 1 older comment
shashwat patnaik on 10 Jun 2019
doesnt work for me
shashwat patnaik on 10 Jun 2019
giving me -1 everytime

### More Answers (3)

Jiaqi Liu on 2 Sep 2019
function distance = get_distance(x,y)
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

Ahmed Mamdouh on 9 Jun 2020
function result=get_distance(ct1,ct2)
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

Pavel Radko on 15 Aug 2020
Edited: Pavel Radko on 15 Aug 2020
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