MATLAB: Locating variables across multiple tables

MATLABvariable sorting

Hi, I'm trying to track a certain variable through several data sets and look at some corresponding numbers as time goes on.I have 91 excel sheets with 10,000 lines, but I'll make a silly simplified example….. I need to know how many fleas are on a dog every year, but the order of the dogs changes, and sometimes new dogs are added… year 1:
  • Spike 10
  • Mr.Dog 12
  • Chow 11
Year 2:
  • Spike 11
  • Chow 13
  • Mr.Dog 15
Year 3:
  • * Spike 18
  • * Chow 17
  • * Mr.Dog 16
  • * Charlie 20
I need an outcome that looks sort of like this
  • * Spike 10 11 18
  • * Chow 11 13 17
  • * Mr.Dog 16…etc
  • * Charlie 20
I created a loop that picks a name as a variable and then scrolls through the column containing names until it finds a match, in which case it grabs the corresponding values from the same row. I'm having a tough time with errors defining what a variable is…can a text/number combo not be a variable? does it need to be in parentheses? I imagine there is an easier way to do this in general.
for i=1:9572;
    if CO01(i,3)==302;
        t302(i,1,1)=CO01(i,3);
        t302(i,2,1)=CO01(i,4);
        t302(i,3,1)=CO01(i,5);
        t302(i,4,1)=CO01(i,6);
        t302(i,5,1)=CO01(i,9);
        t302(i,6,1)=i;
        %y=y+1;
    end
end
for i=1:9500;
    nt302(i,1,1)=t302(i,2,1);
    nt302(i,1,2)=t302(i,3,1);
    nt302(i,1,3)=t302(i,4,1);
    nt302(i,1,4)=t302(i,5,1);
end
for i=1:9500;
    row=t302(i,6,1);
    name1=nCO01(row,1);
    for j=1:9500;
        if nCO03(j,1)==name1(1,1);
            nt302(j,2,1)= CO03(j,4);
            nt302(j,2,2)= CO03(j,5);
            nt302(j,2,3)= CO03(j,6);
            nt302(j,2,4)= CO03(j,9);
        end
    end
end

Best Answer

How's this:
>> dogs = categorical(["Spike";"Mr. Dog";"Chow";"Spike";"Chow";"Mr. Dog";"Spike";"Chow";"Mr. Dog";"Charlie"]);
>> fleas = [10;12;11;11;13;15;18;17;16;20];
>> years = [1;1;1;2;2;2;3;3;3;3];
>> t = table(years,dogs,fleas)
t =
  10×3 table
    years     dogs      fleas
    _____    _______    _____
      1      Spike       10  
      1      Mr. Dog     12  
      1      Chow        11  
      2      Spike       11  
      2      Chow        13  
      2      Mr. Dog     15  
      3      Spike       18  
      3      Chow        17  
      3      Mr. Dog     16  
      3      Charlie     20  
>> unstack(t,'fleas','dogs')
Warning: Variable names were modified to make them valid MATLAB identifiers. 
ans =
  3×5 table
    years    Charlie    Chow    Mr_Dog    Spike
    _____    _______    ____    ______    _____
      1        NaN       11       12       10  
      2        NaN       13       15       11  
      3         20       17       16       18
Is that "All Dogs Go to Heaven", or John Steinbeck?
Related Question