MATLAB Answers

Fill date gap and fill missing data wit NaN

2 views (last 30 days)
Hello, i have to fill the gaps of these type of data:
Date Temperature
30/4/19 20:10 15.75
30/4/19 20:15 15.65
30/4/19 20:20 15.45
30/4/19 20:25 15.2
30/4/19 20:30 14.95
30/4/19 20:35 14.7
30/4/19 20:40 14.45
30/4/19 20:45 14.2
11/5/2019 13:45 12.8
11/5/2019 13:50 12.9
11/5/2019 13:55 12.8
11/5/2019 14:00 12.8
11/5/2019 14:05 12.8
11/5/2019 14:10 13
i would like to transform them like this:
Date Temperature
30/4/19 20:10 15.75
30/4/19 20:15 15.65
30/4/19 20:20 15.45
30/4/19 20:25 15.2
30/4/19 20:30 14.95
30/4/19 20:35 14.7
30/4/19 20:40 14.45
30/4/19 20:45 14.2
30/4/19 20:50 NaN
30/4/19 20:55 NaN
...................
11/5/2019 13:50 12.9
11/5/2019 13:55 12.8
11/5/2019 14:00 12.8
11/5/2019 14:05 12.8
11/5/2019 14:10 13
thanks!

  1 Comment

KSSV
KSSV on 20 Aug 2020
Create datetime arrays and use setdiff to get indices which are missing and fill thos with NaNs.

Sign in to comment.

Accepted Answer

Jalaj Gambhir
Jalaj Gambhir on 24 Aug 2020
Hi,
As already pointed out,
startTime = datetime(2019,4,30,20,10,0);
endTime = datetime(2019,4,30,23,55,0);
allRange = startTime:minutes(5):endTime;
dateRangeKnown = [] % initialize the datetime objects for which temperature is known.
[~,indices] = setdiff(allRange, dateRangeKnown);
% Initialize Table appropriately.
Table(indices,2) = {NaN}; % Table contains 2nd column as temperatures.
Hope this helps!

  4 Comments

Show 1 older comment
Jalaj Gambhir
Jalaj Gambhir on 24 Aug 2020
Can you share the code you have used?
Davide Cerra
Davide Cerra on 24 Aug 2020
I'm using the import data from file option, that's the code generated:
% % Import data from text file
% Script for importing data from the following text file:
%
% filename: C:\Users\cerra\Desktop\Projects\Climate_road\Climate road data\Weather data\Hedensted_temperature_rain.csv
%
% Auto-generated by MATLAB on 24/08/2020 15:57
%% Setup the Import Options and import the data
opts = delimitedTextImportOptions("NumVariables", 4);
% Specify range and delimiter
opts.DataLines = [88452, 88452];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["VarName1", "Var2", "Var3", "Var4"];
opts.SelectedVariableNames = "VarName1";
opts.VariableTypes = ["datetime", "string", "string", "string"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["Var2", "Var3", "Var4"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["Var2", "Var3", "Var4"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, "VarName1", "InputFormat", "MM/dd/yy HH:mm");
% Import the data
temperaturerain = readtable("C:\Users\..._temperature_rain.csv", opts);
%% Clear temporary variables
clear opts
Jalaj Gambhir
Jalaj Gambhir on 24 Aug 2020
I suspect the issue is because of Input Format mismatch.

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!