filtering excel commands in matlab

Hello, i have the attched file .

i want using script command to extract the table and filter the rows of the table where the dogs are.

so it basicky out of my excel table i need to get 3D array of of 1X1X2 (animal,size,Color)

dog M brown

dog M red

is there an effective way to do it in matlab commands?

Thanks.

Answer 1

[dpb]

tA=readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1121415/animals.xlsx');
tA=convertvars(tA,@iscellstr,'categorical');
tA

tA = 6×3 table
     Animal     size    color 
    ________    ____    ______

    cat         s       black 
    cat         ss      orange
    cat         sss     white 
    dog         M       brown 
    elephant    L       grey  
    dog         MM      red   

Now we've got the table, don't make piecemeal of it, use the variables of interest as grouping variables instead...

groupsummary(tA,'Animal')

ans = 3×2 table
     Animal     GroupCount
    ________    __________

    cat             3     
    dog             2     
    elephant        1     

When you subsequently add other data such as weights, ages, etc., etc., ... then you can compute statistics or whatever the same way with whatever functions you need.

tA.Animal=='dog'% see the logical addressing vector

ans = 6×1 logical array
   0
   0
   0
   1
   0
   1

tD=tA(tA.Animal=='dog',{'size','color'})

tD = 2×2 table
    size    color
    ____    _____

     M      brown
     MM     red  

illustrates picking only the non-grouping variables since 'Animal' is now superfluous. To continue to carry it along anyway, use ":" for the column addressing vector.

[fima v]

Hello dpb, i am looking for getting a data structure which includes only the 'dog' rows form the table

" dog M brown"

"dog MM red"

what is the most effective method to get these two rows alone?

Thanks.

[dpb]

In general, it's wrong approach to physically separate tables unless it really is known that aren't going to use any of the other data for further analysis, but if you're adamant --

tDog=tA(tA.Animal=='dog',:);

Answer 2

[Star Strider]

I have no idea what you want or the reason a 3D matrix is necessary.

A different approach using unstack —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1121415/animals.xlsx')

T1 = 6×3 table
       Animal        size        color   
    ____________    _______    __________

    {'cat'     }    {'s'  }    {'black' }
    {'cat'     }    {'ss' }    {'orange'}
    {'cat'     }    {'sss'}    {'white' }
    {'dog'     }    {'M'  }    {'brown' }
    {'elephant'}    {'L'  }    {'grey'  }
    {'dog'     }    {'MM' }    {'red'   }

T1u = unstack(T1,'size','Animal')

T1u = 6×4 table
      color          cat           dog         elephant 
    __________    __________    __________    __________

    {'black' }    {'s'     }    {0×0 char}    {0×0 char}
    {'orange'}    {'ss'    }    {0×0 char}    {0×0 char}
    {'white' }    {'sss'   }    {0×0 char}    {0×0 char}
    {'brown' }    {0×0 char}    {'M'     }    {0×0 char}
    {'grey'  }    {0×0 char}    {0×0 char}    {'L'     }
    {'red'   }    {0×0 char}    {'MM'    }    {0×0 char}

Different results are available with different argument permutations and more arguments as described in Name-Value Arguments.

.

[fima v]

Hello Star Strider, i am looking for getting a data structure which includes only the 'dog' rows form the table

" dog M brown"

"dog MM red"

what is the most effective method to get these two rows alone?

Thanks.

[Star Strider]

Try something like this —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1121415/animals.xlsx')

T1 = 6×3 table
       Animal        size        color   
    ____________    _______    __________

    {'cat'     }    {'s'  }    {'black' }
    {'cat'     }    {'ss' }    {'orange'}
    {'cat'     }    {'sss'}    {'white' }
    {'dog'     }    {'M'  }    {'brown' }
    {'elephant'}    {'L'  }    {'grey'  }
    {'dog'     }    {'MM' }    {'red'   }

Dogs = T1(strcmpi(T1.Animal,'dog'),:)

Dogs = 2×3 table
    Animal      size       color  
    _______    ______    _________

    {'dog'}    {'M' }    {'brown'}
    {'dog'}    {'MM'}    {'red'  }

.

[fima v]

Hello Star strider,you method works great,

could you please help me with a method of multiple criteria?

i want animal to be a "dog" size to be "MM" and color "red".

So the output will be only the row shown bellow.

{'dog'} {'MM'} {'red' }

is there some method to do it in my example?

Thanks.

[Star Strider]

Try this —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1121415/animals.xlsx')

T1 = 6×3 table
       Animal        size        color   
    ____________    _______    __________

    {'cat'     }    {'s'  }    {'black' }
    {'cat'     }    {'ss' }    {'orange'}
    {'cat'     }    {'sss'}    {'white' }
    {'dog'     }    {'M'  }    {'brown' }
    {'elephant'}    {'L'  }    {'grey'  }
    {'dog'     }    {'MM' }    {'red'   }

Dogs = T1(strcmpi(T1.Animal,'dog') & strcmpi(T1.color,'red'),:)

Dogs = 1×3 table
    Animal      size      color 
    _______    ______    _______

    {'dog'}    {'MM'}    {'red'}

.