How to filter columns and count data

I would like to filter the data in the screenshot by TTC value, Conflict Type, Veh1Type and Veh2Type.

I would like to filter TTC for a value of 1.0, Conflict type = 1 (which is rearend) , Veh1Type = 630 and Veh2Type = 630

After filtering I would like a table to display the results and count the number of rows and write the table to a excel file.

How can this be done in MATLAB?

Answer 1

[dpb]

At the most basic, if your table is "tData" for lack of better name and your particular variable used not provided,

tG=groupcounts(tData,{'TTC','ConflictType','Veh1type','Veh12ype'});

will return a table of counts for all groups; you can then select from it the record(s) with TTC==1; the above would produce only one, of course.

The question with more data is whether/how to count the subcategories if are more than one or to add them altogether for the count. We don't have that complete a definition of the desired output.

There are a number of grouping functions available, see the list of functions in the doc at the top of the doc page for the above for further ideas.

Or, if it is really only the one single set of specific values that is of interest, then logical addressing --

tData=convertvars(tData,'ConflictType','categorical'); % set appropriate variable type
ix=(tData.TTC==1)&(tData.ConflictType=="rear end")&(tData.Veh1Type==630)&(tData.Veh2Type==630);
nCounts=nnz(ix);            % just the counts, ma'am...
tSubset=tData(ix,:);        % select the subset --> number would be height(tSubset)

See writetable to output.

Answer 2

[Star Strider]

It would help to have the data. I would be tempted to do something like this:

Mishaps = readtable('YourData.xlsx')

TTCv = Mishaps.TTC == 1;

CTv = ismember(Mishaps.ConflictType, 'rear end');

VT1v = Mishaps.Veh1Type = 630;

VT2v = Mishaps.Veh2Type = 630;

Lv = all([TTCv,CTv,VT1v,VT2v],2)

That would be analogous to this:

TTCv = randi([0 1],50,1)==1;
CTv = randi([0 1],50,1)==1;
VT1v = randi([0 1],50,1)==1;
VT2v = randi([0 1],50,1)==1;
Lv = all([TTCv,CTv,VT1v,VT2v],2);
Result = Mishaps(Lv,:)                                  % Table Of Results
Complete = nnz(Lv)                                      % Number Of Complete Results

Complete = 6

LogicalMatrix = [TTCv,CTv,VT1v,VT2v]                    % Random Matrix Created To Test Code

LogicalMatrix = 50×4 logical array
   1   1   1   1
   0   0   0   0
   1   0   1   1
   1   1   0   1
   0   1   0   0
   1   1   1   0
   0   0   1   1
   1   1   0   0
   1   1   1   1
   0   1   1   0
   1   0   1   0
   0   1   0   0
   0   1   0   1
   0   1   0   1
   1   1   1   0
   0   1   0   0
   1   1   1   1
   1   1   1   0
   0   1   1   0
   1   0   1   1
   1   0   1   0
   0   0   1   0
   0   1   1   0
   0   1   1   0
   1   1   0   0
   1   1   1   1
   1   1   0   0
   1   1   0   1
   1   0   1   1
   1   1   1   0
   0   0   0   1
   1   1   1   1
   0   0   0   1
   1   0   1   1
   0   0   1   0
   0   0   0   0
   0   1   0   1
   1   1   0   0
   1   1   1   1
   1   1   0   1
   0   1   0   1
   1   0   0   0
   0   1   0   0
   0   1   1   0
   1   1   1   0
   0   0   1   0
   1   0   0   0
   0   1   1   1
   1   1   0   0
   0   0   1   1

.