How to convert time into nearest hour and to do interpolation of NaN values extracted from .csv file?
13 次查看(过去 30 天)
显示 更早的评论
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
0 个评论
采纳的回答
Guillaume
2020-3-13
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
4 个评论
Guillaume
2020-3-17
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.
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Timetables 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!