How can I manipulate a matrix based on its elements?

So I have a matrix of time values in the form of date/time values from Excel. I subtracted the first element of the vector of dates from each element, which yields elements for the year's worth of dates going from 0 to 365. These numbers are broken into 5 minute time intervals, i.e.,

41709.416666667
41709.420138889
41709.423611111
41709.427083333
41709.430555556
41709.434027778
41709.437500000
41709.440972222
41709.444444444
41709.447916667
41709.451388889
41709.454861111
41709.458333333
41709.461805556
41709.465277778
41709.468750000
41709.472222222
41709.475694444
41709.479166667

And so on. I have a corresponding value associated with each date, an amount. I want to sum up the amounts in the corresponding vector of values for each day, so get 365 sums. I want to be able to sum up those values based on the integer increasing in the date matrix by one, because each day does not necessarily have the same number of measurements. Thus, in the end I want a matrix of 365 dates with 365 sums consisting of the result of adding up the precise number of measurements, taken approximately every 5 minutes each day.

[Vivek]

Happy to try to answer your question - but I do not understand exactly the format your data is in. Are you saying that you have 365 rows worth of data with a different number of observations for each row, and you want to obtain the sum for each row?

[Arianna K]

I have thousands of rows of data, but multiple measurements from each day, so only 1 to 365, just with about 200 measurements for each day. I want the sum for each day. Measurements are taken about every 5 minutes and I want the total sum of these values. Thank you so much!

Answer 1

[Matt J]

Sounds like it would be

dailySums = accumarray(Day(:),Val(:))

where Day(i) = 1 to 365 depending on which day the i-th data point is acquired. Likewise, val(i) is the value of the i-th data point.

[Arianna K]

I am confused about this: it seems like a great concept, but how would I go about using this ? Would I replace 'Day' with the vector I created? And if it has integers from 1 to 365 in it, does this automatically pull those out, and the same numbered element from the 'Val' vector, which would be my collected data? Thanks!!

[Matt J]

Would I replace 'Day' with the vector I created?

You must derive the Day vector from your dates by converting them to the days of the year that each of your Val(i) are recorded. In other words, if you record a particular value Val(i) on Jan. 1, you must correspondingly set Day(i)=1. If you record value Val(j) on December 31, you must set Day(j)=365.

Example:

>> yourDates=datetime({'01/01/2014';,'12/31/2014'},'InputFormat','MM/dd/yyyy')

yourDates = 

     01-Jan-2014
     31-Dec-2014

>> Day=day(yourDates,'dayofyear')

Day =

       1
     365

And if it has integers from 1 to 365 in it, does this automatically pull those out, and the same numbered element from the 'Val' vector, which would be my collected data?

Yes. And then it sums together all Val(i) whose D(i) match each other. As an example, with

Day =

     1     1     2     2     2     3

Val =

    10    20    30    40    50    60

you get

>> accumarray(Day(:),Val(:)).'

ans =

      30   120    60

[Arianna K]

I am trying to use this method, and when trying to import my dates from the Excel file, I am getting this error.

Error using datetime (line 602)
Unable to parse date/time string 'B2:B84265' using
the format 'MM/dd/yy'.

Error in STREAMWETLAND (line 32)
Dates=datetime(Range2,'InputFormat','MM/dd/yy')

If I leave my dates from Excel in MM/dd/yy form, do I need to do this? Sorry, I need to spend some time looking at this, it is kind of confusing how to fit my data to the pattern. Thanks!

[Matt J]

datetime() expects dates as input. It looks like you skipped the step of importing your dates into MATLAB from Excel. You passed the Excel cell range 'B2:B84265' to datetime() directly.

[Arianna K]

No I did import the data but then it says that the string of data I selected, which is in the format MM/dd/yy, "cannot be parsed" into this format.

[Matt J]

Well, without seeing the contents of your "Range2" variable, there's not much I can do but guess. However, note that I can reproduce your error message exactly with the following simple statement.

>> yourDates=datetime('B2:B84265','InputFormat','MM/dd/yyyy')

Error using datetime (line 602)
Unable to parse date/time string 'B2:B84265' using the format 'MM/dd/yyyy'.

This of course leads me to think that Range2 doesn't consist entirely of date strings as you think it does, and in fact contains the particular non-date 'B2:B84265' somewhere within it. I would search Range2 for this string and if you find it there, question how it got there.

[Arianna K]

Okay so I think I fixed that problem because I had to use a dates vector I had previously created rather than the range itself because it was from a particular sheet. But now it is telling me this:

Error using datetime (line 556)
Numeric input data must be a matrix with three or six
columns, or else three or six separate numeric
arrays. You can also create datetimes from a single
numeric array using the 'ConvertFrom' parameter.

Error in STREAMWETLAND (line 32)
Dates=datetime(dates,'InputFormat','MM/dd/yy')

Thank you so much for being so helpful, sorry I am being so difficult; I am usually better at this!

[Matt J]

Well, again, there is only one place in STREAMWETLAND (line 32) that the problem could be coming from. You haven't given 'dates' in a format that datetime() expects,

http://www.mathworks.com/help/matlab/ref/datetime.html

[Arianna K]

Thank you so much for all your help! I had a few more issues, but I have gotten them all hammered out now, and I have an accurate output document with the daily sums for my data!