Excel I/o matlab

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

[Walter Roberson]

We are already discussing this at https://www.mathworks.com/matlabcentral/answers/445405-assignment-question-based-on-excel-file-i-o#comment_684611 where you have already been given a fair bit of assistance, along with the one major hint that you need to solve the problem in the code you had posted.

[Priyamvada Shankar]

Yeah...but you didn't tell me the correct code...I have already tried it too many times..so what bad to send me the right code

[Walter Roberson]

You have a vector of values that are the result of strcmp. What happens if you any() the vector ? What does that mean?

[Sanket khullr]

function distance = get_distance(a,b)
[~,text,raw] = xlsread('Distances.xlsx');


    for i=2:size(text,1)
      if strcmp(text{i,1}, a)
          break
      end
    end
    if strcmp(text{i,1}, a)
        x=i;
    else
        distance=-1;
    end
       for j=2:size(text,2)
      if strcmp(text{1,j}, b)
          break
      end
    end
    if strcmp(text{1,j}, b)
        distance= raw{x,j};
    else
        distance=-1;
    end

[Sanket khullr]

This is the correct matlab code

[Walter Roberson]

No. If a is not found but b is found, then you do not set the value of x to anything, but you still try to use raw{x,j}

[Yueqi Li]

Could you please tell me how can I correct this problem?

[Walter Roberson]

There are many approaches.

One of the approaches is to initialize distance to -1. If it does not get changed, then it will still be that when you return. Then, as soon as you find the appropriate match on the two parts, set distance appropriately, and return from the function.

[sujhan vikram]

function distance = get_distance(x,y)
[~,~,raw] = xlsread('Distances.xlsx');
[a b]=size(raw);q=0;w=0;
for i = 2:a
    if contains(raw{i,1},x)==1
        q=i;
    end
end
for j = 2:b
    if contains(raw{1,j},y)==1
        w=j;
    end
end
if q>0 && w>0
    distance = raw{q,w};
else 
    distance = -1;
end

[sujhan vikram]

Whats the mistake ??

[Walter Roberson]

What error are you encountering?

[per isakson]

"The attached Distances.xlsx" Where is it?

[sujhan vikram]

[sujhan vikram]

there seems to be 'north las vegas' and 'las vegas' in the .xls file and north las vegas comes first from top so my code chooses it.

corrected it by running the for loop from bottom ie.(a to 2).

[madhan ravi]

Where is the data file?

[Jaimin Motavar]

you should use strcmp function instead of contains.

function distance = get_distance(x,y)
[~,~,raw] = xlsread('Distances.xlsx');
[a b]=size(raw);q=0;w=0;
for i = 2:a
    if strcmp(raw{i,1},x)==1
        q=i;
    end
end
for j = 2:b
    if strcmp(raw{1,j},y)==1
        w=j;
    end
end
if q>1 && w>1
    distance = raw{q,w};
else 
    distance = -1;
end

[Deep Raj Bhujel]

Thank you Jaimin.

[nicola Braham]

why do you need the if q>1 && w>1 line? isn't that a given?

[nicola Braham]

is it just because the first row and first column contain city names not distances?

[Walter Roberson]

Neither q nor w are given (not passed in by the user, not prompted for). Both of them are calculated.

Notice this particular part of the assignment:

"If one or both of the specified cities are not in the file, the function returns -1."

Therefore we cannot assume that the cities passed in will definitely be in the file, so we cannot assume that the positions q or w will have been set to valid values. Testing q and w is checking to be sure that the cities were actually found.

Answer 1

[Sravani Kurma]

code with explaination:

function distance=get_distance(alpha,beta)
 [~,~,d]=xlsread('Distances.xlsx');% to read values 
 [m,n]=size(d);% to find the size of d interms of matrix dimensions mxn 
 
 s=strcmp(alpha,d(1,1:n));% comparing each string of one row of sheet with given name of the city say alpha
p=(sum(s==1));% aftr comparing output will be logical 1 or 0, so, we get max one logical 1 value and all other zeros ,so sum will be 1+n(0)=1
if p==0 %if p==0,no matching city,,,
   distance= -1;
   return
end

t=strcmp(beta,d(1,1:m));
q=(sum(t==1));
if q==0
distance= -1;
return
end
distance=d{find(s==1),find(t==1)};%find will given the index value of t==1 and s==1

[Walter Roberson]

p=(sum(s==1));% aftr comparing output will be logical 1 or 0, so, we get max one logical 1 value and all other zeros ,so sum will be 1+n(0)=1

That is not "perfect". You could simply ask any(s) instead of doing that summation.

[Sravani Kurma]

Yeah,I agree with your suggestion also...but may I know any problem in using my logic of addition? Because i tested the code and it's working fine...

[Rik]

There is nothing wrong with the code, but it is not optimal or intuitive, and therefore not perfect. Compare the two blocks below. For positive integer values of b they will have the same result, but one is clearly better.

a+b

for n=1:b
    a=a+1;
end

[Sravani Kurma]

Ok..tq

Answer 2

[Irfan Hussain]

function distance = get_distance(city_1 , city_2)
[num,txt,raw] = xlsread('Distances.xlsx');
p = 0; q = 0;
name_1 = strcmpi(city_1,raw(:,1));
p = find(name_1 == 1);
name_2 = strcmpi(city_2,raw(1,:));
q = find(name_2 ==1);

if p > 1 && q > 1
    distance = raw{p, q};
else
    distance = -1;

end

[Walter Roberson]

You do not use name_1 or name_2 except in the immediately following line, so it would probably make more sense to use

p = find(strcmpi(city_1, raw(:,1)) == 1);

Now, strcmpi() returns logical 0 (false) and logical 1 (true), and find() searches for non-zero values, so this in turn could be done more compactly as

p = find(strcmpi(city_1, raw(:,1)))

Question for you:

if p > 1 && q > 1

Under what circumstances could p be exactly 1, or less than 1 ?

[Irfan Hussain]

yes you are right. basically the output give correct that i will past it in hurry. now i am improving the problem, i face non-existance city issue

[Walter Roberson]

https://www.mathworks.com/help/matlab/ref/find.html

• If X contains no nonzero elements or is empty, then find returns an empty array.

Notice this does not say that "if X containst no nonzero elements or is empty then find returns 0"

Also, in your logic, suppose that p or q were 0 so you set distance to -1. But then you go ahead and try to do

distance = raw{p,q};

anyhow, even though you already know that one of the cities was not found.

[Sai kalyan Kolapally]

function distance= get_distance(a,b)
[~,~,all]=xlsread('Distances.xlsx');
p=0;q=0;
city1= strcmpi(a,all(1,:));
p=find(city1==1);
city2= strcmpi(b,all(:,1));
q= find(city2==1);
if isempty(p)==1 || isempty(q)==1
    distance=-1;
else
    distance= all{p,q};
end
end

[Rohit Singh Chauhan]

function distance = get_distance(city1,city2)
[~, ~, everything] = xlsread('Distances.xlsx');
[row col] = size(everything);
c1 = 0;
c2 = 0;
for ii = [1:row]
    c1_row = everything{ii,1};
    if strcmp(c1_row,city1)
        c1 = ii
    end    
end    
for jj = [1:col]
    c2_col = everything{1,jj};
    if strcmp(c2_col,city2)
        c2 = jj
    end    
end
if (c1==0 || c2==0)
    distance = -1
else    
    distance = everything{c1,c2}
end
end

[Lucero]

thanks

Answer 3

[SAMARTH MAHESHKUMAR GEMLAWALA]

This is how i done it using for loop, make it simple to understand.

function distance = get_distance(a,b)
[num,txt,raw] = xlsread('Distances.xlsx');
x = size(raw)
v = a
c = b
for i=2:x(1,1)
    y = raw{1,i};
         if strcmp(y,v)
             g=1
             for j= 1:x(1,2)
                 z = raw{j,1};
                     if strcmp(z,c)
                         f=1
                        distance = raw{i,j};
                        break
                     else
                        distance = -1;
                     end
               end
               break
         else
              distance = -1;
         end
end
      

[manish Singh]

Brother can you elaborate your code

I am unable to understadnd, It will be very good if you add comment after every loop for better understanding

Answer 4

[Mulayam Singh Choudhary]

function distance=get_distance(a,b)
a= convertCharsToStrings(a);
b= convertCharsToStrings(b);
[bar,~,raj]=xlsread('Distances.xlsx');
ii=2;
for k=1:length(raj)-1
    raj{1,ii}= convertCharsToStrings(raj{1,ii});
    raj{ii,1}= convertCharsToStrings(raj{ii,1});
    ii=ii+1;
end
ii=2;
jj=2;
for m=2:length(raj)
    if raj{1,ii}==a
        break;
    end    
    ii=ii+1;
end
for n=2:length(raj)
    if raj{jj,1}==b
        break;
    end    
    jj=jj+1;
end
if ii>length(raj)||jj>length(raj)
    distance=-1;
else
distance=bar(jj-1,ii-1);
end
    
    
    

Answer 5

[Prince Raj Yadav]

Get_Distance

function distance = get_distance(a,b)
[~,~,raw] = xlsread('Distance.xlsx');
[row,col] = size(raw);
for i = 2:row
    if strcmp(raw{i,1},a) == 1
        break;
    else
        i = 1;
    end
end
for j = 2:col
    if strcmp(raw{1,j},b) == 1
        break;
    else
        j = 1;
    end
end
if i > 1 && j > 1
    distance = raw{i,j};
else
    distance = -1;
end
end

[Rik]

Same here. You don't add any comments, nor do you provide any reasons for the functions you're using. Why did you bother post this? (these are not meant as rhetorical questions)