how to expand the date matrix?

2 次查看（过去 30 天）
roudan
roudan 2021-7-7
评论： roudan 2021-7-28
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

请先登录，再回答此问题。

回答（2 个）

Peter Perkins
Peter Perkins 2021-7-28
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 个评论
roudan
roudan 2021-7-28
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

请先登录，再进行评论。

roudan
roudan 2021-7-7
编辑：roudan 2021-7-7
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 个评论
roudan
roudan 2021-7-7
the for loop take a long time, how can i convert it to broadcasting to make it faster? Thanks

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

产品


版本

R2020a

Community Treasure Hunt

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

Start Hunting!

Translated by