extract especific rows from matrix
Show older comments
Hi, I have a two columns data. The first column includes time yyyymmddhhmm (which means Year; month; day, hour and minutes). The second column contains numerical data. The interval between time tables are 15 minutes so you see the first row is 202002041000 and the second row is 202002041015.
I have a very big matrix (3500*2) and some times some data are missed, so the pattern is not uniform. I want to write a code to extract the rows which are for every 30 minutes (the rows that ends to 30). So maybe a code which check whether the first column data ends to 30 helps.
Could you please help me with this
202002041000 25
202002041015 35
202002041030 54
202002041045 54
202002041100 23
Accepted Answer
One option —
c = [202002041000 25
202002041015 35
202002041030 54
202002041045 54
202002041100 23
202002041200 23]; % Last Row Added To Test Code
T1 = array2table(c);
T1.c1 = datetime(num2str(T1.c1),'InputFormat','yyyyMMddHHmm')
TT1 = table2timetable(T1)
newTimes = TT1.c1(1):minutes(30):TT1.c1(end);
TT2 = retime(TT1,'regular','fillwithmissing','TimeStep',minutes(30))
This fills the missing data with NaN. To interpolate the missing values instead, replace 'fillwithmissing' with 'linear' or any of the other applicable options.
It would help to have more data, especially with missing times and more detail on the desired result for the missing data, however this will work for a start.
See the documentation for the various functions to understand how the code works.
.
11 Comments
Armin Azad
on 23 Oct 2021
Armin Azad
on 23 Oct 2021
I am still not certain what the desired result is. I am reading this as wanting only the data for every hour beginning with the first time the data are collected at the 30-minute time (on the half-hour).
Also, while there do not appear to be any missing values here, are the missing data to be treated as NaN values, or interpolated?
c = {'04-Feb-2020 10:00:00' 25
'04-Feb-2020 10:30:00' 54
'04-Feb-2020 11:00:00' 23
'04-Feb-2020 11:30:00' 25
'04-Feb-2020 12:00:00' 23
'04-Feb-2020 12:15:00' 30
'04-Feb-2020 12:30:00' 45
'04-Feb-2020 12:45:00' 50
'04-Feb-2020 13:00:00' 60};
T1 = cell2table(c);
T1.c1= datetime(T1.c1) % Original Table
Iv = find(minute(T1.c1) == 30); % Data On The Half-Hour
TT1 = table2timetable(T1) % Create 'timetable'
newTimes = TT1.c1(Iv(1)):hours(1):TT1.c1(Iv(end)); % Times To Sample (Or Interppolate)
TT2 = retime(TT1,newTimes) % Selected Data
I do not understand the reason for omitting the last row (for 12:30) from the posted desired result. The code includes it here.
.
Armin Azad
on 24 Oct 2021
Does this produce the desired result?
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/776583/file.xlsx', 'VariableNamingRule','preserve')
Iv = find(minute(T1.datetime) == 30); % Data On The Half-Hour
TT1 = table2timetable(T1) % Create 'timetable'
newTimes = TT1.datetime(Iv(1)):hours(1):TT1.datetime(Iv(end)); % Times To Sample (Or Interppolate)
TT2 = retime(TT1,newTimes) % Selected Data
This produces rows for every hour on the half-hour, as I believe was the requested result.
.
Armin Azad
on 24 Oct 2021
Star Strider
on 24 Oct 2021
As always, my pleasure!
.
Armin Azad
on 24 Oct 2021
Star Strider
on 24 Oct 2021
Good moirning!
I only tested ot in R2021b, since I use that here and on my own computer offline. I no longer have access to either R2018 version, so I cannot test it with them. (The online documentation for R2018b is still available, so I am referring to it here.)
I always recommend upgrading to the most recent version, since there are a number of new features that are not present in R2018b.
The following applies to R2018b. (No changes will be required to run my code in R2021b.)
It will be necessary to omit the 'VariableNamingRule ,'preserve' name-value pair from the readtable call because that option (nor any similar option) does not exist in R2018b.
It looks like the retime call will work without modification.
To write it, use the writetable function. It may be necessary to convert the timetable back to a table with the timetable2table function first, since it may not support writing timetable arrays. It will be necessary to experiment first to be certain that step is necessary. (That also appears to be true for R2021b.)
.
Armin Azad
on 24 Oct 2021
Star Strider
on 24 Oct 2021
As always, my pleasure!
.
More Answers (0)
Categories
Find more on Workspace Variables and MAT Files in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
