Calculate standard deviation with different time interval
4 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Good day, everyone.
Dummy_Raw.xlsx is a spreadsheet that contains data for each minute of a 24-hour period. I would like to calculate the standard deviation of Data_C for every five minutes. I realised I should use a loop, however it's a little tricky because my data is in time format and in excel files. The end result is expected to be as in Dummy Expected.xlsx. The empty value in the excel files will be considered as null or 0. Meaning that at the end, it will be: 288×3 table (refer below).
I'm hoping the community can assist me with this because I usually do it manually and it takes a long time.
DAY TIME Data_Exp
___ _____ ______
1 00:00 NaN
1 00:05 NaN
1 00:10 NaN
1 00:15 NaN
1 00:20 NaN
...
1 17:25 0.010648
1 17:30 0.010981
1 17:35 NaN
1 17:40 0.013079
...
1 23:50 NaN
1 23:55 NaN
3 commentaires
Walter Roberson
le 15 Fév 2022
Have you tried converting to a timetable() object and using retime() ?
Réponse acceptée
Walter Roberson
le 17 Fév 2022
Here is a considerable improvement in performance:
Load the data
filename = "PRN1_365.xlsx";
opt = detectImportOptions(filename);
opt = setvartype(opt, "DAY", "double");
opt = setvartype(opt, "TIME", "duration");
opt = setvartype(opt, "TEC0", "double");
opt.SelectedVariableNames = ["DAY", "TIME", "TEC0"];
data = readtable(filename, opt);
% Create a datetime
data.TimeStamp = datetime(2014,01,01) + data.DAY-1 + data.TIME;
data.TimeStamp.Format = "MM-dd-yy HH:mm";
tic
% Convert the table to a timetable
dataTT = table2timetable(data(:,["TimeStamp", "TEC0"]),"RowTimes","TimeStamp");
toc
tic
% Retime for everyday with 5 minutes in 24 hrs
PRNTT5min = retime(dataTT(:,"TEC0"),"regular","mean","TimeStep",minutes(5));
toc
On my system, this executes in a fairly small number of seconds and does not run out of memory.
By contrast, before I added in the detectImportOptions, the code took several minutes and then MATLAB would abruptly quit -- probably ran out of memory. Because of all the empty rows of TEC0 data, MATLAB's default detection is that most columns are character.
3 commentaires
Walter Roberson
le 27 Fév 2022
Modifié(e) : Walter Roberson
le 27 Fév 2022
Ah. At the location of 'mean' in the retime() command, replace the 'mean' with a handle to a function
@(x)std(x,[],1,'omitnan')
Note that for blocks that have no data in the time span, the result will be nan. Blocks that only have nan data within the time span will also generate nan. Blocks that have exactly one finite numeric reading will generate 0 no matter what the reading is. You will only get a nonzero result if there are multiple finite entries in a time slot (and, of course, they are not all the same)
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Preprocessing Data dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!