extract data from table matlab

for a job I have to extract information from this table, in particular I want to have a new table with only the information regarding T001, T002, T003, T004 (third coloumn) there is a quick way to do this on matLab. Thanks I have recently used it

S=readtable('paris.txt')
day=S(:,1)
time=S(:,2)
sensor=S(:,3)
sensor_out=S(:,4)
paris_table=table(day,time,sensor,sensor_out) 

This is what i do

Answer 1

[dpb]

More than likely you don't need to actually build a new table for each; instead use grouping variables to process the column by value.

S=readtable('paris.txt')
tSensor=rowfun(@mean,S,'GroupingVariables',{'Sensor'},'InputVariables',{'Reading'}, ...
                       'OutputVariableNames','SensorMean');

I've presumed column names for the variables in columns based on your variable names above; match to suit what you have in the table.

Rightfully, you should be able to input the data with the date/time interpreted as a MATLAB datetime variable in which case the Date column will be a single column, not two. Otherwise, combine the two into a datetime and replace the two original columns.

This could be the place for a timetable instead of a regular table as well.

I also presumed to just compute the global average for each sensor over the full dataset; you could also group by a time increment such as hourly, daily, weekly, ... where retime and a timetable would be quite handy.

The function can also do multiple statistics or whatever is wanted; there are examples in the documenation for rowfun to show how.

[Emanuele De Astis]

i'll try

[dpb]

Indeed, give it a go and see if you don't get where you're headed. It's really not as complicated as seems once do just one or two examples.

Attaching a sample of your input file would let somebody play if you get stuck somewhere.

[Emanuele De Astis]

it doesn't work

[dpb]

Don't attach images; can't read them with my geezer-aged eyes.

As said, attaching a sample dataset would let folks do something specific.

Why are you working with a copy of the table instead of the table itsefl? There's no point in duplicating data just for the sake of duplication.

The above problem is the type of the 'Sensor' variable isn't one of those in the list that is allowed for a grouping variable -- what is it? Surely looks like a categorical, string or cellstr() would be appropriate for it from what we can see here.

S.Sensor=categorical(S.Sensor); % convert to categorical variable

The above may need to convert to a cellstr() first; it's simply not possible to know what, precisely without having the data in hand.

Attach a .mat file containing S or the input file itself to go back to the beginning.

[Emanuele De Astis]

S=readtable('data')

day=S(:,1)
time=S(:,2)
sensor=S(:,3)
sensor_out=S(:,4)
paris_table=table(day,time,sensor,sensor_out)

Ttable=timetable(day,time,sensor,sensor_out)

tSensor=rowfun(@mean,paris_table,'GroupingVariables',{'sensor'},'InputVariables',{'T001'}, ...
                       'OutputVariableNames','SensorMean');

Answer 2

[dpb]

That's messy...that they didn't put the data into separate columns makes it rougher...almost do have to separate out the T sensors from the M whatever-they-ares to do anything useful. Could separate into another column; for demo here since it's only T you show above I just split them out...

Here's about how I'd go about it with that input file as starting point:

opt=detectImportOptions('milin.txt','ReadVariableNames',0);     % first get an import object
opt.VariableNames={'Date','Sensor','Value'};                    % set variable names for those wanted
opt.SelectedVariableNames=opt.VariableNames;                    % and read only them
opt.ExtraColumnsRule='ignore';                                  % and don't add other variables
M=readtable('milin.txt',opt);                                   % now read the table 

The above gives us

>> head(M)
>> [head(M);tail(M)]
ans =
  16×3 table
            Date             Sensor       Value  
    ____________________    ________    _________
    16-Oct-0009 00:01:00    {'M017'}    {'ON'   }
    16-Oct-0009 00:01:00    {'M009'}    {'ON'   }
    16-Oct-0009 00:01:00    {'M017'}    {'OFF'  }
    16-Oct-0009 00:01:00    {'M019'}    {'ON'   }
    16-Oct-0009 00:01:00    {'M009'}    {'OFF'  }
    16-Oct-0009 00:01:00    {'M019'}    {'OFF'  }
    16-Oct-0009 00:08:00    {'M020'}    {'ON'   }
    16-Oct-0009 00:08:00    {'M020'}    {'OFF'  }
    06-Jan-0010 12:48:00    {'D001'}    {'CLOSE'}
    06-Jan-0010 12:48:00    {'M001'}    {'OFF'  }
    06-Jan-0010 12:48:00    {'M003'}    {'ON'   }
    06-Jan-0010 12:48:00    {'M002'}    {'OFF'  }
    06-Jan-0010 12:48:00    {'M027'}    {'OFF'  }
    06-Jan-0010 12:48:00    {'M027'}    {'ON'   }
    06-Jan-0010 12:48:00    {'M003'}    {'OFF'  }
    06-Jan-0010 12:48:00    {'M003'}    {'ON'   }
>> 

as starting point. NB: the import function recognized the datetime first column altho I did have to fix the display format in Excel that seemed to only be time. That's up to you what detail you need/don't...

M=M(contains(M.Sensor,'T'),:);              % save only the 'T' sensors for now
M.Sensor=categorical(M.Sensor);             % turn sensor into categorical variable
M.Value=str2double(M.Value);                % and data into numeric
meanBySensor=rowfun(@mean,M,'InputVariables',{'Value'}, ...
                            'GroupingVariables',{'Sensor'}, ...
                            'OutputVariableNames','SensorMeans');

is the example of operating by sensor globally...

>> format bank,format compact
>> meanBySensor
meanBySensor =
  2×3 table
    Sensor    GroupCount    SensorMeans
    ______    __________    ___________
     T001      6734.00         22.25   
     T002      5539.00         21.09   
>> 

gives the above table.

To illustrate multiple variables grouping, lets do by DOW...

[DOW,WKDY]=weekday(M.Date);             % first get the ordinal dow, weekday name for table
[~,wkdays]=weekday(1:7);                % generate weekday names for a week
wkdays=circshift(string(wkdays),-1);    % put in sequential order begin Sunday
M.DOW=categorical(string(WKDY),wkdays); % and add to the table 
meanBySensor_DOW=rowfun(@mean,M,'InputVariables',{'Value'}, ...
                                 'GroupingVariables',{'Sensor','DOW'}, ...
                                 'OutputVariableNames','SensorMeans');

gives:

>> meanBySensor_DOW
meanBySensor_DOW =
  14×4 table
    Sensor    DOW    GroupCount    SensorMeans
    ______    ___    __________    ___________
     T001     Sun      927.00         22.58   
     T001     Mon      978.00         22.50   
     T001     Tue      986.00         21.81   
     T001     Wed     1070.00         22.01   
     T001     Thu     1053.00         22.62   
     T001     Fri      852.00         22.17   
     T001     Sat      868.00         22.03   
     T002     Sun      753.00         21.43   
     T002     Mon      797.00         21.34   
     T002     Tue      788.00         20.71   
     T002     Wed      867.00         20.83   
     T002     Thu      884.00         21.39   
     T002     Fri      736.00         21.04   
     T002     Sat      714.00         20.91   
>> 

[Emanuele De Astis]

thank you so much its exactly my problem

[dpb]

NB: A correction -- had deleted one line needed to generate the wkdays array from weekday

[dpb]

If that does get you going, please ACCEPT the Answer to let others know is a solution if for no other reason...

[Emanuele De Astis]

yes, the last part, starting from "[DOW,WKDY]=weekday(M.Date); " doesn't work

but why matlab show in the command windows only a small part of the table? and if i try to save M like a csv file and then i open its don't show the value but simbols?

[Emanuele De Astis]

yes, the last part, starting from "[DOW,WKDY]=weekday(M.Date); " doesn't work

but why matlab show in the command windows only a small part of the table? and if i try to save M like a csv file and then i open its don't show the value but simbols?

[dpb]

Oh, yeah...I had created a DOW column previously to test in the workspace and didn't catch when copied to forum. It's not yet available when first building the categorical variable. Use

M.DOW=categorical(string(WKDY),wkdays);

instead -- I made the edit above.

I just showed head(M) or [head(M);tail(M)] to only look at a manageable amount of the whole table -- it's

>> height(M)
ans =
       12273
>> 

rows long; I didn't much want to look at all of that....just a little to see was doing what wanted it to.

Have to see what you did to write the .csv file; writetable should work just fine.

>> writetable(head(M),'testmilin.csv')
>> type testmilin.csv

Date,Sensor,Value,DOW
16-Oct-0009 01:12:00,T001,20.5,Fri
16-Oct-0009 01:27:00,T002,20.5,Fri
16-Oct-0009 01:32:00,T002,21,Fri
16-Oct-0009 01:37:00,T002,20.5,Fri
16-Oct-0009 04:38:00,T002,20,Fri
16-Oct-0009 04:48:00,T001,20,Fri
16-Oct-0009 07:49:00,T002,19.5,Fri
16-Oct-0009 07:54:00,T002,20,Fri
>> 

works here; again NB: I just did head(M) so the file wouldn't be huge; use

writetable(M,'newmilan.csv','QuoteStrings',1)

for the whole thing. This will also put the date-time into a quoted string so when reading the file the date and time will be interpreted as one field as should be instead of two.

>> type testmilan.csv

Date,Sensor,Value,DOW
"16-Oct-0009 01:12:00","T001",20.5,"Fri"
"16-Oct-0009 01:27:00","T002",20.5,"Fri"
"16-Oct-0009 01:32:00","T002",21,"Fri"
"16-Oct-0009 01:37:00","T002",20.5,"Fri"
"16-Oct-0009 04:38:00","T002",20,"Fri"
"16-Oct-0009 04:48:00","T001",20,"Fri"
"16-Oct-0009 07:49:00","T002",19.5,"Fri"
"16-Oct-0009 07:54:00","T002",20,"Fri"
>> 

[Emanuele De Astis]

Fine thanks for the help, you save me

[dpb]

No problem, but again,

"If that does get you going, please ACCEPT the Answer to let others know is a solution if for no other reason..."

[Emanuele De Astis]

I have accepted, tell me if It's ok

Answer 3

[Emanuele De Astis]

[DOW,WKDY]=weekday(M.Date);             % first get the ordinal dow, weekday name for table
wkdays=weekday(1:7);                    % generate weekday names for a week
wkdays=circshift(string(wkdays),-1);    % put in sequential order begin Sunday
M.DOW=categorical(string(WKDY),wkdays) 

sorry, when i run this part, matlab give me error about the line "[DOW,WKDY]=weekday(M.Date);"

Index in position 1 is invalid. Array indices must be positive
integers or logical values.
Error in weekday (line 102)
        w = week1(d,:);
Error in milan_prova (line 21)
[DOW,WKDY]=weekday(M.Date);             % first get the
ordinal dow, weekday name for table 
>> 

[dpb]

[~,wkdays]=weekday(1:7);                    % generate weekday names for a week

[Emanuele De Astis]

sorry but i think the problem is in

[DOW,WKDY]=weekday(M.Date)

[dpb]

No

[Emanuele De Astis]

i don't know, i've tried with another dataset and your script works fine

[dpb]

Oh...I had forgotten about the problem in that data file.

One of the comments to the above script I wrote when first posted it was--

". NB: the import function recognized the datetime first column altho I did have to fix the display format in Excel that seemed to only be time. That's up to you what detail you need/don't..."

If there is no date in the time field it will barf, indeed. That's a problem with the input file can't fix in MATLAB; only by correcting the input file to also show the date besides the time.