How to convert time into nearest hour and to do interpolation of NaN values extracted from .csv file?

Hi,

I have a .csv file from which I extracted values of three columns. Now I want to convert time into nearest hour and interpolate NaN values of last column named 'prec'. I tried to round time using 'dateshift'. However, it is not working. I also tried to fill the missing values. But, it is returning negative values which are supposed to be something else. Can anyone help me please? Thanks

function main

fid = fopen('see.csv','r')
c = textscan(fid,'%s %s %s','delimiter',','); 
fclose(fid);
date = c{:,1};
time = c{:,2};
prec = str2double(c{:,3});
Rtime = dateshift(time,'start','hour') + minutes(00); % not working
Fprec = fillmissing(prec,'spline',10,'endvalues','nearest'); % returnig negative values which are supposed be something else

end

Answer 1

[Guillaume]

Here's how I'd import your data.

opts = detectImportOptions('see.csv');  %let matlab figure out the format of the file.
opts = opts.setvaropts(1, 'Type', 'datetime', 'InputFormat', 'uuuu/MM/dd', 'DatetimeFormat', 'dd/MM/uuuu HH:mm:ss');  %however matlab doesn't detect the first input as datetime
opts.VariableNames = {'DateTime', 'Time', 'Something'};  %give the variable names some more useful names. If the csv file had a header matlab would automatically use it to name the variables
something = readtable('see.csv', opts);  %import as table. Obviously use a different name for the output.

something.DateTime = something.DateTime + something.Time;  %merge date and time into one datetime
something.Time = [];  %and get rid of the time column now that it is part of the date

something.DateTime = dateshift(something.DateTime, 'start', 'hour', 'nearest');  %shift to the nearest hour
something.Something = fillmissing(something.Something, 'linear');

With regards to the fillmissing, considering your numbers are all near 0, it's no wonder you get negative numbers when using cubic interpolation. If you don't want that, use a different fill method.

However, are you sure that you really want to round the time to the nearest hour without affecting the values in the 3rd column? Instead you may want to convert the table to a timetable (at the step before the dateshift, then retime the timetable to hourly:

%.. same code as before up to:
something.Time = [];

something = table2timetable(something);
something = retime(something, 'hourly', 'spline');  %or some other interpolation method

[Preyanka Dey]

Hi Guillaume,

Thanks for the answer. Yes, I need to round the time to the nearest hour without affecting the values in the 3rd column. I followed your instruction and did it. I used a different file (2015_NOAA.csv) which I have attached herein. However, at the final step I need to write my files in a text file with a single space between columns. I have written a function to write it. But, it is showing some bugs. Would you please suggest me how to fix it? Thanks.

function main

fid = fopen('2015_NOAA.csv','r')
fgetl(fid);
c = textscan(fid,'%s %s','delimiter',','); % 2 columns are extracted by 2 %s
fclose(fid);
dt = [c{:,1}];
out=datetime(dt,'InputFormat','yyyy-MM-dd''T''HH:mm:ss');

prec = [c{:,2}];

out2 = dateshift(out, 'start', 'hour', 'nearest');
tab3 = table(out2, prec);

d = sortrows(tab3, [1 2]);
[~,iprec] = unique(d(:,1),'last');
d1 = d(iprec,:);
c1 = [d1.out2];
c2 = [d1.prec];

write_output_file('my_output2.txt', c1, c2);

end

function write_output_file(fname, c1, c2)

fid = fopen(fname, 'w');

fprintf('%s %s\n',[c1 c2]');
fprintf(fid,'%s %s\n',[c1 c2]');    

fclose(fid);

end

[Guillaume]

Again, you're not using the tools that matlab give you to make these tasks much easier. You can use readtable as I've shown to read the file directly as required. Even better in this case, use readtimetable:

opts = detectImportOptions('2015_NOAA.csv', 'Delimiter', ',');  %Matlab autodectect as much of the format as possible. It still doesn't detect the time right
opts = opts.setvaropts('DATE', 'Type', 'datetime', 'InputFormat', 'uuuu-MM-dd''T''HH:mm:ss'); %so override the decoding of that
precipitation = readtimetable('2015_NOAA.csv', opts);  %read the file directly as timetable

This gives you an easy to use timetable with your file header used to name the variables of the time table. You then have many lines of code to basically chose the largest precipitation value of each hour (this is what your sortrow and unique(.., 'last') end up doing). Again, there's a much simpler way to do that with retime:

hourlyprecipitation = retime(precipitation, 'hourly', 'max');  %take the maximum precipitation of each hour

And writing to another text file with a space delimited is just as easy with writetimetable:

writetimetable(hourlyprecipitation, 'my_output2.txt', 'Delimiter', ' ', 'WriteVariableNames', false);

[Preyanka Dey]

Thanks Guillaume. The second line is not working. Howerver, I created a timetable and your mentioned 'writetimetable' (last line) also worked. Actually, it has been only two months that I am using MATLAB. That's why I am not familiar with most of the tools. Anyway, again thank you very much for your help and patience.

[Guillaume]

What error message do you get (please give the full message).

Also which version of matlab?

I tested the code with your attached file and get no error.