extract data from table matlab

242 次查看（过去 30 天）
Emanuele De Astis
Emanuele De Astis 2021-1-30
编辑： dpb 2021-2-1
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

请先登录，再回答此问题。

采纳的回答

dpb
dpb 2021-1-30
编辑：dpb 2021-2-1
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.
  5 个评论
显示 3更早的评论
dpb
dpb 2021-1-30
编辑：dpb 2021-1-30
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
Emanuele De Astis 2021-1-30
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');

请先登录，再进行评论。

更多回答（2 个）

dpb
dpb 2021-1-30
编辑：dpb 2021-1-31
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
>>
  9 个评论
显示 7更早的评论
dpb
dpb 2021-1-30
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
Emanuele De Astis 2021-1-30
I have accepted, tell me if It's ok

请先登录，再进行评论。

Emanuele De Astis
Emanuele De Astis 2021-1-31
[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
>>
  5 个评论
显示 3更早的评论
Emanuele De Astis
Emanuele De Astis 2021-1-31
i don't know, i've tried with another dataset and your script works fine
dpb
dpb 2021-1-31
编辑：dpb 2021-1-31
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

产品


版本

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by