how to read selected data from text file matlab ?

hi, I have this text file. (attached).

from this text file I only want read only second column(date), third column(time) and the last column. first 11 rows can be deleted. since time and date are two different column I am not able to combine them and make single vector. I just want do hourly average which I can do for sure. but I can not read and collect the data from this file in the first place. I Hope you understand my question. thanks

Answer 1

[jonas]

readtable can do this for you easily.

%%Read the relevant columns, exclude 10 rows
opts=detectImportOptions('str.txt','NumHeaderLines',10);
opts.SelectedVariableNames = {'timestamp','RPM','absE_f'};
opts = setvartype(opts,{'timestamp','RPM'},{'datetime','duration'})
T=readtable('str.txt',opts)
t=T.timestamp+T.RPM;

readtable automatically detects the datetime and duration format of columns 2 and 3. If it fails, you can specify the format in detectImportOptions. Now, just build a timetable:

TT=timetable(t,T.absE_f)

ans =

10×1 timetable

             t                Var1  
    ____________________    ________

    02-Jan-2006 00:00:00      131.96
    02-Jan-2006 00:00:10    -0.26753
    02-Jan-2006 00:00:20    -0.29573
    02-Jan-2006 00:00:30      127.73

Finally,

TT2 = retime(TT,'hourly','mean')

[pruth]

thank you so much. when I tried to run the third line of your syntax i.e.

 t=T.timestamp+out.RPM;

I get this error.

 Undefined variable "out" or class "out.RPM".

I don't understand what thing I need to change. please clarify. thanks again,

[jonas]

ops, I changed the table name from out to T. Forgot to change that one. I've updated the answer to only include the relevant columns.

[jonas]

Yep, it should work fine in 2017a!

[pruth]

Jonas, even you changed that one I am still getting an error

 Addition is not defined between datetime arrays.

[jonas]

Okay! Then your readtable does not automatically detect the format of the 3rd column. Give me a minute to fix.

[pruth]

Jonas thank you for your patience... but I am repeatedly getting this error on

 t=T.timestamp+T.RPM;

Addition is not defined between datetime arrays.

I am not able to make any changes since this function is very new to me. Hope you understand. thanks

[jonas]

Don't worry, I had not updated the code yet :)

Try the updated code now, including this line. There is a small risk that readtable will not automatically detect the format, but I think it should work.

opts = setvartype(opts,{'timestamp','RPM'},{'datetime','duration'})

I'll leave for few hours, but will check on this thread later if you run into trouble.

[pruth]

brother, error again. :(

 Error using matlab.io.ImportOptions/setvartype (line 279)
Unsupported type 'duration'.

I wonder how can this code is working on your matlab but not on mine. Do i need to get newer version. ?

[jonas]

My guess is that readtable uses some local settings to detect formats, but I'm not certain.

One last comment before I leave for a two hours. Type this in your command window and show me the entire output:

opts=detectImportOptions('str.txt','NumHeaderLines',10)

Also try this code:

opts=detectImportOptions('str.txt','NumHeaderLines',10);
opts.SelectedVariableNames = {'timestamp','RPM','absE_f'};
opts = setvartype(opts,'timestamp','datetime')
opts = setvartype(opts,'RPM','datetime');
opts = setvaropts(opts,'timestamp','InputFormat','MM/dd/yyyy')
opts = setvaropts(opts,'RPM','InputFormat','HH:mm:SS');
T=readtable('str.txt',opts);
Date=datetime(T.timestamp,'format','yyyy-MM-dd HH:mm:SS')
TimeOfDay=hours(hour(T.RPM))+minutes(minute(T.RPM))+seconds(second(T.RPM));
t=Date+TimeOfDay;
TT=timetable(t,T.absE_f)

[pruth]

out put

 >> opts=detectImportOptions('2jan.txt','NumHeaderLines',10)

opts =

DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {'\t'}
                   Whitespace: '\b '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'windows-1252'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties:   Set types by name using setvartype
                VariableNames: {'DOY', 'timestamp', 'RPM' ... and 5 more}
                VariableTypes: {'double', 'datetime', 'char' ... and 5 more}
        SelectedVariableNames: {'DOY', 'timestamp', 'RPM' ... and 5 more}
              VariableOptions: Show all 8 VariableOptions 
  Access VariableOptions sub-properties using setvaropts/getvaropts

   Location Properties:
                     DataLine: 12
            VariableNamesLine: 11
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0

[pruth]

the code you provided it works. But it is in timetable format. any idea how can we convert it in to simple matrix format ?. seems datenum(TT(:,1)) does not work.

[Walter Roberson]

datenum(TT{:,1})

However, your first column is already a double, and applying datenum() to a double does not seem likely to be what is desired.

[jonas]

The reason I put it in a timetable is that you can use

TT2 = retime(TT,'hourly','mean')

to get the hourly mean, as per your request. The time-column is already stored in the variable 't' in datetime format, and you can get the column with data simply by referring to the original table

T.absE_f

I strongly recommend using datetime format over datenum format, but if you insist, then follow Walter Roberson's fix in the previous comment.

The reason my original code did not work, is because your machine did not recognize the third column as duration format. I'm still not sure why my second attempt did not work however.

Important note: There was an error in the code. It should be:

Date=datetime(T.timestamp,'format','yyyy-MM-dd HH:mm:SS')

Note that capital SS (originally ss).

[pruth]

yes but it wont work, since (:,1) in timetable is the variable vector. it is directly taking second column as a {:,1}.

[pruth]

thank you so much , Jonas, for your time and patience !!

[jonas]

Yes sorry, my bad. Grab the time by

datenum(TT.t)

No problem! Happy to help.

Answer 2

[pruth]

final code. !!

clear all
close all 
clc
opts=detectImportOptions('2jan.txt','NumHeaderLines',10); %%%Read the relevant columns, exclude 10 rows
opts.SelectedVariableNames = {'timestamp','RPM','absE_f'};
opts = setvartype(opts,'timestamp','datetime');
opts = setvartype(opts,'RPM','datetime');
opts = setvaropts(opts,'timestamp','InputFormat','MM/dd/yyyy');
opts = setvaropts(opts,'RPM','InputFormat','HH:mm:SS');
T=readtable('2jan.txt',opts);
Date=datetime(T.timestamp,'format','yyyy-MM-dd hh:mm:SS');
TimeOfDay=hours(hour(T.RPM))+minutes(minute(T.RPM))+seconds(second(T.RPM));
t=Date+TimeOfDay;
TT=timetable(t,T.absE_f);
TT2 = retime(TT,'hourly','mean');
final(:,1)=datenum(TT2.t(:,1));
final(:,2)= TT2.Var1(:,1);

[jonas]

Please don't flag posts as it alerts the mods!

[pruth]

oh fine. unflagged :)