Need help writing code to attach date (1-365) from 30 years of data to the actual data needed to be processed. Trying to group into months.

Hi! So I have a 3 column matrix that is around 800,000 rows in length. The data is attached to the numerical day of the year 1-365 (over a period of around 30 years worth of data, hence the rows). I am trying to write a code that will allow me to group these dates (1-365) to the data that is attached. There is not a specific occurance of dates as some is 15 minute data and some is hourly. It would be fantastic if someone would be able to help me. It would need to group the data by 1-365 and then pull dates January for column 1 where the number is 1-31 ,February 32-59, ect. The end goal is to be able to take monthly averages of this data set that has been provided to me. Please let me know if you need more information.

[Lindsey Nixon]

I am hoping there is a way to attach a "time" by use of the first column to then group these into monthly values based on what number is located in the first column.

Answer 1

[Sindar]

edit: final code for this case:

"What I am working with is a 842,382x3 matrix where the first column is a repetition of numbers 1-365 (year 1), 1-365 (year 2), counting up through the years every time 1-365 and then starting again. The columns 2 and 3 are holding data that is directly related to the day of the year.

I am looking to find a way to put the occurance of 1-365 the first time to be (1992) and then the second time it runs through 1-365 be (1993)."

data = my_matrix;
rows = size(my_matrix,1);
% set the date of the first entry
start_year = 1992;
start_month = 1;
start_day = 1;
% find rows where a year ends (where the day # is larger than the next row)
% also, add the last row
idx = [find(diff(data(:,1))<0) ; rows];
% compute the year for each row
% basically, if the first year ends on row-9, then make 9 copies of year=1
% if the next year ends on row-12, add [12-9]=3 copies of year=2, etc.
y = repelem((1:length(idx))',diff([0;idx]));
% visual check: print the day column and the determined year side-by-side
tmp_check = [data(:,1)  y]
% create a date-time array from the year and day arrays
t = datetime(start_year-1+y,start_month,start_day-1+data(:,1)');
% extract the month from the date-time
m = month(t);
% create a timetable from your data and the datetimes
TT = array2timetable(data(:,2:3),'RowTimes',t);
% group data by month and take the average
G = groupsummary(TT,'Time','month','mean');

---------------------------------------------------------------------------------

What form does the time stamp data take? Is it in an 800,000 x 1 array? Are the values just the integers 1-365, or is there extra info?

Let's say each row is a different day, with no gaps. You'd use this:

% create some example data
rows = 8e5;
data = rand(rows,3);
% set the date of the first entry
start_year = 1970;
start_month = 1;
start_day = 1;
% create a date-time array counting up from the first day
t = datetime(start_year,start_month,start_day-1+(1:rows));
% create a timetable from your data and the datetimes
TT = array2timetable(data,'RowTimes',t);
% group data by month and take the average by month-year (e.g., Jan 2001, Feb 2001, ..., Jan 2002)
G = groupsummary(TT,'Time','month','mean');
% or, take the average by month name (e.g., Jan, Feb, Mar, ... , Dec)
G = groupsummary(TT,'Time','monthname','mean');

If parts of this aren't quite what you need, ask here and/or check the documentation for datetime (esp. if you have an array of date data), timetables, and groupsummary

[Lindsey Nixon]

Hello,

It would appear that I did not successfully ask my question, and I apologize for that.

What I am working with is a 842,382x3 matrix where the first column is a repetition of numbers 1-365 (year 1), 1-365 (year 2), couting up through the years every time 1-365 and then starting again. The columns 2 and 3 are holding data that is directly related to the day of the year.

I am looking to find a way to put the occurance of 1-365 the first time to be (1992) and then the second time it runs through 1-365 be (1993).

After that has been completed I would like to be able to go through those years and depending on the number in the first column be able to attach it to a month as 1-31 would be January, 32-59 February, etc.

The issue I am having is there is no set amount of times the number 1 occurs for the first day of january. It could appear like [1 1.24 3.45

1 2.35 3.21

1 3.45 0.23

2 1.23 0.32

3 1.23 0.21

3 1.23 0.34]

However, that example is incredibly smaller as the data was collected every 15 minutes for 365 days.

I am sorry for creating a difficult question and possibly not understanding your response. I am a new student to matlab and am trying to pick up these skills quickly.

I appreciate your time and will look further into the datetime function, but I hope this additional information makes the question a little bit clearer.

Thanks again.

[Sindar]

I suspected it might be that. I think this'll work, but you should check. (diff subtracts adjacent elements)

% create some example data
rows = 10;
% this time, the first column is random integers 1-365
data = [randi(365,[rows 1])  rand(rows,2)];
% set the date of the first entry
start_year = 1992;
start_month = 1;
start_day = 1;
% find rows where a year ends (where the day # is larger than the next row)
% also, add the last row
idx = [find(diff(data(:,1))<0) ; rows];
% compute the year for each row
% basically, if the first year ends on row-9, then make 9 copies of year=1
% if the next year ends on row-12, add [12-9]=3 copies of year=2, etc.
y = repelem((1:length(idx)),diff([0;idx]));
% visual check: print the day column and the determined year side-by-side
[data(:,1)  y']

% create a date-time array counting up from the first day
t = datetime(start_year-1+y,start_month,start_day-1+(1:rows));
% extract the month from the date-time
m = month(t);
% create a timetable from your data and the datetimes
TT = array2timetable(data,'RowTimes',t);
% group data by month and take the average
G = groupsummary(TT,'Time','month','mean');

[Sindar]

Also, if you haven't done the Matlab Onramp, I highly recommend it

[Lindsey Nixon]

Thank you for your assistance and suggestion. I will defintely start Matlab Onramp to improve my skills!

I will let you know if this ends up giving me any trouble or if I need any additional guidance.

I appreciate your help!

[Lindsey Nixon]

also - if i were using my data rather than random numbers, do I just adjust the rows=800000 and then data=my_matrix <- my matrix variable uploaded into matlab.

I have actual numbers rather than random numbes - trying to adjust to fit the data.

I also have used the squeeze function before - is this necessary to make it a single rather than a double matrix?

[Sindar]

data = my_matrix;
rows = size(my_matrix,1);

squeeze is for arrays with more than two dimensions, so it shouldn't be necessary here. Basically, Matlab sees scalars and vectors as matrices (1x1, nx1, 1xn), so squeezing them doesn't do anything.

[Lindsey Nixon]

I also keep getting an error with the repelem line

Error using repelem

In repelem(v, N), N must be a scalar or a vector of length equal to v.

any suggestions? could this be because it is a double matrix?

[Sindar]

What exactly do you mean by a double matrix?

But, you can check by adding these lines:

size(idx)
size(diff([0;idx]))
y = repelem((1:length(idx)),diff([0;idx]));

and looking at the output. (they should be the same)

[Lindsey Nixon]

I have a my_matrix = 823438x3 double.

Apparently my index exceeds bounds as it is a 3 column matrix.

The end lines of the code are working - however due to the reoccuring numbers of 1 1 1 1 1 1 2 2 2 2 2 etc in the first column it is recording these as individual days and counting into the year 4000. I am sure the middle portion of the code is supposed to correct that, but it is not.

If this ends up not working I might have to shift my plan to a different route.

Thank you for helping.

[Sindar]

Oh, I missed fully updating this line:

t = datetime(start_year-1+y,start_month,start_day-1+(1:rows));

to work with the new system:

t = datetime(start_year-1+y,start_month,start_day-1+data(:,1));

What line is throwing the "index exceeds bounds" error?

[Sindar]

Here's the full code:

data = my_matrix;
rows = size(my_matrix,1);
% set the date of the first entry
start_year = 1992;
start_month = 1;
start_day = 1;
% find rows where a year ends (where the day # is larger than the next row)
% also, add the last row
idx = [find(diff(data(:,1))<0) ; rows];
% compute the year for each row
% basically, if the first year ends on row-9, then make 9 copies of year=1
% if the next year ends on row-12, add [12-9]=3 copies of year=2, etc.
y = repelem((1:length(idx)),diff([0;idx]));
% visual check: print the day column and the determined year side-by-side
tmp_check = [data(:,1)  y']
% create a date-time array from the year and day arrays
t = datetime(start_year-1+y,start_month,start_day-1+data(:,1)');
% extract the month from the date-time
m = month(t);
% create a timetable from your data and the datetimes
TT = array2timetable(data(:,2:3),'RowTimes',t);
% group data by month and take the average
G = groupsummary(TT,'Time','month','mean');

[Lindsey Nixon]

I actually realized the index bounds was a typo on my part - my bad!

[Lindsey Nixon]

the updated t = datetime line does give back an error of inputs being the same size

[Sindar]

Ah, silly error. I didn't think it would matter whether y was a row or column vector. Change:

y = repelem((1:length(idx)),diff([0;idx]));
% visual check: print the day column and the determined year side-by-side
tmp_check = [data(:,1)  y']

to

y = repelem((1:length(idx))',diff([0;idx]));
% visual check: print the day column and the determined year side-by-side
tmp_check = [data(:,1)  y]

(i.e. move the apostrophe from y' to (1:length(idx))' )

[Lindsey Nixon]

Thank you so very much. It works and is running perfectly. I really appreciate your help with this! I will be able to use it for all of my files with simply minor adjustments to the start year.