Excel file with three columns and compare the data

Hellow,

I have an excel file with three columns (visitors,lacation,score). I want to find out which visitors went to the same location and what score they made.

Code I wrote:

for j=1:locations
    if (visitors(j)~=0 & locations(j)~=0)
        flag=1;
        score= score + abs(visitors9j0-locations(j))
    end
    if flag==1
        score
    else 
        string("no similarity")
    end
end

[Adam Danz]

How can we help you? I don't see a question.

[stelios loizidis]

Apologize. My question is: How will I find which visitors went to the same location and what score each visitor put.

[Adam Danz]

How is your data represented once you load it into matlab? Is it in the form of an [n by 3] matrix or numeric data? Could you provide a sample?

[stelios loizidis]

I entered the data as follows:

visitors=xlsread("data.xlsx","A1:A100")
locations=xlsread("data.xlsx","B1:B100")
scores=xlsread("data.xlsx","C1:C100")
e.g: locations(100X1):

[Adam Danz]

Ok, that's clear. I still have 1 more quesiton.

"I want to find out which visitors went to the same location and what score they made."

Does that mean that 1) you want to choose a location (eg: # 70) and then list all visitors who went to that location and their scores or 2) for all locations, you want a list of visitors who have been there?

Also, how do you want this formatted? In a table? a cell array? It would be helpful for you to share an example of your desired output.

Answer 1

[Adam Danz]

"I want to find out which visitors went to the same location and what score they made."

There are several ways to interpret this and several ways how to organize the data. Here are two interpretations.

% visitorNum: [n x 1] vector; numeric
% location: [n x 1] vector; numeric
% score: [n x 1] vector; numeric

% If you want to list all users who have been to location 3, and their scores (in a table)
targetLocation = 3; 
locIdx = location == targetLocation; 
table(visitorNum(locIdx), score(locIdx), 'VariableNames', {'Visitor', 'Score'})

% If you want to apply this to all locations, loop through each location and store each table 
% in a cell array. 
unqLocations = unique(location); 
tbls = cell(size(unqLocations)); 
for i = 1:length(unqLocations)
    locIdx = location == unqLocations(i); 
    tbls{i} = table(visitorNum(locIdx), score(locIdx), 'VariableNames', {'Visitor', 'Score'}); 
end
% tbls{x} is a table associated with location unqLocations(x)

[stelios loizidis]

I just saw it now!!

What I an to find which visitors went to the same location and what score the made (e.g: I have a visitor 1 who went to locations10 , 20 , 30 visitor 2 went to locations 20,50,60 and visitor 3 went to 50,70,100. In every location that went, they scored a score, so I have to find that visitor1 and 2 went to the location 20 and each one put a score and the same ,must be done with visitor 2 and 3 eho went to location 50 and scored a score there. )

This is to be done for all data (100 visitors are in the excel file)

For instance, in the event that some visiotrs have not visited loactions that others have visited, then Matlab has the message that there are no common locations. They must be a array cell.

[Adam Danz]

Have you looked at the tables in my answer? If they don't suit your needs, maybe you could create a demo of how the data should look.

[stelios loizidis]

Okay, I tried what you said and worked just fine.

Thank you very much for the help!!!!

[Adam Danz]

Ok, glad it worked out - no problem.