I have time series data in excel. How do I divide it to a certain number of intervals?

2 views (last 30 days)
I have a time series data which spans to 22000 years before past. It looks like:
So, there are 22041 rows (Starts from -21.999418 and ends at 0.039583). I need to divide the data into 100 year intervals. Then calculate the 'SST' average of each interval. After this i need to subtract the average with the data from which it was calculated and generate another column of it.
Let me explain it with an example:
For the set ( -21.999418 to -21.900418), say the average of SST is 295.5678, then i need another column next to SST values, for the first row it is (295.81604-295.5678). This goes on for all 22041 rows.
I will attach the excel sheet here for reference.
Please tell me how do I write a code for this in matlab.
If such question is already answered please guide me to it.
Thank you.

Accepted Answer

Sanyam
Sanyam on 3 Jul 2022
Hey Rohit!
First step would be to import the excel data in matlab environment. Go to the home button in your editor and click on import. This will open a new window where you can set the options for importing your data
Below is the snippet of code attached to help you perform your task.
Hope that helps! Thanks!
data = TRACEninoorg;
startIdx = 1
interval = 100 % set this as the number of years you want
newCol = [] % temporarily created array to store the (val - average) for an interval
while startIdx <= size(data, 1) % Keep looping till you have covered all datapoints
if startIdx+interval-1 <= size(data, 1)
sst_temp = table2array(data(startIdx:startIdx+interval-1, "SST")); % take values from sst column, number of values = interval
else
sst_temp = table2array(data(startIdx:end, "SST")); % If it does not contain sufficient values then take what's left
end
avg = mean(sst_temp); % calculate the mean of elements in the array
avg_mat = repmat(avg, size(sst_temp,1), 1);
sub = sst_temp - avg_mat;
newCol = cat(1, newCol, sub); % concatenate the newly calculated array to the newCol
startIdx = startIdx+interval; % Update start index
end
addvars(data, newCol) % add the newCol to the original table
  1 Comment
Rohit V
Rohit V on 3 Jul 2022
WOW!!
Thanks lot Sanyam. The code works like magic. You have really simplified my work a lot.
I owe you this work.
Once again thanks a lot.

Sign in to comment.

More Answers (0)

Products


Release

R2022a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!