Find variable name of categorical values repeated a number of times

I am trying to find the variable names of columns in a table that match a value, in my case a string. The values in my table are categorical and have values of 'p' or 'f'. I can do this for one row.

row=categorical({'p' 'f' 'p' 'f'});
t=array2table(row)

% t =
% 
%   1×4 table
% 
%     row1    row2    row3    row4
%     ____    ____    ____    ____
% 
%      p       f       p       f  

t.Properties.VariableNames(table2array(t(1,:))=='p')

% ans =
% 
%   1×2 cell array
% 
%     {'row1'}    {'row3'}

But I want to find when the values have been repeated a few times, three in this case.

rows=[row;row;row];
ttt=array2table(rows)

% ttt =
% 
%   3×4 table
% 
%     rows1    rows2    rows3    rows4
%     _____    _____    _____    _____
% 
%       p        f        p        f  
%       p        f        p        f  
%       p        f        p        f  

t.Properties.VariableNames(table2array(ttt(1:3,:))=={'p';'p';'p'}) %does not work
%Variable index exceeds table dimensions.

isequal(table2array(ttt(1:3,3)),{'p';'p';'p'}) %does work for one column

% ans =
% 
%   logical
% 
%    1

Not sure what to do, also not sure if there is a better way without converting my table to an array.

[dpb]

What specifically are you trying to count? The rows that are repeated or repeats of the same value in a given row/column or ...?

What would be the answer you'd want from the example?

[Marcus Glover]

I want to return the variable names of the columns that have the value ‘p’ repeating 3 times. Output would be:

rows1 rows3

This is the table that I have:

>> ttt

ttt =

  3×4 table

    rows1    rows2    rows3    rows4
    _____    _____    _____    _____

      p        f        p        f  
      p        f        p        f  
      p        f        p        f  

This is what I want to match- It does not have to be a cell array- I just wanmt to match the string in the categorical variable for 3 consecutive rows.

>> match={'p';'p';'p'}

match =

  3×1 cell array

    {'p'}
    {'p'}
    {'p'}

[Image Analyst]

Make it easy for us to help you, not hard. Attach the table in a .mat file with the paper clip icon so we can try things with your actual data.

[Marcus Glover]

I have attached the table as a .mat file here.

The actual data is longer, but I am only interested in the last three rows. I am looking for the column variable name when the last three rows all match the string 'p'.

Or for easy cut and paste:

row=categorical({'p' 'f' 'p' 'f'});
rows=[row;row;row];
ttt=array2table(rows)

Answer 1

[dpb]

This is one of two ways...convert to a string represenation so can use string matching functions or use logical addressing and then find locations of runs. The string function may be simplest here. Brief outline of idea...

s=reshape(char(tt.Var1),1,[]);                                 % convert column to char() row string
fn=@(v,c) ~isempty(regexp(reshape(char(v),1,[]),[c '{3,}']));  % anon function to find 3 or more consecutive values in s

For your sample

>> fn(tt.Var1,'p')
ans =
  logical
   1
>> fn(tt.Var2,'p')
ans =
  logical
   0
>> fn(tt.Var1,'f')
ans =
  logical
   0
>> 

To use, apply with varfun or a loop over the desired columns building logic vector by column. Then the tt.PropertiesVariableNames property with that addressing vector will return the column names.

Answer 2

[Image Analyst]

Try this:

s = load('ttt.mat')
ttt = s.ttt
% Get a value to match, let's say from ttt.rows1(1)
thingToMatch = ttt.rows1(1)
for row = 1 : size(ttt, 1)
	% Extract this row, just for simplicity in debugging.
	thisRow = ttt{row, :}
	% See how many match p
	ia = ismember(thisRow, thingToMatch)
	numMatches(row) = sum(ia);
end
numMatches % Echo to command window.

You'll see the number of matches:

thingToMatch = 
  categorical
     p 
numMatches =
     2     2     2

[dpb]

"I just wanmt to match the string in the categorical variable for 3 consecutive rows."

I thought about ismember too, but that leads back to finding the breaks in the logical vector mentioned.

[Marcus Glover]

I'm not looking for the number of matches, - I want to find the columns where there are 3 matches in all 3 rows.

This does work though, I need to sum the logical indices from the 3 rows and check if the answer is 3.

s = load('ttt.mat')
ttt = s.ttt
% Get a value to match, let's say from ttt.rows1(1)
thingToMatch = ttt.rows1(1);

sum=[0 0 0 0];

for row = 1 : size(ttt, 1)
	% Extract this row, just for simplicity in debugging.
	thisRow = ttt{row, :};
	% See how many match p
	ia = ismember(thisRow, thingToMatch);
 
    sum=sum+ia;

end

ttt.Properties.VariableNames(find(sum,3))

[dpb]

That's a different problem than the one stated, but if that's the actual need, glad it works for you...

[Image Analyst]

Here is the table you uploaded:

ttt =

3×4 table

rows1 rows2 rows3 rows4

_____ _____ _____ _____

p f p f

p f p f

p f p f

Now you said "I am looking for the column variable name when the last three rows all match the string 'p'" So there are only 3 rows in this table, so my loop would work, but if there were more rows, the for loop would look like

numRows = size(ttt, 1);
for row = (numRows - 2) : numRows

but clearly nothing satisfies your condition "when the last three rows all match the string 'p'" because column 2 with variable name "rows2", and column 4 with variable name "rows4" have an 'f' in them instead of a 'p'.

So let's work on your new criteria: "I want to find the columns where there are 3 matches in all 3 rows." So we need to look at each column, and if there are 3 matches in all 3 rows (or perhaps the last 3 rows???) then tell what column it's in. The columns (variable names) are mysteriously named rows1-4 for some misleading or unknown reason. The code for seeing if the last 3 rows match each other, and then to identify (print out) the column name where this occurs is:

s = load('ttt.mat')
ttt = s.ttt
[rows, columns] = size(ttt)
variableNames = ttt.Properties.VariableNames
for col = 1 : columns % For each column
	% Extract this columm, just for simplicity in debugging.
	thisColumn = ttt{:, col};
	% See if third to last row matches last row, 
	% and if next to last row matches last row.
	% If both match the last row, then all 3 match and print out the variable name.
	if isequal(thisColumn(rows-2), thisColumn(rows)) && ...
			isequal(thisColumn(rows-2), thisColumn(rows))
		fprintf('Last 3 rows match for variable name (column) "%s"\n', variableNames{col});
	else
		fprintf('Last 3 rows DO NOT match for variable name (column) "%s"\n', variableNames{col});
	end
end

Now we'll see:

ttt =

3×4 table

rows1 rows2 rows3 rows4

_____ _____ _____ _____

p f p f

p f p f

p f p f

rows =

3

columns =

4

variableNames =

1×4 cell array

{'rows1'} {'rows2'} {'rows3'} {'rows4'}

Last 3 rows match for variable name (column) "rows1"

Last 3 rows match for variable name (column) "rows2"

Last 3 rows match for variable name (column) "rows3"

Last 3 rows match for variable name (column) "rows4"

[Marcus Glover]

Thank you (both) very much for your help, I definitely learned a lot from it.

I apologize for my misleading question. You have it correct now- the variable names of columns I was looking for 3 repeated matches in was indeed referring to the values of the 3 rows within the column.

I tried to clarify that I was looking to match repeated values columnwise, like this:

>> match={'p';'p';'p'}

match =

3×1 cell array

{'p'}

{'p'}

{'p'}

I believe there is a typo in your if statement, you have the conditions before and after the && the same. To check for three consecutive rows repeated in one column the statement would read :

if isequal(thisColumn(rows-2), thisColumn(rows)) && ...
			isequal(thisColumn(rows-1), thisColumn(rows))

% for 3 matches to my string it would be:

if isequal(thisColumn(rows-2), 'p') && ...
			isequal(thisColumn(rows-1), 'p')

Though the columns in my data only contain 3 rows, this can of course be extended to check any number of rows whose value is repeated in a single column.

[Image Analyst]

Your "correction" does not check the very bottom row to see if they're p.

[dpb]

MG, IA is correct...his test is 3==1(3-2) & and 3==2(3-1) which tests 1,2,3 are same. Your test is only 1(3-2)=='p' & and2(3-1) =='p'

It appears you're back to the three consecutive elements...I submit the string solution I posted above is far simpler for the purpose; besides being scalable to any value m for the number instead of being specifically coded for 3 only and requiring recoding to include specific conditions if the number ever does change.