Extract rainfall values and the corresponding date from a time series when it satisfies certain condition

I have a daily time series data of one rain gauge station for 113 years. I want to extract those rainfall values and the corresponding dates only for those rainfalls which have a value greater than 2.5 mm and count the number of days with rainfall values greater than 2.5 mm for each year. The data is in .xlsx format and sample data is attached here.

Any help will be highly appreciated.

Answer 1

[Star Strider]

This required a few extra lines because the original date strings didn’t have uniform formats (so the regexp call).

This works:

[d,s,r] = xlsread('martin atam Rainfall.xlsx');
rain = d;                                                                       % Daily Rainfrall
dc = regexp(s, {'/|-'}, 'split');                                               % Pares Dates
dm = cellfun(@(x)sprintf('%2s/%2s/%4s\n', x{:}), dc, 'UniformOutput',false);    % Uniform Date Strings
dn = datenum(dm, 'dd/mm/yyyy');                                                 % Date Numbers
% dns = datestr(dn(1:15))                                                         % Check Conversion (Optional)
rain25idx = rain > 2.5;                                                         % Logical Index Vector
rain_dv = datevec(dn(rain25idx));                                               % Rain Day Years
[Uyr,ia,ic] = unique(rain_dv(:,1), 'stable');                                   % Create Indices (‘ic’) To Tally
hc = accumarray(ic, 1);                                                         % Count Rain Days By Year
Out = [Uyr, hc];                                                                % Years & Rain Days
Out_Check = Out(1:10,:)                                                         % Sample Output (Optional)

Out_Check =

          1901          88
          1902         106
          1903          77
          1904          67
          1905          93
          1906          92
          1907         113
          1908          89
          1909          92
          1910          86

[martin atam]

Thank you for the help. I have added few lines to extract only those rainfall values and the corresponding dates where rainfall is greater than 2.5 mm. After exporting the result in excel sheet and converting the date numbers to dd/mm/yyyy format, I am getting an absurd date (19/01/3801 which should be 20/01/1901). please see the image i've attached.

dates=datenum(rain_dv); 
out1=cat(2,rain,rain25idx); 
out1(out1(:, 2)== 0, :)= []; 
out2=cat(2,dates,out1(:,1)); 
xlswrite('C:\Users\aa\Documents\MATLAB\result.xlsx',out2);

[Star Strider]

My pleasure!

It’s difficult for me to follow what you’re doing, or how you managed to predict the rainfall so far into the future.

The ‘rain25idx’ is a logical vector, so if you want only the dates corresponding to rainfall greater than 2.5 mm, see if this does what you want:

dates = dn(rain25idx);
out1 = rain(rain25idx);
out_table = table(datestr(dates, 'dd/mm/yyyy'),out1, 'VariableNames',{'Date','Rainfall'})
writetable(out_table, 'C:\Users\aa\Documents\MATLAB\result.xlsx');

I tested all of this except the writetable call, because I don’t really need to write the file to my disk. The table looks correct. It should work.

The first 15 rows of the table are:

out_table = 

         Date       Rainfall
      __________    ________

      20/01/1901    17.4    
      12/02/1901     9.2    
      21/02/1901    11.1    
      22/02/1901       4    
      01/03/1901     2.7    
      02/03/1901     4.1    
      03/03/1901     7.3    
      19/03/1901    10.4    
      04/04/1901    10.3    
      05/04/1901    19.3    
      08/04/1901    11.3    
      15/04/1901     6.1    
      19/04/1901     2.7    
      20/04/1901    33.6    
      22/04/1901    23.4

That is how it should appear in your ‘result.xlsx’ file, including the column headings. Note that ‘Date’ is a string, and will be read as a string from your file.

If you use your data only with MATLAB, I would save ‘dates’ and ‘out1’ in a .mat file. This is easier than reading and converting the Excel file every time you want to use it.

[martin atam]

I just found out that it is because of my excel date formatting option. Both the codes work fine for me. Thank you once again.

[Star Strider]

My pleasure.

An Excel formatting problem is something I’d not considered.