Calculate standard deviation with different time interval

Question

Ann 2022-2-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1650045-calculate-standard-deviation-with-different-time-interval

评论： Ann 2022-2-27

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
    ___     _____    ______
    00:00     NaN
    00:05     NaN
    00:10     NaN
    00:15     NaN
    00:20     NaN
     ...
    17:25     0.010648
    17:30     0.010981
    17:35     NaN
    17:40     0.013079
     ...
    23:50     NaN
    23:55     NaN

3 个评论
显示 1更早的评论隐藏 1更早的评论

Walter Roberson 2022-2-15

Have you tried converting to a timetable() object and using retime() ?

Ann 2022-2-17

编辑：Ann 2022-2-17

在 MATLAB Online 中打开

Hi Dyuman, I did not make use a loop.

Hello Walter, I did use retime but it's possible that my original spreadsheet file is too large for MATLAB to run, as it stated "array exceeds maximum array size preference".

I'm jammed on making them in 288 minutes (1440 minutes divided 5 minutes) per day for 365 days.. Meaning 288 minutes (per day) x 365 days. Then I can't continue to calculate the standard deviation also.

Original Data

% Load the data
data = readtable("PRN1_365.xlsx");
% Create a datetime
data.TimeStamp = datetime(2014,01,01) + data.DAY-1 + data.TIME;
data.TimeStamp.Format = "MM-dd-yy HH:mm";
% Convert the table to a timetable
dataTT = table2timetable(data,"RowTimes","TimeStamp");
% Retime for everyday with 5 minutes in 24 hrs
PRNTT5min = retime(dataTT(:,"VTEC"),"regular","mean","TimeStep",minutes(5));

请先登录，再进行评论。

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

Answer 1

Walter Roberson 2022-2-17

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1650045-calculate-standard-deviation-with-different-time-interval#answer_898365

在 MATLAB Online 中打开

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 个评论
显示 1更早的评论隐藏 1更早的评论

Walter Roberson 2022-2-27

编辑：Walter Roberson 2022-2-27

在 MATLAB Online 中打开

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)

Ann 2022-2-27

Many thanks, Walter. It functions as expected. I also make manual calculations to confirm that everything is calculated accurately and that the answer is as expected. Thank you so much and have a great day ahead.

请先登录，再进行评论。