Acquire annual data from table

I am trying to calculate the annual cumulative freezing degree days from a dataset with air temperature observations. I came up with the below code, but the code 1) skips the final observation for each year and 2) the row index exceeds the table dimension. How can I better get the value for each year? And additionally is it also possible to get a value when the year is defined from july to june? I am using R2021b

T = readtable('RaaheNahkiainen.csv'); % skips the first row of data

T_f = -0.3; % freezing temperature

CFDD = zeros(T.Year(end) - T.Year(1) + 1,1); % annual cumulative freezing degree days to be filled

i = 2;

for j = 1:(T{end,1} - T{1,1} + 1)

while T{i,1} == T{i-1,1}

if T{i,6} < T_f

CFDD(j) = CFDD(j) + (T_f - T{i,6}) % add freezing degree days if temp is below freezing temperature

else

CFDD(j) = CFDD(j);

end

i = i + 1;

end

i = i + 1;

end

Thank you for your time!

[dpb]

Use a timetable instead and then retime with a custom function.

[Florian van der Stap]

Thank you for your answer. Unfortuntaly when I use:

T = readtimetable('RaaheNahkiainen.csv');

I get an error saying:

Unable to detect datetime or duration data in file for row times.

Answer 1

[Adam Danz]

Convert your table to a timetable,

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/836120/RaaheNahkiainen.csv');

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

head(T) % Show sample

ans = 8×7 table
    Year    m     d    Time    TimeZone    AirTemperature_degC_    GroundMinimumTemperature_degC_
    ____    __    _    ____    ________    ____________________    ______________________________

    1997    10    2     0      {'UTC'}             7.1                          NaN              
    1997    10    3     0      {'UTC'}             6.8                          NaN              
    1997    10    4     0      {'UTC'}             6.4                          NaN              
    1997    10    5     0      {'UTC'}             5.2                          NaN              
    1997    10    6     0      {'UTC'}             5.9                          NaN              
    1997    10    7     0      {'UTC'}             6.3                          NaN              
    1997    10    8     0      {'UTC'}             5.3                          NaN              
    1997    10    9     0      {'UTC'}             4.2                          NaN              

dtm = datetime(T.Year, T.m, T.d); % Create datetime vector
TT = [timetable(dtm), T(:,5:end)]; % Create timetable
head(TT) % Show sample

ans = 8×3 timetable
        dtm        TimeZone    AirTemperature_degC_    GroundMinimumTemperature_degC_
    ___________    ________    ____________________    ______________________________

    02-Oct-1997    {'UTC'}             7.1                          NaN              
    03-Oct-1997    {'UTC'}             6.8                          NaN              
    04-Oct-1997    {'UTC'}             6.4                          NaN              
    05-Oct-1997    {'UTC'}             5.2                          NaN              
    06-Oct-1997    {'UTC'}             5.9                          NaN              
    07-Oct-1997    {'UTC'}             6.3                          NaN              
    08-Oct-1997    {'UTC'}             5.3                          NaN              
    09-Oct-1997    {'UTC'}             4.2                          NaN              

Now apply retime to compute anual averages. Only pass numeric columns to retime.

isNumericColumn =  varfun(@isnumeric, TT, 'OutputFormat','uniform');
TTavg = retime(TT(:,isNumericColumn),'yearly','mean')

TTavg = 25×2 timetable
        dtm        AirTemperature_degC_    GroundMinimumTemperature_degC_
    ___________    ____________________    ______________________________

    01-Jan-1997          0.041758                       NaN              
    01-Jan-1998            2.4402                       NaN              
    01-Jan-1999            3.3825                       NaN              
    01-Jan-2000            4.4366                       NaN              
    01-Jan-2001            2.2332                       NaN              
    01-Jan-2002            4.3782                       NaN              
    01-Jan-2003            3.7912                       NaN              
    01-Jan-2004            4.8192                       NaN              
    01-Jan-2005             4.627                       NaN              
    01-Jan-2006            4.3875                       NaN              
    01-Jan-2007            4.4748                       NaN              
    01-Jan-2008            4.7219                       NaN              
    01-Jan-2009            2.9052                       NaN              
    01-Jan-2010            2.6398                       NaN              
    01-Jan-2011            4.9234                       NaN              
    01-Jan-2012            3.3216                       NaN              

[Florian van der Stap]

Thank you, I managed to get what I want with this answer!

T = readtable('RaaheNahkiainen.csv');  % skips the first row of data

T_f = -0.3; % Freezing temperature
dtm = datetime(T.Year, T.m, T.d); % Create datetime vector
T.FDD = (-0.3 - T.AirTemperature_degC_);
TT = [timetable(dtm), T(:,8)]; % Create timetable
Tnew = TT(TT.FDD>0,:)

head(TT)

isNumericColumn =  varfun(@isnumeric, Tnew, 'OutputFormat','uniform');
TTavg = retime(Tnew(:,isNumericColumn),'yearly','sum')

[Adam Danz]

The readtable() line should not skip the first row of data (responding to the first comment in your code above).

Instead of TT = [timetable(dtm), T(:,8)];

use TT = [timetable(dtm), T.FDD];

[Siddharth Bhutiya]

Adam has already answered your question but I'll add a few more things that you might find helpful. Here you used concatenation to create your timetable but another way could be to use the table2timetable function

dtm = datetime(T.Year, T.m, T.d);
TT = table2timetable(T(:,5:end),'RowTimes',dtm);

Also whenever you have timestamped data consider using timetables since they are specifically designed to make working with timestamped data easier.

You might want to take a look at the Example on working with timetables in the documentation. This demonstrates a lot of useful things you can do with timetables.

[Florian van der Stap]

Thanks for the answers, they were very helpful! I do have two followup questions. When I use T.FDD it does not work, since I get the error: All input arguments must be tables. So now I use T(:,end) or, as I renamed it: data(:,end).

Since I have to use it for multiple datasets I now defined a function to do it, but my second issue is that I like to have the data collected from jul to jun instead of jan to dec. By using calmonths I can collect the data together in sets of 12 months, but it starts from the first data point in the set which differs per dataset. For instance oktober in the file I provided. Is there anyway I can specify that it should collect from certain months to certain months?

h_RN = analysis('RaaheNahkiainen.csv', 2)

function h = analysis(A, S)

data = readtable(A);

k_i = 2.3 * 3600 *24; % thermal conductivity ice in J/day/degree

rho_ice = 910; % ice density dependent on saliniyt

l_f = 333.4 * 1000; % latent heat of fusion in J/kg

P = 1.002; % pressure

T_f = (-57.7*S + 1.71*S.^1.5 - 0.2155*S.^2 - 7.53*P)/10^3; % Freezing temperature

dtm = datetime(data.Year, data.m, data.d); % Create datetime vector

data.FDD = [T_f - data.AirTemperature_degC_]; % daily difference air temperature and freezing temperature

data2 = [timetable(dtm), data.FDD]; % Create timetable

data3 = data2(data2.FDD>0,:); % only consider days below freezing temperature

isNumericColumn = varfun(@isnumeric, data3, 'OutputFormat','uniform');

dataCFDD = retime(data3(:,isNumericColumn),'regular','sum', 'TimeStep',calmonths(12));

h = sqrt(2*k_i * max(dataCFDD{:,1} / (rho_ice * l_f))); % ice thickness

end

[Adam Danz]

> When I use T.FDD it does not work, since I get the error: All input arguments must be tables.

My bad. What I meant to suggest is instead of using numeric column indexing, use the name of the column if the name is known in advanced. For example, instead of T(:,8), use T(:,'FDD') where FDD is column 8. Table indexing using variable names is safer because the order of columns can change in a table.

Regarding your second question, it sounds like you just need to used indexing. Use month to return the month number for each datetime value and then use ismember to determine which elements of the datetime vector are within your list of months.

Example:

dtm = datetime(1999,01,01) + days(1:15:1000)'
desiredMonths = [7 8 9];
isDesiredMonth = ismember(month(dtm), desiredMonths)

Now you can use the logical vector isDeisredMonth to isolate rows of your table that are within your list of desired months.