Extract the last day of each month in daily data set
41 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
I have a matrix of daily data from 1990 to 2015. The data is daily, but I need the all column of last day for each month to make it monthly data.Please note, that the end date of each month does not correspond to the calender end date of each month. The dates are in the first column and the date format is like 01/02/1990 .I need to extract the data of last day for each month and put them in the new matrix. I need the whole column for all last day of months.
for example for the first month in data I need this variables:
28/02/1990 21.99 21.99 21.99 21.99 21.99 0
for the second month :
30/03/1990 19.73 19.73 19.73 19.73 19.73 0
for the third month:
30/04/1990 19.52 19.52 19.52 19.52 19.52 0
and so on and then put them in a new matrix. How would I do this? Data is attached as VIX.CSV.
0 Commenti
Risposta accettata
Star Strider
il 8 Mar 2022
One approach —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/918889/VIX.csv', 'VariableNamingRule','preserve')
[G,Y,M,D] = findgroups(year(T1.Var1),month(T1.Var1), day(T1.Var1));
Yu = unique(Y);
for k1 = 1:numel(Yu)
for k2 = 1:12
d = max(D((M==k2) & (Y==Yu(k1))));
if ~isempty(d)
EOM(k1,k2) = d;
end
end
end
% EOM
End_of_Month_Day = array2table(EOM, 'RowNames',compose('%d',Yu), 'VariableNames',compose('%2d',1:12))
.
4 Commenti
Più risposte (2)
Arif Hoq
il 7 Mar 2022
if your data is arranged properly it would be 10227 rows from 1990 to 2017. then try this
N=1990:2017;
for i=1:length(N)
A(i,:)=eomday(i, 1:12); % returns the last day of month from 1990 to 2017
end
A1=A'; % transpose
A2=A1(:); % convert to column vector
A3=cumsum(A2); % last day number of a month from 1990 to 2017
last_row=A3(end,:)
Output= T(A3,:) % if T is the Table name
6 Commenti
Arif Hoq
il 9 Mar 2022
Modificato: Arif Hoq
il 9 Mar 2022
data=readtable('VIX.csv','ReadVariableNames', false);
N=1990:2017;
for i=1:length(N)
A(i,:)=eomday(i, 1:12); % returns the last day of month from 1990 to 2017
end
A1=A'; % transpose
A2=A1(:); % convert to column vector
C=cell(length(N),1);
month=1:12;
for j=1:length(N)
for k=1:12
C{j,k}=strcat(string(N(j)),{'-'},string(month(k)));
end
end
out=cellstr(C)';
out2=out(:);
for m=1:length(A2)
D(m,:)=strcat(out2(m),{'-'},string(A2(m)));
end
date=data(:,1);
date2=string(table2cell(date));
simdate=datenum(D);
OPdate=datenum(date2);
[Lia out3]=ismember(simdate,OPdate);
out4=unique(out3);
out4(1)=[];
finaloutput=data(out4,:)
Steven Lord
il 8 Mar 2022
Let's look at a three month data set.
dates = (datetime(2022, 01, 01):datetime(2022, 03, 31)).';
data = randperm(numel(dates)).';
t = timetable(dates, data);
Let's make this monthly data by taking the mean of the data for each month.
t2 = retime(t, 'monthly', 'mean')
The element of data in the first row of t2 should be the mean of the first 31 elements of data. Similarly the second row of t2 should be the mean of the next 28 days, and the third row the final 31.
mean(data(1:31)) - t2{1, 'data'}
mean(data(31 + (1:28))) - t2{2, 'data'}
mean(data(31+28 + (1:31))) - t2{3, 'data'}
But if you need to manually determine the end of the month for each of the dates in t, you can dateshift them.
datesEOM = dateshift(dates, 'end', 'month');
% Show a small sample of the dates to check
sample = randperm(numel(dates), 8);
[dates(sample), datesEOM(sample)]
0 Commenti
Vedere anche
Categorie
Scopri di più su Dates and Time in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!