How can I cross reference the data in two excel tables?

Hello-

New Matlab user here! Had a question for y'all:

I have two excel tables that I would like analyze in mat-lab. One table has a bunch of user ids and category groups associated to each user. The second table is ten sets of two columns in which one column has the user id and the other has a user action. What I would like to do is find out how many of each kind of action is associated with each category group. How would I go about doing this?

Thanks!

[Bob Thompson]

Do you have anything written for this so far? I could give a basic layout of what you're going to be looking to do if you don't have anything yet, but it's easier to work with what you already have if you have anything.

[Alan Bederka]

I'm sorry I don't have anything written yet as far as code goes. Again, I'm very new to Matlab and got it specifically for this problem. I tried to do this in Excel but it kept crashing. If it would help, I could provide you with the Excel function I was using.

Thanks!

[Bob Thompson]

Ok, so for your actions, how are they represented? Are they just a word, or is it some kind of numerical system?

Are you just looking to determine how many times any specific action happens, or does it need some kind of association with another value?

[Alan Bederka]

So for the actions there are three separate actions: "sent", "Open", and "click". They are all represented with the quoted word.

I am looking to determine how many time each user (represented with a user number) has performed each action. I would then like to be able to sum the totals for each type based on the tag each user has which is represented as a word

Answer 1

[Bob Thompson]

Ok, since you're a new Matlab user I'm going to give you a basic method for accomplishing what you're looking to do. This is not the best method, just the most basic. If others want to make things more sleek feel free, but this will be good to know.

I'm gonna write some pseudo code with some commands and structure that you're going to need to look up on your own to fully implement.

[num, text, all] = xlsread(filename,range,etc.); % Start here to read your files. Since you have mixed text
                                                 % and numbers I would suggest using the 'all' output as your
                                                 % primary array. Just use '~' for the others if you don't want
                                                 % them. You will need one of these commands for each file.
                                                 % I am going to call 'all' from first file 'userdata'.
for j = 1:size(userdata,1); % Run a for loop through all users to gather their information. Assumes 1 unique
                            % user per line
    Userinfo(j) = struct('ID',userdata{j,2}; % Creates structure array called 'Userinfo' with field 'ID'
                                          % identified by user ID number found in userdata array. Curly braces
                                          % are used since userdata is a cell array.
    [Userinfo(j).tags] = {j,3:end}; % Should record all tags for user j into 'tags' field. May need indexing on
                                 % tags to create cell array properly.
    [Userinfo(j).count] = cell(); % This is a little bit up to you, the idea is to create a 'count' field which
                               % will hold the counted data for what you want to count up.
end
for k = 1:size(actiondata,1); % Run through each row of the action file.
   if actiondata{k,1}==Userinfo(1).ID; % check if user ID is for first user
      if strcmp(actiondata{k,2},'sent'); % Check for action type
         Userinfo(1).count = Userinfo(1).count+1; % Increase count by 1
      elseif strcmp() % Check for second condition
         % Count increase
      else % Catch all option
         % Command
      end % Action check statement
   elseif ... % Check for next user data
      % Repeat action check
   end % User ID check
end % Action data for loop
% This previous section really isn't pretty, and it's gonna be huge if you have a lot of users, but it will
% scan all values and should count properly.

It's definitely rough, and will be quite large, but it should run fairly quickly and perform what you're looking for. Good introduction to several commands and both structure and cell array types.

[Alan Bederka]

Thank you so much for your help!