I need help converting a large data set with daily temperature values (including NaN values) to monthly values

Using the daily temperature data which I am obtaining from a canadian website with temperature data from 1977-2016, how can I extract the mean monthly temperature of each month from 1977-2016, the extreme minimum monthly temperature( so the coldest temperature of the month for every month between 1977-2016) and the same for the extreme max temperature (so the hottest temperature of the month for each month in the range of 1977-2016). Note there are some data missing which is replaced with NaN values. So the nanmean,nanmin,nanmax functions will need to be used I believe. Here is a screenshot of a portion of the the data I am using. So basically my main problem is converting the daily temperature data into monthly data based on what Im given. If anyone can help and provide simple and clear example(s) it would be greatly appreciated. Thanks.

%col1=years

% col2=months

% col3=days of the week

% col4=max daily temp

% col5=blank

% col6=min daily temp

% col7=blank

% col8=mean daily temp

[Walter Roberson]

Often the easiest way is to readtable() the file, use the appropriate columns to create datetime() values, then convert the table to a timetable() object, after which you can use retime() to get the information you are looking for.

[AStar]

I dont really understand the functions youre referring to. I am a novice in matlab. If anyone can give me an example using the information I provided that would be greatly appreciated, thanks.

Answer 1

[Walter Roberson]

T = readtable('YourFile.xlsx', 'readvariablenames', false);   %true if there is a header
dt = datetime(T{:,1}, T{:,2}, T{:,3});
TT = table2timetable(T, 'rowtimes', dt);
month_mean = retime(TT(:,8), 'monthly', 'mean');
month_min = retime(TT(:,6), 'monthly', 'min');
month_max = retime(TT(:,4), 'monthly', 'max');

summary = [month_mean, month_min, month_max];

[AStar]

Thank you so much! However I dont think it works since im on a mac, it doesnt read the excel file. What else can I try?

[Walter Roberson]

Which MATLAB release are you using? readtable() has been able to read .xls and .xlsx and .csv files since R2013b.