Filtering rows according to values in different columns

I am new at Matlab and I am struggling to use it. Please, need some help.

I have a spreadsheet 3189x89 and I need to split rows according to nine different criteria in columns.

The first column contains Date & Time data. The others contains only numerical data.

What I need is to export all the rows that meet a criteria in a spreadsheet. For example: all the rows that meet the criteria C2>1 AND C3>2 AND 4<C4<=6 ... AND C10 <= 7, need to be copied, stored and exported to an Excel spreadsheet.

I tried to import excel data to a table and work with it in Matlab, but it is not working.

If somebody could help me, I would appreciate! Thanks!

[madhan ravi]

C??

[Fausto Moretti]

C I mean column. Sorry...

It is something like this:

Date&Time C2 C3 C4 C5 C6 C7 C8 C9 C10 C11...

2019.05.23.09:00 2 4 5 6 8 10 11 7 5 3 ...

...

[Guillaume]

all the rows that meet the criteria C2>1 AND C3>2 AND 4<C4<=6 ... AND C10 <= 7

How is the criteria actually known and stored?

What you want to do is easily done, and for a few columns it could be hardcoded (e.g. mytable(mytable.C1 > 1 & mytable.C3 > 2, :)). With many columns, hardcoding is not practicle and would be a waste of time since the computer can do the work of working out what needs to be compared with what for you. To tell you how to do that, we need to understand how the conditions are stored or where they come from.

[Fausto Moretti]

The purpose here is to identify different scenarios within lots of experimental data. Each scenario is characterized by a combination of this 9 variables. Conditions are predetermined bands and, for now, are being inputed manually. I know this is not the wisest way but, as I said, I am beginning...

I will try to clarifi with an example (spreadsheet attached).

Data available:

Criteria:

18 < C2 <=18.5 ; 64.6< C3 <= 65 ; 8.5<C4<=8.8; 4.0 < C5 <=4.4 ; 0.57<C5<=0.6 ; 0.6 <C7<=0.8; 0.5< C8 <=0.7; 41 < C9 < 44; 4650 < C10 <= 4750

Results:

Here I included the next desired step, wich is calculate the mean of columns values to the selected rows.

Again, criteria are bands that will vary for each one of the 9 variables. I believe these conditions can be stored as vector or matrix, but I did not tried yet...

Answer 1

[Aditya]

To filter out the data based on some specific criteria in MATLAB and export it back to excel, you can use the following approach. This involves reading the data into a table, applying logical indexing, and then exporting the filtered data to an Excel spreadsheet.

Here's how you can acheive it:

1] Import the Data:

• Use readtable to import your Excel data into MATLAB

dataTable = readtable('yourfile.xlsx');

2] Apply the criteria:

• Use logical indexing to filter rows based on your conditions.

% Define the criteria and filter the data
% Example criteria: 18 < C2 <= 18.5, 64.6 < C3 <= 65, ..., 4650 < C10 <= 4750
filteredData = dataTable(dataTable.C2 > 18 & dataTable.C2 <= 18.5 & ...
                         dataTable.C3 > 64.6 & dataTable.C3 <= 65, :)

3] Export the Filtered Data:

• Use writetable to export the filtered data to a new Excel spreadsheet.

% Export the filtered data to a new Excel file
writetable(filteredData, 'filteredData.xlsx');

4] Calculate the Mean of Selected Rows:

• Calculate the mean of the selected rows for each column.

% Calculate the mean of the selected rows for each column
means = varfun(@mean, filteredData, 'InputVariables', 2:10);

% Display the means
disp(means);

Hope it helps!