how to expand the date matrix?

2 views (last 30 days)
roudan
roudan on 7 Jul 2021
Commented: roudan on 28 Jul 2021
Hi
I need your help.
I have a csv file with date minimum and date maximum in the last 2 columns. I'd like to expand the date column such that each column is one date. For example, below first is my data, column 3-4 is date min ( Aug 1 2019) and date max (Sept 1 2019). From this matrix, I'd like to expand the matrix to the 2nd figure with 30 columns ( from Aug 1 2019-Aug 2 2019 to Sept 1 2019). How to do it? Thanks

Answers (2)

Peter Perkins
Peter Perkins on 28 Jul 2021
roudan, this question doesn't seem to make any sense. For one thing, it doesn't seem useful that you have a column whose header is 8/1/2019, and all of whose values are 8/1/2019. Same for all the other columns. For another, you say Aug 1 to Sep 1, but you example doesn't even show that.
You are also showing only Excel. If you are asking about how to do this in Excel, you are in the wrong place.
In any case, I suggest you use readtable to import your spreadsheet into a table. Your spreadsheet has two header rows, I have no idea what will actually happen but you will have to sort that out yourself. At that point, you have a table that looks somethign like this:
>> t = table([1;2;3],[4;5;6],datetime(2019,8,[1;1;1]),datetime(2019,9,[1;1;1]))
t =
3×4 table
Var1 Var2 Var3 Var4
____ ____ ___________ ___________
1 4 01-Aug-2019 01-Sep-2019
2 5 01-Aug-2019 01-Sep-2019
3 6 01-Aug-2019 01-Sep-2019
Then do something like this:
>> Dates = t.Var3 + caldays(0:5)
Dates =
3×6 datetime array
01-Aug-2019 02-Aug-2019 03-Aug-2019 04-Aug-2019 05-Aug-2019 06-Aug-2019
01-Aug-2019 02-Aug-2019 03-Aug-2019 04-Aug-2019 05-Aug-2019 06-Aug-2019
01-Aug-2019 02-Aug-2019 03-Aug-2019 04-Aug-2019 05-Aug-2019 06-Aug-2019
>> t = [t(:,1:2) array2table(Dates)]
t =
3×8 table
Var1 Var2 Dates1 Dates2 Dates3 Dates4 Dates5 Dates6
____ ____ ___________ ___________ ___________ ___________ ___________ ___________
1 4 01-Aug-2019 02-Aug-2019 03-Aug-2019 04-Aug-2019 05-Aug-2019 06-Aug-2019
2 5 01-Aug-2019 02-Aug-2019 03-Aug-2019 04-Aug-2019 05-Aug-2019 06-Aug-2019
3 6 01-Aug-2019 02-Aug-2019 03-Aug-2019 04-Aug-2019 05-Aug-2019 06-Aug-2019
  1 Comment
roudan
roudan on 28 Jul 2021
Thanks Peter, yes header is the date, and data in the column is equal to header. That is what I design it for. I am not using excel to do. I am doing it in Matlab.. I like your code to use caldays() which is new to me. Thanks. for your method, how to add header to be date as well? Thanks

Sign in to comment.


roudan
roudan on 7 Jul 2021
Edited: roudan on 7 Jul 2021
ok,
Here is what I come up with, not efficient. could you please review it and appreciate any suggestion for improvement. Thanks
A=rawdata(3:,4:end);
numdays=datenum(A(3,end),'mm/dd/yyyy')-datenum(A(3,1),'mm/dd/yyyy')+1;
[row,column]=size(A);
%create an empty array with known size
B=zeros(row,numdays);
%fill the array with date based on min and max
for col=1:numdays % loop over columns
B(:,col)=datenum(A(:,1),'mm/dd/yyyy')+col-1;
end
  1 Comment
roudan
roudan on 7 Jul 2021
the for loop take a long time, how can i convert it to broadcasting to make it faster? Thanks

Sign in to comment.

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!

Translated by