I have large number of precipitation daily data in a column (for 50 years) for 221 meteorological stations. Now I need to convert it to monthly sum. How can I do that? thanks.

I have large number of precipitation daily data in a column (for 50 years) for 221 meteorological stations. Now I need to convert it to monthly sum. How can I do that? thanks

Answer 1

[Chris Turnes]

Things like leap days make this a bit tricky, so here are some general steps you might consider:

1. Create a datetime array of your starting date through your ending date.
2. Iterate over each month of each year in your data.
3. Use logical indexing to pull the data from that month and sum it.

Here's a small example that you could use as a starting point:

>> startDate = datetime('08/01/2015','InputFormat','MM/dd/uuuu');
>> endDate   = datetime('09/17/2015','InputFormat','MM/dd/uuuu');
>> dt = startDate:days(1):endDate;
>> A = randn(length(dt), 221);
>> augInd = (month(dt) == 8) & (year(dt) == 2015); % get all August data
>> AugSum = sum(A(augInd,:));

[Md Shamim Shams]

Thanks Cris....Actually my data is excel column. There are 221 excel files. I like to work on them individually. In excel sheet one column contain last 50 years date (no missing date; dd/mm/yyyy format) and in other column everyday rainfall (last 50 years; no blank). I want to calculate what is the sum of monthly rainfall for every month of last 50 years in Matlab. Remember, there four types of month ending date: 30, 31, 28 and 29. Upto now I am able read the dates and rainfall value from excel file like below

filename = 'rainfalldate.xlsx';

% Extracts the data from each column of textData

[~,DateString ]= xlsread(filename,'A:A')

formatIn = 'dd/mm/yyyy';

DateVector = datevec(DateString,formatIn)

rainfall = xlsread(filename,'C:C');

what is the next step so that I can see every months of last fifty years rainfall sum? I mean suppose July/1986 rainfall sum... Any new solutions?

Answer 2

[dpb]

Probably the better way to approach this presuming a new (>=R2014) would be to convert the array to a table and use grouping variables over which to compute the desired statistics.

If you're on an earlier release and hence don't have this facility, I would make a multi-dimensional array of month,day,year,station where the day dimension is 31 to accomodate the longest month, but fill in the shorter month's values with NaN and use the nanXXX functions over the proper dimension(s) of the array to compute the desired statistics. Eases the above computational burden significantly.

It is, of course, also possible to do grouping with accumarray and friends.

ADDENDUM

It's not the most friendly data organization possible, but...

filename = 'rainfalldate.xlsx';
[data,txt]=xlsread(filename,'A:C')
[Y,M] = datevec(datenum(txt,'dd/mm/yyyy'));
[u,~,ix] = unique([Y,M],'rows');
tot = [u, accumarray(ix,data(:,2)];

You may have to fix up the references to the data columns I just used data(:,2) assuming the 'B' column is also numeric. If you're going to keep the multiple files, I'd also point you to the File Exchange submission FILEFUN: APPLY A FUNCTION TO FILES

NB: tot above will be an array containing the year, month and totals for that month in the three columns, respectively, in that it concatenates the results from unique to get the month info in direct confluence with the associated sum.

[Md Shamim Shams]

Yes, I am using Matlab 2014a version. My asking as above with little modification. Actually my data is excel column. There are 221 excel files. I like to work on them individually. In excel sheet one column contain last 50 years date (no missing date) and in other column everyday rainfall (last 50 years; no blank). I want to calculate what is the sum of monthly rainfall for every month of last 50 years in Matlab. Remember, there four types of month ending date: 30, 31, 28 and 29. Any new solutions?

Answer 3

[Ilham Hardy]

As you perhaps realize, it is important to start the question with all necessary details.

Maybe it best if you could upload one excel file example to support your explanation (especially the "Remember, four types of month ending date...")?

Several questions though:

• If I understood correctly, all 221 weather stations have identical row length (50years*12months)?
• Does every file starts with identical starting time/date and ending with identical time/date?