Write missing data as NaN

Question

Danilo M 2018-7-16

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/410545-write-missing-data-as-nan

评论： dpb 2018-7-16

I have multiple rain time series with 15 minute interval, but with some missing datas, like this:

yyyy mm dd hh mm ss data
01 30 11 00 00 3.00 
01 30 11 15 00 2.00 
01 30 11 45 00 0.00 
01 30 12 00 00 0.00

And I need to add the missing datas with NaN at data row, resulting this

yyyy mm dd hh mm ss data
01 30 11 00 00 3.00 
01 30 11 15 00 2.00 
01 30 11 30 00 NaN 
01 30 11 45 00 0.00 
01 30 12 00 00 0.00

There's some way to do that in MatLab?

Tks

4 个评论
显示 2更早的评论隐藏 2更早的评论

Adam Danz 2018-7-16

Got it. Q3) What's the last element of each row ('data')?

Danilo M 2018-7-16

There's nothing specific at the end of the matrix. It's just the last instant measured by the gauge, like

2017 12 31 23 45 00 00.00

请先登录，再进行评论。

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

Answer 1

dpb 2018-7-16

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/410545-write-missing-data-as-nan#answer_328993

编辑：dpb 2018-7-16

在 MATLAB Online 中打开

OK, without timetable and retime...

dt=(datetime(data(1,1:6)):minutes(15):datetime(data(end,1:6))).';  % build the full time vector
t=table(dt,nan(size(dt)),'VariableNames',{'Time','Rain'});         % empty table
ix=ismember(t.Time,datetime(data(:,1:6)));                         % data available locations
t.Rain(ix)=data(:,end)                                             % and insert...
t =
5×2 table
          Time            Rain
  ____________________    ____
  30-Jan-2000 11:00:00      3 
  30-Jan-2000 11:15:00      2 
  30-Jan-2000 11:30:00    NaN 
  30-Jan-2000 11:45:00      0 
  30-Jan-2000 12:00:00      0 
>>

2 个评论
显示无隐藏无

Danilo M 2018-7-16

Tks a lot, dpb, it worked perfectly!

dpb 2018-7-16

U're welcome, as always, "more than one way to skin..." :)

Note one could do the same thing with the array by converting back via datevec if need be but the table is really a useful data structure as long as doesn't get too large that performance begins to lag.

请先登录，再进行评论。

Answer 2

dpb 2018-7-16

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/410545-write-missing-data-as-nan#answer_328989

在 MATLAB Online 中打开

tt=timetable(datetime(data(:,1:6)),data(:,end));
tt.Properties.VariableNames={'Data'};
tt=retime(tt,tt.Time(1):minutes(15):tt.Time(end))
tt =
5×1 timetable
          Time            Data
  ____________________    ____
  30-Jan-2000 11:00:00      3 
  30-Jan-2000 11:15:00      2 
  30-Jan-2000 11:30:00    NaN 
  30-Jan-2000 11:45:00      0 
  30-Jan-2000 12:00:00      0

2 个评论
显示无隐藏无

Danilo M 2018-7-16

Tks a lot, but I guess my matlab version does not have timetable function. I'm using R2015a and returns "Undefined function or variable 'timetable'"

dpb 2018-7-16

Bummer! About first time found it to be useful adjunct... :) Unfortunately, retime came along with it also in R2016b. Can't update, I suppose?

Something similar to the other solution is the way although probably some shorter paths to the same end are possible.

请先登录，再进行评论。

Answer 3

Adam Danz 2018-7-16

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/410545-write-missing-data-as-nan#answer_328990

编辑：Adam Danz 2018-7-16

在 MATLAB Online 中打开

Here's another method that keeps the data in matrix format but dpb's answer is quicker and more direct.

This method creates a list of all possible time stamps between two bounds given a sample rate. Then it assigns NaN data to each time stamp, finds the time stamps you've got, and fills in the data you got.

startDate = '01/30/2000 11:00:00'; 
endDate = '01/1/2001 12:00:00'; 
sampleRate = '00:15:00';  %every 15 min
% Create all possible time stamps
allTimeStamps = datetime(startDate, 'Format', 'MM/dd/yyyy HH:mm:ss') : ...
    minutes(15) : datetime(endDate,  'Format', 'MM/dd/yyyy HH:mm:ss');
% Convert to matrix of time-vectors
allTimeStamps = datevec(allTimeStamps'); 
% Create your (fake) rain data and time stamps
rainData = [allTimeStamps, randi(10, size(allTimeStamps,1),1)]; 
% Remove some random rows
idx = randi(size(rainData,1),10, 1);  %randomized row numbers to remove. 
rainData(idx, :) = [];                %now we have missing data. 
% Detect which time stamps in 'rainData' are in 'allTimeStamps'
matchIdx = ismember(allTimeStamps, rainData(:,1:end-1), 'rows'); 
% add all NaNs to final column of allTimeStamps, then fill in the data you've got. 
allTimeStamps = [allTimeStamps, nan(size(allTimeStamps,1),1)]; 
allTimeStamps(matchIdx, end) = rainData(:,end);