Fill date gap and fill missing data wit NaN

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!

[KSSV]

Create datetime arrays and use setdiff to get indices which are missing and fill thos with NaNs.

https://www.mathworks.com/help/matlab/matlab_prog/generate-sequence-of-dates-and-time.html

Answer 1

[Jalaj Gambhir]

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!

[Davide Cerra]

Thanks! the problem now is that some dates are read as NaT "not a time"

[Jalaj Gambhir]

Can you share the code you have used?

[Davide Cerra]

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]

I suspect the issue is because of Input Format mismatch.