05/01/2012 17:30:00.000001 is missing
05/01/2012 17:30:00.000002 is missing
05/01/2012 17:30:00.00000201 is missing...
Your question is not well-defined.
Finding out the missing dates and times from the time series data
65 views (last 30 days)
Show older comments
Hi to All
Please how to find the missing data (v) linke it with a time column or Julian time
Julian time time v
2455932.229 05/01/2012 17:30 2.095
2455932.271 05/01/2012 18:30 2.096
2455932.313 05/01/2012 19:30 2.098
The example is in the data file.
my appreciation to all.
Accepted Answer
Star Strider
on 26 Dec 2024 at 17:06
Edited: Star Strider
on 26 Dec 2024 at 17:15
I am not certain what result you want.
T1 = readtable('data.txt', VariableNamingRule='preserve')
% DTJ = datetime(T1.Jday,ConvertFrom='juliandate') % Information
TT1 = table2timetable(T1);
newt = T1.Datetime(1) : hours(1) : T1.Datetime(end);
TT1r = retime(TT1, newt,'linear')
MissingDatesTimes = setdiff(newt(:), T1.Datetime); % Miissing Dates & Times
disp(MissingDatesTimes)
figure
tiledlayout(2,1)
nexttile
plot(T1.Datetime, T1.('WL (m)'), '.-b')
title('Original')
grid
nexttile
plot(TT1r.Datetime, TT1r.('WL (m)'), '.-r')
title('Interpolated')
grid
The ‘Jday’ times do not precisely match the ‘Datetime’ times, however they are close. I went with the ‘Datetime’ time here.
EDIT — (26 Dec 2024 at 17:15)
Added ‘MissingDatesTimes’ and corrected typographical errors.
.
2 Comments
Star Strider
on 27 Dec 2024 at 12:06
As always, my pleasure!
The setdiff function has a second output that returns the indices of the missing rows (in this instance). Use it to return the rows of the ‘TT1r’ table that corresponds to the missing (interpolated) data—
T1 = readtable('data.txt', VariableNamingRule='preserve')
% DTJ = datetime(T1.Jday,ConvertFrom='juliandate') % Information
TT1 = table2timetable(T1);
newt = T1.Datetime(1) : hours(1) : T1.Datetime(end);
TT1r = retime(TT1, newt,'linear')
[MissingDatesTimes,ia] = setdiff(newt(:), T1.Datetime); % Miissing Dates & Times With Index
MissingDatesTimesWL = TT1r(ia,:);
disp(MissingDatesTimesWL)
figure
tiledlayout(3,1)
nexttile
plot(T1.Datetime, T1.('WL (m)'), '.-b')
title('Original')
grid
ylim('padded')
nexttile
plot(TT1r.Datetime, TT1r.('WL (m)'), '.-r')
title('Original With Interpolated')
grid
ylim('padded')
Ax2 = gca;
nexttile
plot(MissingDatesTimesWL.Datetime, MissingDatesTimesWL.('WL (m)'), '.-g')
title('Only Interpolated')
grid
Ax3 = gca;
Ax3.XLim = Ax2.XLim;
Ax3.YLim = Ax2.YLim;
The ‘missing’ (interpolated) dates, timies, and ‘Water Level’ (?) are now in the ‘MissingDatesTimesWL’ table. Note that I used the ‘linear’ interpolation method. Others are available, see the retime documentatiion I linked to above for details.
.
More Answers (2)
dpb
on 26 Dec 2024 at 16:44
Moved: dpb
on 26 Dec 2024 at 16:44
tD=readtable('data.txt'); % read the file
head(tD,15) % see what it contains
tD.Properties.VariableNames(2:end)={'Date','WL'}; % make convenient variable name
nnz(~isfinite(tD.WL)) % how many are missing?
None by that definition of missing...so what is the Q? about? Maybe the times aren't uniform???
histogram(diff(tD.Date))
Ah, there are some big gaps it appears...
ix=find(diff(tD.Date)>hours(1))
for i=1:numel(ix)
ix1=ix(i)-1; ix2=ix(i)+1; % the indice before/after the breaks
tD(ix1:ix2,:) % show the range
diff(tD.Date(ix(i):ix2)) % and the difference found
end
So, the Q? is, now what do you want to do with these? (But the above answers the Q? asked, it appears)
Shantanu Dixit
on 26 Dec 2024 at 16:57
Edited: Shantanu Dixit
on 26 Dec 2024 at 17:11
Hi Nada,
To identify and interpolate missing data in your time series (the current shared data seem to have no missing values), you can use the 'isnan' function to find the missing values and then use 'interp1' for interpolation. Since your data is periodic, you can use the 'spline' interpolation method.
You can refer to the following approach in MATLAB:
% Indices of missing values in v
missingIdx = find(isnan(v));
% Interpolate the missing values in v based on time
% time to be in datetime format
vInterp = interp1(time(~isnan(v)), v(~isnan(v)), time, 'spline');
For more information you can refer to the following MathWorks documentation for interpolation:
interpolation of Dates and Times: https://www.mathworks.com/help/matlab/ref/double.interp1.html#bvkp4l9
Hope this helps!
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom(English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)