Is it possible to extract 2 separated sections from the same table?

I'm defining 2 separate groups as variables from an excel table. Namely, I want to separate the data with the ID (second column) 'pilot' from the datasets labeled 'subject'

filename='RES_IAS (1).xlsx';       %Title renamed file as filename
rawdata=readtable('filename')      %readcell undefined, used readtable instead 

% Determine number of elements in a group and the number of groups in the table
cnt = length(unique(rawdata.ID))  % 17 total sets of data, including 5 pilots
grps = ceil(height(rawdata)/cnt)  
var3 = ones(cnt,1)*[1:grps]; % Create a grouping variable by making an array
var3 = var3(:); % Use linear indexing to convert the array to a column vector
var3 = var3(1:height(rawdata)); % Before adding to the table, make column same height as table
newdata=addvars(rawdata,var3); % Add new grouping variable to the table

I was able to extract the subjects as below by sorting for the subject ID, but there's a problem. one of the pilots was filed incorrectly, and is sorted to the very bottom of the table (line 837 on). Is there a way to get all pilots into the same variable? I've tried concatenating with all variables using a + and &, but I receive the error 'Undefined operator for input arguments of type 'table'.' Is there a way to do this?

% Sort data using fixation time and ID of subjects
newdata= sortrows(newdata,{'IA_FIRST_FIXATION_TIME','ID'},{'ascend','descend'})
first_fixations = (newdata(5391:end,:));  %extract only data with a fixation time > 0 
subject_sort = sortrows(first_fixations,{'ID'},{'ascend'}); %sort by subject ID
subjects_only = subject_sort(363:836,:);  %extracts data with subjects only, pilots excluded
pilots_only = subject_sort(1:362,:) % all pilots except pilot2

Answer 1

[Konrad]

Hi Ruth,

you can create indices into rawdata for your subjects and pilots programmatically like this:

filename='RES_IAS (1).xlsx';       %Title renamed file as filename
rawdata=readtable(filename);
idxPilot = startsWith(lower(rawdata.ID),'pilot');
idxSubj = startsWith(lower(rawdata.ID),'subj');
subjects_only = rawdata(idxSubj,:);
pilots_only = rawdata(idxPilot,:);

to check whether all rows were identifies as either subject or pilot you can use:

assert(all(idxPilot|idxSubj));

which will throw an error if there was any ID not starting with 'pilot' or 'subj'

Hope this helps!

Best, Konrad

[Ruth Ronalter]

Thanks Konrad. I didn't know that an index would work with a character string like that, and that the case didn't matter when selecting for it. Lot easier than what I had with a whole new dataset edited, and it wasn't that much work to edit in what I had for the pilot group results

[Konrad]

The case does matter! I just convert all characters in ID to lower case using lower().