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

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.

Answer 1

[Sanyam]

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

[Rohit V]

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.